A free Solvency II SCR calculator in Excel, with a complete worked insurer
The standard formula, simplified for teaching: market, counterparty default, life and non-life modules, the regulatory correlation matrices, the Basic SCR, operational risk, the adjustments and the solvency ratio. Seven linked sheets, one fictional insurer, no macros.
The file takes Northgate Mutual, a fictional composite insurer, from its exposures to its solvency ratio, in euro millions. The six market sub-modules sum to €530.2 million and aggregate to €439.7 million once the Annex IV correlations are applied, a 17.1% diversification benefit. With counterparty default at €34.3 million, life underwriting at €122.5 million and non-life at €155.5 million, the modules sum to €752.0 million and aggregate to a Basic SCR of €556.6 million. Operational risk adds €29.0 million, the loss-absorbing adjustments take off €85.0 million, and the SCR lands at €500.6 million. Against €900.0 million of eligible own funds, the solvency ratio is 179.8%. Every one of those numbers is a formula you can move.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Solvency_II_SCR_Calculator.xlsx (27 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from Insurance Capital for Asset Managers. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →
A teaching simplification, not a regulatory return. The aggregation is the standard formula’s own: the Annex IV matrices, the square root of the correlation-weighted sum of charge pairs, the operational risk cap. Several inputs are simplified. Interest rate risk, the life stresses, non-life lapse and catastrophe, counterparty type 1 and concentration are typed in as losses or charges computed elsewhere. Spread risk uses the Article 176 slope for durations up to five years only, and the Checks sheet flags anything longer. Non-life premium and reserve risk is one segment, operational risk leaves out the growth terms, the deferred tax adjustment is capped by a single number, and health is one typed-in charge.
What is in the file
Seven sheets, in the order the calculation runs. Blue type on a pale blue fill is an input, blue type with no fill is a regulatory parameter, and black type is a formula.
Read Me
What the file does, what it simplifies, and how to enter your own insurer.
Inputs
Every number you can change: market exposures and losses, up to eight bond and loan lines with their credit quality step and duration, the seven life stresses, non-life volumes and standard deviations, counterparty exposures, the operational risk bases, the adjustments and eligible own funds.
Parameters
The regulatory factors the formulas read: the equity, property and currency shocks, the spread slope by credit quality step, the counterparty and operational risk factors.
Market
Six sub-modules, the spread charge line by line, the interest rate scenario that binds, and the market correlation matrix with every pair of charges laid out.
Underwriting
Life underwriting on its seven-by-seven matrix, non-life premium and reserve risk with lapse and catastrophe, and counterparty default from its two exposure types.
SCR
The five modules aggregated into the Basic SCR, operational risk with its cap, the two adjustments, the SCR, the surplus and the solvency ratio.
Checks
Nine identities that hold for any inputs, symmetric matrices included, then the worked case figures, each with a verdict. The file ends ALL OK.
What the worked case returns
Measure
Value
Market risk
€439.7m
Counterparty default
€34.3m
Life underwriting
€122.5m
Health underwriting
€0.0m
Non-life underwriting
€155.5m
Sum of the modules
€752.0m
Basic SCR
€556.6m
Diversification between modules
€195.4m (26.0%)
Operational risk
€29.0m
Loss-absorbing adjustments
(€85.0m)
Solvency Capital Requirement
€500.6m
Eligible own funds
€900.0m
Solvency ratio
179.8%
Read the diversification before the ratio. Summed module by module, Northgate’s risks would need €752.0 million; aggregated with the regulatory correlations they need €556.6 million, because the formula assumes market losses, life losses and non-life losses do not all peak in the same year. Life and non-life are set at zero correlation, so a composite carrying both books gets a large part of its sum back, here 26.0%. Inside the market module, the downward interest rate scenario binds, and that switches on a 0.50 correlation between interest rate risk and equity, property and spread: an insurer whose rate charge comes from the upward shock gets more diversification from the same portfolio. Operational risk is set by the provisions basis, €29.0 million, far below its cap of 30% of the Basic SCR, and the adjustments are the profit-sharing buffer of €25.0 million plus deferred taxes capped at €60.0 million.
How to use it on your own insurer
Open Inputs and overwrite the blue cells. Start with the market block: exposures for equity, property and currency, and the own-funds losses under the two interest rate shocks from your own revaluation.
Enter your bonds and loans line by line, each with its credit quality step (0 to 6, or Unrated) and modified duration. Leave government bonds out: they carry no spread charge. If a line runs beyond five years, the Checks sheet says so; use the full Article 176 table for it.
Type in the life stresses and the non-life volumes. The standard deviations are inputs because they depend on the segment: take them from Annex II for your line of business.
Read the SCR sheet from the top: the module charges, the Basic SCR, what diversification is worth, operational risk, then the ratio.
Keep the Checks sheet. The identities hold for any inputs; the worked case rows stop matching once you change anything, which is expected.
Questions people ask about it
Is this Solvency II SCR calculator really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
How is the SCR calculated under the standard formula?
Each risk is charged on its own, then charges are aggregated inside each module with a correlation matrix: the module charge is the square root of the sum, over every pair, of correlation times charge times charge. The five modules are aggregated the same way into the Basic SCR. Operational risk is added, capped at 30% of the Basic SCR, and the loss-absorbing capacity of technical provisions and deferred taxes is deducted. The solvency ratio is eligible own funds over the result.
Is this a regulatory calculation?
No. It is a teaching simplification. The aggregation follows the Delegated Regulation, but several charges are typed in rather than rebuilt, spread risk covers durations up to five years only, and non-life is a single segment. Use it to understand and test the arithmetic, not to file a return.
Does it use macros or circular references?
No macros and no circular reference: every charge flows one way, from the inputs to the modules to the SCR. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Can I use it for a real insurer?
You can reuse the structure and the formulas. Northgate Mutual and every figure in the worked case are fictional, and nothing here is regulatory or investment advice.
The rest of the files
This calculator sits alongside the companion files for Insurance Capital for Asset Managers: six workbooks that carry the book’s own figures, from the charge on any asset to Annex IV aggregation and the marginal capital an investment costs, plus five blank models, thirteen working documents to print, forty self-marking questions and three cases the book does not work. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
This template comes from Insurance Capital for Asset Managers. The book is on Amazon.
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.