Question

Problem 2: Using a Spreadsheet to Calculate Mortgage Payments
	What is the monthly payment on a $150,000, 30-year mortgage if the mortgage rate is 5.75 percent? 6.25 percent?
	7.5 percent? 9 percent?
	Present		Annual	Periodic	=>	Payment
	value	Periods	Interest Rate	Interest Rate
	$150,000	FORMULA	5.75%	FORMULA	=	FORMULA
	$150,000		6.25%		=
	$150,000		7.50%		=
	$150,000		9.00%		=

Answer 1

Monthly installment payment amount for mortgage is given by (M):

M = (P * r) / [1 - (1+r)^-n ]

where P = mortgage amount, r= monthly interest rate = annual interest rate / 12, n = number of monthly installments = 30*12 = 360

A B C D E F 13 => Payment 16 14 Present 15 value $150,000 17 $150,000 18 $150,000 19 $150,000 Periods 360 360 360 360 | Annual Interest Rate 5.75% 6.25% 7.50% 9.00% Periodic Interest Rate 0.4792% 0.5208% 0.6250% 0.7500% 875.3593 923.5758) 1048.822 1206.934 = 20

The excel cell with formula for the above sheet is copied below:

Present		Annual	Periodic	=>	Payment
value	Periods	Interest Rate	Interest Rate
150000	=30*12	0.0575	=C16/12	=	=D16*A16/(1-(1+D16)^-B16)
150000	=30*12	0.0625	=C17/12	=	=D17*A17/(1-(1+D17)^-B17)
150000	=30*12	0.075	=C18/12	=	=D18*A18/(1-(1+D18)^-B18)
150000	=30*12	0.09	=C19/12	=	=D19*A19/(1-(1+D19)^-B19)