Question

3. Five years ago the Jones purchased a $380,000 home in New Westminster. They made a down payment of exactly 25% and mortgaged the balance with New Westminster Credit Union. The interest rate was 5.25% compounded semi-annually, for a five-year term, amortized over 25 years. (a) Calculate the size of the monthly payment required. The credit union rounds the payment up to the next dollar. [5 marks] ANSWER (b) How much interest did the Jones pay in the third year of the mortgage? [3] ANSWER: (c) The Jones, after having made 5 years of payments, made a lump-sum payment to reduce the outstanding balance to $230,000. What was the size of the lump-sum payment? [3] ANSWER: (d) After making their lump sum payment, the Jones renew their mortgage for a further 5-year term at an interest rate of 4.5% compounded semi- annually, over a reduced amortization period of 14 years. Calculate the new monthly payment. Round the payment up to the next dollar. [4 marks] ANSWER: (e) Assuming the interest rate remains the same over the remaining 14 years, what is the size of the final payment? [4 marks]

Answer 1

		Effective Interest Rate per six months=5.25/2	2.625%	0.02625
		Effective Monthly Interest =(1.0265^(1/6))-1=	0.4369%
	(a)	Monthly Payment Required
	Pv	Loan amount =380000*(1-0.25)	$285,000
	Rate	Monthly interest =	0.4369%
	Nper	Number of months of amortization	300	(25*12)
	PMT	Monthly Payment Required	$1,706.58
		(Using PMT function of excel)
	(b)	Interest paid in third year of mortgage
		Loan Balance at the end of Year2= Present Value of future payments for (25-2)*12=276 months
	PV1	Loan Balance at the end of Year2	$273,349
		(Using PV function of excel)
		Loan Balance at the end of Year3= Present Value of future payments for (25-3)*12=264 months
	PV2	Loan Balance at the end of Year2	$267,050
		(Using PV function of excel)
	A	Total Payment during third year	$20,479	(1706.58*12)
	B=PV1-PV2	Principal Paid during third year of mortgage	$6,299
	C=A-B	Interest Paid during third year of mortgage	$14,180
	.(c)	Size of lump sum payment
		Loan Balance at the end of Year5= Present Value of future payments for (25-5)*12=240 months
	PV3	Loan Balance at the end of Year5=	$253,420
		(Using PV function of excel)
	D	Reduced outstanding balance	$230,000
	E=PV3-D	Size of lump sum payment	$23,420
	(d)	Monthly Payment Required after 5 years
		Effective Interest Rate per six months=4.5/2	2.250%	0.02250
		Effective Monthly Interest =(1.0225^(1/6))-1=	0.3715%
	Pv	Principal Balance	$230,000
	Rate	Monthly interest =	0.3715%
	Nper	Number of months of amortization	168	(14*12)
	PMT	Monthly Payment Required	$1,842.93
		(Using PMT function of excel)
	.(e)	Size of Final Payment after another 5 years
		Loan Balance at the end of Year10= Present Value of future payments for (14-5)*12=108months
		Loan Balance at the end of Year10=	$163,703
		(Using PV function of excel)
		Size of Final Payment after another 5 years	$163,703
								File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do D8 - x fc =PMT(D6,D7,-05) ДА В D 0.02625 2.625% 0.4369% (a) Effective Interest Rate per six months=5.25/2 Effective Monthly Interest =(1.0265^(1/6))-1= Monthly Payment Required Loan amount =380000*(1-0.25) Monthly interest =(5.25/12)% Number of months of amortization Monthly Payment Required (Using PMT function of excel) Rate Nper PMT $285,000 0.4369% 300 (25*12) $1,706.58 File Home Insert Page Layout Formulas Data Review View H elp Tell me what you want to do D13 X V for =PV(D6,276,-08) A B E F G H Rate Nper PMT C Monthly interest =(5.25/12)% Number of months of amortization Monthly Payment Required (Using PMT function of excel) D 0.4369% 300 (25*12) $1,706.58 (b) Interest paid in third year of mortgage Loan Balance at the end of Year2= Present Value of future payments for (25-2)*12=276 months Loan Balance at the end of Year2 $273,349 (Using PV function of excel) PV1 File Home Insert Page Layout Formulas Data Review View H elp Y Tell me what you want to do D22 : foc =D20-021 A B F G H I Rate Nper PMT C Monthly interest =(5.25/12)% Number of months of amortization Monthly Payment Required (Using PMT function of excel) D E 0.4369% 300 (25*12) $1,706.58 (b) Interest paid in third year of mortgage Loan Balance at the end of Year2= Present Value of future payments for 25-2)*12=276 months Loan Balance at the end of Year2 $273,349 (Using PV function of excel) PV1 PV2 Loan Balance at the end of Year3= Present Value of future payments for (25-3)*12=264 months Loan Balance at the end of Year2 $267,050 (Using PV function of excel) A B=PV1-PV2 C=A-B Total Payment during third year Principal Paid during third year of mortgage Interest Paid during third year of mortgage $20,479 (1706.58*12) $6,299 $14,180 f =PV(D6,240,-08) G H : A B Rate Nper PMT Monthly interest =(5.25/12)% Number of months of amortization Monthly Payment Required (Using PMT function of excel) 0.4369% 300 (25*12) $1,706.58 (b) Interest paid in third year of mortgage Loan Balance at the end of Year2= Present Value of future payments for (25-2)*12=276 months Loan Balance at the end of Year2 $273,349 (Using PV function of excel) PV1 PV2 Loan Balance at the end of Year3= Present Value of future payments for (25-3)*12=264 months Loan Balance at the end of Year2 $267,050 (Using PV function of excel) A B=PV1-PV2 CEA-B Total Payment during third year Principal Paid during third year of mortgage Interest Paid during third year of mortgage $20,479 (1706.58*12) $6,299 $14,180 Size of lump sum payment Loan Balance at the end of Year5= Present Value of future payments for (25-5)*12=240 months Loan Balance at the end of Year5= Present Value $253,420 PV3 - x fc =PMT(D37,038,-D36) А в E F G H Loan Balance at the end of Year3= Present Value of future payments for (25-3)*12=264 months Loan Balance at the end of Year2 $267,050 (Using PV function of excel) PV2 BEPV1-PV2 C=A-B Total Payment during third year Principal Paid during third year of mortgage Interest Paid during third year of mortgage $20,479 (1706.58*12) $6,299 $14,180 (c) PV3 Size of lump sum payment Loan Balance at the end of Year5= Present Value of future payments for (25-5)*12=240 months Loan Balance at the end of Year5= Present Value $253,420 (Using PV function of excel) Reduced outstanding balance $230,000 Size of lump sum payment $23,420 E=PV3-D Pv Rate Nper Monthly Payment Required after 5 years Effective Interest Rate per six months=4.5/2 Effective Monthly Interest =(1.0225^(1/6))-1= Principal Balance Monthly interest = Number of months of amortization Monthly Payment Required (Using PMT function of excel) 2.250% 0.02250 0.3715% $230,000 0.3715% 168 (14*12) $1,842.93 PMT D45 X fix =PV(D35,108,-D39) А в F G H I D E $20,479 (1706.58*12) $6,299 $14,180 Total Payment during third year Principal Paid during third year of mortgage Interest Paid during third year of mortgage B=PV1-PV2 CEA-B (c) PV3 Size of lump sum payment Loan Balance at the end of Year5= Present Value of future payments for (25-5)*12=240 months Loan Balance at the end of Year5= $253,420 (Using PV function of excel) Reduced outstanding balance $230,000 Size of lump sum payment $23,420 E=PV3-D d) Pv Rate Nper PMT Monthly Payment Required after 5 years Effective Interest Rate per six months=4.5/2 Effective Monthly Interest =(1.0225^(1/6))-1= Principal Balance Monthly interest = Number of months of amortization Monthly Payment Required (Using PMT function of excel) 2.250% 0.02250 0.3715% $230,000 0.3715% 168 (14*12) $1,842.93 Size of Final Payment after another 5 years Loan Balance at the end of Year10= Present Value of future payments for (14-5)*12=108months Loan Balance at the end of Year10= $163,703 (Using PV function of excel)