Free Excel templates

Purchase Price Allocation Model

Consideration and earn-out, four intangible asset valuations, the allocation with deferred taxes and goodwill, amortization and the goodwill tests.

Type the deal once and the workbook allocates the price under ASC 805: the consideration transferred with the earn-out at fair value, the customer relationships by excess earnings, the technology and the trade name by relief from royalty, the noncompetition agreement with and without, the deferred tax liability and the goodwill residual, then amortization and the goodwill tests under ASC 350. The worked case starts from a headline price of $120,000,000.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Purchase_Price_Allocation_ASC805_Model.xlsx (46 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Purchase Price Allocation Handbook, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

10 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
The deal, the earn-out scenarios, the balance sheet acquired, the projection and the valuation assumptions, in yellow cells.
Projection
Revenue, EBITDA and free cash flow for ten years, discounted at mid-year with a terminal value: the enterprise value.
Customers
Multi-period excess earnings with contributory asset charges, the useful life and the tax amortization benefit.
Technology
Relief from royalty on the revenue of the existing technology as it migrates away.
Trade name
Relief from royalty on all revenue sold under the name.
Noncompete
After-tax EBITDA with and without the agreement, weighted by the probability of competition.
Allocation
Provisional and final allocation, deferred tax liability, goodwill, the earn-out and the measurement period adjustment.
Amortization
Annual amortization of each intangible asset over its life, and the step-up depreciation.
Impairment
The 2025 and 2026 goodwill tests and the ASC 360 recoverability test.
Checks
Nine general checks and twenty figures of the book, with an overall status.

What the worked case returns

MeasureValue
Consideration transferred (final)$130,322,000
Earn-out at fair value at the acquisition date$6,672,000
Customer relationships$21,298,000
Developed technology$15,724,000
Deferred tax liability (final)$12,131,500
Goodwill (final)$65,777,500
Goodwill, percent of the consideration50.5%
Goodwill impairment, 2026 test$13,151,162

Goodwill is the residual: $130,322,000 of consideration less $64,544,500 of net identifiable assets leaves $65,777,500, or 50.5% of the price. The net assets already carry a deferred tax liability of $12,131,500 on the step-ups, and leaving it out understates goodwill by the same amount, which is the step most homemade allocations miss.

How to use it on your own case

  1. Enter the headline price, the cash and debt acquired, the working capital peg and estimate, and the tax rate.
  2. Enter the earn-out terms and the EBITDA and probability of each scenario; Allocation computes the payouts and the fair value.
  3. Enter the balance sheet acquired and the projection; the four intangible asset tabs value themselves from it.
  4. Read the provisional and final columns of Allocation: deferred tax liability, goodwill and its share of the price.
  5. Check that the Checks tab reads ALL PASS before you take any figure into a memo.

Questions people ask about it

Why does the deferred tax liability increase goodwill?

In a stock acquisition the tax bases carry over, so every step-up creates a deferred tax liability at the tax rate. It is a liability assumed, so it reduces the net identifiable assets and goodwill rises by the same amount: $12,131,500 in the case.

Is the earn-out part of the purchase price?

When it pays the sellers for the business whether or not they stay employed, yes: it is measured at fair value at the acquisition date, $6,672,000 in the case, and a cash-settled earn-out is then remeasured through earnings at each reporting date. Only a measurement period adjustment, new information about facts that existed at the acquisition date, goes to goodwill.

Does it test goodwill for impairment?

Yes. The Impairment tab runs the 2025 test, which passes, and the 2026 test after a lost customer, which compares the fair value of the reporting unit with its carrying amount and books a loss of $13,151,162, after the ASC 360 recoverability test of the long-lived assets.

Go further

The free workbooks of The Purchase Price Allocation Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Purchase Price Allocation Handbook. See the book.