Question

Can someone do this in Excel and show me the formulas as well.

The management of Tri-County Air Taxi, Inc., is considering the replacement of an old machine used in its helicopter repair facility. It is fully depreciated but it can be used by the corporation through 20x5. If management decides to replace the old machine, James Transportation Company has offered to purchase it for $69,000 on the replacement date. The old machine would have no salvage value in 20x5. If the replacement occurs, a new machine would be acquired from Hillcrest Industries on December 31, 20x1. The purchase price of $1,200,000 for the new machine would be paid in cash at the time of replacement. Due to the increased efficiency of the new machine, estimated annual cash savings of $360,000 would be generated through 20x5, the end of its expected useful life. The new machine is not expected to have any salvage value at the end of 20x5. Tri-County’s management requires all investments to earn a 12 percent after-tax return. The company’s tax rate is 30 percent. The new machine would be classified as three-year property for MACRS purposes.

Use Appendix A and Exhibit 16-9. for your reference. (Use appropriate factor(s) from the tables provided.)

Required:
1. Compute the net present value of the machine replacement investment.
3. Compute the payback period for the replacement of the machine.
4. How much would the salvage value of the new machine have to be on December 31, 20x5, in order to turn the machine replacement into an acceptable investment?

Answer 1

1)

After tax proceess will be received from disposal of old assets= $69000- 30%= $48300 (Equipment is fully depreciated so alla received is gain so it should be taxable)

Net initial investment = Purchase price of new equipment- proceeds from disposal of old equipment= $1200000- $48300= $1151700

After tax cash savings= $360000- 30%= $252000

MACRS Depreciation rates for 3 year property;

1st year= 58.33%

2nd year= 27.78%

3rd year = 12.35%

4th year= 1.54%

Depreciation expenses;

1st year= Purchase price× depreciation percentage

= $1200000× 58.33%= $700000

2nd year= $1200000× 27.78%= $333333

3rd Year= $1200000× 12.35%= $148148

4th Year= $1200000× 1.54%= $18519

(Since nothing mentioned assume that half year convention is not applicable.)

Year	Annual after tax cash savings	Depreciation tax shield	Present value factor @ 12% rate	Present value
1	$252000	$700000×30%= $210000	0.8929	$462000× .8929= $412520
2	$252000	$333333× 30%= $100000	0.7972	$352000× .7972= $280614
3	$252000	$148148× 30%= $44444	0.7118	296444× .7118= $211009
4	$252000	$18519×30%= $5556	0.6355	257556×.6355= $163677
Present value of cash inflows	$1067820

Net present value of machine replacement investment= present value of cash inflows - present value of cash outflows

= $1067820- $1151700= ($83880)

3)

Cash flows generated from 3 years

= $462000+ $352000+= $296444= $1110444

Cash flow required in the 4th year to meet th einitial investment= 1151700- 1110444= $41256

Portion of the 4th yera required to get this amount= 41256/ cash flow from 4th year

= 41256/ 257556= 0.16

Payback period for the replacement of the machine= 3+ 0.16= 3.16 Years

4)

Present value of cash inflows needed to the net present value become positive

= $83880

After tax cash flow needed from the salvage value in the 4th Year= $83880/ Present value factor for the 4th Year

= $83880/ 0.6355= $131991 (Equipment will be fully depreciated so salvage value fully will be taxable for gain)

So, Salvage value of the new machine have to on December 31, 2015 in order to turn the machine replacement investment to an acceptable investment= $131991/ (1- tax rate)

= $131991/ (1- 0.3)= $188559