How to Calculate the Internal Rate of Return (IRR)

by Landon M. Scott on May 17, 2011

*A quick note to the reader: I have decided that my posts
won’t evolve like an academic course from the basic concepts to the more
advanced in deference to the practitioner who scarcely wants to read another post on, for example, how to calculate a CAP rate (Capitalization Rate). I will get to these more elementary topics, but not necessarily first. You can search for certain terms/metrics using the search bar to the right of every page on this site or through the Category drop down menu.

Is there an equation for the Internal Rate of Return (IRR)?

The short answer is that there is no equation for IRR. The Internal Rate of Return, or IRR, is a common metric in commercial real estate and finance. In fact, after the CAP rate (Capitalization Rate), it is the most widely used metric to measure the performance of income properties. But few of those that use the Internal Rate of Return (IRR) in real estate know how to calculate it.

But don’t feel relatively uninformed if you are in the 90%. I took a course at UCLA Extension from an individual who was the former president of the CCIM (Certified Commercial Investment Member) association. The Internal Rate of Return (IRR) was one of the topics he covered. To my surprise, he told us that he didn’t know, nor is really that interested in, how IRR actually gets calculated or what the “equation” for IRR is; after all, he’s not a mathematician, he conceded, and just uses the Excel function “=IRR()” to calculate it when needed.

A word of wisdom for the beginner in commercial real estate: mastering vocabulary is necessary to discuss and operate in the world of income properties, but it is not sufficient to properly evaluate properties with cash flow and adequately represent yourself or your clients. So that we may prevent you from becoming another overly confident acronym-spewing real estate agent, let’s take a look at how the Internal Rate of Return gets calculated.

The Internal Rate of Return (IRR) is the discount rate which yields a zero Net Present Value (NPV) for any given stream of cash flows. Please see this post on NPV for a detailed look at how NPV gets calculated. There is no equation for IRR; digest it, remember it, come to terms with this core fact. IRR is found through perturbation, or the adjustment of a single element (in this case the discount rate) in the equation for NPV until a satisfactory answer is given (in this case zero).

You can think about it as backing into a zero NPV by adjusting the discount either up or down. In Microsoft Excel, you can find the IRR by using the Solver Add-In, Goal Seek or by the Excel formula (not to be confused with a mathematical equation) “=IRR(array)”, where “array” is a stream of cash flows greater than one with a negative value as the first cash flow. For a guide to using solver, go to http://chandoo.org/wp/2011/05/11/using-solver-to-assign-item/. Chandoo is a great source for all things excel.

Let’s take a look at using solver to solve a real example:

Here we see that the IRR for the cash flows (200,000); 13,468; 12,591; 11,767; 10,993; and 166,309 is 13.87%. We found this using Excel’s IRR formula, =IRR(C11:H11). Excel is simply hypothesizing a discount rate and then checking to see if it yields a zero NPV. If not, it adjustes the discount rate up or down until zero NPV is found. Once found, it displays that discount rate as the IRR. We can do the same thing using Solver:

So all we are doing is telling Solver to “Set Target Cell” to the Net Present Value so that a “Value” of zero is found “By Changing Cells” in which the discount rate is. If Solver can find a solution, it will change the discount rate until zero NPV is found. Did it work?

Et Voila! Net Present Value reads zero and the discount rate which achieved that is exactly the same rate that Excel gave us through the IRR formula. And that’s all there is to it – you just calculated IRR and passed where the finest CCIM practitioners fear to tread.