Adjustable Rate Mortgage Excel Assignment Consider the following information and answer each of the questions below using arithmetic, algebra, and Excel functions as appropriate. Lary is considering an ARM loan offered by a local lender. The loan amount is $385,000. The term of the loan is 10 years. The index today is 1.1%. The margin on the loan is 3%. The teaser (first year only) is 1%. The composite rate adjusts annually and is subject to annual and lifetime caps of 2% and 5%. The loan does not provide for negative amortization nor does it provide floors. Assume the following forecast of index rates. The prevailing rate in the market for similar FRM loans is 5.25%. Make no changes to this Excel workbook other than entering information needed in the yellow and pink highlighted cells and your answer calculation in the green highlighted cells. Only the green highlighted cells will be graded. Do not round intermediate calculations (let Excel store all decimal values). Index Rate Forecast Year 0 1.10% 1 2 3 Composite Rate 2.10% 4.10% 5.50% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 4 2.25% 2.50% 3.10% 4.00% 7.50% 5.50% 7.50% 8.00% 4.10% 5 6 7 8 9 F64 fx F G H A B D 60 5. What is the payment in months 25 through 36? 61 Loan Balance Payment 62 Loan Term (years) 63 Payments per Year 64 Composite Rate 65 66 67 6. What is the balance at the EOY 3? 68 Payment Balance 69 Composite Rate 70 Loan Term (years) 71 Payments per Year 72 73 74 7. What is the payment in months 37 through 48? 75 Loan Balance Payment 76 Loan Term (years) 77 Payments per Year 78 Composite Rate 79 80 81 8. What is the balance at the EOY 4? 82 Payment Balance 83 Composite Rate 84 Loan Term (years) 85 Payments per Year 86 87 88 9. What is the payment in months 49 through 60? 89 Loan Balance Payment 90 Loan Term (years) 91 Payments per Year 92 Composite Rate 93