Adjustable Rate Mortgage Excel Assignment Consider the following information and answer each of the questions below using arithmetic, algebra, and Excel functions as appropriate. Larz is considering an ARM loan offered by a local lender. The loan amount is $585,000. The term of the loan is 10 years. 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%, respectively. 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). 3. 4567 Year Index Rate Composite Forecast Rate: 0 1.80% 1 2.25% 17 2 2.50% 18 3 3.75% 19 4 5.75% 20 5 7.50% 21 6 5.50% 22 7 4.00% 23 8 3.25% + 24. 9 4.10% 25 1. What is the payment in months 1 through 12? Loan Balance Loan Term (years) Payments per Year Index Margin Annual cap Lifetime cap Teaser 5 Composite Rate Payment 7 B 2. What is the balance at the EOY 1? Balance Payment 1 Composite Rate -2 Remaining Loan Term (years) 13 Payments per Year 14 45 46 3. What is the payment in months 13 through 24? 47 Loan Balance 48 Remaining Loan Term (years) 49 Payments per Year 50 Composite Rate 51 52 53 4. What is the balance at the EOY 2? 54 Payment 55 Composite Rate 56 Remaining Loan Term (years) 57 Payments per Year 58 Payment Balance 5. What is the payment in months 25 through 36? Loan Balance Remaining Loan Term (years) Payments per Year Composite Rate 5 76. What is the balance at the EOY 3? Payment 9 Composite Rate 0 Remaining Loan Term (years) 1 Payments per Year 72 73 74 7. What is the payment in months 37 through 48? 75 Loan Balance 76 Remaining Loan Term (years) 77 Payments per Year 78 Composite Rate 79 80 81 8. What is the balance at the EOY 4? 82 Payment 83 Composite Rate 84 Remaining Loan Term (years) 85 Payments per Year 86 87 88 9. What is the payment in months 49 through 60? 89 Loan Balance 90 Remaining Loan Term (years) 91 Payments per Year 92 Composite Rate Payment Balance Payment Balance Payment 5 10. What is the balance at the EOY 5? 5 Payment 7 Composite Rate 8 Remaining Loan Term (years) 9 Payments per Year 00 01 102 11. What is the payment in months 61 through 72? 103 Loan Balance 104 Remaining Loan Term (years) 105 Payments per Year 106 Composite Rate 107 108 109 12. What is the balance at the EOY 6? 110 Payment 111 Composite Rate 112 Remaining Loan Term (years) 113 Payments per Year 114 115 116 13. What is the payment in months 73 through 84? 117 Loan Balance 118 Remaining Loan Term (years) 119 Payments per Year 120 Composite Rate 121 122 123 14. What is the balance at the EOY 7? 124 Payment 125 Composite Rate 126 Remaining Loan Term (years) 127 Payments per Year 128 Balance Payment Balance Payment Balance 15. What is the payment in months 85 through 96? Loan Balance Remaining Loan Term (years) B Payments per Year 4 Composite Rate 5 6 -7 16. What is the balance at the EOY 8? 58 Payment 39 Composite Rate 40 Remaining Loan Term (years) 41 Payments per Year 42 43 44 17. What is the payment in months 97 through 108? 145 Loan Balance 146 Remaining Loan Term (years) 147 Payments per Year 148 Composite Rate 149 150 151 18. What is the balance at the EOY 9? 152 Payment 153 Composite Rate 154 Remaining Loan Term (years) 155 Payments per Year 156 157 158 19. What is the payment in months 109 through 120? 159 Loan Balance 160 Remaining Loan Term (years) 161 Payments per Year 162 Composite Rate 163 Payment Balance Payment Balance Payment 20. What is the yield to the lender if this loan is held to maturity? Month 0 (Hint: annualized IRR by multiplying by 12 due to monthly cash flows) 5 7 58 1 59 2 70 3 71 4 172 173 6 174 7 175 8 176 9 177 10 178 11 179 12 180 13 181 14 182 15 183 16 184 17 185 18 186 19 187 20 188 21 189 22 190 23 191 24 192 25 193 26 194 27 195 28 Ho B C D E F G H 1 J K L M N 0 P 9] 0 21. Build an amortization schedule for the loan in this assignment. 91 The two charts below will automatically reflect the data in the schedule when completed ARM Loan Payment, Interest, and Principal Chart 12 1 08 92 Month 293 Payment Interest Principal Balance 0 294 1 295 2 296 3 297 4 2981 299 6 300 2 301 8 " 63 302 9 303 10 3041 11 305 12 306 13 307 14 30N 15 309 16 310 11001 17 341 IN 312 19 313 20 314 21 315 23 MA 316 23 317 24 May BIRL 25 310 26 320 27 321 28 322 29 325 10 ARM ARM Loan Balance Chart