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 disbursementdiscount. 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 do this in Excel by creating a spreadsheet that takes the mortgage contract terms (amount, rate, etc.) as inputs and then use financialfunctions [=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.)