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. Adj Period monthly Loan annual Composite Amt points Years IRR IRR 0.0150 rates used 30 12 intial Payment Interest Amort. Balance $1,877.93 $1,425.00 $452.93 $359,547.07 $1,877.93 1 1 $1,877.93 $1,423.21 $454.72 $359,092.35 $1,877.93 2 2 $1,877.93 $1,421.41 $456.52 $358,635.82 $1,877.93 EOY3 $1,877.93 $1,877.93 $1,419.60 $1,417.79 $458.33 $460.14 $358,177.49 $357,717.35 $1,877.93 $1,877.93 4 5 $1,877.93 $1,877.93 $1,415.96 $461.97 $357,255.38 Price 6 $450,000 $1,877.93 $1,877.93 $1,414.14 $1,412.30 $463.79 $356,791.59 $356,325.96 $1,877.93 $1,877.93 $465.63 % down $1,877.93 1,410.46 $467.47 S355,858.48 20% 7.93 annual $1,877.93 $1,408.61 $469.32 $355,389.16 $1,877.93 10 cap $1,877.93 $1.406.75 $471.18 $354,917.98 $1,877.93 $1,877.93 0.02 11 life cap $1,877.93 $1,404.88 $473.05 $354,444.93 12 0.05 13 14 The final column, under monthly IRR is for calculating IRR. 15 16 17 18 19 20 21 22 23 24 $2,798.35 $2,554.71 $243.64 $2,798.35 25 $2,798,35 $245.41 $349,871.85 $2,798.35 26 $2,798.35 $2,551.15 $2,549.35 $247.20 $249.00 $349,624.65 $349,375.64 $2,798.35 $2,798.35 27 $2,798,35 28 $2,798.35 $2,547.53 $250.82 $349,124.82 $2,798.35 29 $2,798.35 $2,798.35 $2,545.70 $2,543.86 $252.65 $348,872.17 $348,617.68 $2,798.35 30 $254,49 $2,798.35 31 $2,798.35 $2,542.00 $256,35 $348,361.34 $2,798.35 32 $2,798.35 $2,540.13 $258.22 $348,103.12 $2,798.35 33 $2,798,35 $2,538.25 $2,536.36 $260.10 $262.00 $347,843.02 $347,581.03 $2,798.35 $2,798.35 34 $2,798.35 35 $2,798.35 $2,534.44 $263.91 $347,317.12 $2,798.35 36 37 38 39 40 41 42 43 44 45