A free cap rate and exit yield calculator in Excel, with a complete worked sale
Rent roll, income account, six entry yields, the sale, an exit yield sensitivity from 5.50% to 8.50% and the waterfall: ten linked sheets, one fictional estate, no macros.
The file prices the sale of Bracken Wharf, a multi-let industrial and office estate of ten units bought for 26,400,000, and works out which entry yield an exit yield (the exit cap rate) can honestly be compared with. Of six entry yields it computes, only one is comparable: stabilised net income of 2,229,184 over all-in capital of 31,595,200, which is 7.06%. Sold at a 6.65% exit yield on year-eleven net operating income of 2,453,319, the estate is worth 36,892,021 gross; the property earns 6.82% and the equity 8.66%. The promote of 1,748,716 exists only because the yield moved 0.41 points: it first appears at an exit yield of 6.99%.
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.
Cap_Rate_and_Exit_Yield_Model.xlsx (49 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 project or fund? The Pro Editions go further: Development (60 months, S-curves, senior and mezzanine loans, JV waterfall), $99 → · Fund (up to eight assets, European or American waterfall), $129 →
The book behind this model
This file is one worked case from Commercial Real Estate Investing. 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).
Real Estate Fund Model, Pro Edition: a closed-end fund with up to eight assets, fees, European or American waterfall, 30 built-in checks, a blank copy and a 19-page guide. $129 → · See what is inside
REPE Interview Prep Pack: 18 real estate PE interview cases solved step by step, with three Excel models. $129 →
Real Estate Acquisition and Development Model, Pro Edition: monthly ground-up model over 60 months, S-curves, senior and mezzanine loans, bulk or unit sale, JV waterfall, 33 checks, a 14-page guide. $99 → · See what is inside
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Ten sheets, in the order the calculation runs. Inputs are blue figures on a yellow ground and sit on Inputs (plus the two yellow cells on Other exits); everything else is a live formula.
Read me
What the workbook does, the two experiments to run first, and how the sensitivity is built.
Inputs
The price, acquisition costs and capital programme, the seven market assumptions (rental growth, void, letting fee, costs, management, exit yield, cost of sale), the debt and the manager's terms.
Rent roll
Ten units with area, passing rent, rental value and expiry. The re-let rent is derived by rule, and the vacancy, reversion and stabilised net income are read off the roll.
Income account
Eleven years of rent unit by unit, with the nine-month void and the letting fees. Year eleven is there because the sale is priced on it.
Yields
Six entry yields, each with the reason it is or is not comparable with an exit yield, and the like-for-like entry yield derived line by line.
Cash flows
The sale on year-eleven net operating income, what pricing on year ten would cost, the property and equity flows, and the returns by the IRR function.
Exit yield
Twelve exit yields from 5.50% to 8.50%, plus the like-for-like entry yield and the yield at which the promote appears: gross value, net proceeds, property, equity and investor returns, multiple and promote on each row, with the flows behind every IRR.
Waterfall
European, whole of fund: 8% preferred return on unreturned capital, capital back, full catch-up, then 80/20, year by year, with what each party ends with.
Other exits
The sale priced on year ten, any basis year and exit yield you type, and the exit yields at which the catch-up completes and the promote first appears.
Checks
Every figure the book prints set against what the workbook computes, with a verdict on each line: 61 pass, none fail.
What the worked sale returns
Measure
Value
Headline yield: passing rent over price
5.97%
Like-for-like entry yield: stabilised net income over all-in capital
7.06%
Exit yield, central case
6.65%
Gross value at exit, on year-eleven net operating income
36,892,021
Net proceeds after 1.25% cost of sale
36,430,871
Property return, unlevered
6.82%
Equity return, levered, and multiple
8.66% (2.17x)
Promote at the central case
1,748,716
Exit yield at which the promote first appears
6.99%
What pricing the sale on year ten would cost, gross
3,528,768
Set the exit yield of 6.65% against the like-for-like entry yield of 7.06%, not against the 5.97% on the particulars: the market repriced the estate by 0.41 points between purchase and sale. Sell at the yield it was bought at and the promote is zero, the equity earns 7.86% and the investor finishes below the 8% hurdle. The year the sale is priced on matters as much: year ten carries a void, and capitalising it would take 3,528,768 off the gross price.
How to use it on your own case
Open Inputs and overwrite the blue cells: price, acquisition costs, the capital programme, the seven market assumptions, the debt and the manager's terms.
Replace the rent roll with your own units, areas, passing rents, rental values and expiry years. Re-let rents, voids and letting fees follow by rule.
Read Yields before anything else. Compare your exit yield with stabilised net income over all-in capital, not with the headline yield.
Walk down Exit yield. Only the flow of the sale year changes from row to row, so each row shows what one yield assumption is worth.
On Other exits, type a basis year (10 or 11) and an exit yield in the yellow cells to test a sale priced on a void year or a softer market.
Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this exit yield calculator 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.
Is the exit yield the same as a cap rate?
Yes: it is the capitalisation rate applied at the sale. Gross value is the net operating income of the year after the hold divided by the exit yield, so 2,453,319 at 6.65% gives 36,892,021, and 1.25% cost of sale leaves 36,430,871. A letting fee is a one-off payment and is never capitalised.
Why is the sale priced on year eleven and not year ten?
A buyer pays for the income it will receive. Year ten is the year Tannoch Foods re-lets, so it carries a void; capitalising it prices that void into perpetuity. On year ten's 2,218,656 the gross value would be 33,363,253, the same as an exit yield of 7.35% on year eleven. The file has no macros and no circular reference: each sensitivity row carries its own flows and the returns are the IRR function over them.
Can I use it for a real acquisition?
You can reuse the structure. Bracken Wharf, its tenants and every figure in the file are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Commercial Real Estate Investing: the full set adds The Income Account, Attribution (the equity return taken apart four ways) and The Waterfall. 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.