A free IFRS 17 CSM Roll-Forward Calculator in Excel
Four live worksheets covering GMM CSM roll-forwards and the OCI option, Onerous Loss Components and Reinsurance Loss-Recovery (IFRS 17.66A), PAA discounted LIC, and VFA participating savings.
Pre-loaded with Meridian Life & General Insurance plc (MLG) in USD thousands ($000): Cohort A starts with $6,832.6k initial CSM and unwinds its Year 2 OCI gain (+$244.6k) to $0.0k at Year 5; Cohort B books a $3,868.7k Day-1 onerous loss offset by a +$1,547.5k 40% reinsurance loss-recovery component; Cohort C produces a 93.29% IFRS 17 combined ratio; Cohort D absorbs +12% and -8% market swings directly into its $2,538.4k VFA CSM.
Get the Excel calculator
One Excel workbook, no macros, nothing locked. Enter your email address and the download opens immediately, along with the other free practitioner models.
IFRS_17_CSM_Roll_Forward_Calculator.xlsx (19 KB)
You join the reader list: occasional notes when new models or handbooks release. Unsubscribe in one click. How your address is used.
The book behind this calculator
The IFRS 17 Handbook walks through every equation, journal entry, and disclosure table for Meridian Life & General Insurance plc across GMM, PAA, VFA, Onerous Contracts, Reinsurance Held, and Transition (FRA/MRA/FVA).
GMM_CSM_RollForward: 5-year Term Life engine with locked-in 4.0% vs. current 5.0% discount curves, Year 2 future claims assumption shock absorbed by CSM, Year 3 experience variance in P&L, coverage unit release ratios, and exact OCI reserve convergence to zero.
Onerous_Reinsurance: 4-year Guaranteed Term Rider modeling Day-1 onerous loss, systematic Loss Component allocation ratio (alpha = 23.20%), revenue exclusion, and 40% quota-share Reinsurance Loss-Recovery Component (IFRS 17.66A-66B).
PAA_LIC_Model: 1-year non-life PAA coverage with 3-year claims settlement tail, discounted LIC, 8% Risk Adjustment unwind, and IFRS 17 vs. undiscounted combined ratio comparison.
VFA_Participating: 5-year participating savings contract ($50,000.0k pool, 90%/10% sharing) demonstrating entity variable fee absorption into CSM and zero Net Financial Result under VFA mirror accounting.