Fill in the missing 17 boxes in the following spreadsheet. The spread sheet is analyzing as adjustable rate loan for 30 years at an initial rate of 4.75% for 80% of a $450,000 property. The loan requires 1.50% points, is adjustable annually, with a 2% annual cap and 5% lifetime cap. Based on the estimated forward rates, the index to the ARM is tied is forecasted as: end of year (EOY) 1= 6.00%, EOY 2= 7.50%, EOY 3 = 8.50%. A margin of 1.50% is added to this index. The property is expected to be sold at the end of four years and there is no negative amortization. intial EOYL BOY2 EOY3 Loan Composite Amt rates used Price $450,000 % down 20% annual cap 0.02 We cap 0.05 The final column, under monthly IRR, is for calculating IRR. Years 30 1 2 3 4 5 6 9 2 952922222222 15 16 17 18 19 Balance $452.93 $359,547.07 $1,877.93 $1,877.93 $1,423.21 $454.72 $359,092.35 $1,877.93 $456.52 $358,635.82 $1,877.93 $1,877.93 $1,421.41 $1,877.93 $1,419.60 $458.33 $358,177.49 $1,877.93 $1,877.93 $1,417.79 $460.14 $357,717.35 $1,877.93 $1,877.93 7 $1,877.93 $1,415.96 $461.97 $357,255.38 $1,877.93 $1,414.14 $463.79 $356,791.59 $1,877.93 $465.63 $356,325.96 $1,877.93 $467.47 $355,858.48 $1,877.93 8 $1,877.93 $1,412.30 9 $1,877.93 $1,410.46 20 21 $1,877.93 10 $1,877.93 $1,408.61 $469.32 $355,389.16 11 $1,877.93 $1,406.75 $471.18 $354,917.98 $1,877.93 $1,877.93 $1,404.88 $473.05 $354,444.93 $1,877.93 12 13 14 23 24 26 27 28 29 Adj. Period Payment Interest Amort. $1,877.93 $1,425.00 41 42 12 30 31 32 33 34 35 36 $2,798.35 $2,534.44 37 38 39 40 points 0.0150 monthly IRR $2,798.35 $2,554.71 $243.64 $2,798.35 $2,798.35 $245.41 $349,871.85 $2,798.35 $2,798.35 $2,551.15 $247.20 $349,624.65 $2,798.35 $2,798.35 $2,549.35 $249.00 $349,375.64 $2,798.35 $2,798.35 $2,547.53 $250.82 $349,124.82 $2,798.35 $2,798.35 $2,545.70 $252.65 $348,872.17 $2,798.35 $2,798.35 $2,543.86 $254.49 $348,617.68 $2,798.35 $2,798.35 $2,542.00 $256.35 $348,361.34 $2,798.35 $2,798.35 $2,540.13 $258.22 $348,103.12 $2,798.35 $2,798.35 $2,538.25 $260.10 $347,843.02 $2,798.35 $2,798,35 $2,536.36 $262.00 $347,581.03 $2,798.35 $263.91 $347,317.12 $2,798.35 annual IRR