Please show your work. Also show the excel functions.

H Adjustable Rate Mortgage Excel Assignment 3 Consider the following information and answer each of the questions below using arithmetic, algebra, and Excel 4 functions as appropriate. Lary is considering an ARM loan offered by a local lender. The loan amount is $400,000. 5 The term of the loan is 10 years. The index today is 2.1%. The margin on the loan is 3%. The teaser (first year only) is 6 1%. The composite rate adjusts annually and is subject to annual and lifetime caps of 2% and 5%. The loan does not 7 provide for negative amortization nor does it provide floors. Assume the following forecast of index rates. The loan 8 requires 2 points at origination. The prevailing rate in the market for similar FRM loans is 6.25%. Make no changes to 9 this Excel workbook other than entering information needed in the yellow and pink highlighted cells and your answer 10 calculation in the green highlighted cells. Only the green highlighted cells will be graded. Do not round intermediate 11 calculations (let Excel store all decimal values). 12 13 Index Rate Composite Year Forecast Rate 0 2.10% 4.10% 2.25% 2.50% 3.10% 4.15% 5 7.50% 5.50% 7.50% 8.00% 4.10% - Payment 1. What is the payment in months 1 through 1 Loan Balance Loan Term (years) Payments per Year Index Margin Annual cap Lifetime cap Teaser Annual Interest Rate Origination Points Balance 2. What is the balance at the E Payment Annual Interest Rate Loan Term (years) Payments per Year Payment 3. What is the payment in months 13 through 24? Loan Balance Loan Term (years) Payments per Year Annual Interest Rate E F G H I L M N Balance 10 2. What is the balance at the EOY 1? 11 Payment 12 Annual Interest Rate 13 Loan Term (years) 14 Payments per Year Payment 16 17 3. What is the payment in months 13 through 24? 18 Loan Balance 19 Loan Term (years) 50 Payments per Year 51 Annual Interest Rate Balance 54 4. What is the balance at the EOY 2? 55 Payment 56 Annual Interest Rate 57 Loan Term (years) 58 Payments per Year 50 Payment 51 5. What is the payment in months 25 through 36? 52 Loan Balance 53 Loan Term (years) 54 Payments per Year 55 Annual Interest Rate Balance 58 6. What is the balance at the EOY 3? 59 Payment 20 Annual Interest Rate 21 Loan Term (years) 22 Payments per Year Payment 75 7. What is the payment in months 37 through 48? 76 Loan Balance 77 Loan Term (years) 78 Payments per Year 79 Annual Interest Rate H Adjustable Rate Mortgage Excel Assignment 3 Consider the following information and answer each of the questions below using arithmetic, algebra, and Excel 4 functions as appropriate. Lary is considering an ARM loan offered by a local lender. The loan amount is $400,000. 5 The term of the loan is 10 years. The index today is 2.1%. The margin on the loan is 3%. The teaser (first year only) is 6 1%. The composite rate adjusts annually and is subject to annual and lifetime caps of 2% and 5%. The loan does not 7 provide for negative amortization nor does it provide floors. Assume the following forecast of index rates. The loan 8 requires 2 points at origination. The prevailing rate in the market for similar FRM loans is 6.25%. Make no changes to 9 this Excel workbook other than entering information needed in the yellow and pink highlighted cells and your answer 10 calculation in the green highlighted cells. Only the green highlighted cells will be graded. Do not round intermediate 11 calculations (let Excel store all decimal values). 12 13 Index Rate Composite Year Forecast Rate 0 2.10% 4.10% 2.25% 2.50% 3.10% 4.15% 5 7.50% 5.50% 7.50% 8.00% 4.10% - Payment 1. What is the payment in months 1 through 1 Loan Balance Loan Term (years) Payments per Year Index Margin Annual cap Lifetime cap Teaser Annual Interest Rate Origination Points Balance 2. What is the balance at the E Payment Annual Interest Rate Loan Term (years) Payments per Year Payment 3. What is the payment in months 13 through 24? Loan Balance Loan Term (years) Payments per Year Annual Interest Rate E F G H I L M N Balance 10 2. What is the balance at the EOY 1? 11 Payment 12 Annual Interest Rate 13 Loan Term (years) 14 Payments per Year Payment 16 17 3. What is the payment in months 13 through 24? 18 Loan Balance 19 Loan Term (years) 50 Payments per Year 51 Annual Interest Rate Balance 54 4. What is the balance at the EOY 2? 55 Payment 56 Annual Interest Rate 57 Loan Term (years) 58 Payments per Year 50 Payment 51 5. What is the payment in months 25 through 36? 52 Loan Balance 53 Loan Term (years) 54 Payments per Year 55 Annual Interest Rate Balance 58 6. What is the balance at the EOY 3? 59 Payment 20 Annual Interest Rate 21 Loan Term (years) 22 Payments per Year Payment 75 7. What is the payment in months 37 through 48? 76 Loan Balance 77 Loan Term (years) 78 Payments per Year 79 Annual Interest Rate