Adjustable Rate Mortgage [40 points] - 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 2.0%. 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
1	4.00%
2	4.50%
3	5.00%
4	5.00%
5	5.75%
6	6.25%
7	6.75%
8	4.00%
9	4.50%
10	5.25%

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 3.0%, what is the loans EIR if it is held until maturity? d.) Assuming upfront points of 3.0%, what is the loans EIR if it is prepaid at the end of year 6 (assume no prepayment penalties)?

PLEASE HELP SOLVE THIS IN EXCEL

3b 3c EIR 3d EIR EIR Loan Amount Balloon Payment Prepay Penalty Term (Years) Rate Points 3a) Index Cash flows Cash flows Cash flows Year 1 2 3 Beg balance Payment Interest Principal End balance 4 5 O 1 2 3 4 5 6 7 8 9 Contract 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 6 7 8 9 10 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34