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 Composite Year Forecast Rate 1.10% 3.10% 2.25% 5.10% 2.50% 5.50% 3.10% 6.10% 4.00% 7.00% 7.50% 8.10% 5.50% 8.10% 7.50% 8.10% 8.00% 8.10% 4.10% 7.10%1. What is the payment in months 1 through 12? Loan Balance 5385,000.00 Payment 53,735.39 Loan Term (years) 10 Payments per Year 12 Index 1.10% Margin 3.00% Annual cap 2.00% Lifetime cap 5.00% Teaser 1.00% Composite Rate 3.10% 2. What is the balance at the EOY 1? Payment 53,735.39 Balance 5351.639.01 Composite Rate 3.10% Loan Term (years) 9 Payments per Year 12 3. What is the payment in months 13 through 24? Loan Balance 5351,639.01 Payment $4,066.91 Loan Term (years) 9 Payments per Year 12 Composite Rate 5.10% 4. What is the balance at the EOY 2? Payment $4,066.91 Balance 5291,337.53 Composite Rate 5.10% Loan Term (years) 8 Payments per Year 125. What is the payment in months 25 through 36? Loan Balance 5291,337.53 Payment 53,758.06 Loan Term (years) Payments per Year 12 Composite Rate 5.50% 6. What is the balance at the EOY 3? Payment $3,758.06 Balance $261.520.21 Composite Rate 5.50% Loan Term (years) 7 Payments per Year 12 7. What is the payment in months 37 through 48? Loan Balance $261,520.21 Payment $3,832.98 Loan Term (years) 7 Payments per Year 12 Composite Rate 6.10% 8. What is the balance at the EOY 4? Payment $3,832.98 Balance $230.622.81 Composite Rate 6.10% Loan Term (years) 6 Payments per Year 12 9. What is the payment in months 49 through 60? Loan Balance $230,622.81 Payment $3.931.89 Loan Term (years) Payments per Year 12 Composite Rate 7.00%10. What is the balance at the EOY 5? Payment $3,931.89 Balance 5198.568.27 Composite Rate 7.00% Loan Term (years) 5 Payments per Year 12 11. What is the payment in months 61 through 72? Loan Balance $198,568.27 Payment $4,035.76 Loan Term (years) Payments per Year 12 Composite Rate 8.10% 12. What is the balance at the EOY 6? Payment 54,035.76 Balance $164,994.96 Composite Rate 8.10% Loan Term (years) 4 Payments per Year 12 13. What is the payment in months 73 through 84? Loan Balance $164,994.96 Payment 54,035.76 Loan Term (years) Payments per Year 12 Composite Rate 8.10% 14. What is the balance at the EOY 7? Payment $4,035.76 Balance $128,598.94 Composite Rate 8.10% Loan Term (years) 3 Payments per Year 1215. What is the payment in months 85 through 96? Loan Balance $128,598.94 Payment 54,035.76 Loan Term (years) 3 Payments per Year 12 Composite Rate 8.10% 16. What is the balance at the EOY 8? Payment $4,035.76 Balance 589,142.89 Composite Rate 8.10% Loan Term (years) 2 Payments per Year 12 17. What is the payment in months 97 through 108? Loan Balance 589,142.89 Payment 54,035.76 Loan Term (years) 2 Payments per Year 12 Composite Rate 8.10% 18. What is the balance at the EOY 9? Payment $4,035.76 Balance 546,369.55 Composite Rate 8.10% Loan Term (years) 1 Payments per Year 12 19. What is the payment in months 109 through 120? Loan Balance $46,369.55 Payment 54,014.34 Loan Term (years) Payments per Year 12 Composite Rate 7.10%20. What is the yield to the lender if this loan is held to maturity? (Hint: annualized IRR by multiplying by 12 due to monthly cash flows) Month Cash Flow
