Question

V 3 4 5 6 Case Study #2 7 8 9 10 - 11 12 13 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? 14 15 16 17 18 19 20 21 22 22 24 25 26 27 21 Sheet1 Sheet2 Slie Type here to search

Answer 1

A B с D E F G H 1 Option 1- Monthly rental payments 2 3 3 4 5 6 7 8 9 Tax shield at Net Cash PV factor Discounted End of Payment 22% flow at 12% cash flow Year 1 $ 26,400 $ 5,808 $ 20,592 0.89286 $ 18,386 Year 2 $ 29,040 $ 6,389 $ 22,651 0.79720 $ 18,057 Year 3 $ 31,944 $ 7,028 $ 24,916 0.71179 $ 17,735 Year 4 $ 35,138$ 7,730$ 27,408 0.63553 $ 17,419 Present value of cash outflow $ 71,597 8 9 10 11 12 Option 2- Lumpsum payment 13 14 15 16 17 18 19 20 21 22 23 Tax shield at Net Cash End of PV factor Discounted Payment 22% flow at 12% cash flow Year 1 $ 29,600 $ 6,512 $ 23,088 0.89286 $ 20,614 Year 2 $ 27,200 $ 5,984 $ 21,216 0.79720 $ 16,913 Year 3 $ 24,800 $ 5,456 $ 19,344 0.71179 $ 13,769 Year 4 $ 22,400 $ 4,928 $ 17,472 0.63553 $ 11,104 Present value of cash outflow $ 62,400 Conclusion The second offer of lumpsum rental payment of $80,000 may be accepted due to lower PV of cash outflow.

Formulas used

A B с D E F G H 1 2 Option 1- Monthly rental payments 3 End of Payment Tax shield at 22% Net Cash flow PV factor at 12% Discounted cash flow 4 5 6 Year 1 Year 2 Year 3 Year 4 Present value of cash outflow =ROUND(24000*1.1,0) =ROUND(C5*22%,0) =ROUND(C5*1.1,0) =ROUND(C6*22%,0) =ROUND(C6*1.1,0) =ROUND(C7*22%,0) =ROUND(C7*1.1,0) =ROUND(C8*22%,0) =C5-D5 =C6-D6 =C7-D7 =C8-D8 7 8 9 =ROUND(1/1.12,5) =ROUND(E5*F5,0) =ROUND(F5/1.12,5) =ROUND(E6* F6,0) =ROUND(F6/1.12,5) =ROUND(E7*F7,0) =ROUND(F7/1.12,5) =ROUND(E8*F8,0) =SUM(G5:68) 10 11 12 Option 2- Lumpsum payment End of Payment Tax shield at 22% Net Cash flow PV factor at 12% Discounted cash flow 13 14 15 16 17 18 19 Year 1 Year 2 Year 3 Year 4 Present value of cash outflow =20000+(80000*12%) =ROUND(C14*22%,0) =C14-D14 =20000+60000*12%) =ROUND(C15*22%,0) =C15-D15 =20000+(40000*12%) =ROUND(C16*22%,0) =C16-D16 =20000+(20000*12%) =ROUND(C17*22%,0) =C17-D17 =ROUND(1/1.12,5) =ROUND(E14* F14,0) =ROUND(F14/1.12,5) =ROUND(E15*F15,0) =ROUND(F15/1.12,5) =ROUND(E16*F16,0) =ROUND(F16/1.12,5) =ROUND(E17*F17,0) =SUM(G14:G17)