Free Excel templates

A free goodwill impairment test model in Excel

One reporting unit, from a five-year forecast to the discount rate, the DCF and market values, the fair value, the carrying amount, the headroom, the loss and its tax effect, the market capitalization check and a grid of scenarios.

A reporting unit carries $217,000,000. Its forecast, discounted at 10.3 percent, is worth $233,801,770; its earnings at a multiple of 8.40x are worth $226,800,000; weighted, the fair value is $231,001,062. The unit passes with a headroom of $14,001,062, or 6.5 percent, and one point on the rate is enough to turn that into a shortfall. The model lays the whole test out on one sheet, ready for your own unit.

Get the model

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

Goodwill_Impairment_Test_Model.xlsx (14 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 is a simplified, one-unit version of the companion workbook of The Goodwill Impairment Handbook, which follows a listed company with four reporting units through two years of tests: a reorganization, a qualitative assessment, an interim failure on deductible goodwill, a trade name and an asset group tested first, the note and the sensitivities.

Paperback PDF edition, $19.99

What is in the file

One sheet. Inputs are blue; everything else is a formula; a grey column shows the example's results so you can check the model before you change it; the last block counts the checks that fail.

1. Inputs
Revenue and margin of the last twelve months, growth and margins for five years, depreciation, capital expenditure and working capital, the rate inputs (risk-free rate, beta, equity risk premium, specific premium, cost of debt, structure, tax rate), terminal growth, the selected multiple and the weight of the DCF, the lines of the carrying amount, whether the goodwill is deductible, and the market data for the reconciliation.
2. Forecast and DCF
Free cash flow year by year, mid-year discounting, the terminal value and the DCF value.
3. Fair value and the test
Market value, weighted fair value, carrying amount, headroom in dollars and percent, and the loss: the excess capped at the goodwill, or the simultaneous equation when the goodwill is deductible.
4. Market capitalization and scenarios
Market capitalization, implied equity value and implied control premium, then a grid of headroom by discount rate and terminal growth, and the breakeven rate of the example.
5. Checks
Growth below the rate, weights within range, the loss never above the goodwill, the simultaneous equation closing to the fair value, no loss when the unit passes.

What the worked example returns

MeasureValue
Discount rate (cost of equity 12.05 percent, after-tax cost of debt 4.875 percent)10.3 percent
DCF value (terminal value $258,388,912)$233,801,770
Market value ($27,000,000 x 8.40x)$226,800,000
Fair value (0.6 x DCF + 0.4 x market)$231,001,062
Carrying amount$217,000,000
Headroom$14,001,062 (6.5 percent)
Market capitalization$465,600,000
Implied equity value$576,001,062
Implied control premium23.7 percent
Breakeven discount rate11.18 percent
Headroom at 11.3 percent and growth of 2.5 percent-$1,730,817

The example unit is fictional and every input is a teaching assumption, not a market norm. The model is separate from the book's case: read its figures against each other, not against the book's four units.

How to use it on your own unit

  1. Enter the forecast your valuation specialist would defend as a market participant view, not the budget as approved.
  2. Build the rate from its parts and the multiple from your guideline companies and transactions, and write the reason for each choice next to it.
  3. Enter the carrying amount on the same base as the fair value: on an enterprise premise, without debt and cash, with the unit's share of headquarters lines.
  4. Read the headroom, then the grid: if one point on the rate or half a point on growth changes the answer, that is what the audit committee needs to see.

Questions people ask

What does the goodwill impairment test model do?

It takes one reporting unit's forecast, discount rate inputs, terminal growth, multiple and carrying amount, and returns the DCF value, the market value, the fair value, the headroom in dollars and percent, the loss and its tax effect, the market capitalization reconciliation and a grid of headroom by rate and growth.

How does it handle deductible goodwill?

Set the deductible flag to 1. If the carrying amount exceeds the fair value, the loss is the excess divided by one minus the tax rate, capped at the goodwill, and the tax effect is the loss minus the excess, so that the carrying amount after both equals the fair value.

Can I use it for a real company?

It is built for a first pass and for learning the mechanics. A real test needs the company's own forecasts, a rate and multiples supported by its valuation specialist, and a carrying amount agreed with its auditors.

Is this model really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbook of The Goodwill Impairment Handbook goes further: four units at three dates, the reassignment after a reorganization, carrying amounts built line by line with headquarters keys, the trade name and the asset group, the roll-forward, the sensitivities and the private company scenario.

See the book and its free files →

Related reading