In this article we’ll learn what is irr in excel and how to build an irr excel calculator.

Excel formula for IRR is one of the financial formulas in Excel which is used to calculate Internal Rate of Return. Let’s see how to use the formula.

What is IRR in Excel?

IRR is used to calculate rate of return of an investment basis forecasted cash flows. The calculation is used to determine the best project to invest in.

Example: There are two investment opportunities- launching a new product or refining existing product lines. We’ll forecast the future cash flows and calculate IRR for both the opportunities.
Let’s say New Product Launch’s IRR is 80% and Refining Existing Product’s IRR is 55%. Then we’ll choose the one with the highest IRR and Net Present Value.

IRR Excel Formula

To calculate internal rate of return in excel, we’ll use the IRR formula. Here’s the syntax:

=IRR(Values,[Guess])

Values: This is the series of forecasted cash flows which includes both net income along with investments.

Guess: This is an optional argument. You can guess and tell excel according to you what might be the value of IRR. The default value is 10%.

IRR Excel Calculator

Let’s see how to calculate irr in excel. Here’s a sample dataset.

Type =IRR(B2:B8) and hit enter.

You can see internal rate of return in excel comes out as 29%. This is how you can build an irr excel calculator.

Note:
1. Initial Investment is always a negative value as that’s cash outflow for us. Rest cash inflows are positive values.
2. IRR interprets cash flows according to the order so make sure the cash flows are entered according to proper order in years
3. If the system is unable to calculate internal rate of return in excel after 20 iterations/tries, you’ll get #NUM! error.

Calculate CAGR using IRR Excel Formula

We can also calculate CAGR from formula for irr in excel. Although IRR function is not designed to calculate CAGR but we can tweak it a bit.

Type =IRR(B2:B10) and press enter.

We’ll get 37% CAGR.

Note:
1. The first investment value should always be negative.
2. The ending value of investment should be positive.
3. All values between start and end values should be zero.

Calculate Modified Rate of Return in Excel | MIRR

We use Modified Rate of Return formula in excel when there are different borrowing rate of reinvestments.
The difference between MIRR & IRR is that-
IRR assumes the cash inflow will be reinvested at same rate as it generates. While MIRR assumes that the cash inflows will get reinvested at an external rate of return.
Here’s the syntax:

=MIRR(Values, Finance_Rate, Reinvest_Rate)

Values: Series of forecasted cash flows.
Finance Rate: Is the borrowing rate
Reinvest Rate: Is the rate at which the cash inflows will be reinvested

Here’s a sample dataset.

Type =MIRR(B2:B8,E2,E3)

You can see the MIRR comes out to be 25% while IRR comes out to be 29%

Conclusion

Excel formula for irr is used to calculate rate of return of an investment basis forecasted cash flows

I hope this article was helpful to you and explained you formula for irr in excel, calculating CAGR using irr function and calculating MIRR in excel.

If you have any questions or tips, I’d love to hear them in the comment section below.