Free Excel templates

A free fair value hierarchy and Level 3 roll-forward model in Excel

List each item at fair value with its level at two year-ends: the model returns the hierarchy table of the note, items at net asset value apart, the Level 3 roll-forward and its checks.

At 31 December 2026 a listed company carries $15,420,000 of assets in Level 1, $11,808,000 in Level 2, $6,875,000 in Level 3 and $8,655,000 in a fund at net asset value: $42,758,000 on the balance sheet. During the year its earn-out left Level 3 for Level 2 at $3,946,800 and its swap turned from an asset into a liability. Getting the table and the roll-forward to agree with each other, and with the balance sheet, is where the close loses time. The model lays the mechanics out on one sheet of inputs, ready for your own items.

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.

Fair_Value_Hierarchy_and_Level_3_Model.xlsx (18 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 version of the companion workbook of The Fair Value Measurement Handbook, which follows one listed company through two year-ends: seven instruments measured and placed in the hierarchy, a pricing service challenged, a swap with credit adjustments, an earn-out, private preferred shares, a fund at net asset value, a plant written down on its highest and best use, and the fair value note written line by line.

Paperback PDF edition, $19.99

What is in the file

Five tabs. Inputs are blue on yellow cells; everything else is a formula; the Checks tab counts the failures. Fifteen input rows leave room for your own items.

1. Start here
What the model does, how to fill it, the example, and what it leaves out, with the rules as read and their dates.
2. Inputs
One row per item: name, type (asset or liability), level and fair value at each year-end (1, 2, 3 or NAV), and for Level 3 items the purchases and issues, sales and settlements, and gains or losses in other comprehensive income of the year. A swap that changed sides takes two rows.
3. Hierarchy
The table of the note for assets and liabilities, by level and by year, with items at net asset value shown apart and the reconciliation to the balance sheet.
4. Level3
The roll-forward of the current year for every item in Level 3 at either date, with gains and losses in net income as the balancing figure, transfers in and out at the end of the period, and unrealized gains for items still held.
5. Checks
Ten controls: totals by item against totals by level, the roll-forward against the table at both dates, and every item given a level and a type. A failure count that must read 0.

What the worked example returns

MeasureValue
Level 1, 31 December 2025 / 2026$13,480,000 / $15,420,000
Level 2$13,359,000 / $11,808,000
Level 3$6,150,000 / $6,875,000
Total assets in the hierarchy$32,989,000 / $34,103,000
Fund at net asset value, outside the hierarchy$6,710,000 / $8,655,000
Assets at fair value on the balance sheet$39,699,000 / $42,758,000
Liabilities: Level 3 in 2025, Level 2 in 2026$4,806,360 / $4,775,800
Level 3 roll-forward 2026, private shares: opening, gain, closing$6,150,000 / $725,000 / $6,875,000
Level 3 roll-forward 2026, earn-out: opening, gain, transfer out$4,806,360 / $859,560 / $3,946,800
Unrealized gains for Level 3 items still held$725,000

The example is the book's fictional case, and every figure is a teaching assumption, not a market figure. Every check of the example reads PASS.

How to use it on your own items

  1. Decide the level of each measurement first, input by input: the model records your conclusion, it does not make it.
  2. Enter each recurring item with its type, its level and its fair value at the two year-ends; enter liabilities as positive balances.
  3. For each Level 3 item, enter the purchases and issues, sales and settlements, and gains or losses in other comprehensive income of the year.
  4. Read the Checks tab before any figure: a count above zero means a cell to fix.

Rules described as read in November 2026 (FASB ASC Topic 820). Education only, not accounting or audit advice: your entity's facts govern, and its accountants and auditors decide.

Questions people ask

What does the model do?

You list each item measured at fair value on a recurring basis, its type (asset or liability), its level at each of two year-ends and its fair value at each. The model returns the hierarchy table of the fair value note by level, with items at net asset value shown apart, the Level 3 roll-forward of the current year with gains and losses in net income as the balancing figure, and checks that reach each total by two routes.

Which rules does it apply?

As read in November 2026: the three levels of ASC 820, the presentation of investments measured with the net asset value practical expedient outside the hierarchy, and the Level 3 reconciliation of recurring measurements (opening, purchases and issues, sales and settlements, gains and losses in net income and in other comprehensive income, transfers in and out, closing, and unrealized gains for items still held). Transfers are recognized at the end of the period.

What does it leave out?

The measurements themselves (prices, models, credit adjustments, highest and best use), the judgment of which level an input belongs to, nonrecurring measurements, the table of significant unobservable inputs, the disclosures specific to items at net asset value, and IFRS 13 differences. The book's full workbook covers them on one case.

Can I rely on it for a filing?

No. It is built to organize a first pass and to learn the mechanics. The levels and the figures belong to your entity, its accountants and 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 Fair Value Measurement Handbook goes further: the measurements themselves, from the pricing service challenge and the swap credit adjustments to the earn-out scenarios, the calibration of private shares, the fund roll-forward and the highest and best use of a plant.

See the book and its free files →

Related reading