Using EXCEL: Create an amortization spreadsheet and answer the following questions for a $600,000 10-year 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 5.0% over the initial teaser rate. Expectations for the beginning-of-year values for the appropriate index are as follows:

4.) Adjustable Rate Mortgage Create an amortization spreadsheet and answer the following questions for a $600,000 10-year 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 5.0% over the initial teaser rate. Expectations for the beginning-of-year values for the appropriate index are as follows Year Index 5.25% 5.75% 6.00% 6.25% 6.50% 6.50% 7.25% 7.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 296, what is the loan's EIR if it is held until maturity? d) Assuming upfront points of 296, what is the loan's EIR if it is prepaid at the end of year 5 (assume no prepayment penalties)? 4.) Adjustable Rate Mortgage Create an amortization spreadsheet and answer the following questions for a $600,000 10-year 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 5.0% over the initial teaser rate. Expectations for the beginning-of-year values for the appropriate index are as follows Year Index 5.25% 5.75% 6.00% 6.25% 6.50% 6.50% 7.25% 7.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 296, what is the loan's EIR if it is held until maturity? d) Assuming upfront points of 296, what is the loan's EIR if it is prepaid at the end of year 5 (assume no prepayment penalties)