IRR vs. MIRR Formulas in Excel

September 2, 2016
As investors, we have become (perhaps overly) reliant on the returns spit out by our trusty Excel models. However, are we, perhaps, a bit too trusting of our Excel models? Re-used and modified from project to project, how can we be certain that all of the various cells and formulas are correctly highlighting the key components of our investment future?

There may be no more critical metric in a commercial real estate pro forma than the all-important IRR calculation. As one of the primary litmus tests of a project’s feasibility, the overall IRR has become a benchmark indicator within the commercial real estate investment community.
Given its importance, should an investment project be evaluated based on Excel’s IRR or MIRR formulas?

Initially, the returned rate between IRR and MIRR may not vary a great deal. Because the reinvestment rate represents the amount of interest earned when money moves out of one investment and put into another, it is important to look at this variable when assessing the correct, or actual, returns provided by an investment opportunity. Therefore, the MIRR calculation is more appropriate in a real life application because it incorporates a stated reinvestment rate into the calculated return.
The problem with the IRR calculation is its assumption about cash generated during an investment. The IRR calculation presumes that the money produced by the investment reinvests at the rate calculated by the IRR. For projects expected to generate a lot of cash during an investment, the IRR calculation, therefore, can overstate the financial benefits significantly. This assumption of the reinvestment rate is why the MIRR calculation seems more appropriate for this real estate analysis. We can apply a reinvestment rate, noting that as the reinvestment rate increases, so does the MIRR of the project.