IRR calculation in Excel can be done using three different formulae - IRR, XIRR and MIRR. In this post we will be discussing syntax and specific use of each formula.
If you want to understand the basic of IRR calculation in general, you must see this post NVP & IRR ? Key Metrics of a Feasibility Analysis.?
Syntax and Description
Below are the syntaxes and descriptions of each formula for the ?IRR calculation:
=IRR(values() [, guess ] )
Where values() is the array of cash flow values and guess is an optional variable specifying value you estimate will be returned by IRR. If omitted, guess is 10%.
=XIRR(values,dates, guess )
Where values() is the array of cash flow values and dates is the schedule of payment dates that corresponds to the cash flow payments. Guess is an optional variable specifying value you estimate will be returned by IRR. If omitted, guess is 10%.
=MIRR(values, finance_rate, reinvest_rate)
Where values() is the array of cash flow values. Finance_rate is the interest rate you pay on the money used in the cash flows or say it is your cost of capital. Reinvest_rate is the interest rate you receive on the cash flows as you reinvest them.
MIRR is a modification of the internal rate of return (IRR) and as such aims to resolve the following problems with the IRR calculation:
- IRR assumes that interim positive cash flows are reinvested at the same rate of return as that of the project that generated them. This is usually an unrealistic scenario and a more likely situation is that the funds will be reinvested at a different rate.
- Using IRR function, more than one IRR can be found for projects with alternating positive and negative cash flows. MIRR formula solves this issue and finds only one value.?
Examples
Now consider the following cash flow:
IRR calculation can be done by using Excel IRR function.
Here in this example we used IRR function for the IRR calculation and Excel IRR function assumes that the cash flow is annual.
However, consider a case where the cash flow is happening at different intervals during the year or take any other periodic cash flow:
IRR calculation can be done by using Excel XIRR formula.
We all know that IRR has some flaw (discussed above) and MIRR is a modification of the internal rate of return (IRR).
Consider the following cash flow:
Assume 7% finance rate and 9% re-investment rate.
Modified IRR calculation can be done by using Excel MIRR formula.
Challenge
Now a challenge for all of our readers! Can you tell me the modified internal rate of return for the following cash flows:
If you are wondering about calculating project IRR and Equity IRR, refer this post Project IRR and Equity IRR: A Curious Connection.
Hope you enjoyed this post on IRR calculation. What do you think, use the comment section below.
Check out more useful articles on Our Blog Page
Are you someone involved with real estate feasibility?
We are excited to launch the next generation of real estate feasibility software by April 2023.
claim your 15% early-bird discount
Know more about the product features
I used the following to calculate the MIRR in the challenge?is this correct? What is the correct solution?
=MIRR(C9:H9,EFFECT(C5,12),EFFECT(C6,4))
Matt, unfortunately this is not the correct solution. 🙂 Wait for update.
Thanks for the article.
I posted a comment / question a few minutes ago that seems to have been removed?
Anyway, my question now is why, when using the example spreadsheet you provided, does the MIRR not update when I change the annual interest rate on the loan but it does update when I change the interest rate on reinvestment?
Thanks.
Thanks Matt for your comments.
The comments were not deleted, they were in moderation queue.
I noticed that MIRR is not impacted by interest rate, I will look into this and post it here. Thanks for pointing this.