Free Excel templates

A free implied cap rate calculator for REITs in Excel, with a complete worked case

From the share price to the cap rate the stock market pays for the buildings: enterprise value, the assets that earn no income, forward NOI, the spread to private market and the discount to NAV. Six linked sheets, one fictional REIT, no macros.

The implied cap rate formula runs backwards from the share price. Take the market capitalization, add preferred equity, debt and other liabilities, take off cash and every asset that earns no NOI, and what is left is the value the stock market puts on the operating real estate. Divide forward NOI by it. Bayline Residential Trust, a fictional apartment REIT at $36.75 a share, has a market capitalization of $6,688.5 million and an enterprise value of $11,878.5 million; after $1,105.0 million of cash, land, construction, joint ventures and other assets, the buildings are priced at $10,773.5 million. On forward NOI of $601.1 million, that is an implied cap rate of 5.58%, 58 basis points above the 5.00% private buyers pay, and the same gap shows up as a 15.7% discount to a net asset value of $43.61 a share. Every one of those numbers is a formula you can move.

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.

Implied_Cap_Rate_Calculator.xlsx (18 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 REIT Analysis and Valuation. The book explains every assumption, every sheet and the decision the numbers support, 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 →

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 →

The formula

Implied real estate value = market capitalization + preferred equity + debt (at market value) + other liabilities − cash − assets that earn no NOI

Implied cap rate = forward NOI / implied real estate value

What is in the file

Six sheets, in the order the calculation runs. Blue type on a pale blue fill is an input; black type is a formula.

Read Me
What the file does, the choices that move the answer, and how to enter your own REIT.
Inputs
Share price and diluted share count, preferred equity, debt at face and its mark to market, other liabilities, cash, the five kinds of asset that earn no NOI, last quarter’s cash NOI with acquisitions, disposals, growth and a capital reserve, and the private market cap rate.
Implied Cap Rate
The bridge from market capitalization to enterprise value to the implied value of the buildings, forward NOI built from the last quarter, the implied cap rate, the spread to private market and the value per apartment unit both ways.
NAV
The same forward NOI capitalized at the private cap rate, walked down to net asset value per share and the premium or discount.
Sensitivity
The implied cap rate at five share prices, and NAV per share at five private cap rates. The steps are inputs.
Checks
Five identities that hold for any inputs, including the one that ties the two methods together, then the worked case. The file ends ALL OK.

What the worked case returns

MeasureValue
Share price$36.75
Market capitalization$6,688.5m
Enterprise value$11,878.5m
Implied value of the operating real estate$10,773.5m
Forward NOI$601.1m
Implied cap rate5.58%
Private market cap rate5.00%
Spread58 bp
NAV per share$43.61
Discount to NAV15.7%
Implied value per unit$279,831
Private market value per unit$312,244

Read the spread and the discount as one finding seen twice. The stock market pays $279,831 for each apartment the private market would value at $312,244; capitalized, that is 58 basis points of cap rate, and on the equity it is a 15.7% discount, because leverage concentrates the whole $1,247.9 million gap on $6,688.5 million of market capitalization. The two methods meet at one price: at $43.61 a share, net asset value, the implied cap rate is exactly 5.00%, and the Checks sheet tests that for any inputs. The sensitivity shows how much the spread moves with the stock: a 20% rally to $44.10 takes the implied cap rate to 4.96%, inside private market, and a 20% fall to $29.40 takes it to 6.37%.

How to use it on your own REIT

  1. Open Inputs and overwrite the blue cells with the latest balance sheet and the share count including operating partnership units.
  2. Decide what earns NOI. Land, construction in progress, joint ventures carried at book and loans receivable are deducted only if their income is not in your NOI; if you include your share of joint venture NOI, add your share of joint venture debt and do not deduct the investment.
  3. Mark fixed rate debt to market when rates have moved since it was issued. A below-market coupon makes the debt worth less than face, and the implied cap rate rises accordingly.
  4. Use the same NOI definition on both sides. If the private cap rate you quote is after a capital reserve, deduct the reserve here; if it is not, leave the reserve at zero.
  5. Keep the Checks sheet. The identities hold for any inputs; the worked case rows stop matching once you change anything, which is expected.

Questions people ask about it

What is the implied cap rate formula?

Implied cap rate = forward NOI divided by the implied value of the operating real estate, where that value is market capitalization plus preferred equity, debt and other liabilities, less cash and the assets that earn no NOI. It is the cap rate the share price pays for the buildings.

Why compare it with the private market cap rate?

Because the gap is the finding. An implied cap rate above the private rate means the stock market prices the same buildings cheaper than private buyers, which is a discount to net asset value seen from the property side. The two agree only when the share price equals net asset value per share.

Should debt be at face value or market value?

At market value when you can. Fixed rate debt issued at lower rates is worth less than its face value, and leaving it at face overstates the implied real estate value and understates the implied cap rate. The file takes the face value and a separate mark to market.

Is this 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. It has no macros and no circular reference.

Can I use it for a real REIT?

You can reuse the structure and the formulas. Bayline Residential Trust and every figure in the worked case are fictional, and nothing here is investment advice.

The rest of the files

This calculator sits alongside the companion files for REIT Analysis and Valuation: the full REIT valuation model and its blank template, two timed practice cases, a 54-item analysis checklist, a three-year run of the price and NAV feedback loop, forty self-marking questions and three unworked cases, plus thirteen printable working documents. 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 REIT Analysis and Valuation. 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.