Question

B F H K L 8 9 10 11 12 13 14 15 16 17 18 DE Early in the year 2005, the owner of a building made a lessee an offer. The lease contract has four more years to run, and the rent is to be increased by 10% a year each year over the preceding year. The rent is payable in equal monthly installments but (for the sake of simplicity) assume it is all paid at year-end. The building owner's offer is that a lump sum payment now (early in January 2005 before the January rent check had been prepared) of $80,000 would be considered as prepaid rent for the remaining four years of the contract. If the offer is accepted, Charlie would borrow $80,000 from the bank. The arrangement with the bank is that $20,000 of the principal will be repaid on December 31, of the years 2005 through 2008, with interest at 12% on the amount owed at the beginning of each year. Use the interest rate as the discount rate. The tax rate is 22%. Assume that in 2004, the rent payment was $24,000. Should the offer be accepted? Sheet1 Sheet2 Sheet3 +

solve it on excel

Answer 1

Hi

Pls see below:

Please note that Part 1 is the answer. Part 2 is only for your understanding of how the question is been solved.

Part 1: Copied and pasted from excel I worked on so that you can just copy and paste the data into your excel.

Part 2: This snip shows the formulas used in excel to solve.

Here you go...

Part 1: Copied and pasted from excel I worked on so that you can just copy and paste the data into your excel.

Year 2004 Rent	24000
Option 1: Pay the Rent at every year end
Year	Rent	Tax Savings on Rent	Net outflow	PVF	Present value
	Previous year Rent x 110%	Rent x 22%	Rent + (Tax savings)	[1/(1+r)^n]	Net outflows x PVF
2005	$         26,400	$                    (5,808)	$      20,592	0.8929	$         18,386
2006	$         29,040	$                    (6,389)	$      22,651	0.7972	$         18,057
2007	$         31,944	$                    (7,028)	$      24,916	0.7118	$         17,735
2008	$         35,138	$                    (7,730)	$      27,408	0.6355	$         17,418
Present value of Rental payments			$         71,596
Option 2: Take a Bank loan and pay $ 80,000 rent now
Bank loan repayment schedule
Year	Opening Loan	Principal Repaid	Closing loan	Interest @ 12%	Tax Savings on interest	Total outflow	PVF	Present value
	A	B	A-B = C	A x 12% =D	D x 22% E	B+D+E= F	[1/(1+r)^n]= G	F x G
2005	$         80,000	$                    20,000	$      60,000	$             9,600	$         (2,112)	$       27,488	0.8929	$         24,543
2006	$         60,000	$                    20,000	$      40,000	$             7,200	$         (1,584)	$       25,616	0.7972	$         20,421
2007	$         40,000	$                    20,000	$      20,000	$             4,800	$         (1,056)	$       23,744	0.7118	$         16,901
2008	$         20,000	$                    20,000	$             -	$             2,400	$            (528)	$       21,872	0.6355	$         13,900
			Present value of total outflows			$         75,764

Conclusion: Since the present value of cash flows is less in the rental payments at every year-end, it is better to not go for a lumpsum payment option by taking a loan.

Part 2: This snip shows the formulas used in excel to solve.

Year 2004 Rent	24000
Option 1: Pay the Rent at every year end
Year	Rent	Tax Savings on Rent	Net outflow	PVF	Present value
	Previous year Rent x 110%	Rent x 22%	Rent + (Tax savings)	[1/(1+r)^n]	Net outflows x PVF
2005	=C2*110%	=-C7*22%	=D7+C7	=1/(1.12)^1	=E7*F7
=B7+1	=C7*110%	=-C8*22%	=D8+C8	=1/(1.12)^2	=E8*F8
=B8+1	=C8*110%	=-C9*22%	=D9+C9	=1/(1.12)^3	=E9*F9
=B9+1	=C9*110%	=-C10*22%	=D10+C10	=1/(1.12)^4	=E10*F10
Present value of Rental payments				=SUM(G7:G11)
Option 2: Take a Bank loan and pay $ 80,000 rent now
Bank loan repayment schedule
Year	Opening Loan	Principal Repaid	Closing loan	Interest @ 12%	Tax Savings on interest	Total outflow	PVF	Present value
	A	B	A-B = C	A x 12% =D	D x 22% E	B+D+E= F	[1/(1+r)^n]= G	F x G
2005	80000	20000	=C18-D18	=C18*12%	=-F18*22%	=D18+F18+G18	=1/(1.12)^1	=I18*H18
=B18+1	=E18	20000	=C19-D19	=C19*12%	=-F19*22%	=D19+F19+G19	=1/(1.12)^2	=I19*H19
=B19+1	=E19	20000	=C20-D20	=C20*12%	=-F20*22%	=D20+F20+G20	=1/(1.12)^3	=I20*H20
=B20+1	=E20	20000	=C21-D21	=C21*12%	=-F21*22%	=D21+F21+G21	=1/(1.12)^4	=I21*H21
			Present value of total outflows				=SUM(J18:J22)