Kilby Company is considering the purchase of new automated manufacturing equipment that would cost [tex]\$150,000[/tex]. The equipment would save [tex]\$42,500[/tex] 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 [tex]\$25,000[/tex]. The equipment would have a [tex]\$7,500[/tex] salvage value at the end of its life. Kilby's cost of capital is 12 percent.

Required

Complete the spreadsheet to calculate the net present value, the present value index, and the internal rate of return.

- Negative amounts or amounts to be deducted should be input and displayed as negative values. All other answers should be input and displayed as positive values.

Spreadsheet Tips

1. Net Present Value (NPV):
- Use the formula: [tex]=NPV(\text{rate}, \text{value1}, \text{value2}, \text{value3}, \ldots, \text{value29})[/tex], 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 (e.g., one year).
- Example formula: [tex]=NPV(B17, C21, C22, C23, C24, C25, C26) + C20[/tex].

2. Internal Rate of Return (IRR):
- Use the formula: [tex]=IRR(\text{values}, \text{guess})[/tex], where values is the range that includes the cash flows (C20 to C26) and guess is an estimate of the rate. Use the cost of capital as the guess.

3. Percentage:
- Rather than entering [tex]12\%[/tex] directly in the formulas, refer to cell B17. This will allow you to change the rate and see the effect on the NPV and present value index.

4. Present Value Index:
- Construct a formula manually as there is no built-in function for it.

Let's solve this step-by-step using the information provided.

1. Identify the Cash Flows:
- Initial cost in Year 0: [tex]$-\$150,000$[/tex]
- Annual savings from Year 1 to Year 6: [tex]$\$42,500$[/tex] each year
- Overhaul cost at the end of Year 4: [tex]$-\$25,000$[/tex]
- Salvage value at the end of Year 6: [tex]$\$7,500$[/tex]

2. Construct the Cash Flow Timeline:
- Year 0: [tex]$-\$150,000$[/tex] (initial investment)
- Year 1: [tex]$\$42,500$[/tex]
- Year 2: [tex]$\$42,500$[/tex]
- Year 3: [tex]$\$42,500$[/tex]
- Year 4: [tex]$\$42,500 - \$25,000 = \$17,500$[/tex] (savings minus overhaul cost)
- Year 5: [tex]$\$42,500$[/tex]
- Year 6: [tex]$\$42,500 + \$7,500 = \$50,000$[/tex] (savings plus salvage value)

3. Net Present Value (NPV) Calculation:
The NPV formula is:
[tex]\[
NPV = \sum \left( \frac{\text{Cash Flow}_{t}}{(1+\text{rate})^t} \right)
\][/tex]
where [tex]$ \text{Cash Flow}_{t} $[/tex] is the cash flow at time [tex]$ t $[/tex] and [tex]$\text{rate}$[/tex] is the cost of capital (12% = 0.12).

- Calculate NPV:
[tex]\[
NPV = -150,000 + \frac{42,500}{1.12^1} + \frac{42,500}{1.12^2} + \frac{42,500}{1.12^3} + \frac{17,500}{1.12^4} + \frac{42,500}{1.12^5} + \frac{50,000}{1.12^6}
\][/tex]

4. Internal Rate of Return (IRR):
IRR is the discount rate that makes the NPV of all cash flows from the investment equal to zero—or the breakeven point for the investment. It's generally calculated using a financial calculator or software.

5. Present Value Index (Profitability Index):
The Present Value Index (PVI) or Profitability Index is calculated as:
[tex]\[
\text{PVI} = \frac{\text{NPV} + \text{Initial Investment}}{\text{Initial Investment}}
\][/tex]

Now, to compute the exact numbers, you could use a financial calculator or an Excel spreadsheet to plug in these values to compute the NPV, IRR, and PVI.

If you have more specific questions on any step or need further explanations, feel free to ask!