Insurance underwriting and valuation model
Premium, reserves, combined ratio, float, solvency, P/B. Build an insurer from its premium: written, ceded and earned through the unearned premium reserve; claims incurred by line and rolled through a reserve with prior-year development; the loss, expense and combined ratios; investment income on the float; a solvency ratio and the dividends it allows; and a value per share from price to book, an embedded-value style sum and a dividend discount.
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: insurance underwriting and valuation model.Updated 30 October 2026
Who builds it, and for whatThe model an insurance analyst builds for a property and casualty insurer: in equity research to forecast the combined ratio and earnings, in a financial institutions group to value a company or a book of business for a sale, and at a rating agency or reinsurer to judge how much capital the risks need. It answers whether the underwriting makes money on its own, how much the float earns while claims wait to be paid, how much capital stands above the requirement, and what the shares are worth against the return on equity.
Every sheet below, in your browser, with its outputs and checks
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | |||||||||
| 2 | $m unless stated. FY0A is the opening balance sheet and premium; the forecast runs FY1E to FY5E. Every figure is invented for teaching. | |||||||||
| 4 | Year | Unit | FY0A | FY1E | FY2E | FY3E | FY4E | FY5E | Notes | |
| 5 | Year number | # | 0 | 1 | 2 | 3 | 4 | 5 | FY0A = 0. Drives cost growth and discounting. | |
| 7 | Scenario | |||||||||
| 8 | Scenario | # | 1 | 1 = base, 2 = upside, 3 = downside. Moves premium rates, attritional losses, catastrophe losses and the investment yield in FY1E to FY5E. | ||||||
| 9 | Premium rate change, upside adjustment | pp | 1.0% | Added to every line’s rate change when Scenario = 2. | ||||||
| 10 | Premium rate change, downside adjustment | pp | -3.0% | Added when Scenario = 3: a softening market. | ||||||
| 11 | Attritional loss ratio, upside adjustment | pp | -2.0% | |||||||
| 12 | Attritional loss ratio, downside adjustment | pp | 4.0% | Claims inflation running ahead of rates. | ||||||
| 13 | Catastrophe losses, upside multiplier | x | 0.6x | Scales property catastrophe losses when Scenario = 2. The base case is 1.0x. | ||||||
| 14 | Catastrophe losses, downside multiplier | x | 2.0x | A heavy catastrophe year, every year. | ||||||
| 15 | Investment yield, upside adjustment | pp | 0.5% | |||||||
| 16 | Investment yield, downside adjustment | pp | -1.0% | |||||||
| 18 | Premium by line | |||||||||
| 19 | Motor: gross written premium, FY0A | $m | 1,200.0 | |||||||
| 20 | Motor: premium rate change, base case | % | 5.0% | 4.0% | 3.0% | 3.0% | 3.0% | Price per unit of risk. | ||
| 21 | Motor: exposure growth | % | 2.0% | 2.0% | 2.0% | 2.0% | 2.0% | More policies, vehicles or sums insured. | ||
| 22 | Motor: share ceded to reinsurers | % | 10.0% | Proportional (quota share): premium and claims ceded alike. | ||||||
| 23 | Property: gross written premium, FY0A | $m | 900.0 | |||||||
| 24 | Property: premium rate change, base case | % | 4.0% | 3.0% | 3.0% | 2.5% | 2.5% | Price per unit of risk. | ||
| 25 | Property: exposure growth | % | 2.0% | 2.0% | 2.0% | 2.0% | 2.0% | More policies, vehicles or sums insured. | ||
| 26 | Property: share ceded to reinsurers | % | 25.0% | Proportional (quota share): premium and claims ceded alike. | ||||||
| 27 | Liability: gross written premium, FY0A | $m | 600.0 | |||||||
| 28 | Liability: premium rate change, base case | % | 5.0% | 4.0% | 4.0% | 3.0% | 3.0% | Price per unit of risk. | ||
| 29 | Liability: exposure growth | % | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | More policies, vehicles or sums insured. | ||
| 30 | Liability: share ceded to reinsurers | % | 15.0% | Proportional (quota share): premium and claims ceded alike. | ||||||
| 31 | Unearned share of the year’s net written premium | % | 50.0% | Annual policies written evenly through the year: half is unearned at the year end. | ||||||
| 33 | Loss ratios by line, net of reinsurance | |||||||||
| 34 | Motor: attritional loss ratio, base | % | 68.0% | The everyday claims, % of net earned premium. | ||||||
| 35 | Motor: large losses, % of net earned premium | % | 4.0% | |||||||
| 36 | Property: attritional loss ratio, base | % | 50.0% | The everyday claims, % of net earned premium. | ||||||
| 37 | Property: catastrophe losses, % of net earned premium | % | 10.0% | |||||||
| 38 | Liability: attritional loss ratio, base | % | 60.0% | The everyday claims, % of net earned premium. | ||||||
| 39 | Liability: large losses, % of net earned premium | % | 8.0% | |||||||
| 41 | Claims payment and reserves by line | |||||||||
| 42 | Motor: net claims reserves, FY0A | $m | 900.0 | |||||||
| 43 | Motor: share of the year’s claims paid in the year | % | 45.0% | |||||||
| 44 | Motor: share of opening reserves paid in the year | % | 50.0% | The speed at which older claims settle: slow for liability. | ||||||
| 45 | Motor: prior-year development, % of opening reserves | % | -2.0% | Negative is a release of reserves that proved too high. | ||||||
| 46 | Property: net claims reserves, FY0A | $m | 350.0 | |||||||
| 47 | Property: share of the year’s claims paid in the year | % | 55.0% | |||||||
| 48 | Property: share of opening reserves paid in the year | % | 60.0% | The speed at which older claims settle: slow for liability. | ||||||
| 49 | Property: prior-year development, % of opening reserves | % | -3.0% | Negative is a release of reserves that proved too high. | ||||||
| 50 | Liability: net claims reserves, FY0A | $m | 1,500.0 | |||||||
| 51 | Liability: share of the year’s claims paid in the year | % | 10.0% | |||||||
| 52 | Liability: share of opening reserves paid in the year | % | 22.0% | The speed at which older claims settle: slow for liability. | ||||||
| 53 | Liability: prior-year development, % of opening reserves | % | 0.0% | Negative is a release of reserves that proved too high. | ||||||
| 55 | Expenses | |||||||||
| 56 | Motor: acquisition costs, % of net earned premium | % | 14.0% | Commissions to brokers and agents. | ||||||
| 57 | Property: acquisition costs, % of net earned premium | % | 20.0% | Commissions to brokers and agents. | ||||||
| 58 | Liability: acquisition costs, % of net earned premium | % | 16.0% | Commissions to brokers and agents. | ||||||
| 59 | Administration expenses, FY0A | $m | 270.0 | Largely fixed: claims handling, systems, staff and premises. | ||||||
| 60 | Administration expense growth | % | 3.0% | |||||||
| 62 | Investments, debt and tax | |||||||||
| 63 | Investment yield, base case | % | 3.5% | 3.6% | 3.7% | 3.7% | 3.7% | 3.7% | On a portfolio of high-grade bonds matched to the claims. | |
| 64 | Premium receivables, % of gross written premium | % | 15.0% | Premium written but not yet collected from brokers. | ||||||
| 65 | Shareholders’ equity, FY0A | $m | 1,800.0 | |||||||
| 66 | Goodwill and intangible assets | $m | 150.0 | Held flat. | ||||||
| 67 | Subordinated debt | $m | 400.0 | Held flat. Counts as own funds for solvency. | ||||||
| 68 | Interest rate on debt | % | 5.5% | |||||||
| 69 | Tax rate | % | 25.0% | |||||||
| 71 | Capital | |||||||||
| 72 | Motor: premium risk factor, % of net earned premium | % | 20.0% | |||||||
| 73 | Motor: reserve risk factor, % of net reserves | % | 15.0% | |||||||
| 74 | Property: premium risk factor, % of net earned premium | % | 25.0% | |||||||
| 75 | Property: reserve risk factor, % of net reserves | % | 18.0% | |||||||
| 76 | Liability: premium risk factor, % of net earned premium | % | 28.0% | |||||||
| 77 | Liability: reserve risk factor, % of net reserves | % | 20.0% | |||||||
| 78 | Catastrophe risk capital | $m | 220.0 | The 1-in-200 year catastrophe loss after reinsurance. | ||||||
| 79 | Market risk factor, % of opening invested assets | % | 7.0% | |||||||
| 80 | Diversification credit | % | 25.0% | The risks do not all strike in the same year. | ||||||
| 81 | Minimum solvency ratio | x | 1.0x | |||||||
| 82 | Target solvency ratio | x | 1.7x | Distributions are paid only from own funds above this. | ||||||
| 83 | Ordinary dividend, % of net income | % | 50.0% | |||||||
| 84 | Special dividends, % of the capital left above the target | % | 50.0% | |||||||
| 85 | Shares in issue | m shares | 200 | Held flat: capital is returned by dividend. | ||||||
| 87 | Valuation | |||||||||
| 88 | Risk-free rate | % | 4.2% | |||||||
| 89 | Equity beta | β | 0.85 | |||||||
| 90 | Equity risk premium | % | 5.5% | |||||||
| 91 | Long-term growth | % | 2.5% | Growth in book value and dividends after FY5E. Must be below the cost of equity. | ||||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
Insurance underwriting and valuation model: the workbook
An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.
- Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
- Free accountWork with every model online: change the inputs, switch the case, see every formula. And every starter workbook, with its key schedules left blank to build.
- L3VLUP Plus$4.99/monthEverything in Free, and one worked model each billing month as an Excel file: every schedule built, formulas live, checks at zero, yours to keep.
- L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
- The worked model
- 12 sheets and 980 live formulas, all 6 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves.
- The starter
- The same inputs and layout with the insurance premium and claims reserves left blank for you to build, each blank cell marked. The worked model is its answer.
What the base case says
- Net earned premium, FY1E
- $2,343.5m
- Combined ratio, FY1E
- 94.4%
- Loss ratio, FY1E
- 66.3%
- Investment income, FY1E
- $198.8m
- ROE, FY1E
- 12.8%
- Solvency ratio, FY1E
- 1.7x
- Value per share, price to book ($)
- 14.56
- Justified price to book
- 1.6x
- Value per share, embedded value style ($)
- 8.97
- Value per share, dividend discount ($)
- 16.49
Read from the workbook as served, every input at its default. Periods: FY0A, FY1E, FY2E, FY3E, FY4E, FY5E. The figures are invented and move with whatever you type in.
What this model is
A five-year model (FY1E to FY5E, from an FY0A opening position) of a property and casualty insurer writing motor, property and liability business, built the way an insurer is run: premium written, ceded, earned; claims incurred, reserved and paid; and the float invested in between.
It runs from gross written premium by line, through reinsurance and the unearned premium reserve, to net earned premium; from loss ratios split into attritional and large or catastrophe losses, through a claims reserve roll-forward with prior-year development, to the loss, expense and combined ratios and the underwriting result; then to investment income on the float, net income, a balance sheet that balances, a solvency ratio and the dividends it allows. Set Scenario on the Assumptions sheet to 2 (upside) or 3 (downside).
An insurer is paid before it pays. The premium arrives at the start of the policy and the claims are settled over months or years, so it holds the policyholders’ money in the meantime. That float is invested, and its income is why an insurer can make a fair return with a combined ratio near 100%.
The combined ratio is the underwriting scorecard: claims and expenses as a share of earned premium. Below 100% the underwriting itself makes money. Prior-year development shows whether last year’s reserves were right, which is why the underlying ratio excludes it.
Insurers are valued on equity. The Valuation sheet sets the justified price to book from forward ROE, builds an embedded-value style figure from net assets plus the business already on the books, and cross-checks both against the dividends the solvency ratio allows.
Seats: Investment banking, Equity research and hedge funds.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
Insurance premium and claims reserves
Written to earned premium, UPR and the reserve roll
Assumptions, rows 19–31 · Assumptions, rows 42–53 · Premiums, rows 6–10 · Premiums, rows 13–17 · Premiums, rows 20–24 · Premiums, rows 27–33 · Claims, rows 14–18 · Claims, rows 25–29 · Claims, rows 36–40 · Claims, rows 43–48
Loss ratio, expense ratio and the combined ratio
Attritional and catastrophe losses, expenses, result
Assumptions, rows 34–39 · Assumptions, rows 56–60 · Claims, rows 6–7 · Claims, rows 10–12 · Claims, rows 21–23 · Claims, rows 32–34 · Underwriting, rows 6–13 · Underwriting, rows 16–26 · Underwriting, rows 29–38
Float and investment income
Policyholders’ money, invested in the meantime
Assumptions, rows 63–69 · Investments, rows 6–10 · Investments, rows 13–19 · Investments, rows 22–26
Insurance solvency ratio and dividend capacity
Required capital, own funds, the ratio and payouts
Assumptions, rows 72–85 · Capital, rows 6–11 · Capital, rows 14–25
Insurance valuation: P/B, embedded value and dividends
Justified P/B, value in force, dividend discount
Assumptions, rows 88–91 · Valuation, rows 6–7 · Valuation, rows 10–15 · Valuation, rows 18–33 · Valuation, rows 36–42
Sensitivity tables
Two assumptions at once, without a data table
Sensitivity, rows 5–8 · Sensitivity, rows 11–17 · Sensitivity, rows 20–27 · Sensitivity, rows 30–46
What you should be able to explain
- Why written premium is not revenue: it is earned over the policy’s life, so earned premium lags written premium through the unearned premium reserve.
- How a claims reserve rolls forward, and what prior-year development says about how prudently last year’s reserves were set.
- Why the combined ratio is the underwriting scorecard, and why a ratio near 100% can still earn a fair return once the float is invested.
- How required capital is built from premium, reserve, catastrophe and market risk, and why the solvency ratio decides the dividend.
- Why insurers trade on price to book against ROE, and what an embedded-value style figure leaves out: the business not yet written.
What a reviewer looks for
- Treating written premium as revenue, so a year of fast growth looks more profitable than it is.
- Forecasting claims as a single loss ratio, so a catastrophe year and a reserve release cannot be told apart.
- Forgetting that reserves are paid out over years, so the float and the investment income it earns are understated for long-tail lines.
- Quoting a combined ratio that mixes prior-year releases into the current year without saying so.
- Paying dividends from profit without checking the solvency ratio they leave behind.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Annual periods. FY0A is the opening balance sheet and premium; the forecast runs FY1E to FY5E. Written premium grows by rate change plus exposure growth; policies are annual and written evenly, so half of each year’s net written premium is unearned at the year end.
- All figures are net of a proportional reinsurance cession, so a line’s loss and expense ratios are the same gross or net. Excess-of-loss reinsurance, deferred acquisition costs and discounting of reserves are not modelled; acquisition costs are charged as premium is earned.
- Each line’s reserve rolls forward: opening, plus the year’s claims, plus prior-year development (a share of opening reserves), less claims paid (a share of the year’s claims and a share of opening reserves). The Checks sheet rebuilds the closing reserve from what remains unpaid.
- Invested assets roll forward from net income, the growth in the float and dividends. Investment income is earned on opening invested assets plus half the year’s growth in the float, and market risk capital is charged on opening invested assets, so there is no circular reference.
- Required capital is the sum of premium, reserve, catastrophe and market risk less a diversification credit; own funds are equity less intangibles plus subordinated debt. Dividends come only from own funds above the target ratio: the ordinary dividend, then a share of what is left as a special dividend.
- Valuation date is the end of FY0A, with year-end discounting at the cost of equity. The justified price to book uses forward ROE; the embedded-value style figure is tangible equity plus the after-tax value of the unearned premium and the investment income on today’s reserves as they run off, less the cost of the capital held against them. It leaves out business not yet written.
- The value grid recomputes FY1E ROE at each combined ratio (moving every line’s attritional losses alike, with the reserves they add) and investment yield. The capital grid rebuilds FY1E at each premium growth and catastrophe loss ratio from the model’s own lines.
Build it yourself
The starter workbook
The premium and reserve schedules have been cleared: written, ceded, net written and earned premium with the UPR on the Premiums sheet, and each line’s claims reserve roll-forward on the Claims sheet. Rebuild them from the Assumptions sheet. The Checks sheet shows when the UPR and reserves roll forward and the balance sheet balances again.
Blanks: Insurance premium and claims reserves. Download it beside the worked model.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Build · Lab · ~10 min
Beta Refresher
Estimate a beta, see how far one regression lands from the truth, and know when to use raw, adjusted, unlevered or bottom-up.
Read · Guide · 7 min
Equity Research Interview Questions: The Coverage Mindset
Read · Guide · 7 min
Enterprise Value vs. Equity Value, Once and For All
Read · Guide · 10 min
Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested
Read · Guide · 11 min
Financial Modelling Best Practices: The Conventions That Make a Model Auditable
Apply · Skill
Company Profile
Build a banker-grade one-pager on any company, fast.
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: Cost of Equity, Equity Value, Duration, Terminal Growth Rate, Dividend Yield.
Questions about this model
What is the combined ratio?
Claims and expenses as a share of net earned premium: the loss ratio plus the expense ratio. Below 100% the underwriting itself makes money. Here FY1E runs at 94.4%, a 66.3% loss ratio (net of releases from older years’ reserves) and a 28.1% expense ratio, so the insurer keeps about 5.6 cents of every premium dollar before it invests anything.
What is the float, and why does it matter?
The money an insurer holds for its policyholders: claims reserves plus unearned premium, less premium not yet collected. Premium arrives before claims are paid, sometimes years before, so the float is invested in the meantime. Here it earns $198.8m of investment income in FY1E, more than the underwriting result, which is why a liability writer with slow-paying claims can be valuable at a combined ratio near 100%.
How is the solvency ratio built?
Required capital is the sum of four risks: premium risk on next year’s business, reserve risk on claims already reserved, catastrophe risk and market risk on the investments, less a diversification credit. Own funds are equity less intangibles plus subordinated debt. The insurer ends FY1E at 1.73 times, above its 1.7 times target, and pays out only what stands above it.
Why do the three valuations differ?
Because they value different things. Price to book at the forward ROE values the business as a going concern ($14.56 a share, 1.62 times book). The embedded-value style figure ($8.97) is net assets plus the business already on the books and nothing written after today. The gap is what the market pays for business not yet written. The dividend discount ($16.49) rests on the forecast’s rising returns.
Related models
Financial institutions · Advanced
Bank operating and valuation model
Loans, deposits, NIM, credit losses, CET1, residual income
Financial institutions · Core
Asset manager AUM and fee economics model
AUM roll-forward, flows, fee rates, margin, P/E and DCF
Core modelling · Core
Discounted cash flow model
Unlevered free cash flow to a share price
Questions about this model
Discussions open here soon.