Question

A fully amortizing CPM loan is made for $150,000 at 5% interest rate for 20 years with monthly repayments.

1. Calculate the monthly debt service.

2. What will be the outstanding loan balance at the end of year 10 and how much total interest will have been paid on the loan by then?

3. If the borrower chooses to reduce the loan balance by $20,000 at the end of year 10, when will the loan be fully repaid if the borrower keeps paying the same amount every month as previously agreed?

Answer 1

1	Calculation of monthly repayment amount;
	PMT(R,Nper,Pv,Fv,0/1)
	R=Rate of Interest=5%
	Nper=No of Periods=240
	Pv =Amt set Aside Today=150000
	Fv=Goal To Be Achived=0
	0=For Investment at yr end
	993.25
	Monthly Debt Service=993.25/-
2	By Making This Type of table calculating :
	O/s Balance at the end of yr.10	93356.125
	Interst which have been paid by then	62636.125
		Principal	Interest	Installment	O/s Balance
					150000
		installment - interest	(O/s Balance*(5%/12))
	Apr-18	364	630	994	149636
	May-18	365.5288	628.4712	994	149270.47
	Jun-18	367.064021	626.935979	994	148903.41
	Jul-18	368.6056898	625.3943102	994	148534.8
	Aug-18	370.1538337	623.8461663	994	148164.65
	Sep-18	371.7084798	622.2915202	994	147792.94
	Oct-18	373.2696555	620.7303445	994	147419.67
	Nov-18	374.837388	619.162612	994	147044.83
	Dec-18	376.411705	617.588295	994	146668.42
	Jan-19	377.9926342	616.0073658	994	146290.43
	Feb-19	379.5802033	614.4197967	994	145910.85
	Mar-19	381.1744401	612.8255599	994	145529.67
	Apr-19	382.7753728	611.2246272	994	145146.9
	May-19	384.3830293	609.6169707	994	144762.51
	Jun-19	385.9974381	608.0025619	994	144376.52
	Jul-19	387.6186273	606.3813727	994	143988.9
	Aug-19	389.2466255	604.7533745	994	143599.65
	Sep-19	390.8814614	603.1185386	994	143208.77
	Oct-19	392.5231635	601.4768365	994	142816.25
	Nov-19	394.1717608	599.8282392	994	142422.08
	Dec-19	395.8272822	598.1727178	994	142026.25
	Jan-20	397.4897568	596.5102432	994	141628.76
	Feb-20	399.1592137	594.8407863	994	141229.6
	Mar-20	400.8356824	593.1643176	994	140828.76
	Apr-20	402.5191923	591.4808077	994	140426.24
	May-20	404.2097729	589.7902271	994	140022.03
	Jun-20	405.907454	588.092546	994	139616.13
	Jul-20	407.6122653	586.3877347	994	139208.52
	Aug-20	409.3242368	584.6757632	994	138799.19
	Sep-20	411.0433986	582.9566014	994	138388.15
	Oct-20	412.7697809	581.2302191	994	137975.38
	Nov-20	414.5034139	579.4965861	994	137560.87
	Dec-20	416.2443283	577.7556717	994	137144.63
	Jan-21	417.9925545	576.0074455	994	136726.64
	Feb-21	419.7481232	574.2518768	994	136306.89
	Mar-21	421.5110653	572.4889347	994	135885.38
	Apr-21	423.2814118	570.7185882	994	135462.1
	May-21	425.0591937	568.9408063	994	135037.04
	Jun-21	426.8444423	567.1555577	994	134610.19
	Jul-21	428.637189	565.362811	994	134181.56
	Aug-21	430.4374652	563.5625348	994	133751.12
	Sep-21	432.2453025	561.7546975	994	133318.87
	Oct-21	434.0607328	559.9392672	994	132884.81
	Nov-21	435.8837879	558.1162121	994	132448.93
	Dec-21	437.7144998	556.2855002	994	132011.21
	Jan-22	439.5529007	554.4470993	994	131571.66
	Feb-22	441.3990229	552.6009771	994	131130.26
	Mar-22	443.2528988	550.7471012	994	130687.01
	Apr-22	445.1145609	548.8854391	994	130241.89
	May-22	446.9840421	547.0159579	994	129794.91
	Jun-22	448.8613751	545.1386249	994	129346.05
	Jul-22	450.7465928	543.2534072	994	128895.3
	Aug-22	452.6397285	541.3602715	994	128442.66
	Sep-22	454.5408154	539.4591846	994	127988.12
	Oct-22	456.4498868	537.5501132	994	127531.67
	Nov-22	458.3669763	535.6330237	994	127073.31
	Dec-22	460.2921176	533.7078824	994	126613.01
	Jan-23	462.2253445	531.7746555	994	126150.79
	Feb-23	464.166691	529.833309	994	125686.62
	Mar-23	466.1161911	527.8838089	994	125220.5
	Apr-23	468.0738791	525.9261209	994	124752.43
	May-23	470.0397894	523.9602106	994	124282.39
	Jun-23	472.0139565	521.9860435	994	123810.38
	Jul-23	473.9964151	520.0035849	994	123336.38
	Aug-23	475.9872001	518.0127999	994	122860.39
	Sep-23	477.9863463	516.0136537	994	122382.41
	Oct-23	479.9938889	514.0061111	994	121902.41
	Nov-23	482.0098633	511.9901367	994	121420.4
	Dec-23	484.0343047	509.9656953	994	120936.37
	Jan-24	486.0672488	507.9327512	994	120450.3
	Feb-24	488.1087312	505.8912688	994	119962.19
	Mar-24	490.1587879	503.8412121	994	119472.03
	Apr-24	492.2174548	501.7825452	994	118979.82
	May-24	494.2847681	499.7152319	994	118485.53
	Jun-24	496.3607641	497.6392359	994	117989.17
	Jul-24	498.4454794	495.5545206	994	117490.73
	Aug-24	500.5389504	493.4610496	994	116990.19
	Sep-24	502.641214	491.358786	994	116487.55
	Oct-24	504.7523071	489.2476929	994	115982.79
	Nov-24	506.8722667	487.1277333	994	115475.92
	Dec-24	509.0011303	484.9988697	994	114966.92
	Jan-25	511.138935	482.861065	994	114455.78
	Feb-25	513.2857185	480.7142815	994	113942.5
	Mar-25	515.4415186	478.5584814	994	113427.05
	Apr-25	517.6063729	476.3936271	994	112909.45
	May-25	519.7803197	474.2196803	994	112389.67
	Jun-25	521.963397	472.036603	994	111867.7
	Jul-25	524.1556433	469.8443567	994	111343.55
	Aug-25	526.357097	467.642903	994	110817.19
	Sep-25	528.5677968	465.4322032	994	110288.62
	Oct-25	530.7877816	463.2122184	994	109757.84
	Nov-25	533.0170903	460.9829097	994	109224.82
	Dec-25	535.255762	458.744238	994	108689.56
	Jan-26	537.5038362	456.4961638	994	108152.06
	Feb-26	539.7613523	454.2386477	994	107612.3
	Mar-26	542.02835	451.97165	994	107070.27
	Apr-26	544.3048691	449.6951309	994	106525.96
	May-26	546.5909495	447.4090505	994	105979.37
	Jun-26	548.8866315	445.1133685	994	105430.49
	Jul-26	551.1919554	442.8080446	994	104879.29
	Aug-26	553.5069616	440.4930384	994	104325.79
	Sep-26	555.8316908	438.1683092	994	103769.96
	Oct-26	558.1661839	435.8338161	994	103211.79
	Nov-26	560.5104819	433.4895181	994	102651.28
	Dec-26	562.8646259	431.1353741	994	102088.41
	Jan-27	565.2286574	428.7713426	994	101523.19
	Feb-27	567.6026177	426.3973823	994	100955.58
	Mar-27	569.9865487	424.0134513	994	100385.6
	Apr-27	572.3804922	421.6195078	994	99813.217
	May-27	574.7844903	419.2155097	994	99238.432
	Jun-27	577.1985852	416.8014148	994	98661.234
	Jul-27	579.6228192	414.3771808	994	98081.611
	Aug-27	582.057235	411.942765	994	97499.553
	Sep-27	584.5018754	409.4981246	994	96915.052
	Oct-27	586.9567833	407.0432167	994	96328.095
	Nov-27	589.4220018	404.5779982	994	95738.673
	Dec-27	591.8975742	402.1024258	994	95146.775
	Jan-28	594.383544	399.616456	994	94552.392
	Feb-28	596.8799549	397.1200451	994	93955.512
	Mar-28	599.3868507	394.6131493	994	93356.125
			62636.12488
3	Calculating lenthg of loan
	NPER(Rate,-PMT,PV)
	NPER=no of periods a loan taken to be repaid with constant intt rate and payment
	Rate=rate of interest=0.42%
	PMT=i.e. amt to be paid each yr.=994
	PV=principal borrowed=93356.125-20000=73356.125/-
	88.52
	i.e. 89 months aggregate
	Alternate answer	continuing with above table but only change is that reduce 20000/- from mar 28 outstanding balance.