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.
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 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
Measure
Value
Annualised IRR, without the line
20.00%
Annualised IRR, six months on the line
38.41%
IRR uplift
18.4 points
Money multiple, without / with
1.20x / 1.18x
Net cash to the investor, without / with
$20.00 / $18.00
All-in rate implied by the $102 call
4.00%
Cost per point of IRR, 3 / 6 / 9 months on the line
0.17 / 0.11 / 0.05
Annualised IRR, nine months on the line
84.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
Open 1. Assumptions and overwrite the yellow cells: purchase price, sale price, total hold, months on the line and the capital called.
Check the all-in rate the call implies. It is a result, not an input: change the capital called to change the rate.
Read 3. With the facility against 2. Without the facility: the IRR uplift, the multiple drag and the cash given up.
Use 4. Calculator to see how the uplift and the cost per point move as the line runs longer.
Put your own facility drawn, months, cost and appreciation into the Northbridge inputs and read the net economic effect before the IRR.
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.
What is the all-in cost of a subscription line?A subscription facility priced the way a CFO pays for it: per dollar actually drawn, at five levels of utilisation, and against a smaller line.
Other free templates
LBO modelSources and uses to sponsor IRR, twelve sheets, every figure checked.
Real estate property modelRent roll, cash flow, debt and returns for one asset, empty and ready to fill.
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.