# Pension Fund Asset Allocation Model Build a long-term pension asset allocation model with liability discounting, asset-liability matching, and funding ratio forecasts. Track contribution requirements and de-risking glide paths across equities, fixed income, and alternatives. - Canonical: https://finamodel.com/templates/pension-fund-model - Excel download: https://finamodel.com/templates/pension-fund.xlsx - Category: Public Finance - Model type: Portfolio - Difficulty: Advanced - Audiences: Fund managers, Investors & analysts, Plan sponsors, Pension consultants, Asset allocators, Benefit analysts - Tags: pension, liability-driven, funding-ratio, asset-allocation, ldi ## Overview Project pension fund or endowment financial health with a liability-driven investment framework that links asset allocation to funding ratios and spending rules. This template models six asset classes (public equities, fixed income, private equity, real assets, hedge funds, cash) with scenario-dependent returns under Base, Bull, and Bear cases, calculates fees by asset class including performance fees, and computes funded ratio and liquidity coverage each year. It addresses the core liability side: benefit obligations that grow via service cost and interest cost, with actuarial gains and losses adjusting the PBO in stress scenarios. The workbook contains assumption controls for scenario toggle (Base/Bull/Bear), asset allocation with rebalancing, base and performance fee schedules, and member demographics (active and retired headcounts, salary escalation, benefit accrual). The AUM roll-forward captures asset returns net of fees and internal opex; the Liability_Rollforward models PBO dynamics separately. A funded status sheet monitors the gap between assets and liabilities, while a liquidity check ensures liquid assets exceed annual cash outflows (benefits, fees, opex). For endowments, a spending rule based on a trailing three-year average AUM provides a sustainable drawdown mechanism. Target users are pension fund trustees, investment committees, endowment boards, and institutional investors managing £1B to £100B+ portfolios requiring liability-asset matching analysis. ## What's included - Benefit obligation and liability projections by member cohort - Asset allocation by major asset class with return and volatility assumptions - Asset-liability matching and duration analysis - Contribution requirement and funding ratio calculations - Stress scenarios and sensitivity analysis on key assumptions - Benefit obligation and liability projections - Asset allocation by major asset class - Return and volatility assumptions by class - Contribution requirement and funding ratio - Stress scenarios and sensitivity analysis ## Pension Fund Model: Asset Allocation and Liability Mechanics This pension fund model projects a defined-benefit plan over ten years, showing whether assets cover liabilities under bull, base, and bear markets. It links contributions, investment returns, fees and benefit payments into a coherent projection. A mode toggle also supports endowments applying a spending rule, so the same engine serves both. ### Operating drivers Contributions and benefits follow member demographics. Active and retiree counts grow at scenario-dependent rates, and the salary bill is average salary multiplied by active members. - Employer and employee contribution rates then fix total contributions, which grow with salaries. On the liability side, service cost accrues as active members earn benefits, while interest cost rolls the opening obligation forward at the discount rate, not asset return. - Benefits paid to retirees reduce the obligation. Under base assumptions, interest and service cost exceed benefits paid, so the obligation grows steadily, matching a going-concern plan rather than a wind-down. ### Calculation flow Each year opens with beginning asset values by class and an opening liability. Asset class returns, chosen from the active scenario column, produce gross investment return. - Base fees and performance fees are charged on beginning balances to avoid circularity, then allocated back to each class alongside internal operating costs. Net cash flow from contributions, benefit payments or endowment spending arrives from the contributions sheet. - The AUM roll-forward combines beginning assets, gross return, fees, opex and net flows to reach ending assets per class, which become next year's starting balances and feed the funded status, cash flow and checks sheets. ### Outputs The funded status sheet compares total assets with the closing obligation to produce net funded position and funded ratio, with a status indicator and a liquidity check comparing liquid assets against annual cash outflows. - The cash flow statement records only actual movements: contributions or gifts, estimated dividend and coupon income, private equity distributions, benefits or spending, external fees and internal opex, leading to a closing cash balance. Mark-to-market gains are deliberately excluded from cash flow, since they are not cash. - A checks sheet validates reconciliation, weight totals, scenario and mode toggles, funded ratio, cash balance, obligation growth, liquidity adequacy and expense ratio range. ### Practical use This template suits users evaluating funding trajectories under different market conditions and testing how contributions, benefit payments and returns interact over a decade. - Switching the mode toggle replaces the liability mechanic with an endowment spending rule based on a trailing three-year asset average, so the same structure covers perpetual funds. - The scenario toggle shifts returns, discount rate, actuarial adjustment and demographic growth together, showing how sensitive funding is to assumptions. - Practical caveats include using beginning asset balances for fees, trailing averages for spending, absolute amounts for the liquidity test, and keeping every named range as a single cell. ## Liability-driven investing framework Align portfolio duration and cash flows to match pension liabilities and minimise funding ratio volatility over the plan horizon. ## Multi-asset allocation modeling Model equities, fixed income, alternatives, and cash with separate return and volatility assumptions to optimise the long-term portfolio mix. ## Funding ratio and contribution planning Project the ratio of plan assets to liabilities over time and forecast required employer contributions under different return scenarios. ## Liability-driven investing framework Align portfolio duration and cash flows to match pension liabilities and minimise funding ratio volatility over the plan horizon. ## Multi-asset allocation modeling Model equities, fixed income, alternatives, and cash with separate return and volatility assumptions to optimise the long-term portfolio mix. ## Funding ratio and contribution planning Project the ratio of plan assets to liabilities over time and forecast required employer contributions under different return scenarios. ## Features - **Liability-driven investing:** Aligns portfolio duration and cash flows to match pension liabilities and minimize funding risk. - **Multi-asset allocation:** Models equities, fixed income, alternatives, and cash with separate return and volatility assumptions. - **Funding ratio forecasting:** Projects plan assets divided by liabilities over time to show path to full funding and contribution requirements. ## Use cases - **Strategic asset allocation reviews:** Rebalance portfolio allocation to achieve funding targets and minimize contribution volatility. - **Contribution planning and budgeting:** Forecast required pension contributions under different return scenarios to support employer cash flow planning. - **De-risking and glide path execution:** Model phased shift toward lower-risk assets as funding ratio improves and liability duration shortens. ## Frequently asked questions ### What is a pension fund financial model? A model that projects pension liabilities, asset returns by class, funding ratios, and required contributions to support strategic asset allocation and plan management decisions. ### What is a pension fund liability? The present value of all future benefit payments owed to active, terminated vested, and retired members, discounted at an assumed long-term rate. ### How do I calculate the funding ratio? Funding ratio equals plan assets divided by the present value of liabilities. A ratio above 100% means the plan is fully funded. ### What is a liability-driven investing strategy? An approach that aligns the portfolio duration and cash flows to match pension liabilities, reducing funding ratio volatility as the plan matures. ### Can I model contribution caps or accelerated funding schedules? Yes. The model allows custom contribution assumptions and can show accelerated schedules to reach full funding within a defined time horizon. ## Related templates - [Endowment Fund Model](https://finamodel.com/templates/endowment-model) - [Sovereign Wealth Fund Portfolio Model](https://finamodel.com/templates/sovereign-wealth-model) - [Insurance Portfolio Risk and Return Model](https://finamodel.com/templates/insurance-portfolio-model)