Consider the following information and answer each of the questions below us ing 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). Year 0 1 5 5 7 8 2 3 Index Rate Composite Forecast Rate 1.10% 2.25% 2.50% 3.10% 4.00% 7.50% 5.50% 7.50% 8.00% 4.10% 9 4 0 1 S 6 3 24 7 8 9 3 14. What is the balance at the EOY 7? 4 Payment Balance 5 Composite Rate -6 Loan Term (years) -7 Payments per Year 8 -9 0 15. What is the payment in months 85 through 96? 1 Loan Balance Payment 2 Loan Term (years) 3 Payments per Year 4 Composite Rate 55 56 57 16. What is the balance at the EOY 8? 58 Payment Balance $9 Composite Rate 0 Loan Term (years) 1 Payments per Year 42 43 44 17. What is the payment in months 97 through 108? 45 Loan Balance Payment 46 Loan Term (years) 47 Payments per Year 48 Composite Rate 49 So 51 18. What is the balance at the EOY 9? 52 Payment Balance 53 Composite Rate 54 Loan Term (years) 55 Payments per Year Consider the following information and answer each of the questions below us ing 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). Year 0 1 5 5 7 8 2 3 Index Rate Composite Forecast Rate 1.10% 2.25% 2.50% 3.10% 4.00% 7.50% 5.50% 7.50% 8.00% 4.10% 9 4 0 1 S 6 3 24 7 8 9 3 14. What is the balance at the EOY 7? 4 Payment Balance 5 Composite Rate -6 Loan Term (years) -7 Payments per Year 8 -9 0 15. What is the payment in months 85 through 96? 1 Loan Balance Payment 2 Loan Term (years) 3 Payments per Year 4 Composite Rate 55 56 57 16. What is the balance at the EOY 8? 58 Payment Balance $9 Composite Rate 0 Loan Term (years) 1 Payments per Year 42 43 44 17. What is the payment in months 97 through 108? 45 Loan Balance Payment 46 Loan Term (years) 47 Payments per Year 48 Composite Rate 49 So 51 18. What is the balance at the EOY 9? 52 Payment Balance 53 Composite Rate 54 Loan Term (years) 55 Payments per Year