An insurance company is negotiating contract terms on a $100m commercial mortgage loan with a potential borrower. The loan in question is a monthly payment, 20-year amortization, fixed rate balloon with a seven-year term (i.e., maturity), and annual interest rate of 5.85% and 1.5 points of disbursement discount. Calculate the yield to the lender assuming the loan is held to the end of the term (assume a loan amount of $100, ignore the millions). (You should use Excel by creating 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. I your Excel you will 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.) 

Consider the situation in the previous question. Now suppose that 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 to 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. (Modify the Excel spreadsheet you created before and add another column to make the required changes to the PMTs and OLB(84),and then determine the additional lump sum required to make the IRR equal 6.5%.) Possible answers for part B.

A). 0 

B). 1004 

C). 3120

D). 6700