Time Value Formulas in Microsoft Excel – Part II

In the first article, I discussed the PV and FV functions in Excel for calculating the time value of money. In this article, I will discuss the NPV and IRR functions in Excel. In the real world, these two formulas are critical in capital budgeting alternatives, as the results determined by NPV and IRR dictate whether or not to commence projects.

NPV and IRR are inexorably linked, as you will find out, but in order to provide the proper reference, some background of the financial theory is appropriate.

NPV is net present value and it is used to calculate what the present value of a stream of future cash flows is based on a specified discount rate. The discount rate is a measure of risk in the cash flows. As a business, you would use the weighted average cost of capital ("WACC", see other article on weighted averages) as the discount rate, as the WACC is the cost of doing business. If you are an investor, you are likely to use as a discount rate your returns threshold over the period of this project. A simple way to think of discount rate is to consider it the minimal acceptable return for doing a deal.

The form of the NPV function is =NPV(rate,values), where rate is the discount rate and values are either nonadjacent specific numbers (A1,C1,E5,G7) or a set of values in rows or columns that are adjacent (A1:N1). As an example, if you make an investment at December 31, 2008 of $50,000, and you are going to receive $9,000 at the end of each year for the next ten years, the NPV of that stream of cash flows would be $4,819 using a 10.0% discount rate. If your minimal acceptable return is 12.0%, the NPV is $761 and if the minimum acceptable return is 15.0%, the NPV is -$4,201.

For positive NPV values, you would accept the project because it is adding value. In the instance of a negative NPV, you would not do the transaction. In general, you DO NOT accept projects with negative NPVs as that is an indication of value erosion (the return on the project is less than your required minimum so you are subtracting value over the time period of the project).

IRR is the internal rate of return and is used to calculate the compounded rate of return over a series of cash flows. In finance, the IRR assumes that each of your cash flows is reinvested at the calculated IRR. In other words, if your IRR result is 4.0%, each of your cash flows is assumed to have been reinvested at that rate to get the proper IRR. A more important feature of the IRR is its relationships with NPV: the IRR of a project is the rate at which the NPV equals zero. Thus, you should begin to see how these two functions are interrelated.

The form of this function is =IRR(values). In this case, the values must be adjacent or you will get an error message. Sticking with our prior example with the $50,000 investment and $9,000 cash payments, the IRR on that stream of cash flow is 12.4%. Based on what was stated earlier, you should have begun to suspect that the IRR was close to 12.0% because the NPV at a 12.0% discount rate is $761, or closer to zero than a 10.0% discount rate. In short, the IRR provides the bottom return at which you would be able to accept any project.

The problem with the IRR function and IRR in general, is that there can only be one change in sign of the cash flows. This is commonly achieved by an investment (negative sign) with payments returning to the investor (positive sign). If there are two changes in the signs of cash flows, you may get an incorrect IRR as there can be more than one. Additionally, the IRR formula assumes that cash flows come in at the same time over each period (end of each year, end of each month, etc.). If this is not the case, you will have to use the XIRR function, which considers different times for payments over the project period. Finally, as was mentioned earlier, the IRR assumes that the cash flows are reinvested at the IRR. If you know that reinvestment rates will be different over the time horizon of the project, you should use the MIRR function, which allows for you to input a specific reinvestment rate.

As a final thought, NPV and IRR are good tools for determining whether or not to pursue a project, but what if you are evaluating two mutually exclusive projects (you can only choose one) and you get conflicting answers? This can happen when Project A has a higher IRR and lower NPV than Project B. If you can only choose one project, you will always choose the project with the higher NPV, and in this case that is Project B. Why is this? The fundamental premise in finance is maximization of value. A higher NPV means that more value is being created for the shareholders and that is the project you want to choose.