Free Excel templates

A free subscription line IRR calculator in Excel, with the worked case the chapter leaves open

The same asset with and without a capital call facility, a calculator from zero to eighteen months on the line, and the Northbridge components a report should separate: seven sheets, no macros.

The file measures what a subscription line does to a fund's IRR and to investors' cash. A fund buys an asset for $100 and sells it for $120 a year later: 20.00% and 1.20x without a facility. Put the purchase on a line for six months and call $102 at month six, and the IRR becomes 38.41% while the money multiple falls to 1.18x. Investors receive $18 instead of $20, and the headline is 18.4 points better. Each of those points costs 0.11 per hundred invested.

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.

Subscription_Line_IRR_Calculator.xlsx (19 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 The Fund Finance Professional. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon →

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: 19 paperbacks by Julian R. Sterling on energy, infrastructure and land, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Seven sheets, in the order the calculation runs. Inputs sit on a pale yellow fill on the Assumptions sheet; everything else is a formula.

0. Read Me
The case, the headline result, what the file adds and what it leaves out (no fees, carry, preferred return or multi-asset netting).
1. Assumptions
Purchase and sale price, hold, months on the line and the capital called, with the all-in rate that call implies; then the four Northbridge inputs.
2. Without the facility
The investor's two cash flows, the money multiple, the annualised IRR and the net cash.
3. With the facility
The same asset with the line: the later, larger call, the shorter holding period, the IRR uplift, the multiple drag and the cost per point of IRR.
4. Calculator
The line run for 0, 3, 6, 9, 12, 15 and 18 months: interest, capital called, holding period, multiple, IRR, uplift and the cost of each point. The IRR stays blank once the holding period reaches zero.
5. Northbridge
120 million drawn for twelve months at a cost of 8 against 25 of appreciation: the net economic effect and the implied rate, with the operational benefit, the risk carried and the timing effect named but not priced.
6. Checks
Eight controls that test the case against the chapter and print their own verdict.

What the worked case returns

MeasureValue
Annualised IRR, without the line20.00%
Annualised IRR, six months on the line38.41%
IRR uplift18.4 points
Money multiple, without / with1.20x / 1.18x
Net cash to the investor, without / with$20.00 / $18.00
All-in rate implied by the $102 call4.00%
Cost per point of IRR, 3 / 6 / 9 months on the line0.17 / 0.11 / 0.05
Annualised IRR, nine months on the line84.24%
Northbridge: net economic effect / implied rate$17.00m / 6.67%

Read the IRR and the multiple together. The asset did the same thing in both cases; only the date of the call moved, and the interest is the price of moving it. The calculator shows the uncomfortable part: the later the call, the cheaper each point of headline IRR becomes. A report that prints only the 38.41% is incomplete, which is why the Northbridge sheet separates appreciation, financing cost and timing effect instead of giving one verdict.

How to use it on your own case

  1. Open 1. Assumptions and overwrite the yellow cells: purchase price, sale price, total hold, months on the line and the capital called.
  2. Check the all-in rate the call implies. It is a result, not an input: change the capital called to change the rate.
  3. Read 3. With the facility against 2. Without the facility: the IRR uplift, the multiple drag and the cash given up.
  4. Use 4. Calculator to see how the uplift and the cost per point move as the line runs longer.
  5. Put your own facility drawn, months, cost and appreciation into the Northbridge inputs and read the net economic effect before the IRR.
  6. Keep 6. Checks: once you change inputs some controls stop matching the chapter, which is expected.

Questions people ask about it

Is this subscription line IRR 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 IRR computed?

Each case has two cash flows, one call and one distribution, so the IRR is the money multiple raised to one over the holding period in years, less one. No IRR function, no macros and no circular reference. The file recalculates in Excel, LibreOffice or Google Sheets.

Why does the IRR almost double while investors earn less?

The line shortens the investor's holding period from twelve months to six. 120 against 102 over half a year annualises to 38.41%, while the $2 of interest lowers the cash gain from $20 to $18. The IRR measures timing; the multiple measures money.

Can I use it for a real fund?

You can reuse the structure. Northbridge and every figure in the file are fictional, and nothing here is investment advice.

The rest of the files

This template is one of the companion files for The Fund Finance Professional: the full set adds Borrowing Base and Availability, NAV Facility Underwriting and The Coverage the Book Asks For, with blank models and three further cases. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from The Fund Finance Professional. 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.