How to Calculate Internal Rate of Return (IRR) in Excel – A Step-by-Step Guide

The internal rate of return (IRR) is an important metric used by investors and companies to evaluate potential investments and projects. By calculating the IRR in Excel, you can easily estimate whether an investment opportunity is worthwhile and profitable.

In this comprehensive guide, I will explain what is IRR how to calculate it in Excel using the IRR function, modified IRR (MIRR) and IRR for irregular cash flows (XIRR). I will also compare IRR to other investment analysis metrics like NPV.

What is Internal Rate of Return (IRR)?

The internal rate of return is the annualized percentage return earned on an investment. It is the discount rate that makes the net present value (NPV) of a project zero.

In other words, IRR is the expected compound annual growth rate that an investment is projected to generate.

Here is the formula to calculate IRR:

IRR = r
Where:
r = Discount rate that makes NPV = 0

By finding the IRR, you can determine whether to invest in a project or not. If the IRR is higher than the required rate of return, the project is considered profitable.

For example, if a project costs $100,000 initially but is expected to provide $20,000 in year 1, $25,000 in year 2 and $150,000 in year 3, the IRR comes out to be 29%.

This means the project is projected to yield an average annual return of 29%, making it a good investment opportunity.

Key Benefits of Using IRR

Here are some of the key benefits of using IRR to evaluate investments:

• IRR accounts for time value of money – It considers the timing and size of cash flows.

• Provides a percentage return – IRR offers a standardized metric to compare different investments.

• Measures profitability – IRR indicates whether an investment will be profitable or not.

• Help ranking investments – Investments can be ranked based on their IRRs. Higher the IRR, the better the investment.

How to Calculate IRR in Excel

Excel has an in-built IRR function to calculate internal rate of return.

Here are the steps to use IRR in Excel:

1. Enter the initial investment and cash flows in consecutive cells – The initial investment should be entered as a negative value while cash inflows are positive values.

2. Use the IRR function – The syntax is =IRR(values, [guess]) where values is the range of cells with cash flows and guess is an optional estimated IRR.

3. Format the result cell as percentage – The calculated IRR will be displayed as decimal by default. Format it to percentage for better readability.

Let’s see an example.

The formula used is =IRR(B2:B7). It calculates an IRR of 18.43% for this investment.

This means the expected annual return is 18.43%, making it a good investment.

Calculate Modified IRR (MIRR) in Excel

The regular IRR has some limitations. It assumes you can reinvest positive cash flows at the IRR itself.

But in reality, the reinvestment rate may be different from the IRR.

This is where Modified IRR (MIRR) comes in.

MIRR allows specifying a finance rate and reinvestment rate separately.

Here are the steps to calculate MIRR in Excel:

1. Enter cash flows – Just like regular IRR

2. Determine finance and reinvestment rates – The finance rate is the cost of borrowing funds. The reinvestment rate is the expected rate at which interim cash flows can be reinvested.

3. Use the MIRR function – The syntax is =MIRR(values, finance_rate, reinvestment_rate)

For example, if the finance rate is 5% and reinvestment rate is 8%, the MIRR formula would be: =MIRR(B2:B7, 0.05, 0.08)

This provides a more realistic estimate of returns than regular IRR.

How to Calculate IRR for Irregular Cash Flows in Excel

The IRR function only works properly if the cash flows are periodic and spaced evenly apart.

If the cash flows are irregular or have unequal intervals, you should use the XIRR function instead of IRR. XIRR takes the timing of cash flows into account by accepting a range of dates as input along with the cash flow values.

Follow these steps:

1. Enter transaction dates in one column and corresponding cash flows in another.

2. Use the XIRR function with cash flows as the first argument and dates as the second.

For example, =XIRR(B2:B9,A2:A9)

This calculates IRR based on the dates and cash flows provided.

IRR vs NPV – Which Should You Use?

Both IRR and net present value (NPV) can be used to evaluate investments. But which one is better?

The short answer is that NPV is a more reliable method than IRR. Here’s why:

• IRR assumes reinvestment at the same high rates. NPV assumes reinvestment at the cost of capital.
• With multiple cash flows, IRR can give conflicting results vs NPV. NPV is more stable.
• IRR doesn’t indicate how big the total returns will be. NPV shows the absolute dollar value.

However, IRR is still helpful to estimate the annual percent return and as a supplementary metric along with NPV.

• Use NPV as the primary evaluation method.
• Calculate IRR as a secondary metric for estimating annual returns.
• If IRR and NPV results conflict, go with NPV.

Limitations of IRR

While IRR is popular, it does have some limitations to keep in mind:

• Assumes reinvestment at the same high IRR rate.
• Can result in multiple IRRs or no IRR for certain cash flow patterns.
• Doesn’t indicate total dollar returns, only the annual percent return.
• Prone to giving conflicting results vs NPV in some cases.
• Requires even cash flow intervals for the regular IRR formula.

When to Use IRR in Excel

Here are some examples of when calculating IRR can be useful:

• Comparing investment opportunities
• Estimating returns for capital budgeting decisions
• Ranking independent projects based on profitability
• Determining expected growth rates for valuation models
• Evaluating equity investments like venture capital opportunities

IRR is a helpful metric to estimate the expected annual returns generated by an investment. Excel makes it easy to calculate IRR using the in-built IRR, MIRR, and XIRR functions.

By finding IRR, you can assess the profitability of investments and projects. Used along with NPV, IRR provides an additional perspective for investment analysis and capital budgeting decisions.