Question

Suppose you have taken out a $175,000 fully amortizing fixed rate mortgage loan that has a term of 15 years and an interest rate of 5.25%. After your first mortgage payment, how much of the original loan balance is remaining?

Can you teach me how to do it on excel, please? I am confused on how to make it monthly compounding.

Answer 1

To calculate loan amortization in excel, steps are as follow:

1. First of all you have to calculate monthly installments. It can be calculated using PMT Function.

i.e. =PMT(Rate, Nper, Pv, Fv, Type)

RATE is Rate of interest . Here as payment is made monthly, you have to divide ROI by12

Nper signifies number of payment made for loan. In the given question, payment is made monthly and loan is of 15 years, Nper is 180

Pv is to be taken in negative as it is a loan.

Fv is 0 as we have to completely pay off the loan

Type is either 0 or 1

0 means payment is due at end of period and 1 is due at begining of period.

Please refer above attachment as how EMI is calculated and see formula bar.

After this, you can make table to calculate amortization.

In amortization table for interest calculation use, ipmt formula and

for principal calculation use ppmt formula

i.e. =IPMT(Rate, Per , Nper, Pv, Fv, Type)

=PPMT(Rate, Per , Nper, Pv, Fv, Type)

Here as payment is made monthly, you have to divide ROI by12

Per signifies period for which you want to make calculation

Nper signifies number of payment made for loan. In the given question, payment is made monthly and loan is of 15 years, Nper is 180

Pv is to be taken in negative as it is a loan.

Fv is 0 as we have to completely pay off the loan

Type is either 0 or 1

0 means payment is due at end of period and 1 is due at begining of period.

In image herein formula bar you can see that I have use dollar sign in all formula other than Per  because I have to freeze the cell, otherwise while dragging, cells in formula also get dragged. I have not freezed Per because period is to be dragged for which we want to calculate interest and principal. (use can use function+f4 for dollar sign)

Also you can refer complete amortization table below:

Period	EMI	Interest component	Principal component	Principal o/s
0	-	-	-	175000
1	1406.79	765.63	641.16	174358.84
2	1406.79	762.82	643.97	173714.87
3	1406.79	760.00	646.78	173068.09
4	1406.79	757.17	649.61	172418.48
5	1406.79	754.33	652.46	171766.02
6	1406.79	751.48	655.31	171110.71
7	1406.79	748.61	658.18	170452.53
8	1406.79	745.73	661.06	169791.48
9	1406.79	742.84	663.95	169127.53
10	1406.79	739.93	666.85	168460.68
11	1406.79	737.02	669.77	167790.91
12	1406.79	734.09	672.70	167118.21
13	1406.79	731.14	675.64	166442.56
14	1406.79	728.19	678.60	165763.96
15	1406.79	725.22	681.57	165082.39
16	1406.79	722.24	684.55	164397.84
17	1406.79	719.24	687.55	163710.30
18	1406.79	716.23	690.55	163019.74
19	1406.79	713.21	693.57	162326.17
20	1406.79	710.18	696.61	161629.56
21	1406.79	707.13	699.66	160929.90
22	1406.79	704.07	702.72	160227.19
23	1406.79	700.99	705.79	159521.39
24	1406.79	697.91	708.88	158812.51
25	1406.79	694.80	711.98	158100.53
26	1406.79	691.69	715.10	157385.44
27	1406.79	688.56	718.22	156667.21
28	1406.79	685.42	721.37	155945.85
29	1406.79	682.26	724.52	155221.32
30	1406.79	679.09	727.69	154493.63
31	1406.79	675.91	730.88	153762.75
32	1406.79	672.71	734.07	153028.68
33	1406.79	669.50	737.29	152291.39
34	1406.79	666.27	740.51	151550.88
35	1406.79	663.04	743.75	150807.13
36	1406.79	659.78	747.00	150060.13
37	1406.79	656.51	750.27	149309.85
38	1406.79	653.23	753.56	148556.30
39	1406.79	649.93	756.85	147799.45
40	1406.79	646.62	760.16	147039.28
41	1406.79	643.30	763.49	146275.79
42	1406.79	639.96	766.83	145508.96
43	1406.79	636.60	770.18	144738.78
44	1406.79	633.23	773.55	143965.23
45	1406.79	629.85	776.94	143188.29
46	1406.79	626.45	780.34	142407.95
47	1406.79	623.03	783.75	141624.20
48	1406.79	619.61	787.18	140837.02
49	1406.79	616.16	790.62	140046.40
50	1406.79	612.70	794.08	139252.31
51	1406.79	609.23	797.56	138454.76
52	1406.79	605.74	801.05	137653.71
53	1406.79	602.23	804.55	136849.16
54	1406.79	598.72	808.07	136041.09
55	1406.79	595.18	811.61	135229.48
56	1406.79	591.63	815.16	134414.32
57	1406.79	588.06	818.72	133595.60
58	1406.79	584.48	822.31	132773.30
59	1406.79	580.88	825.90	131947.39
60	1406.79	577.27	829.52	131117.88
61	1406.79	573.64	833.15	130284.73
62	1406.79	570.00	836.79	129447.94
63	1406.79	566.33	840.45	128607.49
64	1406.79	562.66	844.13	127763.36
65	1406.79	558.96	847.82	126915.54
66	1406.79	555.26	851.53	126064.01
67	1406.79	551.53	855.26	125208.75
68	1406.79	547.79	859.00	124349.76
69	1406.79	544.03	862.76	123487.00
70	1406.79	540.26	866.53	122620.47
71	1406.79	536.46	870.32	121750.15
72	1406.79	532.66	874.13	120876.02
73	1406.79	528.83	877.95	119998.07
74	1406.79	524.99	881.79	119116.27
75	1406.79	521.13	885.65	118230.62
76	1406.79	517.26	889.53	117341.09
77	1406.79	513.37	893.42	116447.67
78	1406.79	509.46	897.33	115550.35
79	1406.79	505.53	901.25	114649.09
80	1406.79	501.59	905.20	113743.90
81	1406.79	497.63	909.16	112834.74
82	1406.79	493.65	913.13	111921.61
83	1406.79	489.66	917.13	111004.48
84	1406.79	485.64	921.14	110083.34
85	1406.79	481.61	925.17	109158.16
86	1406.79	477.57	929.22	108228.94
87	1406.79	473.50	933.28	107295.66
88	1406.79	469.42	937.37	106358.29
89	1406.79	465.32	941.47	105416.82
90	1406.79	461.20	945.59	104471.24
91	1406.79	457.06	949.72	103521.51
92	1406.79	452.91	953.88	102567.63
93	1406.79	448.73	958.05	101609.58
94	1406.79	444.54	962.24	100647.34
95	1406.79	440.33	966.45	99680.88
96	1406.79	436.10	970.68	98710.20
97	1406.79	431.86	974.93	97735.27
98	1406.79	427.59	979.19	96756.08
99	1406.79	423.31	983.48	95772.60
100	1406.79	419.01	987.78	94784.82
101	1406.79	414.68	992.10	93792.72
102	1406.79	410.34	996.44	92796.27
103	1406.79	405.98	1000.80	91795.47
104	1406.79	401.61	1005.18	90790.29
105	1406.79	397.21	1009.58	89780.71
106	1406.79	392.79	1014.00	88766.72
107	1406.79	388.35	1018.43	87748.28
108	1406.79	383.90	1022.89	86725.40
109	1406.79	379.42	1027.36	85698.03
110	1406.79	374.93	1031.86	84666.18
111	1406.79	370.41	1036.37	83629.81
112	1406.79	365.88	1040.91	82588.90
113	1406.79	361.33	1045.46	81543.44
114	1406.79	356.75	1050.03	80493.41
115	1406.79	352.16	1054.63	79438.78
116	1406.79	347.54	1059.24	78379.54
117	1406.79	342.91	1063.88	77315.66
118	1406.79	338.26	1068.53	76247.13
119	1406.79	333.58	1073.20	75173.93
120	1406.79	328.89	1077.90	74096.03
121	1406.79	324.17	1082.62	73013.41
122	1406.79	319.43	1087.35	71926.06
123	1406.79	314.68	1092.11	70833.95
124	1406.79	309.90	1096.89	69737.06
125	1406.79	305.10	1101.69	68635.38
126	1406.79	300.28	1106.51	67528.87
127	1406.79	295.44	1111.35	66417.52
128	1406.79	290.58	1116.21	65301.31
129	1406.79	285.69	1121.09	64180.22
130	1406.79	280.79	1126.00	63054.22
131	1406.79	275.86	1130.92	61923.30
132	1406.79	270.91	1135.87	60787.43
133	1406.79	265.95	1140.84	59646.59
134	1406.79	260.95	1145.83	58500.76
135	1406.79	255.94	1150.85	57349.91
136	1406.79	250.91	1155.88	56194.03
137	1406.79	245.85	1160.94	55033.09
138	1406.79	240.77	1166.02	53867.08
139	1406.79	235.67	1171.12	52695.96
140	1406.79	230.54	1176.24	51519.72
141	1406.79	225.40	1181.39	50338.33
142	1406.79	220.23	1186.56	49151.78
143	1406.79	215.04	1191.75	47960.03
144	1406.79	209.83	1196.96	46763.07
145	1406.79	204.59	1202.20	45560.87
146	1406.79	199.33	1207.46	44353.41
147	1406.79	194.05	1212.74	43140.67
148	1406.79	188.74	1218.05	41922.63
149	1406.79	183.41	1223.37	40699.25
150	1406.79	178.06	1228.73	39470.53
151	1406.79	172.68	1234.10	38236.42
152	1406.79	167.28	1239.50	36996.92
153	1406.79	161.86	1244.92	35752.00
154	1406.79	156.41	1250.37	34501.63
155	1406.79	150.94	1255.84	33245.78
156	1406.79	145.45	1261.34	31984.45
157	1406.79	139.93	1266.85	30717.60
158	1406.79	134.39	1272.40	29445.20
159	1406.79	128.82	1277.96	28167.24
160	1406.79	123.23	1283.55	26883.68
161	1406.79	117.62	1289.17	25594.51
162	1406.79	111.98	1294.81	24299.70
163	1406.79	106.31	1300.47	22999.23
164	1406.79	100.62	1306.16	21693.06
165	1406.79	94.91	1311.88	20381.18
166	1406.79	89.17	1317.62	19063.56
167	1406.79	83.40	1323.38	17740.18
168	1406.79	77.61	1329.17	16411.01
169	1406.79	71.80	1334.99	15076.02
170	1406.79	65.96	1340.83	13735.19
171	1406.79	60.09	1346.69	12388.50
172	1406.79	54.20	1352.59	11035.91
173	1406.79	48.28	1358.50	9677.41
174	1406.79	42.34	1364.45	8312.96
175	1406.79	36.37	1370.42	6942.54
176	1406.79	30.37	1376.41	5566.13
177	1406.79	24.35	1382.43	4183.70
178	1406.79	18.30	1388.48	2795.22
179	1406.79	12.23	1394.56	1400.66
180	1406.79	6.13	1400.66	0.00