Free Excel templates

A free secondaries discount to NAV calculator in Excel

A secondary price quoted as a discount to net asset value is a discount to an appraisal. The file unsmooths the appraisal series, then restates the discount against spot value, against everything the buyer deploys and net of the fees still to pay.

A fund interest is bought at an 18% discount to reported NAV. Unsmooth the appraisals and it is 14.4% to spot value; count the unfunded commitment and it is 13.0%; take off the fees to the end of the fund's life and it is 9.18%. About 49% of the headline discount was never there.

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.

Secondaries_Discount_to_NAV_Calculator.xlsx (27 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 Real Estate Secondaries Investor, which explains the method behind it, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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).

Get the Library, $79 →

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

7 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Price, reported NAV, unfunded commitment, alpha, discount rate and fees.
Unsmoothing
The appraisal series turned into an estimate of spot value.
The four discounts
Against reported NAV, against spot value, against all capital deployed and net of fees to term.
Thresholds
The alpha and the unfunded commitment at which the discount is worth nothing.
Alpha and lag
How the result moves with the speed at which appraisals follow the market.
Bridge and margin
From the headline discount to the discount actually paid.
Checks
Every figure the book prints, recomputed.

What the worked case returns

MeasureValue
Discount to reported NAV18.0%
Appraisal above spot value4.36%
Price of the interest (EUR m)31.57
Spot value of the interest (EUR m)36.89
Discount to spot value14.42%
Discount on all capital deployed13.01%
Present value of fees to term (EUR m)1.57
Discount net of fees to term9.18%
Points between the headline and the deal8.82%
Alpha below which the discount buys nothing0.1317

The carrying assumption is alpha, how fast appraisals follow the market. Below 0.132 the unsmoothed value falls to the price and the discount buys nothing: that is the number to argue about first.

How to use it on your own case

  1. Enter the price, reported NAV, unfunded commitment and the appraisal series on Inputs.
  2. Set alpha, the speed at which appraisals follow the market, and the discount rate.
  3. Read the four discounts side by side on The four discounts.
  4. Read the thresholds: the alpha and the unfunded commitment at which the discount disappears.

Questions people ask about it

What does a discount to NAV mean in secondaries?

The buyer pays less than the fund's reported net asset value. But NAV is an appraisal, which lags the market, so the discount to real value can be much smaller.

Why unsmooth the appraisal series?

Appraisals move slowly and smooth out falls. Unsmoothing estimates the value the market would put on the assets today.

Does the unfunded commitment change the discount?

Yes. The buyer also takes on the commitment, at par. Measured on everything deployed, the discount shrinks.

Is this template really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Real Estate Secondaries Investor go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Real Estate 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.