Spreadsheet Assignment Using Excel Kilby Company is considering the purchase of new automated manufacturing equipment that would cost $150,000. The equipment would save $42,500 in labor costs per year over its six-year life. At the end of the fourth year, the equipment would require an overhaul that would cost $25,000. The equipment would have a $7,500 salvage value at the end of its life. Kilby’s cost of capital is 12 percent.

Required

a. Prepare a spreadsheet similar to the one following to calculate net present value, the present value index, and the internal rate of return.

Spreadsheet Tips Spreadsheets have built-in financial functions that make net present value and internal rate of return calculations very easy. The formats of these formulas are as follows.
1. Net Present Value: = NPV(rate,value1,value2,value3 … value29) where up to 29 values are allowed. The values must be at the end of the period, and each period must be equal in time (one year, for example). The formula is = NPV(D3,D7,D8,D9,D10,D11,D12) + D6.
2. Internal Rate of Return: = IRR(values,guess) where values is the range that includes the cash flows (D6 to D12) and guess is an estimate of the rate. Use the cost of capital as the guess.
3. Percentage: Rather than entering 12% in the formulas, refer to cell D3. This will allow you to change the rate and see the effect on the NPV and present value index.
4. Present Value Index: You must construct a formula because no built-in function calculates it.