Question

1. Calculating the Payment for a Constant Payment Mortgage (CPM)
   • $100000 Mortgage
   • 5% Interest
   • 20 Years
   • Monthly Payments

If he wants to pay off the loan after 8 years, what would be the outstanding balance on the loan? Present Value Method

Answer 1

Solution.>

a> The Payment for a Constant Payment Mortgage (CPM) is $659.96. The formula used is: =PMT(5%/12,20*12,-100000)

b> The outstanding balance on the loan after 8 years = $71,355.02

I have solved it in Excel by creating a Loan Amortization Table for 96 months ie. 8 years. If you still have any doubt, kindly ask in the comment section.

Beginning Balance will be the loan taken.

The monthly payments = PMT(5%/12,20*12,-100000)

Interest Paid = Beginning Balance * 5%/12

Principal Paid = Total Payment - Interest Paid

Ending Balance = Beginning Balance - Principal Paid

Beginning Balance for the next period will be the Ending Balance of previous month.

Period	Beginning Balance	Total Payment	Interest Paid	Principal Paid	Ending Balance
0
1	100000	659.9557392	416.6666667	243.2890725	99756.71093
2	99756.71093	659.9557392	415.6529622	244.302777	99512.40815
3	99512.40815	659.9557392	414.635034	245.3207053	99267.08745
4	99267.08745	659.9557392	413.6128644	246.3428749	99020.74457
5	99020.74457	659.9557392	412.5864357	247.3693035	98773.37527
6	98773.37527	659.9557392	411.5557303	248.4000089	98524.97526
7	98524.97526	659.9557392	410.5207302	249.435009	98275.54025
8	98275.54025	659.9557392	409.4814177	250.4743215	98025.06593
9	98025.06593	659.9557392	408.4377747	251.5179645	97773.54796
10	97773.54796	659.9557392	407.3897832	252.565956	97520.98201
11	97520.98201	659.9557392	406.337425	253.6183142	97267.36369
12	97267.36369	659.9557392	405.2806821	254.6750572	97012.68864
13	97012.68864	659.9557392	404.219536	255.7362032	96756.95243
14	96756.95243	659.9557392	403.1539685	256.8017707	96500.15066
15	96500.15066	659.9557392	402.0839611	257.8717781	96242.27888
16	96242.27888	659.9557392	401.0094953	258.9462439	95983.33264
17	95983.33264	659.9557392	399.9305527	260.0251866	95723.30745
18	95723.30745	659.9557392	398.8471144	261.1086248	95462.19883
19	95462.19883	659.9557392	397.7591618	262.1965774	95200.00225
20	95200.00225	659.9557392	396.666676	263.2890632	94936.71319
21	94936.71319	659.9557392	395.5696383	264.3861009	94672.32709
22	94672.32709	659.9557392	394.4680295	265.4877097	94406.83938
23	94406.83938	659.9557392	393.3618307	266.5939085	94140.24547
24	94140.24547	659.9557392	392.2510228	267.7047164	93872.54075
25	93872.54075	659.9557392	391.1355865	268.8201528	93603.7206
26	93603.7206	659.9557392	390.0155025	269.9402367	93333.78036
27	93333.78036	659.9557392	388.8907515	271.0649877	93062.71537
28	93062.71537	659.9557392	387.7613141	272.1944252	92790.52095
29	92790.52095	659.9557392	386.6271706	273.3285686	92517.19238
30	92517.19238	659.9557392	385.4883016	274.4674376	92242.72494
31	92242.72494	659.9557392	384.3446873	275.611052	91967.11389
32	91967.11389	659.9557392	383.1963079	276.7594313	91690.35446
33	91690.35446	659.9557392	382.0431436	277.9125956	91412.44186
34	91412.44186	659.9557392	380.8851744	279.0705648	91133.3713
35	91133.3713	659.9557392	379.7223804	280.2333588	90853.13794
36	90853.13794	659.9557392	378.5547414	281.4009978	90571.73694
37	90571.73694	659.9557392	377.3822373	282.573502	90289.16344
38	90289.16344	659.9557392	376.2048477	283.7508915	90005.41255
39	90005.41255	659.9557392	375.0225523	284.9331869	89720.47936
40	89720.47936	659.9557392	373.8353307	286.1204085	89434.35895
41	89434.35895	659.9557392	372.6431623	287.3125769	89147.04638
42	89147.04638	659.9557392	371.4460266	288.5097126	88858.53666
43	88858.53666	659.9557392	370.2439028	289.7118364	88568.82483
44	88568.82483	659.9557392	369.0367701	290.9189691	88277.90586
45	88277.90586	659.9557392	367.8246077	292.1311315	87985.77473
46	87985.77473	659.9557392	366.6073947	293.3483445	87692.42638
47	87692.42638	659.9557392	365.3851099	294.5706293	87397.85575
48	87397.85575	659.9557392	364.1577323	295.7980069	87102.05775
49	87102.05775	659.9557392	362.9252406	297.0304986	86805.02725
50	86805.02725	659.9557392	361.6876135	298.2681257	86506.75912
51	86506.75912	659.9557392	360.4448297	299.5109095	86207.24821
52	86207.24821	659.9557392	359.1968676	300.7588717	85906.48934
53	85906.48934	659.9557392	357.9437056	302.0120336	85604.47731
54	85604.47731	659.9557392	356.6853221	303.2704171	85301.20689
55	85301.20689	659.9557392	355.4216954	304.5340438	84996.67285
56	84996.67285	659.9557392	354.1528035	305.8029357	84690.86991
57	84690.86991	659.9557392	352.8786246	307.0771146	84383.7928
58	84383.7928	659.9557392	351.5991367	308.3566026	84075.43619
59	84075.43619	659.9557392	350.3143175	309.6414217	83765.79477
60	83765.79477	659.9557392	349.0241449	310.9315943	83454.86318
61	83454.86318	659.9557392	347.7285966	312.2271426	83142.63604
62	83142.63604	659.9557392	346.4276501	313.5280891	82829.10795
63	82829.10795	659.9557392	345.1212831	314.8344561	82514.27349
64	82514.27349	659.9557392	343.8094729	316.1462663	82198.12722
65	82198.12722	659.9557392	342.4921968	317.4635425	81880.66368
66	81880.66368	659.9557392	341.169432	318.7863072	81561.87737
67	81561.87737	659.9557392	339.8411557	320.1145835	81241.76279
68	81241.76279	659.9557392	338.507345	321.4483943	80920.3144
69	80920.3144	659.9557392	337.1679767	322.7877626	80597.52663
70	80597.52663	659.9557392	335.8230276	324.1327116	80273.39392
71	80273.39392	659.9557392	334.4724747	325.4832645	79947.91066
72	79947.91066	659.9557392	333.1162944	326.8394448	79621.07121
73	79621.07121	659.9557392	331.7544634	328.2012758	79292.86994
74	79292.86994	659.9557392	330.3869581	329.5687811	78963.30116
75	78963.30116	659.9557392	329.0137548	330.9419844	78632.35917
76	78632.35917	659.9557392	327.6348299	332.3209093	78300.03826
77	78300.03826	659.9557392	326.2501594	333.7055798	77966.33268
78	77966.33268	659.9557392	324.8597195	335.0960197	77631.23666
79	77631.23666	659.9557392	323.4634861	336.4922531	77294.74441
80	77294.74441	659.9557392	322.061435	337.8943042	76956.85011
81	76956.85011	659.9557392	320.6535421	339.3021971	76617.54791
82	76617.54791	659.9557392	319.239783	340.7159563	76276.83195
83	76276.83195	659.9557392	317.8201331	342.1356061	75934.69635
84	75934.69635	659.9557392	316.3945681	343.5611711	75591.13517
85	75591.13517	659.9557392	314.9630632	344.992676	75246.1425
86	75246.1425	659.9557392	313.5255937	346.4301455	74899.71235
87	74899.71235	659.9557392	312.0821348	347.8736044	74551.83875
88	74551.83875	659.9557392	310.6326615	349.3230778	74202.51567
89	74202.51567	659.9557392	309.1771486	350.7785906	73851.73708
90	73851.73708	659.9557392	307.7155712	352.240168	73499.49691
91	73499.49691	659.9557392	306.2479038	353.7078354	73145.78908
92	73145.78908	659.9557392	304.7741212	355.1816181	72790.60746
93	72790.60746	659.9557392	303.2941977	356.6615415	72433.94592
94	72433.94592	659.9557392	301.808108	358.1476312	72075.79829
95	72075.79829	659.9557392	300.3158262	359.639913	71716.15837
96	71716.15837	659.9557392	298.8173266	361.1384127	71355.01996

Note: Give it a thumbs up if it helps! Thanks in advance!