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.

Matt says

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))

Naiyer Jawaid says

Matt, unfortunately this is not the correct solution. 🙂 Wait for update.

Matt says

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.

Naiyer Jawaid says

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.