show answer in excel

4.) Adjustable Rate Mortgage (25 points) - Create an amortization spreadsheet and answer the following questions for a $750,000 10-year 3/1 ARM loan that is fully-amortizing with monthly payments. The loan terms include a teaser rate of 2.5%. After the initial teaser rate period, the interest rate resets annually to the index rate plus a margin of 1.50%. The loan terms also include an annual interest rate cap of 2.0% and a lifetime interest rate cap of 6.0% over the initial teaser rate. Expectations for the beginning-of-year values for the appropriate index are as follows: Year Index 4.00% 4.50% 5.00% 5.00% 5.75% 6.25% 6.75% 4.00% 4.50% 5.25% 10 a) Calculate the appropriate contract rate for years 1-10 and then complete the amortization schedule. b) What is the effective interest rate (EIR) for the loan if it is held to maturity (assume no upfront fees/points or prepayment penalties)? c) Assuming upfront points of 2%, what is the loan's EIR if it is held until maturity?! d) Assuming upfront points of 2%, what is the loan's EIR if it is prepaid at the end of year 5 (assume no prepayment penalties)? 4.) Adjustable Rate Mortgage (25 points) - Create an amortization spreadsheet and answer the following questions for a $750,000 10-year 3/1 ARM loan that is fully-amortizing with monthly payments. The loan terms include a teaser rate of 2.5%. After the initial teaser rate period, the interest rate resets annually to the index rate plus a margin of 1.50%. The loan terms also include an annual interest rate cap of 2.0% and a lifetime interest rate cap of 6.0% over the initial teaser rate. Expectations for the beginning-of-year values for the appropriate index are as follows: Year Index 4.00% 4.50% 5.00% 5.00% 5.75% 6.25% 6.75% 4.00% 4.50% 5.25% 10 a) Calculate the appropriate contract rate for years 1-10 and then complete the amortization schedule. b) What is the effective interest rate (EIR) for the loan if it is held to maturity (assume no upfront fees/points or prepayment penalties)? c) Assuming upfront points of 2%, what is the loan's EIR if it is held until maturity?! d) Assuming upfront points of 2%, what is the loan's EIR if it is prepaid at the end of year 5 (assume no prepayment penalties)