How to Calculate the IRR in Excel: A Step-by-Step Guide

Excel's IRR function. Excel's IRR function calculates the internal rate of return for a series of cash flows, assuming equal-size payment periods. Using the example data shown above, the IRR formula would be =IRR(D2:D14,. 1)*12 , which yields an internal rate of return of 12.22%.

If you are looking for a way to measure the profitability of an investment, calculating the Internal Rate of Return (IRR) is a great place to start. IRR is a related metric to Net Present Value (NPV), where you try to figure out the discount rate at which NPV would become zero. In this article, we’ll show you how to calculate the IRR in Excel and how to interpret its value.

To begin, let’s use the same spreadsheet that we’ve used in the “NPV in Excel” video. This spreadsheet assumes an investment of $1000 in year zero, followed by four years of $400 benefits. For NPV calculations, we use a discount rate of 20% and arrive at a net present value of $35.

To calculate the IRR in Excel, you need to insert the IRR formula through the search box and select the values that you want to take into account. In this case, we select the nominal amounts of the project cash outflows and project cash inflows. Once you hit enter, you will get to an internal rate of return of 22 percent.

Now, for NPV, any Net Present Value bigger than zero creates value for the company and should be accepted. For IRR, the rule is to compare the IRR to the discount rate or weighted average cost of capital. If the IRR is 22 percent and the discount rate is 20 percent, then the project is marginally better. It generates marginally better returns than the weighted average cost of capital, so you should accept it.

Let’s take it a step further. Let’s assume that the benefits in the outer years go up to $600, and the Net Present Value becomes $248. The Internal Rate of Return goes up as well. If you compare the Weighted Average Cost of Capital of 20% to the Internal Rate of return of 32%, that feels like it’s a significant premium, it’s a significant improvement versus the Weighted Average Cost of Capital.

It’s important to note that there are some limitations to IRR. If there is no upfront investment, IRR cannot be calculated. For example, if you delete the $1000 upfront investment in our spreadsheet, the NPV can still be calculated, but IRR cannot.

In conclusion, calculating the IRR in Excel is easy, and it can provide valuable insights into the profitability of an investment. While it has some limitations, such as requiring an upfront investment, it is still a useful tool in financial analysis. If you want to learn more about financial metrics, check out our other videos on topics like NPV, IRR, and payback period.


How do I manually calculate IRR in Excel?
Start with a guess of the discount rate 'r'Calculate NPV using the 'r' – refer to our tutorials on how to calculate an NPV with or without Excel formulae.If the NPV is close to zero, then 'r' is the IRR.If the NPV is positive, increase 'r'If the NPV is negative, decrease 'r'More items...
How to do the IRR formula?
The future value (FV) is divided by the present value (PV)The amount is raised to the inverse power of the number of periods (i.e., 1 ÷ n)From the resulting figure, one is subtracted to compute the IRR.
How do you calculate IRR quickly?
So the rule of thumb is that, for “double your money” scenarios, you take 100%, divide by the # of years, and then estimate the IRR as about 75-80% of that value. For example, if you double your money in 3 years, 100% / 3 = 33%. 75% of 33% is about 25%, which is the approximate IRR in this case.