Free Excel templates

A free pension cost and funded status model in Excel

Two years of a funded pension plan: the roll-forwards, net periodic pension cost by component, the corridor, the settlement test, the funded status and its checks.

A plan closes 2026 with an obligation of $142,600,000 and assets of $127,592,210. Its cost rises from $3,817,100 to $5,250,690 while contributions rise to $6,000,000, and a lump-sum window of $12,400,000 crosses the settlement threshold of $11,196,550. Getting the roll-forwards, the cost, accumulated other comprehensive income and the balance sheet to agree with each other is where the close loses time. The model lays the mechanics out on one sheet of inputs, ready for your own plan.

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.

Pension_Cost_and_Funded_Status_Model.xlsx (19 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 Pension Accounting Handbook, which follows one listed company and its two plans through two year-ends: the obligation rolled forward and remeasured, the discount rate set and defended, plan assets and their expected return, the corridor, a benefit improvement, a retiree medical cap, a lump-sum window and the pension note written line by line.

Paperback PDF edition, $19.99

What is in the file

Six tabs. Inputs are blue on yellow cells; everything else is a formula; the Checks tab shows one status, ALL PASS or FAIL.

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
Two columns, 2025 and 2026: the opening obligation and assets, a 1 January amendment, the discount and expected return rates, service cost, benefits, lump sums, the actuary's closing obligation, actual return, contributions, the opening net loss and prior service cost, and the amortization periods. 2026 openings are carried from 2025.
3. Roll
The obligation roll-forward closed to the actuary's figure by the actuarial gain or loss, the asset roll-forward with the expected return and the asset gain or loss, and the prior service cost schedule.
4. Cost
Net periodic pension cost by component, the settlement test (YES or NO) and loss, the corridor and the roll of the net loss.
5. Funded
The funded status, accumulated other comprehensive income and the liability roll: opening, cost, contributions, change in accumulated other comprehensive income.
6. Checks
Ten controls: the funded status by two routes in each year, the change in accumulated other comprehensive income by two routes, the cost, the asset roll, the carry-forward to 2026, and the inputs.

What the worked example returns

Measure, 2025 / 2026Value
Obligation at 31 December 2025 / 2026$155,900,000 / $142,600,000
Plan assets at 31 December 2025 / 2026$138,590,000 / $127,592,210
Funded status, a liability$17,310,000 / $15,007,790
Net periodic pension cost$3,817,100 / $5,250,690
Of which other components, below income from operations$967,100 / $2,550,690
Corridor and amortization$15,160,000 and $790,000 / $15,590,000 and $703,900
Settlement threshold 2026 against lump sums$11,196,550 against $12,400,000
Settlement loss 2026: 8 percent x $24,000,000$1,920,000
Accumulated other comprehensive income at 31 December$26,632,900 / $25,080,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 plan

  1. Take service cost, the closing obligation and the discount rates from the actuary's report; take actual return, benefits and contributions from the trustee and the bank.
  2. Type the actuary's obligation before any lump sums of the year: the gap with the roll is the actuarial gain or loss.
  3. Enter the opening net loss and prior service cost from last year's note.
  4. Read the Checks tab before any figure: anything but ALL PASS means a cell to fix.

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

Questions people ask

What does the model do?

For one funded defined benefit pension plan over two years, it rolls the obligation and the plan assets forward to the actuary's figures, computes net periodic pension cost component by component, applies the corridor, tests a lump-sum window against the settlement threshold, and rolls the funded status and accumulated other comprehensive income. Ten checks reach the key figures by two routes.

Which rules does it apply?

As read in November 2026: ASC 715-30 for a single-employer plan, with the market-related value of assets at fair value, the 10 percent corridor, straight-line amortization of prior service cost, and settlement accounting required when the lump sums of the year exceed service cost plus interest cost.

What does it leave out?

The actuarial valuation itself, a calculated market-related value, immediate recognition, curtailments, retiree medical plans, the split of service cost between inventory and expense, deferred taxes, interim remeasurements, the note and IAS 19. The book's full workbook covers most of them on one case.

Can I rely on it for a filing?

No. It is built to check a first pass and to learn the mechanics. The figures belong to your plan, its actuary, 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 Pension Accounting Handbook goes further: a calculated market-related value and immediate recognition as selectors, the retiree medical plan with its amendment and attribution, the entries, IAS 19 beside ASC 715, and the tables of the note.

See the book and its free files →

Related reading