Free Excel templates

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.

Get the book on Amazon →

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 →

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

The Pro Models Bundle: LBO, Development and Fund models, Pro Editions. $199 instead of $327 → · 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

MeasureValue
Headline yield: passing rent over price5.97%
Like-for-like entry yield: stabilised net income over all-in capital7.06%
Exit yield, central case6.65%
Gross value at exit, on year-eleven net operating income36,892,021
Net proceeds after 1.25% cost of sale36,430,871
Property return, unlevered6.82%
Equity return, levered, and multiple8.66% (2.17x)
Promote at the central case1,748,716
Exit yield at which the promote first appears6.99%
What pricing the sale on year ten would cost, gross3,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

  1. Open Inputs and overwrite the blue cells: price, acquisition costs, the capital programme, the seven market assumptions, the debt and the manager's terms.
  2. 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.
  3. Read Yields before anything else. Compare your exit yield with stabilised net income over all-in capital, not with the headline yield.
  4. 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.
  5. 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.
  6. 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.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Commercial Real Estate Investing. 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.