You expect to live in a house you are planning to own for 5

You expect to live in a house you are planning to own for 5 years, with a $300K loan. You could get a 3/1 ARM amortized over 15 years at 3.9 %. Suppose the expected interest rate of the ARM for years 4 and 5 is 4.5% and 5.5% respectively. MARR is 10% per year compounded monthly.
a. What is your Present Cost (PC) of this loan option?
b. What is the outstanding balance of the loan at the end of 5 years?

Solution

Loan Amount $300,000 Initial Interest Rate 3.90% Amortization period 15 Years Monthly Interest=(3.9/12) 0.325% Number of Months(15*12) 180 Monthly Payments for first three years $2,204.06 Using PMT function with rate =.325%,Nper=180 and PV -300000 Future value of monthly payment at end of 36 months $84,029.75 Using FV function with rate =.325%,Nper=36 and PMT -2204.06 Future value of the loan amount at end of 36 months $337,171.86 Using FV function with rate =.325%,Nper=36 and PV-300000 amount of loan balance at end of 3 years $253,142.11 ($337171.86-84029.75) Interest Rate at end of 3 years 4.50% Monthly interest Rate 4.5/12 0.38% No of months of loan left 180-36 144 Loan Balance $253,142.11 Monthly payment for year 4 $2,278.30 Using PMT function with rate =.3750%,Nper=144 and PV -253142.11 Future value of monthly payment at end of Year 4 $27,910.59 Using FV function with rate =.375%,Nper=12 and PMT -2278.3 Future value of loan at end of Year 4 $264,771.41 Using FV function with rate =.375%,Nper=12 and PV-253142.11 Amount of loan balance at ed of 4 years $236,860.83 (264771.41-27910.58) Interest Rate at end of 4 years 5.50% Monthly interest Rate 5.5/12 0.46% No of months of loan left 144-12 132 Loan Balance $236,860.33 Monthly payment for year 5 $2,395.58 Using PMT function with rate =..45833%,Nper=132 and PV -236860.33 Future value of monthly payment at end of Year 5 $29,482.80 Using FV function with rate =.45833%,Nper=12 and PMT -2395.58 Future value of loan at end of Year 5 $250,221.63 Using FV function with rate =.45833%,Nper=12 and PV-236860.83 Amount of loan balance at end of 5 years $220,738.65 (250221.63-29482.98) MARR 10% Monthly Disc Rate 0.00833 PV of cash flow Cash Flow /(1+i)^N N A B=A/(1.08333^N) Month Cash Flow PV of Cash Flow 1 $2,204.06 $2,185.85 0.991739 2 $2,204.06 $2,167.79 0.983546 3 $2,204.06 $2,149.89 0.975421 4 $2,204.06 $2,132.12 0.967362 5 $2,204.06 $2,114.51 0.959371 6 $2,204.06 $2,097.04 0.951445 7 $2,204.06 $2,079.72 0.943585 8 $2,204.06 $2,062.54 0.93579 9 $2,204.06 $2,045.50 0.928059 10 $2,204.06 $2,028.60 0.920393 11 $2,204.06 $2,011.84 0.912789 12 $2,204.06 $1,995.22 0.905248 13 $2,204.06 $1,978.74 0.89777 14 $2,204.06 $1,962.39 0.890353 15 $2,204.06 $1,946.18 0.882998 16 $2,204.06 $1,930.10 0.875703 17 $2,204.06 $1,914.16 0.868469 18 $2,204.06 $1,898.34 0.861294 19 $2,204.06 $1,882.66 0.854179 20 $2,204.06 $1,867.11 0.847123 21 $2,204.06 $1,851.68 0.840124 22 $2,204.06 $1,836.39 0.833184 23 $2,204.06 $1,821.22 0.826301 24 $2,204.06 $1,806.17 0.819475 25 $2,204.06 $1,791.25 0.812705 26 $2,204.06 $1,776.45 0.805991 27 $2,204.06 $1,761.78 0.799332 28 $2,204.06 $1,747.22 0.792729 29 $2,204.06 $1,732.79 0.78618 30 $2,204.06 $1,718.47 0.779685 31 $2,204.06 $1,704.28 0.773244 32 $2,204.06 $1,690.20 0.766856 33 $2,204.06 $1,676.23 0.760521 34 $2,204.06 $1,662.39 0.754238 35 $2,204.06 $1,648.65 0.748007 36 $2,204.06 $1,635.03 0.741828 37 $2,278.30 $1,676.14 0.7357 38 $2,278.30 $1,662.30 0.729622 39 $2,278.30 $1,648.56 0.723594 40 $2,278.30 $1,634.95 0.717617 41 $2,278.30 $1,621.44 0.711688 42 $2,278.30 $1,608.04 0.705809 43 $2,278.30 $1,594.76 0.699978 44 $2,278.30 $1,581.59 0.694195 45 $2,278.30 $1,568.52 0.68846 46 $2,278.30 $1,555.56 0.682773 47 $2,278.30 $1,542.71 0.677132 48 $2,278.30 $1,529.97 0.671539 49 $2,395.59 $1,595.44 0.665991 50 $2,395.59 $1,582.26 0.660489 51 $2,395.59 $1,569.19 0.655033 52 $2,395.59 $1,556.23 0.649621 53 $2,395.59 $1,543.37 0.644255 54 $2,395.59 $1,530.62 0.638932 55 $2,395.59 $1,517.98 0.633654 56 $2,395.59 $1,505.43 0.628419 57 $2,395.59 $1,493.00 0.623228 58 $2,395.59 $1,480.66 0.618079 59 $2,395.59 $1,468.43 0.612973 60 $2,395.59 $1,456.30 0.607909 SUM $105,833.96 PV of Cost $105,833.96