Pension Fund Asset Allocation Model

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.

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.

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.

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.