A free private credit fund model in Excel: from loan yield to net IRR
Capital called and returned, gross assets at the target leverage, interest and fee income, credit losses, borrowing cost, management fee, expenses and an incentive fee with a hurdle and full catch-up, and the bridge from loan yield to net return: four sheets, no macros.
The file runs a fictional 500 million direct lending fund, called over three years and returned over the last three, levered 1.0 times at the fund level. The loans earn 10.0% plus 0.7% of fees; defaults of 2% a year with 60% recovery cost 0.8%, so the assets return 9.9%. Leverage at 6.5% adds 3.4 points, for 13.3% on equity. The management fee and expenses take 1.45 points and the incentive fee another 1.48, leaving investors a 10.4% net IRR and 1.64 times their money.
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.
Private_Credit_Fund_Model.xlsx (15 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 The Private Credit Investor, which explains the method behind it, chapter by chapter.
Go further: private equity fund finance and controllership
Private Fund Finance & Controllership: capital calls and equalization, fees and offsets, both waterfalls, NAV and capital accounts, IRR and PME, fund financing and ILPA reporting, on one fictional fund. Course Book, Excel exercises that check your answers, a 160-question quiz and the Private Equity Fund Model, Pro Edition included ($199).
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
Four sheets. Inputs are blue cells on a pale blue fill on the Inputs sheet. Millions.
Read me
What the file does and its conventions.
Inputs
Commitments, the call schedule, returns of capital, leverage, base rate, spread, fees, borrowing cost, default and recovery, management fee, expenses, incentive fee and hurdle.
Fund
Eight years: capital called and returned, equity outstanding, gross assets, borrowing, income, fees, losses, borrowing cost, fees to the manager, net income, the incentive test and the LP cash flow.
Bridge and Checks
Loan yield to net return on equity step by step, net IRR and multiple; then the checks: all OK.
What the worked case returns
Measure
Value
Loan yield plus fees
10.7%
Credit losses
-0.8%
Unlevered return on assets
9.9%
Leverage effect at 1.0x
+3.4%
Levered return on equity
13.3%
Management fee and expenses
-1.45%
Incentive fee
-1.48%
Net IRR to investors
10.4%
Net multiple
1.64x
Leverage only helps while the assets earn more than the borrowing costs. Here the assets earn 3.4 points more than the borrowing costs. At 6% defaults a year with the same recovery, the asset return falls to 8.3% and that lead to 1.8 points: the levered fund keeps only about half of its advantage.
How to use it on your own fund
Enter commitments, the call schedule and leverage on Inputs.
Enter the loan pricing, defaults and recovery.
Enter the fund's fees and hurdle.
Read the bridge from loan yield to net return, then the net IRR.
Questions people ask about it
Is this template really free?
Yes. 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.
How does the incentive fee work here?
Each year the manager takes nothing until net investment income reaches a 6% hurdle on equity, then 100% of the income above it until it has 12.5% of the total (the catch-up), then 12.5% of everything.
Can I use it for a real fund?
You can reuse the structure. The fund is fictional and simplified, and nothing here is investment advice.
Go further
For underwriting the loans inside a fund like this and its economics, The Private Credit Investor and its companion workbooks go further.
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.