Question

Acme Limo has a client who will sign a lease for a limousine. Consider a $100,000 limousine that will last for four years and can be depreciated on a three-year MACRS schedule. Assume that lease rates for old and new limousine are the same and that Acme Limo Inc’s pretax administrative costs are $10,000 per limousine per year (these costs will occur at the beginning of the year). The cost of capital is 9% and the tax rate is 21%. The limousine is expected to be sold for $15,000 at the end of 4 years. The Lease payments are made in advance, that is, at the start of each year. The inflation rate is zero. What is the break-even operating lease rate for the limousine?

Use the following table for depreciation-MACRS Rates for three year asset--Half-Year Convention

Year	%
1	33.33%
2	44.45%
3	14.81%
4	7.41%

Please choose all correct answers. However if you choose a wrong answer, then each incorrect answer will reduce the score by 10%.

	1.	The cash flow for year 4 is $13420 to 13440
	2.	The break even leasing payment before tax is $36480 to $36520
	3.	The break even leasing payment after tax $28810 to $28830
	4.	The cash flow at year 2 is $1430 to 1438
	5.	The break even leasing payment before tax is $36450 to $36480
	6.	The break even leasing payment after tax $28790 to $28810
	7.	The cash flow for year 4 is $13400 to 13420
	8.	The break even leasing payment before tax is $36520 to $36550
	9.	The break even leasing payment after tax $28830 to $28850
	10.	The cash flow at year 0 is  -$107,900
	11.	The cash flow at year 2 is $1438 to 1446
	12.	The cash flow at year 0 is  -$110,000

Answer 1

Goal Seek Setting

Formula sheet

A1	B	C	D	E	F	G	H	I
2
3		Tax Rate	0.21
4		Cost of capital	0.09
5
6		Calculation of Tax Basis:
7		Tax basis of the Limousine is the capital investment done in the Limousine.
8		Investment in Limousine	100000
9		Administrative cost at the beginning of each Year	10000
10		Hence tax basis for the Limousine is	=D8
11
12		Breakeven lease payments should be such that the NPV of the Cash flows Becomes Zero.
13		Goal Seek function in excel can be used to find Breakeven lease payments such that the NPV is zero.
14
15		Lease Payment at the beginning of the Year	36462.5486301662
16
17		Depreciation each year can be calculated as follows:
18		Depreciation follows MACRS 3 year convention.
19		Tax basis of the Limousine (B)	=D10
20
21		Hence depreciation each year can be calculated as follows:
22
23				Year 1	Year 2	Year 3	Year 4
24		MACRS 3 Year depreciation rate (rt)		0.3333	0.4445	0.1481	0.0741
25		Depreciation (B*rt)		=$D$19*E24	=$D$19*F24	=$D$19*G24	=$D$19*H24
26		Book Value	=D19	=D26-E25	=E26-F25	=F26-G25	=G26-H25
27
28
29
30		Calculation of after tax net cash flow from the sale of the asset at the end of year 4:
31
32		Proceed from sale of machine at the end	15000
33		Book Value of Machine at the end	=H26	=H26
34		Gain or Loss on sale	=Proceed From Sale - Book value at the end of sale
35			=D32-D33	=D32-D33
36
37		Tax rate	=D3
38		Gain or Loss on sale	=D35	=D35
39		Tax on Gain & Loss	=D38*D37	=D38*D37
40		Net Proceed from Sale of assets at the end	=Proceed from Sale - Tax Expense on gain or loss
41			=D32-D39	=D32-D39
42
43		Hence after-tax net cash flow from the sale of the asset	=D41
44
45
46		Operating cash flow calculation:
47		Operating cash flow is given by following formula:
48		Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital
49		Operating Cash Flow = EBIT*(1-Tax Rate)+Depreciation
50		Tax Rate	=D3
51		Required rate of return	=D4
52		Free cash flow can be calculated as follows:
53		Year	0	1	2	3	4
54		Investment	=-$D19
55		Lease Payments	=$D$15	=$D$15	=$D$15	=$D$15
56		Administrative Costs	=-$D$9	=-$D$9	=-$D$9	=-$D$9
57		Depreciation		=-E25	=-F25	=-G25	=-H25
58		Operating Income Before Tax (EBIT)	=D55+D56	=E55+E56+E57	=F55+F56+F57	=G55+G56+G57	=H55+H56+H57
59		Tax expense	=-D58*$D$50	=-E58*$D$50	=-F58*$D$50	=-G58*$D$50	=-H58*$D$50
60		After Tax operating income (EBIT*(1-T))	=D58+D59	=E58+E59	=F58+F59	=G58+G59	=H58+H59
61		Add Depreciation	=-D57	=-E57	=-F57	=-G57	=-H57
62		Net Operating Cash Flow	=D60+D61	=E60+E61	=F60+F61	=G60+G61	=H60+H61
63		Net cash flow from the sale of the asset					=D43
64		Net Cash Flow	=D54+D62	=E62+E63	=F62+F63	=G62+G63	=H62+H63
65
66
67		NPV calculation:
68
69		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
70
71		Year	0	1	2	3	4
72		Free Cash Flow (FCF)	=D64	=E64	=F64	=G64	=H64
73		MARR (i)	=D51
74		Present value factor (P/F,i,n) for each year		=1/((1+$D73)^E71)	=1/((1+$D73)^F71)	=1/((1+$D73)^G71)	=1/((1+$D73)^H71)
75		Present Value of cash flows = FCF*(P/F,i,n)		=E72*E74	=F72*F74	=G72*G74	=H72*H74
76		Present value if future cash flows	=SUM(E75:H75)	=SUM(E75:H75)
77
78		NPV for Project	=Present value fo future cash flows - Initial investment
79			=D76+D72	=D76+D72
80
81		Hence Breakeven Lease Payment Before Tax is	=D15
82		Breakeven Lease Payment after Tax	=D81*(1-D50)
83		Cash Flow in Year 4	=H64
84		Cash Flow at Year 0	=D64
85		Cash Flow at Year 2	=E64
86
87		Thus,
88
89		Option (5), (6) and (7) are correct.
90