IAS 19 defined benefit pensions template
An Excel template that takes the figures in the actuary’s report and the asset valuations and turns them into the IAS 19 accounting for up to four defined benefit plans: the roll-forward of the DBO and plan assets, net interest, remeasurements in OCI, the asset ceiling under IFRIC 14, the journals and the note. It does not value anything itself – the obligation, service cost and fair values come from your actuary and valuers. It comes with a fully worked example for a fictitious Irish group with Irish, UK and German plans.
What it does
Rolls forward the DBO, plan assets, the effect of the asset ceiling and reimbursement rights for each plan, with actuarial gains and losses and the return on plan assets excluding interest derived as balancing figures and reconciled to the actuary’s analysis.
Calculates net interest on the opening net defined benefit liability (asset) with time-weighted contributions and benefit payments (IAS 19.123A), including interest on the effect of the asset ceiling, and compares it with the actuary’s figure.
Handles a plan amendment, curtailment or settlement during the year: remeasurement at the event date, past service cost and the settlement gain or loss determined before the asset ceiling, and service cost and interest for the rest of the year on the remeasured balances (IAS 19.99–101A, 122A).
Applies IFRIC 14: an unconditional right to a refund, reductions in future contributions, minimum funding requirements for future service, and the additional liability for past-service minimum funding contributions, at the year end and at the event date.
Produces the defined benefit cost and OCI, their presentation under IFRS 18 or IAS 1, a proof of the movement in the net position and the journals.
Drafts the IAS 19.140–147 disclosures: the reconciliation, balances by plan without offsetting, plan assets by class (quoted and unquoted), assumptions, sensitivities, maturity profile, the asset ceiling judgement and the accounting policy, with a 20-point checklist and 25 automatic checks.
What you get
One Excel workbook (.xlsx) with Instructions, the blank template and a worked example: an Irish scheme in surplus restricted by the asset ceiling, a UK section closed to accrual mid-year with a curtailment, a settlement and deficit contributions, and an unfunded German plan with reimbursement rights.
Clearly marked input cells, protected formulas and built-in checks, with a status line on every tab.
Works in Microsoft Excel 2010 or later (Windows or Mac). No macros.
Scope
Defined benefit plans of one entity in one presentation currency, under IFRS Accounting Standards: IAS 19 as currently issued (including the 2018 amendments on plan amendments, curtailments and settlements) and IFRIC 14. The template does not perform an actuarial valuation. Multi-employer, state and group plans and deferred tax are outside its scope; limitations are set out in the Instructions tab.
Restructuring at the same time? See the IAS 37 provisions template. Working out the deferred tax on the pension balance? Use the Deferred tax template.
Designed for finance teams, auditors and advisers preparing or reviewing IFRS financial statements. It is a working aid: it does not replace the Standards, the actuary’s work or professional judgement.
