Question

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% adn 5.5% respectively. MARR is 10% per year compounded monthly. What is your Present Cost (PC) of this loan option?

Answer 1

	Loan Amount	$300,000
	Initial interest rate	3.90%
	Amortization period	15	Years
	Monthly interest rate=(3.9/12)=	0.325%
	Number of months=15*12	180
	Monthly payment for first three years	$2,204.06	(Using PMT function with Rate=0.325%,Nper=180, PV=-300000)
	Future value of monthly payment at end of 36 months(three years)	$84,029.75	(Using FVfunction with Rate=0.325%,Nper=36, Pmt=-2204.06))
	Future value of Loan amount at end of 36 months	$337,171.86	(Using FVfunction with Rate=0.325%,Nper=36, 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.5%
	Monthly interest rate=4.5/12=	0.3750%
	Number 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=0.3750%,Nper=144, PV=-253142.11)
	Future value of monthly payment at end of year4	$27,910.58	(Using FVfunction with Rate=0.3750%,Nper=12, Pmt=-2278.30))
	Future value of Loan amount at end of year4	$264,771.41	(Using FVfunction with Rate=0.3750%,Nper=12, PV=-253142.11))
	Amount of Loan Balance at end of 4years	$236,860.83	(264771.41-27910.58)
	Interest rate at end of 4 years =	5.50%
	Monthly interest rate=5.5/12=	0.458333%
	Number of months of loan left=144-12=	132
	Loan balance	$236,860.83
	Monthly payment for YEAR 5	$2,395.59	(Using PMT function with Rate=0.458333%,Nper=132, PV=-236860.83)
	Future value of monthly payment at end of year5	$29,482.98	(Using FVfunction with Rate=0.458333%,Nper=12, Pmt=-2395.59))
	Future value of Loan amount at end of year5	$250,221.63	(Using FVfunction with Rate=0.458333%,Nper=12, PV=-236860.83))
	Amount of Loan Balance at end of 5years	$220,738.65	(250221.63-29482.98)
	MARR=10%
	Monthly discount Rate=(10/12)%=0.1/12=	0.008333333
	Present Value (PV) of Cash Flow:
	(Cash Flow)/((1+i)^N)
	i=Discount Rate=0.008333333
	N=Month of Cash Flow
	N	A	B=A/(1.008333333^N)
	MONTH	Cash Flow	PV of cash flow
	1	$2,204.06	2185.844725
	2	$2,204.06	2167.779893
	3	$2,204.06	2149.864358
	4	$2,204.06	2132.096884
	5	$2,204.06	2114.47625
	6	$2,204.06	2097.00124
	7	$2,204.06	2079.670652
	8	$2,204.06	2062.483292
	9	$2,204.06	2045.437976
	10	$2,204.06	2028.533531
	11	$2,204.06	2011.768791
	12	$2,204.06	1995.142604
	13	$2,204.06	1978.653822
	14	$2,204.06	1962.301312
	15	$2,204.06	1946.083947
	16	$2,204.06	1930.000609
	17	$2,204.06	1914.050191
	18	$2,204.06	1898.231595
	19	$2,204.06	1882.543731
	20	$2,204.06	1866.985519
	21	$2,204.06	1851.555888
	22	$2,204.06	1836.253773
	23	$2,204.06	1821.078123
	24	$2,204.06	1806.027891
	25	$2,204.06	1791.102041
	26	$2,204.06	1776.299546
	27	$2,204.06	1761.619385
	28	$2,204.06	1747.060548
	29	$2,204.06	1732.622031
	30	$2,204.06	1718.302841
	31	$2,204.06	1704.101992
	32	$2,204.06	1690.018505
	33	$2,204.06	1676.051411
	34	$2,204.06	1662.199747
	35	$2,204.06	1648.462559
	36	$2,204.06	1634.838902
	37	$2,278.30	1675.939146
	38	$2,278.30	1662.08841
	39	$2,278.30	1648.352143
	40	$2,278.30	1634.729398
	41	$2,278.30	1621.219239
	42	$2,278.30	1607.820733
	43	$2,278.30	1594.532959
	44	$2,278.30	1581.355001
	45	$2,278.30	1568.285952
	46	$2,278.30	1555.324912
	47	$2,278.30	1542.470987
	48	$2,278.30	1529.723294
	49	$2,395.59	1595.185278
	50	$2,395.59	1582.001929
	51	$2,395.59	1568.927534
	52	$2,395.59	1555.961191
	53	$2,395.59	1543.102008
	54	$2,395.59	1530.349099
	55	$2,395.59	1517.701587
	56	$2,395.59	1505.158599
	57	$2,395.59	1492.719272
	58	$2,395.59	1480.38275
	59	$2,395.59	1468.148182
	60	$2,395.59	1456.014726
		SUM	105824.0404
	PRESENT VALUE OF COST	$105,824.04