Write a spreadsheet program to calculate the NPV of a project with an irregular pattern of cash flows for up to 10 periods without using the spreadsheet software’s NPV function. Essentially, the task is to program equation 10.1 with n = 10. First input the interest rate (k) in a single cell. Next set up three horizontal rows of 11 cells (including C_{0}). The top row will receive the cash flows as inputs. Program the present value factor for each period into the second row of cells using the interest rate you input earlier as follows.

Note that we’re calling the interest rate k, but it will appear as a cell name in your program. Next form the third row by multiplying the top two cells in each column together.

This makes the third row the present value of each cash flow. Finally, sum the values along the third row in another cell to form the project’s NPV. Notice that your program will handle a project of less than 10 periods if you simply input zero (or leave blank) the cash flow cells from n+1 to 10. Also notice that you can easily extend your program to any reasonable number of periods by extending the horizontal rows and the programming logic. Test your program on the data in Example 10.3 on pages 428–430 to make sure it works correctly.

Example 10.3

The Xavier Motor Company makes outdoor power equipment including lawn mowers and garden tractors and is considering two diversification ventures. The first involves manufacturing a larger, more powerful tractor than the firm has made up until now. Market research indicates a substantial demand for more powerful equipment, and some competitors are already moving in that direction. The second opportunity involves building snowblowers.

The manufacturing and engineering technology required for making snowblowers is essentially the same as that for building garden equipment, but Xavier has never made snowblowers before. Management wants to make a decision based on only five years of projected cash flows, because it feels the future beyond that time is too vague to form a basis for current decisions. In other words, if a project isn’t expected to earn enough to justify itself in five years, management considers it too risky.

Working with representatives from the marketing, engineering, and manufacturing departments, a financial analyst has put together a set of projected incremental cash flows for each project. Xavier’s cost of capital is 9%.

A financial analysis of the project situation should provide answers to the following questions.

a. If these projects were being considered on a stand-alone basis, would either or both be acceptable?

b. If Xavier can raise no more than $5 million for new projects, which of these projects should be chosen?

c. If Xavier’s management is willing to consider two more years of projected cash flow, and the contributions continued at the level of the last two years, which project would be chosen?

d. Are any risk considerations relevant beyond the numbers in this situation?