Question

Assume that your group represents the Credit Manager of a North Vancouver Credit Union and that Mr. Wayne Gretski, on his way through Vancouver to the 2022 Olympics, has asked that you analyze the history of his previous and current mortgage transactions. Exactly 10 years ago, Mr. Wayne Gretski purchased a beautiful condo at Whistler for $760,000 and made a down payment of $295,000. The balance was mortgaged at the Canada Bank at 4.30% compounded semi-annually with monthly payments over 25 years. The interest rate was fixed for a 5 year term, and lump sum payments were allowed at the end of each 5 years without penalty. i) ii) iii) iv) Calculate the monthly payment for the first 5 years. ROUND UP TO THE NEXT CENT. Construct an amortization schedule for the first 60 months. (A schedule showing only the first 3 months, and months 57 to 60 inclusive, with 60 month totals is also required.) Calculate the principal outstanding at the end of the first 5 years. What percentage of the first five years total monthly payments went to reduction of the debt, and what percentage went to interest? What percentage of the debt has been paid off by the first five years of payments? Exactly five years ago, Wayne made a lump-sum payment of $115,000 in addition to the regular payment), and the interest rate was also changed to 4.75% compounded semi-annually. i) ii) Calculate the amount being refinanced. Calculate the monthly payment for the second 5 year period. ROUND UP TO THE NEXT DOLLAR How much total interest did Wayne pay in the past two years? iii) Wayne's mortgage has just come up for further renewal. He has decided to take advantage of your relatively low rates and wishes to investigate tranferring it to your North Vancouver Credit Union branch that charges 5.25% compounded monthly, payable over 15 years. ii) iii) Calculate the size of the principal balance being refinanced. Calculate the size of the new monthly payment. Wayne seems amazed that he has only repaid a small fraction of his original loan. You will draw a large scale, fully labelled graph that visually explains to Wayne how the loan has been amortized over the past ten (10) years. You are only expected to plot the annual balances owing.
please do C part only

Answer 1

		PAYMENT FOR FIRST 5 YEARS
		Monthly interest rate =r
		Semi annual interest rate=(4.3/2)%=	0.0215
		(1+r)^6=1.0215
		1+r=1.0215^(1/6)=	1.003551648
	Rate	Monthly interest rate =r=	0.003551648
	Pv	Amount of loan=760000-295000	$465,000
	Nper	Number of months of mortgage	300	(25*12)
	PMT	Monthly payment	$2,522.20
		(Using PMT function of excel)
		Loan Balance at the end of 60 months =Present Value of future payments for(300-60)=240 months
	Rate	Monthly interest rate =r=	0.003551648
	Nper	Number of months of future payment	240
	Pmt	Monthly payment	$2,522.20
	PV	Loan Balance at the end of 60 months	$406,890
		(Using PV function of excel)
		Lump sum payment at end of 5 years	$115,000
		PAYMENT FOR NEXT 5 YEARS
	Pv1	Loan Balance after lump sum payment	$291,890	(406890-115000)
		Semi annual interest rate =(4.75/2)%	0.02375
	Rate 1	Monthly interest rate =(1.02375^(1/6))-1	0.004705508
	Nper1	Number of months of Mortgage=20*12	240
	PMT1	Monthly payment	$2,032.12
		(Using PMT function of excel)
		Loan Balance at the end of 120 months =Present Value of future payments for(240-60)=180months
	Rate	Monthly interest rate =r=	0.004705508
	Nper	Number of months of future payment	180
	Pmt	Monthly payment	$2,032.12
	PV2	Loan Balance at the end of 120 months	$246,352
		(Using PV function of excel)
	C.(i)	Size of Principal balance being refinaced	$246,352
	(ii)	NEW MONTHLY PAYMENT
	Pv2	Loan Balance	$246,352
	Rate 2	Monthly interest rate =(5.25/12)%	0.4375%
	Nper2	Number of months of Mortgage=15*12	180
	PMT2	Monthly payment	$1,980.37
		(Using PMT function of excel)
		NEW MONTHLY PAYMENT	$1,980.37
		ANNUAL BALANCE:
		First 5 Years
		Principal Payment using PPMT function with Rate =0.003551648,Nper=300,Per=Month,Pv=-465000
			Per	PPMT
			Month	Principal Payment	Principal Balance			End of Year	Principal Balance
			0		$465,000			0	$465,000
			1	$870.69	$464,129.31			1	$454,345
			2	$873.78	$463,255.54			2	$443,227
			3	$876.88	$462,378.66			3	$431,626
			4	$880.00	$461,498.66			4	$418,489
			5	$883.12	$460,615.54			5	$291,890
			6	$886.26	$459,729.28			6	$283,779	E17 =PV(E14,E15,-E16) A B E F G H I J K 0.0215 C 1 PAYMENT FOR FIRST 5 YEARS Monthly interest rate =r Semi annual interest rate=(4.3/2)%= (1+r)^6=1.0215 1+r=1.0215^(1/6)= Monthly interest rate =r= Amount of loan=760000-295000 Number of months of mortgage Monthly payment (Using PMT function of excel) Rate Pv Nper PMT 1.003551648 0.003551648 $465,000 300 (25*12) $2,522.20 Rate Nper Pmt PV Loan Balance at the end of 60 months =Present Value of future payments for(300-60)=240 months Monthly interest rate =r= 0.003551648 Number of months of future payment 240 Monthly payment $2,522.20 Loan Balance at the end of 60 months $406,890 (Using PV function of excel) E32 x fc =PV(E29,E30,-E31) E F G H I J 12 13 14 Rate 15 Nper 16 Pmt 17 PV 18 19 Loan Balance at the end of 60 months =Present Value of future payments for(300-60)=240 months Monthly interest rate =r= 0.003551648 Number of months of future payment 240 Monthly payment $2,522.20 Loan Balance at the end of 60 months $406,890 (Using PV function of excel) Lump sum payment at end of 5 years $115,000 PAYMENT FOR NEXT 5 YEARS Loan Balance after lump sum payment $291,890 (406890-115000) Semi annual interest rate =(4.75/2)% 0.02375 Monthly interest rate =(1.02375^(1/6))-1 0.004705508 Number of months of Mortgage=20*12 240 Monthly payment $2,032.12 (Using PMT function of excel) 21 Pv1 23 Rate 1 24 Nperi 25 PMT1 26 27 28 29 Rate 30 Nper 31 Pmt 32 PV2 Loan Balance at the end of 120 months =Present Value of future payments for(240-60)=180months Monthly interest rate =r= 0.004705508 Number of months of future payment 180 Monthly payment $2,032.12 Loan Balance at the end of 120 months $246,352 (Using PV function of excel) E10 x fc =PMT(E7,99,-E8) F 0.0215 PAYMENT FOR FIRST 5 YEARS Monthly interest rate =r Semi annual interest rate=(4.3/2)%= (1+r)^6=1.0215 1+r=1.0215^(1/6)= Monthly interest rate =r= Amount of loan=760000-295000 Number of months of mortgage Monthly payment (Using PMT function of excel) 7 Rate 8 Pv 9 Nper 10 PMT 11 1.00355165 0.00355165 $465,000 300 (25*12) $2,522.20 E25 X Fax =PMT(E23, E24,-E21) B F I G H C Pmt PV E $2,522.20 $406,890 $115,000 Pv1 Monthly payment Loan Balance at the end of 60 months (Using PV function of excel) Lump sum payment at end of 5 years PAYMENT FOR NEXT 5 YEARS Loan Balance after lump sum payment Semi annual interest rate =(4.75/2)% Monthly interest rate =(1.02375^(1/6))-1 Number of months of Mortgage=20*12 Monthly payment (Using PMT function of excel) (406890-115000) Rate 1 Nper1 PMT1 $291,890 0.02375 0.004705508 240 $2,032.12 E40 x fc =PMT(E38, E39,-E37) H F G (406890-115000) BC D Pv1 Loan Balance after lump sum payment Semi annual interest rate =(4.75/2)% Rate 1 Monthly interest rate =(1.02375^(1/6))-1 Nper1 Number of months of Mortgage=20*12 PMT1 Monthly payment (Using PMT function of excel) E $291,890 0.02375 0.004705508 240 $2,032.12 Rate Nper Pmt PV2 Loan Balance at the end of 120 months =Present Value of future payments for(240-60)=180months Monthly interest rate =r= 0.004705508 Number of months of future payment 180 Monthly payment $2,032.12 Loan Balance at the end of 120 months $246,352 (Using PV function of excel) C.(i) Size $246,352 Pv2 Rate 2 Nper2 PMT2 Size of Principal balance being refinaced NEW MONTHLY PAYMENT Loan Balance Monthly interest rate =(5.25/12)% Number of months of Mortgage=15*12 Monthly payment | (Using PMT function of excel) $246,352 0.4375% 180 $1,980.37 File Home Insert Page Layout Formulas Data Review View H elp Tell me what you want to do F51 X for =PPMT(0.003551648, E51,300,-465000) E F G H I First 5 Years Principal Payment using PPMT function with Rate =0.003551648, Nper=300, Per=Month, Pv=-465000 Per PPMT Principal Principal Month Payment Balance $465,000 1 $870.69 | $464,129.31 2 5873 78 0463255 54 Per First 5 Years Principal Payment using PPMT function with Rate =0.003551648,Nper=300, Per=Month, Pv=-465001 PPMT Principal Principal Principal Month Payment Balance End of Year Balance $465,000 0 $465,000 1 $870.69 $464,129.31 1 $454,345 2 $873.78 $463,255.54 2 $443,227 3 $876.88 $462,378.66 $431,626 4 $880.00 $461,498.66 $418,489 5 $883.12 $460,615.54 $291,890 6 $886.26 $459,729.28 $283,779 7 $889.41 $458,839.88 $275,197 8 $892.56 $457,947.31 $266,119 9 $895.73 $457,051.58 $256,514 10 $898.92 $456,152.66 $246,352 11 $902.11 $455,250.55 $235,257 12 $905.31 $454,345.24 12 $223,564 13 $908.53 $453,436.72 13 $211,243 14 $911.75 $452,524.96 14 $199,368 15 $914.99 $451,609.97 15 $184,578 16 $918.24 $450,691.73 16 $170,160 17 $921.50 $449,770.22 17 $156,264 18 $924.78 $448,845.45 18 $138,957 19 $928.06 $447,917.39 19 $122,086 20 $931.36 $446,986.03 20 $104,307 21 $934.66 $446,051.36 21 $85,572 22 $937.98 $445,113.38 $65,830 23 $941.32 $444,172.06 23 $45,025 24 $944.66 $443,227.41 24 $23,102 25 $948.01 $442,279.39 $0 26 $951.38 $441,328.01 27 $954.76 $440,373.25 X axis: End of Year, Yaxis: Principal balance $500,000 $450,000 $400,000 $350,000 $300,000 T$250,000 $200,000 $150,000 $100,000 $50,000 10 30 14 4 4141 h 2274 24 25 First 5 Years Per Month PPMT Principal Principal Payment Balance $465,000 1 $870.69 $464,129.31 2 $873.78 $463,255.54 3 $876.88 $462,378.66 4 $880.00 $461,498.66 5 $883.12 $460,615.54 6 $886.26 $459,729.28 7 $889.41 $458,839.88 8 $892.56 $457,947.31 9 $895.73 $457,051.58 10 $898.92 $456,152.66 11 $902.11 $455,250.55 12 $905.31 $454,345.24 13 $908.53 $453,436.72 14 $911.75 $452,524.96 15 $914.99 $451,609.97 16 $918.24 $450,691.73 17 $921.50 $449,770.22 18 $924.78 $448,845.45 19 $928.06 $447,917.39 20 $931.36 $446,986.03 21 $934.66 $446,051.36 22 $937.98 $445,113.38 23 $941.32 $444,172.06 24 $944.66 $443,227.41 25 $948.01 $442,279.39 26 $951.38 $441,328.01 27 $954.76 $440,373.25 Principal End of Year Balance 0 $465,000 1 $454,345 2 $443,227 $431,626 $418,489 $291,890 $283,779 $275,197 $266,119 $256,514 $246,352 $235,257 12 $223,564 13 $211,243 14 $199,368 15 $184,578 16 $170,160 17 $156,264 18 $138,957 19 $122,086 20 $104,307 21 $85,572 $65,830 23 $45,025 24 $23,102 $0 NEXT 5 YEARS Principal Payment using PPMT function with Rate =0.004705508,Nper=240,Per=Month,Pv=-291890 Per PPMT Principal Principal Month Payment Balance $291,890 1 $658.63 $291,231.37 $661.73 $290,569.64 $664.84 $289,904.80 4 $667.97 $289,236.83 5 $671.11 $288,565.71 6 $674.27 $287,891.44 7 $677.45 $287,213.99 8 $680.63 $286,533.36 9 $683.84 $285,849.52 10 $687.05 $285,162.47 11 $690.29 $284,472.18 12 $693.53 $283,778.65 13 $696.80 $283,081.85 14 $700.08 $282,381.77 15 $703.37 $281,678.40 16 $706.68 $280,971.72 17 $710.01 $280,261.72 18 $713.35 $279,548.37 19 $716.70 $278,831.67 20 $720.08 $278,111.59 21 $723.46 $277,388.13 22 $726.87 $275,661.26 23 $730.29 $275,930.97 24 $733.73 $275,197.24 25 $737.18 $274,460.06 LAST 15 YEARS Principal Payment using PPMT function with Rate =0.4375%,Nper=180, Per=Month, Pv=-246352 Per PPMT Principal Principal Month Payment Balance $246,352 $902.58 $245,449.42 $906.53 $244,542.89 $910.49 $243,632.40 $914.48 $242,717.92 5 $918.48 $241,799.44 $922.50 $240,876.95 $926.53 $239,950.42 $930.59 $239,019.83 $934.66 $238,085.17 10 $938.75 $237,146.43 $942.85 $236,203.57 $946.98 $235,256.60 $951.12 $234,305.47 14 $955.28 $233,350.19 15 $959.46 $232,390.73 16 $963.66 $231,427.07 17 $967.88 $230,459.20 18 $972.11 $229,487.09 19 $976.36 $228,510.72 20 $980.63 $227,530.09 21 $984.92 $226,545.16 22 $989.23 $225,555.93 23 $993.56 $224,562.37 24 $997.91 $223,564.46 25 $1,002.27 $222,562.19