### Method 1 – Using IRR Function

**Steps:**

- Select a cell to put the IRR value in. We selected cell
**D19**for it. - Write down the following formula in it.

`=IRR(D5:D17)`

- Press
**Enter**on your keyboard.

This will give you the IRR value on a monthly basis.

- Get the value on the yearly scale, follow the same procedure. Select the cell
**D20**and write down the following formula.

`=IRR(D5:D17)*12`

- Press
**Enter**.

Calculate IRR using the **IRR **function for monthly cash flow in Excel.

### Method 2 – Applying XIRR Function

**Steps:**

- Select a cell to put the IRR value in. Here, we have selected cell
**D19**for it. - Write down the following formula.

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

- Press
**Enter**on your keyboard.

Calculate the IRR for monthly cash flow at irregular periods in Excel.

### Method 3 – Utilizing MIRR Function

**Steps:**

- Input the finance rate and the reinvest rate additionally for the dataset.

- Select a cell to store the IRR value. We have selected cell
**D21**here for this. - Write down the following formula in it.

`=MIRR(D5:D17,D19,D20)*12`

- Press
**Enter**.

You will have the modified IRR in Excel for monthly cash flow.

### Method 4 – Use of Conventional Formula

**Steps to Prepare Dataset:**

- An IRR at the start of the process. Let’s say 20%.
- Put it in a cell. Here we have put it in cell
**H4**.

- Create a column to calculate the denominator of the NPV formula.

- Select cell
**D5**and write down the following formula.

`=(1+$H$4)^B5`

- Press
**Enter**.

- Select the cell again. Now click and drag the fill handle icon to the end of the column to replicate the formula.

- Prepare a column for the present value of each payment.

- Select cell
**E5**and write down the following formula.

`=C5/D5`

- Press
**Enter**.

- Select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula.

- Calculate the sum by selecting cell
**E18**and following the formula.

`=SUM(E5:E17)`

- Press
**Enter**.

**Steps to Calculate IRR Value:**

The value of 20% IRR returns a net NPV of -164,221$, which is nowhere near the desired result. Keep changing the values of IRR that give the sum value at the lowest. We don’t have to repeat the same process as what we have done previously. Change the value in cell **H4**.

- Select cell
**H4**and change it to 10%.

- The value in cell E18 has gone up and gotten closer to zero, which means it should go down.
- Enter a value of 5% in cell
**H4**and observe the changes.

- The value has further gone up. But not zero yet. Need to take a lower IRR value.
- Enter a value of 1% in cell
**H4**.

- The sum value is higher than zero now, which indicates that the IRR value should go up at this point.
- Putting the value of 2% in cell
**H4**now, we get something like this.

- Upon entering the value of 2.1%, the outcome looks like this.

- With a value of 2.2%, it looks like this.

The value begins to deviate again, so we can conclude that 2.1% is the closest we can get to the IRR value for this range of data. This is also almost similar to the monthly IRR calculated with the **IRR **function in the first method.

**Download Practice Workbook**

You can download the workbook used for the demonstration from the link below. Download and try it yourself while you go through the article.

**<< Go Back to ****Excel Functions ****|**** Learn Excel**