Hibra, an EE student who is also taking a business minor, is studying depreciation in her engineering management and finance courses. The assignment in both classes is to demonstrate that shorter recovery periods require the same total taxes, but they offer a time-value-of-taxes advantage for depreciable assets. Help her using asset estimates made for a 6-year study period: P = $65,000, S =$5000, GI = $32,000 per year, AOC = $10,000 per year, SL depreciation, i = 12% per year, and Te = 31%. (a) Perform the comparison using recovery periods of 3 and 6 years. (b) Develop the spreadsheet solution. (c) For a spreadsheet function challenge, construct a single-cell function to determine the PW of taxes for the 3-year and 6-year analyses.

Question

What will be an ideal response?

Answer 1

(a) Recovery over 3 years. SL depreciation is 60,000/3 = $20,000 per year



Year 1-3: Taxes = (GI - OE - D)(Te)

= (32,000 - 10,000 - 20,000)(0.31)

= $620



Year 4-6: Taxes = (GI - OE)( Te )

= (32,000 - 10,000)(0.31)

= $6820



Total taxes = 3(620) + 3(6820) = $22,320



PWtax = 620(P/A,12%,3) + 6820(P/A,12%,3)(P/F,12%,3)

= 620(2.4018) + 6820(2.4018)(0.7118)

= $13,149

Recovery over 6 years. SL depreciation is 60,000/6 = $10,000 per year



Year 1-6: Taxes = (GI – OE – D)( Te )

= (32,000 - 10,000 - 10,000)(0.31)

= $3720

Total taxes = 6(3720) = $22,320



PWtax = 3720(P/A,12%,6)

= 3720(4.1114)

= $15,294

Recovery in 3 years has a lower PWtax value; total taxes are the same for both.



(b) Spreadsheet: Solution follows with only the functions shown.



(c) Spreadsheet functions:

Function for 3-year recovery: = -PV(12%,3,620) + PV(12%,3,,PV(12%,3,6820))

displays $13,148

Function for 6-year recovery: = -PV(12%,6,3720) displays $15,294