Question: (Excel Exercise) An insurance company is negotiating contract terms on a commercial mortgage loan with a potential borrower. The loan in question is a monthly payment, 20-year amortization, fixed rate balloon loan with a seven-year term (i.e., maturity), an annual interest rate of 5.85% and 1.5 points of disbursement discount.
a. The yield to the lender assuming the loan is held to the end of the term (assume a loan amount of $100). You should do this first with your financial calculator and then replicate your calculations in Excel; create a spreadsheet that takes the mortgage contract terms (amount, rate, etc.) as inputs and then use financial functions [=PMT(), =FV(), etc.] to calculate PMT and OLB. Show the mortgage cash flows from month 0 to month 84 in a single column, and use the =IRR() function to solve for the yield to the lender.
b. Now suppose the borrower wants to modify the loan cash flows as follows: payments are interest-only for the first two years in the life of the loan, and then amortized payments start in the first month of the third year, with a 20-year amortization period [hence, payments in Years 3 through 7 are the same as those you calculated in the original loan in part (a)]. The lender agrees but only if the borrower promises to make a lump sum payment, in addition to the OLB at the end of Year 7, such that the lender's yield (IRR) is 6.5%. Determine the size of the lump sum payment. You should do this in the Excel spreadsheet you created for part (a). Specifically, add another column, make the required changes to the PMTs and OLB(84), and determine the additional lump sum required to make the IRR equal 6.5%.