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.
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.
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.
Six tabs. Inputs are blue on yellow cells; everything else is a formula; the Checks tab shows one status, ALL PASS or FAIL.
| Measure, 2025 / 2026 | Value |
|---|---|
| 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.
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.
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.
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.
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.
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.
Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.
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.