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.