Longevity Risk Model
Executive summary
I built a comprehensive longevity risk model in Excel that connects mortality modeling with annuity valuation and solvency metrics. The model provides a CRO-ready view of best-estimate liabilities, capital requirements, and sensitivity to mortality improvements.
Mortality modeling framework
- Mortality data: HMD-style central death rates with log-mortality and improvement rates.
- Lee-Carter: αx, βx, κt estimation and stochastic κt projection (random walk with drift).
- CBD: Two-factor logit(qx,t) model with correlated level and slope factors.
Life tables and liability projection
Period life tables translate mortality assumptions into survival curves, annuity factors, and projected cash flows. The model provides a full 30-year liability projection with duration and convexity metrics.
- qx, px, lx, dx, Lx, Tx, ex columns with survival probabilities.
- Portfolio of 15 annuitants with benefit types and demographic splits.
- Best-estimate liability (BEL) calculation and risk margin.
Risk and capital metrics
- Solvency II SCR using a -20% mortality shock scenario.
- Cost-of-capital risk margin with sensitivity to discount rates.
- VaR at 99.5% from Monte Carlo liability distributions.
Why this prototype is useful
This model demonstrates how actuarial assumptions flow through to capital, pricing, and risk appetite. It provides a transparent, auditable framework that can be adapted for ALM, pricing, or regulatory reporting.
Next steps
- Add cohort effects and calendar-year improvements.
- Extend the portfolio to longevity swaps and pension buy-ins.
- Integrate scenario generator for economic + longevity correlation.