How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)

IRR or Internal Rate of Return is a metric commonly used in financial analysis. It is essential to estimate the profitability of potential investments. Calculating IRR with all the statistical calculations is a bit tiring manually. We can perform those repetitive calculations with ease with the help of spreadsheet operations in Excel. Furthermore, Excel even has dedicated functions to calculate IRR for different scenarios. In this article, we will discuss how to calculate IRR in Excel for monthly cash flow in every possible way.

What Is IRR?

IRR is the acronym used for the internal rate of return. It is a discount rate that drives the net present value or NPV of a series of investments to zero. The IRR can also be considered the expected compound annual rate of return that a particular project or investment will achieve.

So IRR calculations follow the same formula as NPV. In fact, it is the annual return rate of the formula that makes NPV equal to zero. The formula for NPV is as follows.

In this formula:

NPV = Net Present Value,

N = Total number of periods

C_n = cash flow

r = Internal Rate of Return

Because of the existence of the summation and the nature of the formula, we can not calculate the IRR directly from the formula. So we need to approach it from a trial-and-error perspective while calculating the value of IRR manually. It is an iteration process for different values of r that return an NPV value of the initial investment or zero depending on how we approach the problem.

How to Calculate IRR in Excel for Monthly Cash Flow: 4 Easy Ways

Excel has three dedicated functions to directly calculate IRR. You can use each depending on the periods of the cash flow and the type of IRR you desire. Of course, Each function returns a slightly different result. So be cautious of the type of outcome you desire. Also, a method to calculate the IRR manually is added at the end. You can also follow that if you prefer the old-school method. Follow the one you need to calculate IRR for monthly cash flow in Excel.

For all the methods we are going to use the following dataset.

1. Using IRR Function

The first function we are going to discuss here is the IRR function. This function returns the internal rate of return for a series of cash flows. These flows of cash do not have to be equal in amount. But the intervals of them should be. The downside of using this function is that it doesn’t consider the time periods- just the flows of cash. So if you have an irregularity in the payments, the function doesn’t properly calculate the time value of each one. And end up returning with a slight error. Still, it gives an apparent result that can be rounded up to an appropriate IRR value.

For arguments, the function takes two values. The primary one is a range of values and the optional one is called the guess which is the estimation of expected IRR.

Follow these steps to see how you can use this function to easily calculate IRR for monthly cash flow in Excel.

Steps:

• First, select a cell to put the IRR value in. Here, we have selected cell D19 for it.
• Then write down the following formula in it.

• After that, press Enter on your keyboard.

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

• To get the value on the yearly scale, follow the same procedure. For example, select the cell D20 and write down the following formula.

• Next, press Enter.

This way you can calculate IRR using the IRR function for monthly cash flow in Excel.

2. Applying XIRR Function

The second function we are going to discuss here is the XIRR function. This function is more suitable for irregular intervals of cash flows. Although the function provides a more accurate result, it uses iteration to calculate the IRR value.

The function takes two primary values- the range of values of cash flow and the range of dates. This function can also take an optional argument called the guess which is the estimation of the expected IRR.

Follow these steps to see how we can calculate the IRR in Excel for monthly cash flow using this function.

Steps:

• First of all, select a cell to put the IRR value in. Here, we have selected cell D19 for it.
• Then write down the following formula.

• Finally, press Enter on your keyboard.

This way, you can calculate the IRR for monthly cash flow at irregular periods in Excel.

3. Utilizing MIRR Function

The final function we are going to discuss here is the MIRR function. This function returns the modified internal rate of return. The difference between the function and the other two is this function additionally considers the finance rate and reinvest rate.

Excel’s MIRR function takes three arguments- the range of values, the finance rate, and the reinvest rate. Then the function returns an IRR value considering those rates.

Follow these steps to calculate the modified IRR for monthly cash flow in Excel.

Steps:

• First of all, you need to input the finance rate and the reinvest rate additionally for the dataset.

• Then select a cell to store the IRR value. We have selected cell D21 here for this.
• Next, write down the following formula in it.

• Finally, press Enter.

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

4. Use of Conventional Formula

The final method we are going to discuss here is the usage of conventional formulas. This is the NPV formula described at the start of the article. Our main goal here is to find the value of IRR by trials for which the summation of all the cash flow values gets closest to zero (NPV to zero).

In this method, we assume IRR beforehand and then proceed to calculate the NPV. If the value of NPV isn’t what we want to achieve, then we repeat the process for other IRR values. We iterate the same process until we are satisfied with a low value of NPV.

Additionally, we are gonna need the help of the SUM function to calculate the sum of the NPV.

Follow these steps for a detailed process.

Steps to Prepare Dataset:

• First of all, let’s assume an IRR at the start of the process. Let’s say 20%.
• Now put it in a cell. Here we have put it in cell H4.

• Then create a column to calculate the denominator of the NPV formula.

• Next, select cell D5 and write down the following formula.

• After that, press Enter.

• Then select the cell again. Now click and drag the fill handle icon to the end of the column to replicate the formula.

• Now prepare a column for the present value of each payment.

• Then select cell E5 and write down the following formula.

• After that, press Enter.

• Next, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula.

• Finally, calculate the sum by selecting cell E18 and writing down the following formula.

• Then press Enter.

Steps to Calculate IRR Value:

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

• First, select cell H4 and change it to 10%.

• We can see the value in cell E18 has gone up and got closer to zero. This means the value should go down.
• Now enter a value of 5% in cell H4 and observe the changes.

• The value has further gone up. But not zero yet. So we need to take a lower IRR value.
• Now enter a value of 1% in cell H4.

• We can see the sum value is higher than zero now. This indicates 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.

• And with a value of 2.2%, it looks like this.

We can see 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. Which 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.

Conclusion

These were all the methods we could use to calculate the IRR for monthly cash flow in Excel. Hopefully, you can easily calculate IRR for your desired scenarios now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

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