Insurance Portfolio Risk and Return Model
Capital Markets Financial Model (Free Excel Download)
Analyse policy cohorts, premium growth, claims frequency, loss ratios, expenses, and reserve development to evaluate portfolio profitability and capital needs.
professionals from Deloitte
Used by professionals from
About this model
This portfolio model manages insurance investment holdings by matching asset duration to liability duration, forecasting investment income, and stress-testing credit and interest rate risk. Build asset allocation across fixed income (government, corporate, MBS), equities, and alternatives; apply duration constraints; and calculate return sensitivity to spread widening, default scenarios, and rate changes.
The workbook tracks fixed income holdings by maturity and credit rating, calculates portfolio duration (weighted average time to cash flow), applies duration matching to liability duration (insurers must match long-duration liabilities with assets). Credit risk: default probability assumptions by rating (AAA 0.1%, BBB 1–2%, BB 5–10%), loss-given-default (typically 40–60%), and expected loss by holding. Dividend and interest income forecasts on known coupons and yields. Mark-to-market sensitivity: 1% parallel rate shift impacts portfolio value by duration × 1% (e.g., 5-year duration portfolio loses 5% value if rates rise 100 bps).
Used by insurance CFOs, asset liability managers (ALM), and portfolio managers ensuring compliance with prudential ratios (Solvency II in Europe, NAIC in US). The model reveals concentration risk and illiquidity drag: if liabilities are 8-year duration but 30% of assets are 2-year, reinvestment risk persists. Credit spread compression (tightening) benefits mark-to-market but reduces prospective yield. Benchmarks: insurance portfolios typically 60–80% fixed income, 10–20% equities, 10% alternatives; duration matched within ±1 year to liability duration.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Insurance Portfolio Risk and Return Model
- Asset allocation by category including fixed income, equities, and alternatives
- Fixed income holdings across government bonds, corporates, MBS, and structured products
- Duration matching to liability duration for interest rate immunization
- Credit quality limits and default risk assumptions by rating category
- Mark-to-market sensitivity and asset-liability management analysis
- Dividend and interest income forecasts
How an Insurance Portfolio Model Links Underwriting and Investment Risk
This insurance portfolio model projects a mid-sized P&C insurer over five years, combining underwriting results with an investment overlay. It traces how premium, reserves and capital drive portfolio income, asset-liability matching, stress tests and NAIC RBC.
The public template is a values-only preview, so formulas do not recalculate.
Premium, Reserves and Underwriting Profitability
The model builds net earned premium through a waterfall: gross written premium grows at a set rate, ceded premium is deducted at the cession rate, and net written premium is adjusted for the change in unearned premium.
- Net incurred losses then equal net earned premium multiplied by the loss ratio, with reinsurance recoveries shown separately.
- This keeps the gross and ceded split explicit and avoids double-counting.
- A single loss reserve pool, sized as a proportion of net earned premium, drives paid claims from the reserve roll-forward, so reserve growth becomes a natural cash source as the book expands.
Underwriting Expenses, Depreciation and Debt Costs
Underwriting expenses are built as a four-part waterfall: gross commission, less deferred acquisition cost amortisation, giving net commission, plus other underwriting expenses. Total underwriting expenses are the sum of those components, not a separate formula.
- The deferred acquisition cost balance rolls forward so that unearned commissions align with unearned premium, and fast growth defers commission relief. Capital expenditure scales with net earned premium, depreciation is charged on opening net PP&E, and interest expense uses the opening debt balance.
- Together these drive the combined ratio and net income.
Investment Portfolio, Income and Asset-Liability Matching
The investment overlay allocates the portfolio across eight asset classes, each with a book yield, duration and NAIC charge.
- Portfolio-weighted yield and duration feed investment income and the ALM schedule.
- Investment income is calculated on opening invested assets to avoid circularity, while the invested asset balance itself is derived upstream from reserves, unearned premium, equity, payables and debt, net of receivables, recoveries, DAC and PP&E.
- The ALM schedule compares asset duration with a liability duration weighted across short-tail and long-tail reserves, producing a duration gap and DV01 that quantify interest-rate exposure.
Stress Testing, Capital Adequacy and Practical Use
Stress tests apply parallel rate shocks, credit spread widening and equity drawdowns to portfolio market value, then recalculate post-shock capital adequacy. The capital schedule computes NAIC risk-based capital from asset, reinsurance, reserving and premium risk charges, with TAC divided by authorised control level giving the CAR.
- Dividend capacity is capped by both a target CAR and a premium-to-surplus limit. A statutory-to-GAAP bridge reconciles net income and equity.
- Practically, the model helps evaluate how underwriting growth, portfolio allocation and liability duration interact with capital adequacy.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is duration matching and why is it important for insurers?+
Duration matching aligns the interest rate sensitivity of assets with liabilities. When assets and liabilities have similar duration, changes in interest rates have offsetting effects on both sides of the balance sheet, protecting book value.
What credit quality should an insurance portfolio target?+
Typical insurance portfolios hold 50-70% investment grade assets. NAIC guidelines often cap below-investment-grade holdings at 10-15% of the total portfolio.
How do I model reinvestment risk?+
Model coupons and principal payments reinvesting at forward-looking yields, which are often lower than current yields. Build a ladder or barbell maturity strategy to manage reinvestment schedules.
Who uses insurance portfolio models?+
Insurance CFOs, investment officers, risk managers, and insurance investors use these models for investment policy frameworks, hedging decisions, and capital adequacy planning.
What is ALM in the context of insurance investing?+
Asset-liability management coordinates investment portfolio decisions with the duration and cash flow profile of insurance liabilities to minimize interest rate risk and ensure claims can be paid when due.
Have more financial modelling questions? Contact us
Related templates
Bond Trading Model
Fixed income trading desk P&L and risk analytics.
Mortgage Portfolio Model
Mortgage loan portfolio analysis with prepayment speeds, default rates, and cash flow projections.
Fixed Income Portfolio Model
Bond portfolio analytics with duration, convexity, credit spreads, and yield curve positioning.
DCF Model
Discounted cash flow valuation model with explicit forecast, terminal value, and WACC.
