Question

Dave takes out a 23-year mortgage of 290000 dollars for his new house. Dave gets an interest rate of 14.4 percent compounded monthly. He agrees to make equal monthly payments, the first coming in one month. After making the 70th payment, Dave wants to buy a boat, so he wants to refinance his house to reduce his monthly payment by 400 dollars, and to get a better interest rate. In particular, he negotiates a new rate of 7.2 percent compounded monthly, and agrees to make equal monthly payments (each 400 dollars less than his original payments) for as long as necessary, followed by a single smaller payment. WHAT WILL BE DAVE'S FINAL PAYMENT AMOUNT BE?

Answer 1

Loan Principle Amount		2,90,000.00
Annual Interest Rate		14.40%= 1.20%(monthly)
Loan Period (in months)		276.00 (23*12
Original Repayment Amount		Pmt(1.20%,276,-290000)= 3,614.34

after 70 repayment , the balance capital amount would be :

Repayment Number	Opening Balance	Loan Repayment	Interest Charged	Capital Repaid	Closing Balance
1	2,90,000.00	3,614.34	3,480.00	134.34	2,89,865.66
2	2,89,865.66	3,614.34	3,478.39	135.95	2,89,729.71
3	2,89,729.71	3,614.34	3,476.76	137.58	2,89,592.12
4	2,89,592.12	3,614.34	3,475.11	139.24	2,89,452.89
5	2,89,452.89	3,614.34	3,473.43	140.91	2,89,311.98
6	2,89,311.98	3,614.34	3,471.74	142.60	2,89,169.38
7	2,89,169.38	3,614.34	3,470.03	144.31	2,89,025.08
60	2,78,565.47	3,614.34	3,342.79	271.56	2,78,293.92
61	2,78,293.92	3,614.34	3,339.53	274.81	2,78,019.10
62	2,78,019.10	3,614.34	3,336.23	278.11	2,77,740.99
63	2,77,740.99	3,614.34	3,332.89	281.45	2,77,459.54
64	2,77,459.54	3,614.34	3,329.51	284.83	2,77,174.72
65	2,77,174.72	3,614.34	3,326.10	288.24	2,76,886.47
66	2,76,886.47	3,614.34	3,322.64	291.70	2,76,594.77
67	2,76,594.77	3,614.34	3,319.14	295.20	2,76,299.57
68	2,76,299.57	3,614.34	3,315.59	298.75	2,76,000.82
69	2,76,000.82	3,614.34	3,312.01	302.33	2,75,698.49
70	2,75,698.49	3,614.34	3,308.38	305.96	2,75,392.53

he has to repay the remaining balance of 2,75,392.53 by paying a monthly EMI of ( 3,614.34 - 400 =$ 3214.34) at rate of 7.2%(0.60 % monthly)

Using the excel function , NPER(rate,pmt,pv,[fv],[type])

i.e. NPER(0.6%,-3214.34,275352.53,0) = 120.61

So, payment made for 120 months =120/12 = 10 years

The last small amount is =2,040.49

Repayment Number	Opening Balance	Loan Repayment	Interest Charged	Capital Repaid	Closing Balance
1	2,75,392.53	3,214.34	1,652.36	1,561.98	2,73,830.55
2	2,73,830.55	3,214.34	1,642.98	1,571.36	2,72,259.19
3	2,72,259.19	3,214.34	1,633.56	1,580.78	2,70,678.40
114	23,926.75	3,214.34	143.56	3,070.78	20,855.97
115	20,855.97	3,214.34	125.14	3,089.20	17,766.77
116	17,766.77	3,214.34	106.60	3,107.74	14,659.03
117	14,659.03	3,214.34	87.95	3,126.39	11,532.64
118	11,532.64	3,214.34	69.20	3,145.14	8,387.50
119	8,387.50	3,214.34	50.32	3,164.02	5,223.48
120	5,223.48	3,214.34	31.34	3,183.00	2,040.49