A free venture debt model in Excel: one loan, its warrants and the fund
A $15 million commitment in two tranches with interest-only months, fees, a prepayment and warrants valued by Black-Scholes, the borrower's runway, the lender's realized IRR, a default and recovery, and the fund's net return: ten sheets, no macros.
The file follows a fictional venture lender. Larkspan, a software company, takes a $15 million commitment at 10% (prime plus 2% with a floor): $10 million at closing and $5 million on an ARR milestone, 48 months with 18 interest-only. Fees and a final payment bring 29% of what the loan earns. Run to term the loan would yield 12.2%; prepaid early it yields 14.6%, and 18.5% with the warrants at their mark. Pallwick, the other case, defaults: the lender recovers 79% of principal.
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.
Venture_Debt_Model.xlsx (54 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 Venture Debt Lender. 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: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Ten sheets. Blue cells on Inputs are the only inputs.
Read Me and Inputs
Commitment, tranches, milestone, term, interest-only months, rate, fees, prepayment fees, warrant coverage and the fund terms.
Larkspan Schedule
Monthly draws, interest, principal, fees and the prepayment.
Larkspan Runway and Rule of 40
Cash and runway before and after the loan.
Warrants
Warrant coverage, shares and Black-Scholes value at issuance and at the mark.
Larkspan Returns
Contractual and realized IRR, with and without warrants, and what fees contribute.
Pallwick Cash and Covenant and Pallwick Recovery
A borrower running out of cash, the default, the sale and the recovery.
Fund Economics and Checks
Interest, fees, losses and warrants to the LPs' net return, and the checks.
What the worked case returns
Measure
Value
Commitment
$15m (Tranche A $10m, B $5m)
Rate
10% floor, prime + 2%
Runway: before and after Tranche A
24 and 30.6 months
Contractual IRR, no warrants
12.2%
Realized IRR with the prepayment
14.6%
Realized IRR with warrants at their mark
18.5%
Share of earnings from fees
28.7%
Pallwick: recovery on principal
79.3%
Fund: net return to LPs
8.5%
Most of the lender's upside is not the coupon. The prepayment adds 2.4 points and the warrants another 3.9, so the loan's realized return depends as much on the borrower raising its next round as on it paying interest.
How to use it on your own loan
Enter commitment, tranches, rate, fees and warrant coverage on Inputs.
Read the schedule and the borrower's runway.
Read contractual against realized IRR on Larkspan Returns.
Run the Pallwick sheets for the downside and the fund sheet for the portfolio.
Questions people ask about it
Is this template 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.
Does it use macros or circular references?
No macros and no circular reference. Warrants are valued with a closed-form Black-Scholes formula.
How do venture lenders make money?
From interest, fees (upfront, final payment, prepayment) and warrants. Here fees are 29% of what the loan earns before warrants, and the warrants add almost four points of IRR.
Can I use it for a real loan?
You can reuse the structure. The lender, the borrowers and every figure are fictional, and nothing here is investment advice.
The rest of the files
This template is the companion workbook for The Venture Debt Lender; the companion page also carries the other files that go with it. 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 The Venture Debt Lender. The book is on Apple Books and Google Play.
If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.