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:

1. 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.
2. 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.

Click to download?

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

Learn More