A free NAV facility model in Excel: eligible NAV, LTV and the second decline
Reported NAV to lending value with exclusions, haircuts and reserves, a $120 million facility on an eight-company fund, a downside by bucket, and the further decline that breaches: seven sheets, no macros.
The file underwrites a NAV facility the way a lender does. First, a reported NAV of $800 million becomes an eligible NAV of $650 million once a restricted asset is excluded, a concentrated one haircut and a tax reserve taken: a $130 million exposure is a 20% LTV, not 16%. Then Atlas, a fund with $620 million of NAV in eight companies, borrows $120 million at a 19.4% LTV. After the chapter's downside its NAV falls to $455 million and the LTV to 26.4%; a further 10% decline takes it to 29.3%, just inside the 30% line.
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.
NAV_Facility_LTV_Model.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.
Go further: subscription lines, NAV facilities and what they do to returns
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: 10 paperbacks by Julian R. Sterling on real estate and housing, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Seven sheets. Every figure the chapters state is on the Assumptions sheet.
0. Read Me
What the file computes, the figure in the book that does not reconcile, and what it does not do.
1. Assumptions
Both cases: reported NAV, exclusions, haircuts, reserves, the facility and its uses, the downside haircuts and the LTV line.
2. Eligible NAV
From reported NAV to lending value, and the LTV on each.
3. Atlas, base case
The portfolio, concentration, the facility and its uses, uncalled capital and the base LTV.
4. Atlas, downside and 5. The second decline
The three haircuts by bucket, the reconciliation with the book, the LTV after a further decline, and the NAV at which the line is breached.
6. Checks
The checks against the book.
What the worked case returns
Measure
Value
Reported NAV to eligible NAV
$800m to $650m
LTV on eligible NAV against reported
20.0% against 16.3%
Atlas NAV and facility
$620m, $120m
Two largest companies, share of NAV
48%
LTV, base case
19.4%
NAV after the downside
$455.3m
LTV after the downside
26.4%
LTV after a further 10% decline
29.3%
NAV at which LTV reaches 30%
$400m
The file prints a discrepancy rather than hiding it: the chapter states a downside NAV of about $431 million, but its own haircuts on its own buckets give $455 million. On the book's figure the second decline breaches the 30% line; on the arithmetic it holds by 0.7 points.
How to use it on your own fund
Enter your reported NAV, exclusions, haircuts and reserves on 1. Assumptions.
Read eligible NAV and the LTV on it.
Enter your portfolio buckets and the downside haircuts.
Read the NAV at which the LTV reaches your line, not just the LTV today.
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.
What is a NAV facility?
A loan to a fund secured on the net asset value of its portfolio, rather than on investors' uncalled commitments. Lenders size it on an eligible NAV after exclusions and haircuts, and test the loan to value under downside cases.
Can I use it for a real fund?
You can reuse the structure. The funds and every figure are fictional, and nothing here is investment or credit advice.
The rest of the files
This template is one of the companion workbooks for The Fund Finance Professional: the others cover the subscription line borrowing base, the coverage the book asks for and the IRR effect of a subscription line. 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.
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.