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

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

## Download Practice Workbook

**Calculating IRR Excel.xlsm**

## 8 Ways to Calculate IRR in Excel

__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. And this bank provides **12%** **yearly interest **on a deposit. Or you can invest your money in a government saving **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**.

**Read More: ****XIRR vs IRR in Excel (Differences with Examples)**

### 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,
**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**.

**Read More: ****How to Use RRI Function in Excel (5 Suitable Examples)**

### 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 even a whole day to find the **internal rate of return** of some **future cash flows**.

**Read More: ****How to Use NPER Function in Excel (3 Relevant Examples)**

### 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
**Method3**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])`

**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 are seeing 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 **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 **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 **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.

## 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. And, visit **ExcelDemy** for many more articles like this. Thank you!

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.