Free Excel templates

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.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
Portfolio exposureEUR 4,000,000,000
Detachment point7.0%
Margin10.50%
Notional investedEUR 266,000,000
IRR at best-estimate losses10.39%
IRR with fund leverage16.60%
Share of the tranche consumed11.8%
Loss multiple at which coupons and losses cancel4.17
IRR, severe recession2.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

  1. Enter the portfolio size, detachment point, share sold, margin and best-estimate loss rate on Investor.
  2. Set replenishment, amortisation, fund leverage and the holding period.
  3. Read coupons, write-downs and principal, and the IRR with and without leverage.
  4. 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.

See the book and its free files →

Other free templates

This template goes with Significant Risk Transfer. 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.