Using EXCEL: Design

4.) Adjustable Rate Mortgage Create an amortization spreadsheet and answer the following questions for a $600,000 10-vear 3/1 ARM loan that is fully-amortizing with monthly payments. The loan terms include a teaser rate of 3.0%. After the initial teaser rate period, the interest rate resets annually to the index rate plus a margin of 1.25%. The loan terms also include an annual interest rate cap of 2.0% and a lifetime interest rate cap of 50% over the initial teaser rate. Expectations for the beginning-of-year values for the appropriate Index are as follows: Year Index 525% 5.75% 6.00% 6.25% 6.50% 125% 7.00% 5.25% a) Calculate the appropriate contract rate for years I 10 and then complete the amortization schedule. b) What is the effective interest rate (EIR) for the loan ifit 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)?