1 Adjustable Rate Mortgage Excel Assignment 4 8 3 4 5 6 7 8 3 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. 5 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 6 only) is 1%. The composite rate adjusts annually and is subject to annual and lifetime caps of 2% and 5%. The 7 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 9 other than entering information needed in the yellow and pink highlighted cells and your answer calculation in the 10 green highlighted cells. Only the green highlighted cells will be graded. Do not round intermediate calculations (let 11 Excel store all decimal values). 12 13 Index Rate Composit 14 Year Forecast e Rate 15 0 1.10% 16 1 2.25% 17 2 2.50% 18 3.10% 19 4.00% 20 7.50% 21 5.50% 22 7.50% 23 8.00% 24 9 4.10% 25 26 27 1. What is the payment in months 1 through 12? 28 Loan Balance Payment 29 Loan Term (years) 30 Payments per Year 31 Index 32 Margin 33 Annual cap 34 Lifetime cap 35 Teaser 36 Composite Rate 37 38 39 2. What is the balance at the EOY 1? 40 Payment Balance 41 Composite Rate 42 Loan Term (years) 43 Payments per Year 44 45 46 3. What is the payment in months 13 through 24? 47 Loan Balance Payment 48 Loan Term (years) 49 Payments per Year 50 Composite Rate 51 53 4. What is the balance at the EOY 2? 54 Payment Balance 55 Composite Rate 56 Loan Term (years) 57 Payments per Year 58 59 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 94 95 10. What is the balance at the EOY 5? 96 Payment Balance 97 Composite Rate 98 Loan Term (years) 99 Payments per Year 100 101 TUT 102 11. What is the payment in months 61 through 72? 103 Loan Balance Payment 104 Loan Term (years) 105 Payments per Year 106 Composite Rate 107 108 109 12. What is the balance at the EOY 6? 110 Payment Balance 111 Composite Rate 112 Loan Term (years) 113 Payments per Year 114 115 116 13. What is the payment in months 73 through 84? 117 Loan Balance Payment 118 Loan Term (years) 119 Payments per Year 120 Composite Rate 121 122 123 14. What is the balance at the EOY 7? 124 Payment Balance 125 Composite Rate 126 Loan Term (years) 127 Payments per Year 128 129 130 15. What is the payment in months 85 through 96? 131 Loan Balance Payment 132 Loan Term (years) 133 Payments per Year 134 Composite Rate 135 136 137 16. What is the balance at the EOY 8? 138 Payment Balance 139 Composite Rate 140 Loan Term (years) 141 Payments per Year 142 143 144 17. What is the payment in months 97 through 108? 145 Loan Balance Payment 146 Loan Term (years) 147 Payments per Year 148 Composite Rate 149 150 151 18. What is the balance at the EOY 9? 152 Payment Balance 153 Composite Rate 154 Loan Term (years) 155 Payments per Year 156 157 158 19. What is the payment in months 109 through 120? 159 Loan Balance Payment 160 Loan Term (years) 161 Payments per Year 162 Composite Rate 163 164 165 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) 1 Adjustable Rate Mortgage Excel Assignment 4 8 3 4 5 6 7 8 3 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. 5 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 6 only) is 1%. The composite rate adjusts annually and is subject to annual and lifetime caps of 2% and 5%. The 7 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 9 other than entering information needed in the yellow and pink highlighted cells and your answer calculation in the 10 green highlighted cells. Only the green highlighted cells will be graded. Do not round intermediate calculations (let 11 Excel store all decimal values). 12 13 Index Rate Composit 14 Year Forecast e Rate 15 0 1.10% 16 1 2.25% 17 2 2.50% 18 3.10% 19 4.00% 20 7.50% 21 5.50% 22 7.50% 23 8.00% 24 9 4.10% 25 26 27 1. What is the payment in months 1 through 12? 28 Loan Balance Payment 29 Loan Term (years) 30 Payments per Year 31 Index 32 Margin 33 Annual cap 34 Lifetime cap 35 Teaser 36 Composite Rate 37 38 39 2. What is the balance at the EOY 1? 40 Payment Balance 41 Composite Rate 42 Loan Term (years) 43 Payments per Year 44 45 46 3. What is the payment in months 13 through 24? 47 Loan Balance Payment 48 Loan Term (years) 49 Payments per Year 50 Composite Rate 51 53 4. What is the balance at the EOY 2? 54 Payment Balance 55 Composite Rate 56 Loan Term (years) 57 Payments per Year 58 59 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 94 95 10. What is the balance at the EOY 5? 96 Payment Balance 97 Composite Rate 98 Loan Term (years) 99 Payments per Year 100 101 TUT 102 11. What is the payment in months 61 through 72? 103 Loan Balance Payment 104 Loan Term (years) 105 Payments per Year 106 Composite Rate 107 108 109 12. What is the balance at the EOY 6? 110 Payment Balance 111 Composite Rate 112 Loan Term (years) 113 Payments per Year 114 115 116 13. What is the payment in months 73 through 84? 117 Loan Balance Payment 118 Loan Term (years) 119 Payments per Year 120 Composite Rate 121 122 123 14. What is the balance at the EOY 7? 124 Payment Balance 125 Composite Rate 126 Loan Term (years) 127 Payments per Year 128 129 130 15. What is the payment in months 85 through 96? 131 Loan Balance Payment 132 Loan Term (years) 133 Payments per Year 134 Composite Rate 135 136 137 16. What is the balance at the EOY 8? 138 Payment Balance 139 Composite Rate 140 Loan Term (years) 141 Payments per Year 142 143 144 17. What is the payment in months 97 through 108? 145 Loan Balance Payment 146 Loan Term (years) 147 Payments per Year 148 Composite Rate 149 150 151 18. What is the balance at the EOY 9? 152 Payment Balance 153 Composite Rate 154 Loan Term (years) 155 Payments per Year 156 157 158 19. What is the payment in months 109 through 120? 159 Loan Balance Payment 160 Loan Term (years) 161 Payments per Year 162 Composite Rate 163 164 165 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)