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 inbuilt IRR
function to calculate internal rate of return.
Here are the steps to use IRR in Excel:

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.

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. 
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:

Enter cash flows – Just like regular IRR

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.

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:

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

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 inbuilt 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.
Step 1: Enter Cash Flows and Dates
The cash flows for the real estate and startup investments arent disbursed at the same time each year, as was the case when calculating IRR and MIRR above. Instead, they occur at different periods. We use the XIRR function below to solve this calculation. Instead, youll enter the dates and cash flow amounts as above.
For investments with cash flows received or cashed at different times for a firm with varying rates of borrowing and reinvestments, Excel and Google Sheets dont have functions that can be used, though they are probably more likely to occur. But to keep things simple, we can certainly work with the above.
What Is the Internal Rate of Return (IRR)?
The internal rate of return (IRR) measures an investments profitability, taking into account the time value of money. Its the discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero. In simpler terms, IRR helps investors determine the rate of return they can expect to earn on an investment, considering the timing and size of cash inflows and outflows.
IRR is an important tool for evaluating and comparing investments. From venture capitalists assessing the potential of a startup to corporate finance professionals weighing the merits of a new project, IRR provides a standardized way to evaluate and compare the profitability of different investments.
 The internal rate of return (IRR) is a metric used in capital budgeting to estimate the return of potential investments.
 MS Excel and Google Sheets have three functions for calculating the IRR.
 When using different borrowing rates of reinvestment, a modified MIRR is the formula to use.
 The XIRR function accounts for payments coming in at different times.
 While IRR is popular for assessing the profitability of potential investments, it has several limitations.
Excel has three functions to calculate the IRR: IRR, the modified IRR (MIRR), and IRR for different payment periods (XIRR).
 IRR is the discount rate that makes the net present value (NPV) of all cash flows from a project equal to zero. Its the expected annual rate of return generated by an investment. However, IRR may give misleading results when comparing projects with different durations or cash flow patterns.
 MIRR addresses some of the limitations of IRR by accounting for the cost of capital and the reinvestment rate of positive cash flows by assuming that positive cash flows are reinvested at the cost of capital rate and that the initial investment is financed at the financing rate. MIRR provides a more realistic annual rate of return than the IRR.
 XIRR is an extension of IRR that takes into account the specific dates of cash flows. XIRR is useful when cash flows are not periodic or when the time intervals between cash flows are not equal.
How to Calculate IRR in Excel
What is IRR function in Excel?
This article describes the formula syntax and usage of the IRR function in Microsoft Excel. Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity.
How to calculate IRR if cash flow comes at different time intervals?
In case you want to calculate the IRR value where the cash flow comes at different time intervals, you should use the XIRR function in Excel, which also allows you to specify the dates for each cash flow. An example of this is covered later in the tutorial
How to calculate internal rate of return in Excel?
Excel allows you to calculate the internal rate of return using the IRR function. This function has the following parameters: values – an array of cells that contain numbers for which you want to calculate the internal rate of return.
How do I calculate internal rate of return (IRR)?
Values must contain at least one positive value and one negative value to calculate the internal rate of return. IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.