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.

**Table of Contents**Expand

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

`$1000+ $1000 x 12% = $1000 (1+12%) = $1120`

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

After **2 years**, you will get:

`$1000 (1+12%) + $1000(1+12%)(12%) = $1000(1+12%)(1+12%) = $1000(1+12%)^2 = $1254.40`

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:

`$1000(1+12%)^3 = $1404.93`

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,

`FV = PV((1+r)^n)`

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

`PV = FV/((1+r)^n)`

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

`IRR (values, [guess])`

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.

`=IRR(C5:C10)`

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
```

**Code Breakdown**

- Firstly, I created
**Sub Procedure**as**Calculate_IRR()**. - Then, I declared a
**Static**array and named it**CashFlow**where the**size**is**and type is**Â**Double**. - After that, I inserted
**6**values of**CashFlow**as**-1000**,**200**,**500**,**200**,**150**,**500**respectively as given in our dataset. - Finally, I used the
**VBA****IRR function**inserting these**CashFlow**array values in the function, and assigned this value to**Cell (12,3)**.

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

`PV = FV/((1+r)^n)`

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.

`=C6/(1+$C$13)^B6`

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

`=SUM(D6:D10)`

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:

`(values, dates, [guess])`

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.

`=XIRR(C5:C17,B5:B17)`

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.

`=IRR(D5:D17)*12`

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.

`=XIRR(D5:D17,C5:C17)`

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 **M****IRR** function:

`MIRR(values, finance_rate, reinvest_rate)`

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.

`=MIRR(C5:C10,C12,C13)`

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.

`=SUM(C5:C7)`

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

`=IRR(C8:H8)`

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**

Kawser

Very understandable and effective article regarding Internal Rate of Return ( IRR). I have one point to complain on comparison IRR and XIRR. It seems that as IRR is a monthly rate and to compute it annually we must use a compound form. as (IRR ^12) and not multiplied. Doing so we have IRR = 0,542% monthly – and ( 1,00542^12) we get 6,70 % – quite the same om XIRR ( 6.72%).

Looking for a prompt reply.

Regards

Hi Colvis.

We are very sorry for not reaching out to you promptly. But the annual IRR is calculated by multiplying the monthly IRR by 12. In that case, we get an annual IRR value of 6.504%. The value of IRR from the IRR formula and the XIRR formula should not be the same for irregular cash flows. And these values reflect that.

Hi – I am always confused with IRR as I say to myself (ok, it is used to give us an indication that Present Investment = Future Cash Flows) but how do we actually apply this in the “real world”. Can you please give me further insight with regard to your last example comparing Project A to Project B. Finally, is IRR of A (3%) and B (5%) of any relevance to the comparison of the “profitability” of these projects? In order to do so, wouldn’t we need to introduce WACC?

Thank you in advance for coming back to me on this.

Regards,

Romesh

Hi Romesh.

IRR is not the sole measure of comparing profitability. It is just one of the many parameters you can use to compare two paths of investment.

This is a wonderful articles . Great many thanks

We are glad that this helped.