How to Calculate IRR (Internal Rate of Return) in Excel (8 Ways)

In this article, we will discuss how to calculate/find IRR (Internal Rate of Return) in Excel. We hope you will enjoy the whole article.

What Is Net Present Value?

If you have $1000 today, you can invest it in a business, or buy a product today and sell it later, or at least you can deposit it with a bank that gives you interest for your deposits.

Say, you have a trustable bank nearby your location and they give 12% interest for your deposits. You go to the bank and deposit your $1000 with the bank at a 12% interest rate per year for the next 3 years.

Assume that you’re not withdrawing any money from the bank.

After 1 year you will get:

So, $1120 after 1 year is the same as $1000 today.

After 2 years, you will get:

So, $1254.40 after 2 years is the same as $1000 today.

In the same way, we can conclude that after 3 years, we shall get:

So, $1404.93 after 3 years is the same as $1000 today.

We can conclude an equation from the above discussion:
Future value of the money,

Here,

• PV = Present value
• r = Interest rate/year
• n = Number of years

Reversely, we can calculate the present value of the money with this equation:

What Is Internal Rate of Return (IRR)?

IRR is the interest rate that balances out your initial investment and future cash flows from the investment.

How to Calculate IRR in Excel: 8 Ways

Let’s start with an investment proposal you got:

Sam is your friend who comes to you with an investment proposal. You are proposed to invest $1000 and these are the cash flows you can expect for the investment over the next 5 years.

There is a bank nearby your place and you trust this bank. This bank provides 12% yearly interest on a deposit. Or you can invest your money in a government savings bond that provides a 10% interest rate/per year. A government bond is more secure than depositing with a bank. So, you have three options to invest your $1000:

• You can invest in your friend Sam’s project
• You can deposit the amount in your preferred bank that provides a 12% interest rate per year
• Or you can buy a government bond and enjoy a more secure income

What will you do? To make a better decision, you must first know the internal rate of return on your investment with Sam’s project.

Let’s calculate the IRR of these cash flows. There are several scenarios of cash flows (it can be regular, discrete, monthly, etc.), so your approach will also be different when you calculate IRR.

1. Use of  IRR Function to Calculate IRR

For our above example, cash flow is continuous. So, we can easily calculate IRR using the IRR Function.

The Syntax of IRR function:

For a set of cash flows, it can return the internal rate of return.

values (Required): the cell range containing the values of cash flow.

• At least one positive value and one negative value should be there in the cash flow values.
• When interpreting the order of cash flows using the order of values, be careful to input your income and payment values in the correct order.
• If the cell range contains text, logical values, or empty cells then the IRR function will ignore them.

guess (Optional): Your guessed number which may be close to the result of IRR.

• IRR is computed using an iterative process in Microsoft Excel. It runs through the calculations starting with a guess until the outcome is accurate to within 0.00001 percent. IRR returns the #NUM! error value if, after 20 attempts, it is unable to identify a solution.
• Otherwise, try again with an alternative number for guessing if IRR returns the #NUM! error.

Now, go through the steps given below to calculate the IRR of the given cash flow using the IRR function.

Steps:

• Firstly, select Cell C12 and insert the following formula.

As the values argument, we have passed this cell range C5:C10. Values must contain at least one negative value and one positive value. Otherwise, the IRR function will provide #NUM! error value.

We did not pass any value for the guess argument, it is not necessary actually.

• Then, press ENTER to get the value of IRR.

2. Using VBA to Calculate IRR in Excel

The IRR function can also be used in Excel VBA. This code will provide the same IRR value that we have found using the IRR Excel function in an Excel spreadsheet.

Follow the steps given below to calculate IRR using VBA.

Steps:

• In the beginning, go to the Developer tab >> click on Visual Basic.

• Now, the Microsoft Visual Basic for Application box will open.
• After that, click on Insert >> select Module.

• Then, write the following code in your Module.

Sub Calculate_IRR()
Static CashFlow(6) As Double
CashFlow(0) = -1000
CashFlow(1) = 200
CashFlow(2) = 500
CashFlow(3) = 200
CashFlow(4) = 150
CashFlow(5) = 500
Cells(12, 3) = IRR(CashFlow())
End Sub

• Next, click on the Save button and go back to your worksheet.

• After that, go to the Developer tab >> click on Macros.

Now, the Macros box will appear.

• Then, select Calculate_IRR.
• After that, click on Run.

• Now, you will get your IRR value in decimal.
• Then, to change the number format, go to the Home tab >> click on Number Format >> select Percentage.

• Finally, you will get the value of IRR in percentage using VBA.

3. Manually Calculating IRR from Net Present Value in Excel

Now, I will show you how you can calculate IRR manually or by hand. To do that, we will calculate the Present Value using this formula:

Then, change the IRR value manually to get our desired Net Present Value.

Go through the steps given below to do it on your own.

Steps:

• Firstly, insert any value of your choice in Cell C13 as IRR. Here, I will insert 10%.

• Then, select Cell D6 and insert the following formula.

• After that, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Now, you will get all the values of the Present Value.

• Then, select Cell D11 and insert the following formula.

Here, in the SUM Function, we added all the Present Values in Cell range D6:D10.

• Next, press ENTER to get the value of Total Present Value.

• After that, in cell C13, you have to change the value manually to get a nearby value that makes the total present values equal to 1000.
• Say, we change the value to 12%. Now, you see the total present value is $1098.57.

• After that, change the value to say, 16%. You see the value in cell C8 is $993.03.

This is the manual way. It is not tough but it will take time to get a very close value from this method. You can also try this method on paper, but that might take a whole day to find the internal rate of return of some future cash flows.

4. Applying Goal Seek Feature to Calculate IRR in Excel

In the fourth method, I will show you how you can calculate IRR by applying the Goal Seek Feature in Excel. Follow the steps given below to do it on your own dataset.

Steps:

• Firstly, go through the steps shown in Method-3 to prepare the dataset to use the Goal Seek feature.

• Then, go to the Data tab >> click on Forecast >> click on What-If Analysis >> select Goal Seek.

• Now, the Goal Seek box will appear.
• After that, insert Cell D11 (sum of all future cash flows) as  Set cell, 1000 (equal to initial investment) as To value, and Cell C13 (Internal Rate of Return) as By changing cell.
• Next, press OK.

• Then, the Excel Goal Seek feature does the iterations and then comes up with a value that meets all the criteria. Here, you will get 15.715% as an IRR value that meets all the criteria and get the same internal rate of return as we got using Excel’s IRR function.

5. Using XIRR Function to Calculate IRR for Uneven Cash Flow

When your cash flows are not regular, you can use Excel’s XIRR function to calculate the internal rate of return.

The Syntax of XIRR function:

The XIRR function takes the cash flow values and dates of the cash flows and outputs the internal rate of return.

Say, we have some discrete periods, and the cash flows like the following image. And we want to calculate the IRR from these values.

Now, follow the steps given below to calculate the IRR value for this dataset.

Steps:

• Firstly, select Cell C19 and insert the following formula.

Here, in the XIRR function, I inserted Cell range C5:C17 as values and Cell range B5:B17 as dates.

• Then, press ENTER to get the value of IRR.

6. Calculating IRR for Monthly Cash Flow in Excel

In this section, I will show how to calculate IRR in Excel for monthly cash flows.

You will see some data in the following image. Our initial investment is -200000 and it is on 31st Dec 2017. Then we have shown every month’s cash flow.

Here, the IRR function is used to calculate equal payment periods like years. You can use the IRR function to calculate monthly cash flows but as all months are not equal in days, so it is better to use Excel’s XIRR function.

Moreover, the XIRR function is used to calculate the internal rate of return of cash flows that are not periodic. We have seen how to use the XIRR function in the previous section.

Now, follow the steps given below to calculate IRR using both of these functions.

Steps:

• Firstly, select Cell D19 and insert the following formula.

Here, in the IRR function, I inserted Cell range D5:D17 as values and then multiplied this value by 12 as we know, 1 year =12 Months.

• Then, press ENTER to get the value of IRR using the IRR function.

• After that, select Cell D20 and insert the following formula.

Here, in the XIRR function, I inserted Cell range D5:D17 as values and Cell range C5:C17 as dates.

• Finally, press ENTER to get the value of IRR using the XIRR function.

You see there is a difference between the values.

Note: If you use the IRR function to calculate the internal rate of return for monthly cash flows, then you need to multiply the IRR value by 12 as IRR has calculated the monthly rate of return, not yearly.

7. Using MIRR Function to Calculate Modified IRR in Excel

Say, you had taken a loan of $5000 at the interest rate of 12% a year to invest in a project.  They will give you periodic cash flows like the following image.

What you do is reinvest the cash flows that come from this existing project in a new project. The new project’s possible internal rate of return is, say, 20%.

So, what is your actual (or modified) internal rate of return for this existing project?

In this situation, you can use the MIRR function.

The Syntax of MIRR function:

It can return the internal rate of return for a range of periodic cash flows, taking into account both the finance rate and reinvestment interest of cash.

Values (Required): the cell range containing the numbers of Cash Flow values. These figures indicate a series of periodic payments (negative values) and revenue (positive values).

• To avoid #DIV/0! Errors include both positive and negative values in the range.
• If the cell range contains text, logical values, or empty cells then the MIRR function will ignore them.

Finance_rate (Required) The amount of interest you charge on the cash used for cash flows.

Now, follow the steps given below to use this function to calculate the IRR of this dataset.

Steps:

• Firstly, select Cell C15 and insert the following formula.

Here, in the MIRR function, I inserted Cell range C5:C10 as values, Cell C12 as finance_rate, and Cell C13 as reinvest_rate.

• Then, press ENTER to get the value of IRR.

8. Calculating IRR for Real Estate in Excel

In the last method, I will show you how to calculate IRR for Real Estate in Excel. Suppose, you have a dataset consisting of the information about Initial Investment, Rental Cash Flow, and Resale Proceeds amount after tax for 5 consecutive years of a Real Estate company.

You can calculate the IRR (Internal Rate of Return) for this dataset by first calculating the Total Cash Flow and then calculating the Internal Rate of Return by using the IRR function. Here are the steps.

Steps:

• Firstly, select Cell C8 and insert the following formula.

• Then, press ENTER and drag right the Fill Handle tool to AutoFill the formula for the rest of the cells.

Here, in the SUM function, I have added all the values of Cell range C5:C7.

• After that, select Cell C10 and insert the following formula.

In the IRR function, I inserted cell range C8:H8 as values.

• Finally, press ENTER to get the value of IRR for this real estate company.

What Is Good IRR?

It is a very tough question to answer. Usually, IRR value is compared with a fixed value like a bank interest rate, or a government bond rate as you can earn these incomes idly without doing anything and risking anything. And IRR has also compared the project to project basis.

In the following image, you’re seeing data of two projects and their expected revenue over the next 5 years.

After calculating the IRR of these projects, it seems that Project B would have more potential for the company.

Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Download Practice Workbook

Conclusion

So, in this article, you will find 8 ways to calculate IRR in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

<< Go Back to Excel IRR Function | Excel Functions | Learn Excel