Purchase Price Allocation in a Merger Model: Build the Goodwill and Asset Write-Up Schedule in Excel

Purchase price allocation (PPA) in a merger model splits the price paid for a target across the target's assets and liabilities at fair value. Whatever is left over after writing up tangible assets, recognizing new intangibles and booking the deferred tax liability is goodwill. In Excel, the PPA schedule links the deal price to the pro forma balance sheet, and it drives the new depreciation and amortization that hits accretion/dilution.

Most merger model errors I have reviewed trace back to this schedule. Goodwill gets plugged instead of calculated, the target's old goodwill is left on the books, or the write-ups never create a deferred tax liability. This guide walks through a purchase price allocation in a merger model step by step, with a worked example you can rebuild in any spreadsheet model. It then checks the method against two public deals: Broadcom/VMware and Microsoft/Activision Blizzard.

What Purchase Price Allocation Does in a Merger Model

Under US GAAP, ASC 805 requires the acquisition method for business combinations. The acquirer is identified, an acquisition date is set, and identifiable assets acquired and liabilities assumed are recognized at fair value on that date. Deloitte's roadmap on business combinations sets out these steps from ASC 805-10-05-4, and BDO's ASC 805 guide covers the same framework for practitioners.

For a model builder, that comes down to four jobs:

Calculate how far the equity purchase price exceeds the target's book value.
Assign part of that excess to specific asset write-ups (identified intangibles, PP&E, inventory) at estimated fair value.
Book a deferred tax liability (DTL) on those write-ups when the deal is a stock purchase with no tax basis step-up.
Put the remainder into goodwill. Goodwill is not amortized under US GAAP for public companies. It is tested for impairment.

The write-ups matter more than the goodwill number for earnings. Identified intangibles are amortized and stepped-up PP&E is depreciated, so every dollar you move out of goodwill and into a finite-lived asset reduces pro forma EPS. If you mark write-ups too low, the deal looks more accretive than it will be once the auditors finish.

Build the PPA as a separate tab with its own inputs. Never fold it into the balance sheet tab as a plug.

Purchase Price Allocation Step by Step: The Goodwill Calculation in Excel

Here is the layout I use. Each row is one line in the spreadsheet, with inputs in blue and formulas in black.

Equity purchase price. Offer price per share times diluted shares (treasury stock method on options and RSUs). Link this to the transaction assumptions tab.
Less: target book value of equity. Taken from the latest target balance sheet.
Plus: write-off of existing target goodwill. The target's old goodwill is eliminated and replaced by new goodwill. Book equity net of existing goodwill is your starting point for net identifiable assets.
Equals: excess purchase price over adjusted book value.
Less: asset write-ups. Intangibles, PP&E and inventory step-ups, each on its own row with its own useful life.
Plus: new deferred tax liability. Total write-ups times the target's marginal tax rate (stock deal only).
Equals: new goodwill.

Worked example

Acquirer pays $1,000m for 100% of Target's equity. Target has $400m of book equity, which includes $50m of existing goodwill. Diligence and a preliminary valuation give these write-ups: $250m of identified intangibles, a $30m PP&E step-up and a $10m inventory step-up. The tax rate is 25%.

Adjusted book value: $400m less $50m existing goodwill = $350m
Excess purchase price: $1,000m less $350m = $650m
Total write-ups: $250m + $30m + $10m = $290m
New DTL: $290m x 25% = $72.5m
New goodwill: $650m less $290m plus $72.5m = $432.5m

As a check, fair value of net identifiable assets is $350m + $290m less $72.5m = $567.5m. Add $432.5m of goodwill and you get back to $1,000m. Put that reconciliation in the model as a check cell that returns zero.

If goodwill comes out negative, ASC 805 treats it as a bargain purchase, recognized as a gain after the acquirer reassesses whether it has identified every asset and liability correctly. Deloitte's roadmap covers this reassessment requirement. In a model, a negative goodwill result almost always means a broken input, so flag it with conditional formatting.

Build the reconciliation check before you build anything else. It catches about half of PPA errors on its own.

Building the Asset Write-Up Schedule

The write-up schedule turns one-time fair value adjustments into annual P&L charges. Each asset class needs three inputs: the step-up amount, the useful life and the line of the income statement it hits.

Identified intangibles

This is usually the largest write-up in software, consumer and pharma deals. Split it by category, because lives differ a lot. Broadcom's preliminary VMware allocation in its 10-Q for the quarter ended February 4, 2024 gives a clean template:

Developed technology: $24,420m, 8-year weighted-average life, valued with the multi-period excess earnings method
Customer contracts and related relationships: $14,837m, 8 years, valued with the with-and-without method
Trade name: $1,150m, 14 years, valued with the relief-from-royalty method
Off-market component of customer contracts: $221m, 2 years
In-process R&D: $4,900m, not amortized until projects complete

Copy that structure. Give each category its own row and its own life input, and keep IPR&D separate because it does not amortize while in development.

PP&E and inventory

Depreciate the PP&E step-up over the remaining life of the target's asset base, typically 5 to 10 years for an industrial target. The inventory step-up flows through cost of goods sold as that inventory sells, so in most models it hits year one in full. Treat it as a non-recurring item when you calculate adjusted EPS, and show it gross in GAAP EPS.

Annual charge in the worked example

Intangibles: $250m over 8 years = $31.25m per year
PP&E step-up: $30m over 5 years = $6.0m per year
Inventory step-up: $10m in year one only
The year one pre-tax charge is $47.25m. The tax shield at 25% is $11.8m. The after-tax drag on net income is $35.4m

Divide that $35.4m by pro forma diluted shares and you have the PPA's direct hit to EPS. On a $1,000m deal where Target earns $80m of net income, this one line wipes out more than 40% of the earnings you bought.

Run a sensitivity table of year-one EPS against intangible useful life (5, 8, 10, 15 years) and the share of excess price allocated to intangibles. Bankers get asked this question in every board meeting.

Deferred Taxes and Deal Structure in the Goodwill Schedule

In a stock purchase, the buyer takes over the target's historical tax basis. Book values go up to fair value and tax basis stays where it was, so the gap creates a DTL. That DTL increases goodwill, as the worked example shows. As the write-ups amortize, the DTL unwinds each year by the amortization times the tax rate. Model the unwind on the pro forma balance sheet so the DTL declines alongside the intangibles.

In an asset purchase, or a stock deal treated as an asset purchase for tax, tax basis steps up with book basis. No DTL is created on the write-ups, and the amortization is deductible for cash taxes. Add a toggle cell (1 = stock deal, 0 = asset deal) and multiply the DTL formula by it. That one switch lets you show a buyer how much structure is worth in cash taxes.

Real deals are messier than the toggle. Broadcom's February 2024 10-Q shows that for VMware it established $3,653m of net deferred tax liabilities along with a $2,129m valuation allowance. The DTL in a filed PPA is a net figure that reflects existing deferred tax assets, reserves and jurisdiction mix, so it will not equal write-ups times one statutory rate. Your model only needs to get close. Use the blended rate, then true it up once tax diligence comes back.

Confirm the tax structure with deal counsel before you lock the DTL row. Getting the toggle wrong moves goodwill by 20 to 25% of total write-ups.

Purchase Price Allocation Example: Broadcom/VMware and Microsoft/Activision

Public filings are the best way to check whether your allocation percentages are reasonable. Two of the largest tech deals of 2023 show the range.

Broadcom and VMware (closed November 22, 2023)

Broadcom's FY2025 10-K reports total purchase consideration of $86,290m, or $79,648m net of $6,642m of cash acquired. The final allocation recorded $54,206m of goodwill and $45,572m of identified intangibles. The February 2024 10-Q had shown goodwill of $53,933m and intangibles of $45,528m, so the numbers moved during the measurement period, which is normal. Broadcom states the goodwill is not tax-deductible.

Intangibles came to roughly 53% of net consideration, which is high. Broadcom's critical accounting estimates name the inputs it used: obsolescence rate, contributory asset charges and discount rate for developed technology, retention rate and ramp-up period for customer relationships, and royalty rate for the trade name. Those are the assumptions to request from the valuation firm when you build your own schedule.

Microsoft and Activision Blizzard (closed October 13, 2023)

Microsoft's 10-K filings put the purchase price at about $75.4 billion, consisting primarily of cash. Microsoft recorded about $51.0 billion of goodwill, assigned to the More Personal Computing segment, and about $22.0 billion of identified intangibles. Those intangibles were split into marketing-related assets of about $11.6 billion, technology-based assets of about $9.7 billion and customer-related assets of about $0.7 billion. Microsoft attributed the goodwill mainly to expected synergies and disclosed that most of it is not tax-deductible. Its FY2024 10-K described the allocation as preliminary, and the FY2025 10-K reported it complete as of September 30, 2024.

Intangibles at about 29% of price is a much smaller share than in the VMware deal. Activision's value sits in franchises, which show up as marketing-related assets, and in expected synergies, which sit in goodwill. VMware's value sat in a software stack and an enterprise contract base, so more of it could be separately identified. The lesson for your spreadsheet model is to set the intangible allocation from what the target actually owns. A flat percentage copied from the last deal will be wrong.

Pull the PPA notes from two or three closed comparable deals in your sector and use their intangible-to-price ratios as the range for your sensitivity table.

Common PPA Mistakes and the Checks That Catch Them

Leaving the target's old goodwill in place. This double-counts. Always eliminate it in the adjusted book value row.
Using enterprise value instead of equity value. Goodwill is calculated against equity purchase price and book equity. Assumed debt goes on the pro forma balance sheet at fair value, separately.
Forgetting the measurement period. ASC 805 allows the acquirer to adjust provisional amounts for up to one year. Both Broadcom and Microsoft revised their figures during that window. Label your schedule "preliminary" until the valuation is final.
Amortizing IPR&D on day one. Keep it as an indefinite-lived asset until each project completes.
Hardcoding the DTL. Link it to the write-up total and the tax rate so it updates when the valuation changes.
No balance sheet check. Pro forma assets minus pro forma liabilities and equity should be zero, every period.

Add a single "Checks" block at the top of the PPA tab that sums every check cell. If it is not zero, the model is not ready to send.

Why a Ready-Made Purchase Price Allocation Excel Template Pays Off

The PPA schedule is about 30 rows of formulas, and nearly every number in the pro forma output depends on it: goodwill, the DTL roll-forward, annual amortization, adjusted versus GAAP EPS and the balance sheet check. Building it from scratch on a live deal, at 1 a.m. before a board deck goes out, is when old goodwill gets left in and the DTL gets hardcoded.

A tested Excel template gives you the structure above already wired: separate rows for each intangible category with its own life, a stock/asset deal toggle, an IPR&D bucket, a measurement-period flag and a reconciliation check that ties back to the purchase price. You spend your time on the inputs that matter, such as the intangible split, the useful lives and the tax structure, and less on fixing circular references. ModelStack's merger model and M&A templates include a PPA and goodwill schedule built this way. Load your deal's numbers, benchmark the allocation against the Broadcom and Microsoft disclosures, and run the sensitivity tables before the first diligence call.