Question

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.

Answer 1

Please see the screenshots below. They are in two parts:

The answers are highlighted in yellow cells. Due to size constraints, I am not able to load the snapshot with excel formulae.

A B C D G Adj.period points Monthly IRR Annual IRR 321 Loan Amount Years Composite rate Used 360,000 0.015 0.6466% 8.04% 322 30 12 323 Initial Amort (354,600) 4.75% Payment $1,877.93 $1,425.00 $1,877.93 $1,877.93 $1,877.93 $1,877.93 Balance Interest $452.93 $359,547.07 $454.72 $359,092.35 $456.52 $358,635.82 6.75% $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 $1,877.93 324 EOY1 1 $1,423.21 $1,421.41 $1,419.60 $1,417.79 325 EOY2 8.75% 2 9.75% 326 EOY3 3 $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 $469.32 $355,389.16 $471.18 $354,917.98 $473.05 $354,444.93 327 4 328 5 $1,877.93 $1,877.93 $1,877.93 $1,877.93 $1,410.46 $1,877.93 $1,408.61 $1,415.96 $1,414.14 $1,412.30 329 6 330 7 331 8 332 9 333 10 $1,877.93 $1,877.93 $2,323.69 $1,993. 75 $2,323.69 $2,323.69 $1,990.03 $1,406.75 $1,877.93 $1,877.93 $2,323.69 $2,323.69 $2,323.69 334 11 $1,404.88 335 12 $329.93 $354,1 15.00 $331.79 $353,783.21 $333.66 $353,449.55 $335.53 $353,114.01 $337.42 $352,776.59 $339.32 $352,437.27 336 13 $1,991.90 337 14 338 15 $2,323.69 $1,988.15 $2,323.69 $2,323.69 $1,984.37 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,798.35 $2,798.35 $2,798.35 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,323.69 $2,798.35 $2,798.35 $2,798.35 339 16 $1,986.27 340 17 341 18 $1,982.46 $1,980.54 $1,978.61 $1,976.67 $341.23 $352,096.05 $343.15 $351,752.90 342 19 343 20 $345.08 $351,407.82 $347.02 $351,060. 80 $348.97 $350,711.83 $350.93 $350,360.90 $243.64 $350,117.26 344 21 345 22 $1,974.72 $1,972.75 $2,554.71 346 23 347 24 348 25 $245.41 $349,871.85 $2,552.94 349 26 $2,551.15 $247.20 $349,624.65 350 27 Sheet 11 Sheet3 Sheet 10 Sheet 12 Sheet1 Sheet2 Sheet9 Sheet5 Sheet4 Sheet6

---------------------

A B E G H $2,323.69$1,972.75 $2,798.35 $2,798.35 $2,552.94 $2,798.35 $2,798.35 $2,798.35 $2,798.35 $2,798.35 $2,798.35 $2,542.00 $2,798.35 $2,798.35 $2,798.35 $2,798.35 $3,043.08 $2,821.95 $3,043.08 $2,820.15 $3,043.08 $2,818.34 $3,043.08 $2,816.52 $350.93 $350,360.90 $243.64 $350,117.26 $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 $2,323.69 $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 $3,043.08 $3,043.08 $3,043.08 $3,043.08 $3,043.08 347 24 $2,554.71 348 25 349 26 27 $2,551.15 350 $2,549.35 $2,547.53 $2,545.70 $2,543.86 351 28 352 29 353 30 354 31 355 32 $2,540.13 $2,538.25 $2,536.36 $2,534.44 356 33 $260.10 $347,843.02 357 34 $262.00 $347,581.03 358 35 $263.91 $347,317.12 359 36 $221.13 $347,095.99 360 37 $222.93 $346,873.06 $224.74 $346,648.32 $226.57 $346,421.75 $228.41 $346,193.34 $230.26 $345,963.08 $232.13 $345,730.94 $234.02 $345,496.92 $235.92 $345,261.00 $237.84 $345,023. 16 $239.77 $344,783.39 $344,783.39 361 38 362 39 363 40 364 $3,043.08 $2,814.68 41 42 $3,043.08 $2,812.82 $3,043.08 $2,810.95 $3,043.08 $2,809.06 $3,043.08 $2,807.16 $3,043.08 $2,805.25 $3,043.08 $2,803.31 48 $347,584.76 $2,801.37 $3,043.08 365 $3,043.08 $3,043.08 $3,043.08 $3,043.08 $3,043.08 $0.00 $347,584.76 366 43 44 367 45 368 369 46 47 370 371