Question

You are buying a house and trying to decide when you want to mortgage to mature. You are going to borrow $170,000. Your credit is good, so you will get the lowest rates available. You have two choices. You can finance for 30 years at an interest rate of 3.85% per year, or for 15 years with an interest rate of 3.15% per year. Set up a full amortization schedule for each of these options to help you make your decisions (so you will have two worksheet tabs in the file, one for the 15-year loan and one for the 30-year loan).

In class, we used three methods to calculate the payment that is required each month. You must use two of these methods in the assignment (use a different method for each of the two loan tabs). ( Methods: PMT Function, Self- Coded, PPMT IPMT)

One a third tab, calculate the extra principal payment needed each month if you take the 30-year loan but decide to pay it off in 15 years (so you owe nothing after 180 monthly payments). You can use either of the methods we used in class to calculate the extra principal.

Answer 1

Calculating the PMT function to find the annual payment -

PMT function --> PMT (rate, nper, pv, [fv], [type])

where, rate is the rate of interest, nper is the number of periods, PV is the value of loan, fv is the remaining balance at end of loan (0 here)

Loan Amount	170000
Payment Period	30
Interest Rate	3.85%
Year	Annual Payment
1	9652.86
2	9652.86
3	9652.86
4	9652.86
5	9652.86
6	9652.86
7	9652.86
8	9652.86
9	9652.86
10	9652.86
11	9652.86
12	9652.86
13	9652.86
14	9652.86
15	9652.86
16	9652.86
17	9652.86
18	9652.86
19	9652.86
20	9652.86
21	9652.86
22	9652.86
23	9652.86
24	9652.86
25	9652.86
26	9652.86
27	9652.86
28	9652.86
29	9652.86
30	9652.86

Loan Amount	170000
Payment Period	15
Interest Rate	3.85%
Year	Annual Payment
1	15130.05
2	15130.05
3	15130.05
4	15130.05
5	15130.05
6	15130.05
7	15130.05
8	15130.05
9	15130.05
10	15130.05
11	15130.05
12	15130.05
13	15130.05
14	15130.05
15	15130.05

Now, we can can calculate the interest payment using the function PPMT as below -

PPMT function = PPMT(rate, per, nper, pv, [fv] [type)]

where, rate is the rate of interest, per is the period for which interest is calculated, nper is the number of periods, PV is the value of loan, fv is the remaining balance at end of loan (0 here)

Hence, Principal = Payment - Interest

Loan Amount	170000				Loan Amount	170000
Payment Period	30				Payment Period	15
Interest Rate	3.85%				Interest Rate	3.85%
Year	Annual Payment	Interest	Principal		Year	Annual Payment	Interest	Principal		Difference
1	9652.86	6545.00	3107.86		1	15130.05	6545.00	8585.05		5477.20
2	9652.86	6425.35	3227.51		2	15130.05	6214.48	8915.58		5688.07
3	9652.86	6301.09	3351.77		3	15130.05	5871.23	9258.83		5907.06
4	9652.86	6172.05	3480.81		4	15130.05	5514.76	9615.29		6134.48
5	9652.86	6038.03	3614.82		5	15130.05	5144.57	9985.48		6370.66
6	9652.86	5898.86	3753.99		6	15130.05	4760.13	10369.92		6615.93
7	9652.86	5754.34	3898.52		7	15130.05	4360.89	10769.16		6870.64
8	9652.86	5604.24	4048.61		8	15130.05	3946.28	11183.77		7135.16
9	9652.86	5448.37	4204.48		9	15130.05	3515.70	11614.35		7409.86
10	9652.86	5286.50	4366.36		10	15130.05	3068.55	12061.50		7695.14
11	9652.86	5118.39	4534.46		11	15130.05	2604.18	12525.87		7991.41
12	9652.86	4943.82	4709.04		12	15130.05	2121.94	13008.12		8299.08
13	9652.86	4762.52	4890.34		13	15130.05	1621.12	13508.93		8618.59
14	9652.86	4574.24	5078.61		14	15130.05	1101.03	14029.02		8950.41
15	9652.86	4378.71	5274.14		15	15130.05	560.91	14569.14		9295.00
16	9652.86	4175.66	5477.20
17	9652.86	3964.79	5688.07
18	9652.86	3745.80	5907.06
19	9652.86	3518.37	6134.48
20	9652.86	3282.20	6370.66
21	9652.86	3036.93	6615.93
22	9652.86	2782.21	6870.64
23	9652.86	2517.69	7135.16
24	9652.86	2242.99	7409.86
25	9652.86	1957.71	7695.14
26	9652.86	1661.45	7991.41
27	9652.86	1353.78	8299.08
28	9652.86	1034.26	8618.59
29	9652.86	702.45	8950.41
30	9652.86	357.86	9295.00

The difference is the difference between the principal of both the options.