5 1 Adjustable Rate Mortgage Excel Assignment 2 3 Consider the following information and answer each of the questions below using arithmetic, algebra, and Excel functions as 4 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 6 annual and lifetime caps of 2% and 5%, respectively. The loan does not provide for negative amortization nor does it provide 7 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 9 answer calculation in the green highlighted cells. Only the green highlighted cells will be graded Do not round intermediate 10 calculations (let Excel store all decimal values) 11 12 The two 21. Build an amortization schedule for the loan in this assignment. Payment Interest Principal Balance AR 3 1 S os 7 8 9 0 02 Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 -2 3 4 15 16 07 08 19 10 11 12 13 14 15 16 5100 SOS SO $0.40 $1.00 $0.90 $0.30 $0.70 $0.60 $0.SO $0.40 $0.30 $0 20 $0.10 $0.00 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 07 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 Composite Rate Year 0 1 1 2 3 4 Index Rate Forecast 1.80% 2.25% 2.50% 3.75% 5.75% 7.50% 5.50% 4.00% 3.25% 4.10% 5 6 7 8 9 5 1 Adjustable Rate Mortgage Excel Assignment 2 3 Consider the following information and answer each of the questions below using arithmetic, algebra, and Excel functions as 4 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 6 annual and lifetime caps of 2% and 5%, respectively. The loan does not provide for negative amortization nor does it provide 7 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 9 answer calculation in the green highlighted cells. Only the green highlighted cells will be graded Do not round intermediate 10 calculations (let Excel store all decimal values) 11 12 The two 21. Build an amortization schedule for the loan in this assignment. Payment Interest Principal Balance AR 3 1 S os 7 8 9 0 02 Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 -2 3 4 15 16 07 08 19 10 11 12 13 14 15 16 5100 SOS SO $0.40 $1.00 $0.90 $0.30 $0.70 $0.60 $0.SO $0.40 $0.30 $0 20 $0.10 $0.00 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 07 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 Composite Rate Year 0 1 1 2 3 4 Index Rate Forecast 1.80% 2.25% 2.50% 3.75% 5.75% 7.50% 5.50% 4.00% 3.25% 4.10% 5 6 7 8 9