Question

Mr. H issues a 15 year mortgage of $275,000 at an annual interest rate of 3.6% to buy a house.The mortgage payments are made annually.

1.What is Mr. H's annual payment of principal and interest?

2.How much interest does Mr. H pay in the second year of the mortgage?

3.Suppose that immediately after making the second annual payment, Mr. H has the opportunity to refinance the remaining mortgage balance at an annual rate of 2.6% for the remaining period of 13 years. What is the largest lump sum refinancing payment that he would be willing to make today to secure the lower cost financing? Assume that he continues to make annual payments on the new mortgage.

4.Using the information from the refinancing question and assuming that Mr. H refinanced his mortgage at the lower rate after making two annual payments, how much is his remaining mortgage balance after making 9 of the lower annual payments in addition to the first two made before refinancing?

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I
2		1)
3		Calculation of annual Payment:
4
5		Annual payment can be calculated using PMT(RATE,NPER,PV,FV,TYPE) function in Excel as follows:
6
7		Given the following data:
8		Loan taken	275000
9		Duration of mortgage	15	Years
10		Interest rate	0.036
11
12		Annual Payment can be calculated as below:
13		RATE (Annual interst rate):	=D10
14		NPER (No of Years):	=D9
15		PV (Loan Amount):	=-D8
16		FV	0
17		TYPE	0
18		Annual Payment	=PMT(D13,D14,D15,D16,D17)	=PMT(D13,D14,D15,D16,D17)
19
20		Hence Annual Payment is	=D18
21
22
23		2)
24		Mortgage Table will be as follows:
25
26		Year	Beginning Balance	Annual Payment	Interest Paid	Principle Paid	Ending Balance
27		1	=D8	=$D$20	=D27*$D$10	=E27-F27	=D27-G27
28		=C27+1	=H27	=$D$20	=D28*$D$10	=E28-F28	=D28-G28
29		=C28+1	=H28	=$D$20	=D29*$D$10	=E29-F29	=D29-G29
30		=C29+1	=H29	=$D$20	=D30*$D$10	=E30-F30	=D30-G30
31
32		Hence interest paid during second year is	=F28
33
34
35		3)
36		The refinancing payment to be made on the existing mortgage will be the present value of mortgage.
37		Calculation of Value of Mortgage after 2nd payment:
38
39		Value of mortage will be the present value of all the monthly payments.
40
41		Given the following data:
42		Annual Payments	=D20
43		Interest Rate	=D10
44		Number of years remaining	=D9-2
45
46
47		Value of loan	=Present value of annuity of monthly payments
48			=$24,047.02*(P/A,3.60%,13)
49			=D42*PV(D43,D44,-1,0)	=D42*PV(D43,D44,-1,0)
50
51		Hence Value of loan after 2 payment is	=D49
52
53		Calculation of Annual Payment after refinancing:
54
55		Monthly payment can be calculated using PMT(RATE,NPER,PV,FV,TYPE) function in Excel as follows:
56
57		Given the following data:
58		Loan taken	=D51
59		Duration of mortgage	=D44	Years
60		Interest rate	0.026
61
62		Annual Payment can be calculated as below:
63		RATE (Annual interst rate):	=D60
64		NPER (No of Years):	=D59
65		PV (Loan Amount):	=-D58
66		FV	0
67		TYPE	0
68		Annual Payment	=PMT(D63,D64,D65,D66,D67)	=PMT(D63,D64,D65,D66,D67)
69
70		Hence Annual Payment is	=D68
71
72		Calculation of profit or loss from Refinancing:
73
74		Savings on annual Payments	=D20-D70	=D20-D70
75		Present Value of Savings	=Annual Savings*(P/A,2.60%,13)
76			=D74*PV(D60,D59,-1,0)	=D74*PV(D60,D59,-1,0)
77
78		Hence largest amount to be paid to secure refinancing is	=D76
79
80
81		4)
82
83		Calculation of Value of refinanced Mortgage after 9 payment:
84
85		Value of mortage will be the present value of all the monthly payments.
86
87		Given the following data:
88		Annual Payments	=D70
89		Interest Rate	=D60
90		Number of years remaining	=D59-9
91
92
93		Value of loan	=Present value of annuity of monthly payments
94			=$22561.57*(P/A,2.60%,4)
95			=D88*PV(D89,D90,-1,0)	=D88*PV(D89,D90,-1,0)
96
97		Hence refinanced loan after 9th payment	=D95
98