Question

Exactly six years ago, Cathy bought her dream home using a 30-year mortgage with an APR of 6.5% on a $230,000 loan. She has been making her monthly payments. Today, she came to know that her bank is offering a special mortgage refinance offer at an APR of 4.25% on 20-year mortgages.
How much will Cathy’s monthly payment change, if she decides to refinance today?

Original loan
Years Ago (N)	6.00		years
Term (years, N0)	30.00		years
APR (R0)	6.50%		annual
Loan Amt (PV0)	$                 230,000.00
		($17,612.81)
New Loan
Term (years, N1)	20.00		years
APR (R1)	4.25%		annual
		($17,300.56)
Monthly savings (Excel)	increase/decrease by	????
Monthly savings (VBA)	increase/decrease by	????
Monthly savings (Math, Extra Credit))	increase/decrease by	????

Answer 1

In the given question, one must compute EMI for the loan through excel function 'PMT' for the old and as well as refinanced loan. (Assuming it is not balloon payment)

For Original Loan=>  '=PMT(6.50%/12,30*12,-230,000)' = 1,454

Since there have been 6 years from the original loan, hence the borrower has paid a part of the principal as well. Therefore one can use the below repayment schedule for 72 months (6 years*12months) of the original loan to find out the refinanced amount.

EMI for refinanced loan => '=PMT(4.25%/12,20*12,-211,750)' = 1,311

Hence the monthly saving = $ 143

Original Loan Schedule
Months	EMI	Interest part	Principal Part	Outstanding Loan
0	-	-	-	$2,30,000
1	$1,454	$1,246	$208	$2,29,792
2	$1,454	$1,245	$209	$2,29,583
3	$1,454	$1,244	$210	$2,29,373
4	$1,454	$1,242	$211	$2,29,162
5	$1,454	$1,241	$212	$2,28,949
6	$1,454	$1,240	$214	$2,28,735
7	$1,454	$1,239	$215	$2,28,521
8	$1,454	$1,238	$216	$2,28,305
9	$1,454	$1,237	$217	$2,28,088
10	$1,454	$1,235	$218	$2,27,869
11	$1,454	$1,234	$219	$2,27,650
12	$1,454	$1,233	$221	$2,27,429
13	$1,454	$1,232	$222	$2,27,207
14	$1,454	$1,231	$223	$2,26,984
15	$1,454	$1,229	$224	$2,26,760
16	$1,454	$1,228	$225	$2,26,535
17	$1,454	$1,227	$227	$2,26,308
18	$1,454	$1,226	$228	$2,26,080
19	$1,454	$1,225	$229	$2,25,851
20	$1,454	$1,223	$230	$2,25,620
21	$1,454	$1,222	$232	$2,25,389
22	$1,454	$1,221	$233	$2,25,156
23	$1,454	$1,220	$234	$2,24,922
24	$1,454	$1,218	$235	$2,24,686
25	$1,454	$1,217	$237	$2,24,450
26	$1,454	$1,216	$238	$2,24,212
27	$1,454	$1,214	$239	$2,23,972
28	$1,454	$1,213	$241	$2,23,732
29	$1,454	$1,212	$242	$2,23,490
30	$1,454	$1,211	$243	$2,23,247
31	$1,454	$1,209	$245	$2,23,002
32	$1,454	$1,208	$246	$2,22,756
33	$1,454	$1,207	$247	$2,22,509
34	$1,454	$1,205	$248	$2,22,261
35	$1,454	$1,204	$250	$2,22,011
36	$1,454	$1,203	$251	$2,21,760
37	$1,454	$1,201	$253	$2,21,507
38	$1,454	$1,200	$254	$2,21,253
39	$1,454	$1,198	$255	$2,20,998
40	$1,454	$1,197	$257	$2,20,741
41	$1,454	$1,196	$258	$2,20,483
42	$1,454	$1,194	$259	$2,20,224
43	$1,454	$1,193	$261	$2,19,963
44	$1,454	$1,191	$262	$2,19,700
45	$1,454	$1,190	$264	$2,19,437
46	$1,454	$1,189	$265	$2,19,172
47	$1,454	$1,187	$267	$2,18,905
48	$1,454	$1,186	$268	$2,18,637
49	$1,454	$1,184	$269	$2,18,368
50	$1,454	$1,183	$271	$2,18,097
51	$1,454	$1,181	$272	$2,17,824
52	$1,454	$1,180	$274	$2,17,550
53	$1,454	$1,178	$275	$2,17,275
54	$1,454	$1,177	$277	$2,16,998
55	$1,454	$1,175	$278	$2,16,720
56	$1,454	$1,174	$280	$2,16,440
57	$1,454	$1,172	$281	$2,16,159
58	$1,454	$1,171	$283	$2,15,876
59	$1,454	$1,169	$284	$2,15,591
60	$1,454	$1,168	$286	$2,15,305
61	$1,454	$1,166	$288	$2,15,018
62	$1,454	$1,165	$289	$2,14,729
63	$1,454	$1,163	$291	$2,14,438
64	$1,454	$1,162	$292	$2,14,146
65	$1,454	$1,160	$294	$2,13,852
66	$1,454	$1,158	$295	$2,13,557
67	$1,454	$1,157	$297	$2,13,260
68	$1,454	$1,155	$299	$2,12,961
69	$1,454	$1,154	$300	$2,12,661
70	$1,454	$1,152	$302	$2,12,359
71	$1,454	$1,150	$303	$2,12,055
72	$1,454	$1,149	$305	$2,11,750

The following screenshots could be used to understand the calculation methodology.

f x Loan Schedule в EMI A12 x ИА 12 13 Months 14 0 15 1 16 2 17 3 18 4 19 5 =PMT(6.5%/12,30*12,-230000) =B15 =B16 =B17 =R18 Loan Schedule Interest part 0 =E14*(6.5%/12) =E15*(6.5%/12) =E16*(6.5%/12) =E17*(6.5%/12) F18*16 5%/121 Principal Part 0 =B15-C15 =B16-C16 =B17-C17 =B18-C18 =B19-019 Outstanding Loan 230000 =E14-D15 =E15-D16 =E16-D17 =E17-018 F18-019