IRR Calculation

IRR calculation

The IRR calculation is one of the most important calculations when evaluating the profitability of a property investment. The term IRR stands for Internal Rate of Return and measures the average periodic rate of return of a property investment in percentage terms. This is the most suitable investment return measure because it is time-weighted, as it takes into account the specific timing of the expected cash flows. An IRR calculation of 10% using annual after-tax cash flows means that the property investment is expected to provide an average annual (not cumulative) return of 10% over the holding period for which the analysis is performed.

 

IRR Calculation

The mathematical formula for calculating the IRR with cash flows over several periods is complicated because it is non-linear. It is calculated as the discount rate that makes the net present value of all net cash flows of the investment, from the time of its purchase until its sale, equal to zero. Given the complexity of the mathematical formula, especially when the analysis includes many periods, we will not elaborate further on it.

Today the IRR calculation can be done automatically using calculators or computers, given that the net cash flow of the property in each period included in the analysis has been calculated. See our posts on Property Investment Analysis: The Discounted Cash Flow Model and Property Investment Basics: Operating Statement for a more elaborate discussion of how the net cash flow of each period is calculated. Once such calculations are completed, then the IRR can be calculated in Excel using the “IRR” function.  In particular, if you type in a cell:

 =IRR(range of cells with net cash flows over holding period)

The range of cells with net cash flows” in the above formula includes the sequential cells that have the expected values of the periodic net after-tax cash flows of the property over the planned holding period of the investment.

When using this function in Excel, but also in an IRR calculation, you need to keep in mind that the periodicity of the estimated IRR is the same as the periodicity of the cash flows used for its calculation.  For example, if the calculation is based on monthly or quarterly cash flows, then the estimated IRR will represent the expected monthly or quarterly return, respectively.

If you want to estimate the implied annual return from the quarterly estimate of the IRR you can use the following formula to do this:

 Annual IRR = (1+Quarterly IRR)4  – 1

One of the significant unknowns in the calculation of the internal rate of return is the property’s resale price at the end of the holding period. The most commonly used technique for the estimation of this resale price is the forecast of an “exit” cap rate which is applied to the predicted Net Operating Income (NOI) of the last year of the holding period. The exact formula applied is:

Resale Price =  NOI/Exit Cap Rate

Usually, but not necessarily,  the assumed exit cap rate is higher than the entry cap rate in order to reflect the uncertainty of future cash flows, but also the fact that the property will be older when it is sold by as many years as it was held.  If the investor has solid reasons to believe that market cap rates at the time of resale will be lower due to much stronger market conditions, then a lower cap rate can be used with caution, given that the aging of the building over the holding period has been taken into account. The entry cap rate is calculated as the ratio of the property’s actual NOI over its acquisition price.

One of the issues with the IRR calculation is the reinvestment assumption. In other words, the IRR is calculated with the assumption that all positive net cash flows produced by the property are reinvested from the period they are received up to the end of the holding period at the same rate as the estimated IRR.  For low and relatively reasonable IRR estimates that reflect achievable returns in the marketplace, this does not create any serious miscalculations of the average return implied by the cash flows used to calculate such an IRR. The problem arises when unrealistically high IRRs are estimated, the magnitude of which is even further exaggerated by the reinvestment assumption. In such a case, it is advisable to estimate the Modified Internal Rate of Return (MIRR), which ensures that a reasonable reinvestment rate is used for all positive net cash flows over the holding period of the property.

The Modified IRR Calculation

If the estimated IRR is unrealistically high, then the estimated net cash flows of the property can be used to estimate the Modified Internal Rate of Return (MIRR) as follows:

  1. Calculate the future value of all positive cash flows at the end of the holding period using a reasonable reinvestment rate (usually the opportunity cost of capital)
  2. Calculate the present value of all negative cash flows using the cost of capital as the discount rate
  3. Calculate the MIRR as the discount rate the makes the future value of positive cash flows equal to the present value of negative cash flows. This is given by the formula below, where N represents the total number of periods included in the analysis:

MIRR = (Future Value of Positive Cash Flows / Present Value of Negative Cash Flows )1/N -1

References

Kolbe, P. T., & Greer, G. E. (Author), Gaylon E. Greer.  (2012). Investment Analysis for Real Estate Decisions, 8th  Edition. Dearborn Real Estate Education.

Sivitanides, P.  2008. Real Estate Investing for Double-Digit Returns. BookSurge Publishing.

Geltner, M., Miller, N. G., Clayton, J., & Eichholtz, P.  (2013). Commercial Real Estate Analysis and Investments (with CD-ROM). Oncource Learning

Clauretie, T. M., & Sirmans, G.S.  (2009). Real Estate Finance: Theory and Practice 6th EditionOncource Learning.

Relevant Posts

Internal Rate of Return (IRR) and Property Investment
Property Investment Basics: Real Estate Return Measures
How to Estimate Future Property Value
Property Investment Loans
Property Investment Basics: Before-Tax Cash Flow

 

Author: SPI Team

13 thoughts on “IRR Calculation

  1. Pingback: Sources of Real Estate Risk – Smart Property Investment
  2. Pingback: Net Operating Income (NOI) – Smart Property Investment
  3. Pingback: What is Cash-out Refinance? – Smart Property Investment
  4. Pingback: Free Spreadsheet for Calculation of Before-Tax Cash Flow for Apartments – Smart Property Investment
  5. Pingback: Investing in Office Property the Smart Way – Smart Property Investment
  6. Pingback: What is the Payback Period of your Property Investment? – Smart Property Investment
  7. Pingback: What is the time-weighted return of your property investment? – Smart Property Investment
  8. Pingback: Real Estate Risk is key to Evaluating a Property Investment
  9. Pingback: Payback period of property investments: How to calculate it
  10. Pingback: Discounted cash flow (DCF) model: The key tool for property investment analysis
  11. Pingback: Net Operating Income: Make sure you calculate it correctly
  12. Pingback: Loan-to-Value Ratio and Property Investment - Smart Property Investment
  13. Pingback: Return on Equity: The Most Important Return Measure when Using a Loan

Comments are closed.