PLEASE CAN YOU SHOW ANSWER EXPLANATION IN EXCEL PLEASE
A borrower and lender negotiate a $20,000,000 interest-only loan at a 5 percent interest rate for a term of 15 years. There is a lockout period of 10 years. Should the borrower choose to prepay this loan at any time after the end of the 10th year, a yield maintenance fee (YMA) will be charged. The YMF will be calculated as follows: A treasury security with a maturity equal to the number of months remaining on the loan will be selected, to which a spread of 150 basis points (1.50%) will be added to determine the lender's reinvestment rate. The penalty will be determined as the present value of the difference between the original loan rate and the lender's reinvestment rate. Required: a. How much will the YMF be if the loan is repaid at the end of year 13 if two year treasury rates are 2 percent? If two- year treasury rates are 4 percent, what will be the lender's reinvestment rate? (Do not round intermediate calculations. Round "YMP' to the nearest dollar amount and "Reinvestment rate" to 2 decimal places.) $ YMF Reinvestment rate 569,847 5.50 % Explanation a. Reinvestment Rate = 2% + 1.5% = 3.5% Monthly payment difference = [(5% - 3.5%)/12] * 20,000,000 = $25,000 per month YMF156 = $25,000 * (MIFPVA, 5%, 24 months)* YMF156 = $569,847 If the 2-year treasury rate was 4%, then the lender's reinvestment rate would be 5.5% (or 4% + 1.5%), which is greater than the original interest rate (of 5%). Therefore, the lender will not charge a YMF, because the loan balance can now be reinvested at a rate greater than the original rate. A borrower and lender negotiate a $20,000,000 interest-only loan at a 5 percent interest rate for a term of 15 years. There is a lockout period of 10 years. Should the borrower choose to prepay this loan at any time after the end of the 10th year, a yield maintenance fee (YMA) will be charged. The YMF will be calculated as follows: A treasury security with a maturity equal to the number of months remaining on the loan will be selected, to which a spread of 150 basis points (1.50%) will be added to determine the lender's reinvestment rate. The penalty will be determined as the present value of the difference between the original loan rate and the lender's reinvestment rate. Required: a. How much will the YMF be if the loan is repaid at the end of year 13 if two year treasury rates are 2 percent? If two- year treasury rates are 4 percent, what will be the lender's reinvestment rate? (Do not round intermediate calculations. Round "YMP' to the nearest dollar amount and "Reinvestment rate" to 2 decimal places.) $ YMF Reinvestment rate 569,847 5.50 % Explanation a. Reinvestment Rate = 2% + 1.5% = 3.5% Monthly payment difference = [(5% - 3.5%)/12] * 20,000,000 = $25,000 per month YMF156 = $25,000 * (MIFPVA, 5%, 24 months)* YMF156 = $569,847 If the 2-year treasury rate was 4%, then the lender's reinvestment rate would be 5.5% (or 4% + 1.5%), which is greater than the original interest rate (of 5%). Therefore, the lender will not charge a YMF, because the loan balance can now be reinvested at a rate greater than the original rate
