Evaluating Cash Flow Results

Most modern spreadsheet applications, such as Excel, allow analysts to calculate net present value automatically, and without much thought to what is occurring. The only requirement for the calculation is to know the discount rate and the range of cash flow values being analyzed.

The Excel function applies the following rule, which calculates the present value of each cash flow in each year.

PV = Cash Flow / (1 + r)n

Where:

• r = discount rate, and n = the period being examined

The important point to remember is the rate and the period need to be the same measure of time. If examining annual cash flows, the discount rate needs to be an annual rate. When looking at monthly cash flows, the discount rate needs to be stated on a per month basis.

Let's see how this calculation works using an example. Here we have an initial investment of $1,000, three years of positive cash flow at $500 per year, and the discount rate is 8.95%.

The convention is to state results in terms of the current year, or Y0 in this example. Applying the correct formula, it's possible to figure out the present value of each cash flow:

PV Y1 = 500 / (1 + 0.0895) = 459 PV Y2 = 500 / (1 + 0.0895)2 = 421 PV Y3 = 500 / (1 + 0.0895)3 = 387

The result for this example would be:

NPV = -1,000 + 459 + 421 + 387 = 267

Based on the rule mentioned earlier, this would be accepted as being a valuable project because the NPV of cash flows is greater than zero.

The primary strength of the NPV approach is that it supports the "time value of money" concept. That is to say, a dollar received today is worth more than a dollar received a year from now. The other strength of this measure is that it recognizes the risk associated with future cash flow: they are less certain.

The weaknesses of the NPV approach are related to the measure's simplicity. The NPV rule tells the decision maker to accept all investments if the NPV is greater than zero. However, the measure doesn't specify when a positive NPV is achieved. Does it happen in five years or 15?

Another limitation of the NPV approach is the model assumes capital is abundant; there is no capital rationing. If resources are scarce, the analyst has to look carefully at not just the NPV for each project being evaluated, but also the size of each investment. Fortunately, there is another measure that can help overcome this weakness: The calculation of internal rate of return.

The internal rate of return is defined as the discount rate where the NPV of cash flows are equal to zero. The IRR can be calculated using an iterative, trial and error process. The analyst changes the discount rate until the NPV = 0. There are handheld calculators, such as the famous HP 12C, that can perform this calculation automatically.

Today's spreadsheet applications such as Microsoft's Excel or OpenOffice Calc perform this calculation using built-in functions. An example of this calculation appears in the spreadsheet provided later on.

When evaluating the flows of money for a project, IRR is an extremely important financial measure. The strengths of this metric are similar to those of net present value. It's based on discounted cash flows and recognizes the time value of money. When used properly, the measure provides excellent guidance on a project's value.

There are three well-known pitfalls of the IRR approach, which are worth reviewing:

• Multiple Rates of Return: when evaluating a project that has more than one change in sign for the cash flow stream, the project may have multiple IRRs, or no IRR at all.

• Changes in Discount Rates: the IRR rule tells the analyst to accept projects if the IRR is greater than the opportunity cost of capital or WACC. But if the discount rate changes each year, it's impossible to make this comparison.

• IRRs Do Not Add Up: one of the strengths of the NPV approach is that if the analyst needs to add a project to an existing project they can simply add the NPVs together to evaluate the entire project. IRRs, on the other hand, cannot be added together. Projects must be combined into one business case or evaluated on an incremental basis.

The calculation of profitability index is based on the relationship between a project's costs and the discounted after-tax cash flow it produces. The formula for profitability index is as follows:

Profitability Index = Present Value of Cash Flows / Cost of Project

The rule of thumb for profitability index would state that a decision maker accepts all projects that produce benefits (present value) that are in excess of the project's cost.

Let's use the discounted cash flows from the prior example to illustrate how profitability index is calculated:

Based on the above information, the analyst knows:

Present Value of Cash Flows = 459 + 421 + 387 = 1,267 Cost of Project = 1,000

Using this information, the profitability index would be 1,267 / 1,000 or 1.267, which is greater than one. The project passes this measure's rule of thumb, and would be deemed a potentially profitable investment opportunity.

One of the strengths of profitability index is that it provides the analyst with the same result as the net present value method. If the NPV of cash flows is positive, then the profitability index will be greater than one.

This measure also allows decision makers to rank investments. Since cash flow is divided by the project's cost, higher values are considered more profitable.

Unfortunately, profitability index does not specify when a positive cash flow is achieved or provide any indication of the magnitude of cash flows or investments.

Payback can be calculated using two approaches: simple payback and discounted payback:

• Simple Payback: with this method, payback is calculated based on the after-tax cash flows.

• Discounted Payback: when calculating discounted payback, all cash flows are shown on an after tax-basis. They are then discounted using the company's discount rate, which is normally the weighted average cost of capital.

Unfortunately, there is no simple spreadsheet function the analyst can use to calculate payback. That being said, we have incorporated a rather complex formula for automating the calculation of payback into the business case spreadsheet provided later on in this article.

The following example illustrates how each type of payback is determined.

This payback example uses a discount rate of 8.95%, and applies that rate to each of the annual cash flows.

In the above example, the simple payback would be 2 years. By year Y2, the project would have recovered the initial investment of -1,000 occurring in Y0. Examining the discounted cash flows, which are smaller, results in a discounted payback of 2.3 years.

Pros and Cons of the Payback Method

Perhaps the greatest strength of the payback method is it allows executives and managers to get a good feel for how much time will pass before they can recoup their investment. This allows for go, no-go, decisions to be made based on simple cutoff date rules.

Discounted cash flow should be the preferred way to evaluate payback since it recognizes the time value of money. Cash in the future is not worth as much as cash today.

Payback's big weakness is it ignores all cash flows that occur after the payback period is reached. In the example above, simply knowing the payback period is 2 years tells the analyst nothing about what happens after year 2.