A free significant risk transfer investor return model in Excel
The investor's side of a synthetic securitisation: a first-loss tranche on a bank's loan portfolio, coupons, write-downs and principal year by year, the IRR with and without fund leverage, the loss multiple that wipes out the coupons, and five stresses.
An investor buys 95% of the first 7% of losses on a EUR 4 billion loan portfolio, at a 10.50% margin. At best-estimate losses the IRR is 10.39%, 16.6% with fund leverage. Losses would have to run at 4.2 times the best estimate to cancel the coupons.
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.
SRT_Investor_Return_Model.xlsx (40 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 model goes with Significant Risk Transfer, which explains the method behind it, 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: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
5 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.
Investor
The tranche, the margin and the portfolio; coupons, write-downs and principal year by year; IRR with and without leverage.
Break-even
The loss multiple at which coupons, or the margin alone, are cancelled by losses.
Stress
Best estimate, regulatory parameters, mild and severe recessions, a stressed LGD and a 2008-style case.
Holding period
The return over a shorter or longer hold, pro rata or sequential amortisation.
Controls
Cash flows reconciled and the book's figures recomputed.
What the worked case returns
Measure
Value
Portfolio exposure
EUR 4,000,000,000
Detachment point
7.0%
Margin
10.50%
Notional invested
EUR 266,000,000
IRR at best-estimate losses
10.39%
IRR with fund leverage
16.60%
Share of the tranche consumed
11.8%
Loss multiple at which coupons and losses cancel
4.17
IRR, severe recession
2.56%
IRR, 2008-style stress
-38.09%
A first-loss tranche is leveraged to the portfolio's losses: each point of extra loss rate on the pool costs the tranche about 14 points. That is the whole risk of the trade, and why the stress sheet matters more than the base case.
How to use it on your own case
Enter the portfolio size, detachment point, share sold, margin and best-estimate loss rate on Investor.
Set replenishment, amortisation, fund leverage and the holding period.
Read coupons, write-downs and principal, and the IRR with and without leverage.
Read the break-even loss multiple and run the stresses.
Questions people ask about it
What is a significant risk transfer?
A bank sells the credit risk of a slice of a loan portfolio, often the first losses, to investors, which lets it hold less regulatory capital against the portfolio.
What return do SRT investors earn?
A margin over a reference rate on the tranche, less the losses that write it down. The file computes the IRR at best-estimate losses and under stress.
Why is the first-loss tranche so sensitive?
Because it absorbs the first losses of a much larger portfolio. A small rise in the pool's loss rate is a large share of the tranche.
Is this template really free?
Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Go further
The free workbooks of Significant Risk Transfer go further: the full companion set, blank models and the worked cases of the book.
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.
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.