A free LP secondary pricing model in Excel, with a complete worked deal
Price as a discount to NAV, distributions and remaining calls over time, MOIC and IRR, the return split between discount and appreciation, and a joint stress on delay and value: six sheets, one fictional interest, no macros.
The file prices one LP interest bought on the secondary market. A buyer is offered an interest in a mid-life buyout fund at 88 against a reported NAV of 100, a 12.0% discount, rebuilds the NAV bottom up at 104 and expects the portfolio to return 130 over four years. On the modelled distributions and calls that gives a MOIC of 1.45x and an IRR of 14.76%. Only 30.0% of the value created comes from the entry discount; the other 70.0% comes from company appreciation. With exits two years late and values 10.0% below plan, the deal still returns 1.33x and 8.80%.
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.
Secondary_Deal_Pricing_Model.xlsx (26 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.
Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview
The book behind this model
This file is one worked case from The Private Equity Secondaries Investor. 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).
LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo
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
Six sheets, in the order the calculation runs. Inputs are blue type on a pale fill and sit mostly on The Deal; black is a formula, and yellow marks an assumption that carries the answer.
0. Read Me
The case in one paragraph, what each sheet does and the colour convention.
1. The Deal
Reported NAV, price, underwriting NAV, expected total value and duration; the distribution profile year by year with the remaining capital calls; optional deferred price and leverage; and the net cash flow to the buyer from year 0 to year 8.
2. Return
MOIC and IRR on the modelled cash flows, tested against the ranges the chapter states. Below it, the Chapter 8 duration table (85 paid for 100 returned over one to five years) and a solver for the price and discount your own hurdle allows.
3. Decomposition
The value created split into the part that comes from the entry discount and the part that comes from company appreciation, with the appreciation already visible in the underwriting NAV shown apart. A sensitivity shows what the return needs from the companies.
4. Stress
Exits delayed and exit values cut together, on both readings of "the exits slip by two years": every distribution two years later, or the fund taking six years instead of four. The stressed MOIC and IRR, and whether they still clear a hurdle.
5. Checks
Every figure Chapter 9 and Chapter 8 state, set against what the model computes, with a status on each line.
What the worked case returns
Measure
Value
Price against reported NAV
88 for 100 (12.0% discount)
Discount to the underwriting NAV of 104
15.4%
Value expected back, net of calls
128 over 4 years
MOIC
1.45x
IRR
14.76%
Value created from the entry discount
12 of 40 (30.0%)
Value created from company appreciation
28 of 40 (70.0%)
Stressed MOIC, exits 2 years late, values 10.0% lower
1.33x
Stressed IRR, stretched reading
8.80%
Stressed IRR, delayed reading
5.81%
The headline discount is 12 points; the value created is 40. Most of the return depends on the companies performing, not on the price paid below the marks, so the diligence budget belongs on the two or three assets that decide the outcome. The stress keeps 1.33x and 8.80% on the stretched reading, which clears the 8.0% hurdle set for a diversified, near-harvest interest: that is why the buyer in the book bids with conviction. The duration table on 2. Return makes the same point from the other side: 85 paid for 100 is 1.18x in every row, but 17.65% over one year and 3.30% over five.
How to use it on your own case
Open 1. The Deal and overwrite the blue cells: reported NAV, price, your underwriting NAV, the total value you expect back and the duration.
Set the distribution profile from your bottom-up view of when the main companies will be sold, and enter the remaining calls. Both check lines under the profile must read OK.
Leave the structure block at zero for the plain case, then add a deferred payment or leverage to see how far it bends the return.
Read 3. Decomposition before 2. Return: it tells you whether the discount or the companies carry the deal, and so where the diligence goes.
Set your own delay, value cut and hurdle on 4. Stress, and use the hurdle solver on 2. Return to see what price your required return allows.
Expect 5. Checks to go out once you change inputs: it matches the book only on the book's assumptions. Keep its structure for your own tie-outs.
Questions people ask about it
Is this secondary pricing model 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 iterative calculation?
No macros and no circular reference. The IRR is computed on the annual net cash flows from year 0 to year 8, and the file recalculates in Excel, LibreOffice or Google Sheets.
Why does the hurdle solver give two prices so far apart?
For a 15.0% return over four years on 130 of value, a single payment at the end allows a price of 74.33, a 25.7% discount. The same value returned evenly over the period allows 92.79, a 7.2% discount. The shape of the cash flows is worth more than several points of headline discount, which is why the model projects distributions year by year.
Can I use it for a real transaction?
You can reuse the structure. The fund, the interest and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for The Private Equity Secondaries Investor: the full set adds the bottom-up NAV rebuild, the bid and floor workbook, the thirty-item underwriting checklist and the interview and case prep tracker. 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 Private Equity Secondaries Investor. 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.