Question

Delmont Transport Company (DTC) is evaluating the merits of leasing versus purchasing a truck with a 4-year life that costs $40,000 and falls into the MACRS 3-year class. If the firm borrows and buys the truck, the loan rate would be 10%, and the loan would be amortized over the truck's 4-year life, so the interest expense for taxes would decline over time. The loan payments would be made at the end of each year. The truck will be used for 4 years, at the end of which time, it will be sold at an estimated residual value of $10,000. If DTC buys the truck, it would purchase a maintenance contract that costs $1,000 per year, payable at the end of each year. The lease terms, which include maintenance, call for a $10,000 lease payment at the beginning of each year (i.e. 4 payments total). DTC's tax rate is 40%.

           What is the net advantage to leasing?

           (Note: Assume MACRS rates for Years 1 to 4 are 0.3333, 0.4445, 0.1481, and 0.0741.)

Please show all work. Thank you

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J	K	L
2
3
4		Cost of purchasing the Truck	40000
5
6		Depreciation each year can be calculated as follows:
7		Investment in Truck	=D4
8		Tax basis of the Truck (B)	=D7
9
10		Depreciation follows MACRS 3 year convention.
11		Hence depreciation each year can be calculated as follows:
12				Year 1	Year 2	Year 3	Year 4
13		MACRS 3 Year depreciation rate (rt)		0.3333	0.4445	0.1481	0.0741
14		Depreciation (B*rt)		=$D$8*E13	=$D$8*F13	=$D$8*G13	=$D$8*H13
15		Book Value	=D8	=D15-E14	=E15-F14	=F15-G14	=G15-H14
16
17
18		Calculation of after tax net cash flow from the sale of the machine:
19		Proceed from sale of machine at the end	10000
20		Book Value of Machine at the end	=H15
21		Gain or Loss on sale	=Proceed From Sale - Book value at the end of sale
22			=D19-D20
23
24		Tax rate	0.4
25		Gain or Loss on sale	=D22
26		Tax on Gain & Loss	=D25*D24
27		Net Proceed from Sale of assets at the end	=Proceed from Sale - Tax Expense on gain or loss
28			=D19-D26
29
30		Hence after-tax net cash flow from the sale of machine	=D28
31
32		Loan Payment Calculation:
33		Amount of loan taken	=D7
34		Interest rate	0.1
35		Period	4
36		Maintenance cost per year	1000
37		Cost of buy option can be calculated as follows:
38		Free cash flow is given by following formula:
39		Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital+New Debt-Debt Repayment
40
41		Year	0	1	2	3	4
42		Maintenance cost		=-$D$36	=-$D$36	=-$D$36	=-$D$36
43		Depreciation		=-E14	=-F14	=-G14	=-H14
44		Income Before Tax		=SUM(E42:E43)	=SUM(F42:F43)	=SUM(G42:G43)	=SUM(H42:H43)
45		Tax expense		=-E44*$D$24	=-F44*$D$24	=-G44*$D$24	=-H44*$D$24
46		Net Income		=E44+E45	=F44+F45	=G44+G45	=H44+H45
47		Add Depreciation		=-E43	=-F43	=-G43	=-H43
48		Cost of asset	=-D7
49		Loan Value	=D33
50		Net cash flow from the sale of the machine					=D30
51		Debt principle repayment		=PPMT($D$34,E41,$D$35,$D$33,0)	=PPMT($D$34,F41,$D$35,$D$33,0)	=PPMT($D$34,G41,$D$35,$D$33,0)	=PPMT($D$34,H41,$D$35,$D$33,0)	=PPMT($D$34,H41,$D$35,$D$33,0)
52		Net Cash Flow from Buying the Machine	=SUM(D46:D51)	=SUM(E46:E51)	=SUM(F46:F51)	=SUM(G46:G51)	=SUM(H46:H51)
53		Cost of capital (i) = cost of debt	=D34
54		(P/F,i,n)	=1/((1+$D53)^D41)	=1/((1+$D53)^E41)	=1/((1+$D53)^F41)	=1/((1+$D53)^G41)	=1/((1+$D53)^H41)
55		Present value of net cash flows	=D52*D54	=E52*E54	=F52*F54	=G52*G54	=H52*H54
56		Cost of purchasing the Truck	=Present Value of net cash flows
57			=SUM(D55:H55)	=SUM(D55:H55)
58
59		Hence net cost of purchasing the Truck is	=-D57
60
61
62		Calculation of Cost of leasing the Truck
63		Annual Lease Payment	10000
64
65
66		Year	0	1	2	3	4
67		Lease Payment	=-D63	=D67	=E67	=F67
68		Tax Expense	=-D67*$D$24	=-E67*$D$24	=-F67*$D$24	=-G67*$D$24	=-H67*$D$24
69		Net cash flow due to lease	=D67+D68	=E67+E68	=F67+F68	=G67+G68	=H67+H68
70		Cost of capital (i) = cost of debt	=D34
71		(P/F,i,n)	=1/((1+$D70)^D66)	=1/((1+$D70)^E66)	=1/((1+$D70)^F66)	=1/((1+$D70)^G66)	=1/((1+$D70)^H66)
72		Present value of net cash flows	=D69*D71	=E69*E71	=F69*F71	=G69*G71	=H69*H71
73		Cost of leasing the Truck	=Present Value of net cash flows
74			=SUM(D72:H72)	=SUM(D72:H72)
75
76		Hence cost of leasing the Truck is	=-D74
77
78
79		Calculation of Net Advantage of leasing:
80		Net advantage to Leasing	=Cost of Purchasing the Truck - Cost of leasing the Truck
81			=D59-D76	=D59-D76
82
83		Hence net advantage of leasing is	=D81
84