Calculating NPV for Investments A and B

 

For Investment A, the timing of each cash flow is shown in column A with the amount of each cash flow shown in column B. Together these columns produce a vertical timeline of cash flows. The discount rate of 10 percent is shown in cell C2, and each of the values in cells C4 to C7 is the present value factor needed to convert the values in column B into the present values in column D. The net present value is simply the sum of all the individual present values in column D. The NPV of $180.32 is highlighted in cell D8.

Excel also provides an NPV function, which is shown in cell C13. Unfortunately, when the initial cash flow occurs at time zero (as the $10,000 investment outflow does in our example), the NPV function does not provide an accurate calculation unless the user conducts a slight manipulation. Excel’s NPV function behaves badly because it assumes that the first cash flow comes at the end of the first year. To treat the initial outlay properly, we must leave the initial outlay out of the NPV function. Then we must subtract the initial outlay separately. Also note that because the initial outlay is entered as a negative number in cell B4, we must actually add the outlay to the NPV calculation. For Investment A, the cash flows in cells B5 through B7 are entered inside the NPV function, and the initial outlay in cell B4 must be added to the NPV function value. Unless you look carefully at cell C13, you may overlook this subtle but important point.

The NPV for Investment B is calculated in an identical manner. The cash flow timeline is inserted in columns F and G. Present value factors are computed in column H using the 10 percent discount rate from cell H2, and the present value of each cash flow is shown in column I. The sum of the present values is the NPV of $1,414.49 value highlighted in cell I10. Using the Excel NPV function yields the same result in cell H13.

Page 387

The standard decision rule for NPV analysis is as follows:

NPV > 0; Accept the project because the project increases firm value.

NPV < 0; Reject the project because the project reduces firm value.

While both proposals here are acceptable, Investment B has a considerably higher net present value than Investment A.2

Internal Rate of Return

The internal rate of return (IRR) is another important metric used to make capital budgeting decisions. While NPV measures the attractiveness of a project in dollar (i.e., currency) terms, the IRR measures the profitability of investments as a return percentage—much like finding the interest rate (i) in a time value of money problem. The key to fully understanding the meaning of the internal rate of return is to understand how IRR relates to NPV: The internal rate of return on an investment or project is the “rate of return” that makes the net present value of the project equal to zero.

The IRR is the interest rate (i) that makes NPV = 0.

Let us return to our analysis of Investment A and Investment B. At the top of Table 12-5, the cash flows for Investment A are set up exactly as before when we calculated the NPV of the investment. However, you will notice that instead of using a discount rate of 10 percent, the discount rate is 11.16 percent. This is the discount rate that forces the NPV value shown in cell D8 to be exactly zero! Because the NPV is zero when the discount rate is 11.16 percent, we have found the IRR. The internal rate of return is 11.16 percent.

An inquisitive student like you may not be satisfied with simply understanding this definition of the IRR (the rate that makes NPV = 0). Instead, you would probably like to know how the IRR was found. Unfortunately, there is usually no simple equation to find the IRR. However, once again we can make use of the Goal Seek function that was introduced in Chapter 10. Recall that Goal Seek was used in Chapter 10 to find the yield to maturity of a bond (YTM). In fact, the concept behind IRR is almost identical to the YTM concept.

FINANCIAL CALCULATOR

IRR (Uneven Inflows)

Function

CF

2nd

CLR WORK

 

YTM equates the present value of inflows (bond payments) to an outflow (the bond’s cost).

IRR equates the present value of inflows (project returns) to an outflow (the project’s cost).

Excel’s Goal Seek feature doesn’t use an equation. It operates by using an iterative method to find a solution. Specifically, it tries an initial input value to see whether that value produces the result you want. If it doesn’t, Goal Seek tries other input values until it converges on a solution.

Page 388

Table 12-5 Calculating IRR for Investments A and B

 

FINANCIAL CALCULATOR

IRR (Uneven Inflows)

Function

CF

2nd

CLR WORK

 

Returning to Table 12-5, you will notice that the inputs in the Goal Seek dialog box tell Excel to set the NPV value in cell D8 to the value 0 by changing the cell C2. When Excel finds the solution that satisfies the requirement, it has found the IRR. This value is 11.16 percent.

The IRR can also be calculated using Excel’s IRR function. See cell C10 for the proper syntax. Fortunately, the IRR function is more straightforward than the NPV function. Unlike the NPV function, the IRR function treats the first value entered as occurring at the beginning of the first period. Therefore, the IRR function only requires us to enter the range of values from B4 to B7. You will see that the IRR calculated using the IRR function is 11.16 percent, the same as that found using Goal Seek. The calculator keystrokes are shown in the margin.

Page 389

The IRR can be found for Investment B in an identical manner. The IRR for Investment B is 14.33 percent because this is the value that produces NPV = 0. The IRR is shown in cell C14 using Goal Seek, and the identical value is shown in cell D24 using the IRR function.

Now that we have determined the IRR of each investment, we will need to assess whether these returns are high enough to justify investing in the firm. The final selection of any project under the internal rate of return method will depend on whether the yield exceeds some minimum threshold, such as the firm’s cost of capital. You will recall that we assumed that the firm has a 10 percent weighted average cost of capital (WACC) in the preceding NPV analysis. Given this threshold, both projects are expected to produce returns in excess of the WACC.

Under most circumstances, both the net present value and the internal rate of return methods give theoretically correct answers. Payback is simple, and it may produce useful insights, but it is not theoretically sound. Payback’s usefulness depends on rules of thumb that differ from firm to firm, and it has serious shortcomings when applied to complicated cash flow patterns. Therefore, subsequent discussion will be restricted to further examination of the NPV and the IRR methods. A summary of the various conclusions reached under the three methods is presented in Table 12-6.