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