Loan repayment schedule

[swapnilk]

Hi,

Can someone please help me create a loan repayment schedule as per attached excel file, with the undernoted available information:

Sanction Date : 18/08/2022
Sanction Amount : 1,50,00,000.00
Installment Frequency : MONTHLY
Interest Frequency : MONTHLY
Installment Start Date : 18/09/2022
Expiry Date : 18/09/2032
Principal Installment : 2,37,430.00
Interest Rate : 14.50
Freq of Compounding : M MONTHLY
No.of Installments : 120

 

[swapnilk]

Book1.xlsx
	A	B	C	D	E	F
1	DATE	DEBITS		CREDITS		BALANCE
2		DISBURSEMENT	INTEREST	PRINCIPAL	INTEREST
3	18/08/2022	1,50,00,000.00				1,50,00,000.00
4	31/08/2022		83,425.00			1,50,83,425.00
5	18/09/2022			1,54,005.00	83,425.00	1,48,45,995.00
6	30/09/2022		1,78,535.00			1,50,24,530.00
7	18/10/2022			58,895.00	1,78,535.00	1,47,87,100.00
8	31/10/2022		1,83,708.00			1,49,70,808.00
9	18/11/2022			53,722.00	1,83,708.00	1,47,33,378.00
10	30/11/2022		1,77,193.00			1,49,10,571.00
11	18/12/2022			60,237.00	1,77,193.00	1,46,73,141.00
12	31/12/2022		1,82,304.00			1,48,55,445.00
13	18/01/2023			55,126.00	1,82,304.00	1,46,18,015.00
14	31/01/2023		1,81,625.00			1,47,99,640.00
15	18/02/2023			55,805.00	1,81,625.00	1,45,62,210.00
16	28/02/2023		1,63,583.00			1,47,25,793.00
17	18/03/2023			73,847.00	1,63,583.00	1,44,88,363.00
18	31/03/2023		1,80,029.00			1,46,68,392.00
19	18/04/2023			57,401.00	1,80,029.00	1,44,30,962.00
20	30/04/2023		1,73,589.00			1,46,04,551.00
21	18/05/2023			63,841.00	1,73,589.00	1,43,67,121.00
22	31/05/2023		1,78,536.00			1,45,45,657.00
23	18/06/2023			58,894.00	1,78,536.00	1,43,08,227.00
24	30/06/2023		1,72,126.00			1,44,80,353.00
25	18/07/2023			65,304.00	1,72,126.00	1,42,42,923.00
26	31/07/2023		1,77,006.00			1,44,19,929.00
27	18/08/2023			60,424.00	1,77,006.00	1,41,82,499.00
28	31/08/2023		1,76,262.00			1,43,58,761.00
29	18/09/2023			61,168.00	1,76,262.00	1,41,21,331.00
30	30/09/2023		1,69,899.00			1,42,91,230.00
31	18/10/2023			67,531.00	1,69,899.00	1,40,53,800.00
32	31/10/2023		1,74,677.00			1,42,28,477.00
33	18/11/2023			62,753.00	1,74,677.00	1,39,91,047.00
34	30/11/2023		1,68,346.00			1,41,59,393.00
35	18/12/2023			69,084.00	1,68,346.00	1,39,21,963.00
36	31/12/2023		1,73,053.00			1,40,95,016.00
37	18/01/2024			64,377.00	1,73,053.00	1,38,57,586.00
38	31/01/2024		1,72,261.00			1,40,29,847.00
39	18/02/2024			65,169.00	1,72,261.00	1,37,92,417.00
40	29/02/2024		1,60,500.00			1,39,52,917.00
41	18/03/2024			76,930.00	1,60,500.00	1,37,15,487.00
42	31/03/2024		1,70,511.00			1,38,85,998.00
43	18/04/2024			66,919.00	1,70,511.00	1,36,48,568.00
44	30/04/2024		1,64,264.00			1,38,12,832.00
45	18/05/2024			73,166.00	1,64,264.00	1,35,75,402.00
46	31/05/2024		1,68,785.00			1,37,44,187.00
47	18/06/2024			68,645.00	1,68,785.00	1,35,06,757.00
48	30/06/2024		1,62,574.00			1,36,69,331.00
49	18/07/2024			74,856.00	1,62,574.00	1,34,31,901.00
50	31/07/2024		1,67,018.00			1,35,98,919.00
51	18/08/2024			70,412.00	1,67,018.00	1,33,61,489.00
52	31/08/2024		1,66,151.00			1,35,27,640.00
53	18/09/2024			71,279.00	1,66,151.00	1,32,90,210.00
54	30/09/2024		1,59,994.00			1,34,50,204.00
55	18/10/2024			77,436.00	1,59,994.00	1,32,12,774.00
56	31/10/2024		1,64,320.00			1,33,77,094.00
57	18/11/2024			73,110.00	1,64,320.00	1,31,39,664.00
58	30/11/2024		1,58,199.00			1,32,97,863.00
59	18/12/2024			79,231.00	1,58,199.00	1,30,60,433.00
60	31/12/2024		1,62,444.00			1,32,22,877.00
61	18/01/2025			74,986.00	1,62,444.00	1,29,85,447.00
62	31/01/2025		1,61,520.00			1,31,46,967.00
63	18/02/2025			75,910.00	1,61,520.00	1,29,09,537.00
64	28/02/2025		1,45,200.00			1,30,54,737.00
65	18/03/2025			92,230.00	1,45,200.00	1,28,17,307.00
66	31/03/2025		1,59,449.00			1,29,76,756.00
67	18/04/2025			77,981.00	1,59,449.00	1,27,39,326.00
68	30/04/2025		1,53,428.00			1,28,92,754.00
69	18/05/2025			84,002.00	1,53,428.00	1,26,55,324.00
70	31/05/2025		1,57,455.00			1,28,12,779.00
71	18/06/2025			79,975.00	1,57,455.00	1,25,75,349.00
72	30/06/2025		1,51,474.00			1,27,26,823.00
73	18/07/2025			85,956.00	1,51,474.00	1,24,89,393.00
74	31/07/2025		1,55,411.00			1,26,44,804.00
75	18/08/2025			82,019.00	1,55,411.00	1,24,07,374.00
76	31/08/2025		1,54,401.00			1,25,61,775.00
77	18/09/2025			83,029.00	1,54,401.00	1,23,24,345.00
78	30/09/2025		1,48,483.00			1,24,72,828.00
79	18/10/2025			88,947.00	1,48,483.00	1,22,35,398.00
80	31/10/2025		1,52,283.00			1,23,87,681.00
81	18/11/2025			85,147.00	1,52,283.00	1,21,50,251.00
82	30/11/2025		1,46,408.00			1,22,96,659.00
83	18/12/2025			91,022.00	1,46,408.00	1,20,59,229.00
84	31/12/2025		1,50,114.00			1,22,09,343.00
85	18/01/2026			87,316.00	1,50,114.00	1,19,71,913.00
86	31/01/2026		1,49,038.00			1,21,20,951.00
87	18/02/2026			88,392.00	1,49,038.00	1,18,83,521.00
88	28/02/2026		1,33,787.00			1,20,17,308.00
89	18/03/2026			1,03,643.00	1,33,787.00	1,17,79,878.00
90	31/03/2026		1,46,673.00			1,19,26,551.00
91	18/04/2026			90,757.00	1,46,673.00	1,16,89,121.00
92	30/04/2026		1,40,912.00			1,18,30,033.00
93	18/05/2026			96,518.00	1,40,912.00	1,15,92,603.00
94	31/05/2026		1,44,367.00			1,17,36,970.00
95	18/06/2026			93,063.00	1,44,367.00	1,14,99,540.00
96	30/06/2026		1,38,653.00			1,16,38,193.00
97	18/07/2026			98,777.00	1,38,653.00	1,14,00,763.00
98	31/07/2026		1,42,005.00			1,15,42,768.00
99	18/08/2026			95,425.00	1,42,005.00	1,13,05,338.00
100	31/08/2026		1,40,829.00			1,14,46,167.00
101	18/09/2026			96,601.00	1,40,829.00	1,12,08,737.00
102	30/09/2026		1,35,187.00			1,13,43,924.00
103	18/10/2026			1,02,243.00	1,35,187.00	1,11,06,494.00
104	31/10/2026		1,38,381.00			1,12,44,875.00
105	18/11/2026			99,049.00	1,38,381.00	1,10,07,445.00
106	30/11/2026		1,32,788.00			1,11,40,233.00
107	18/12/2026			1,04,642.00	1,32,788.00	1,09,02,803.00
108	31/12/2026		1,35,872.00			1,10,38,675.00
109	18/01/2027			1,01,558.00	1,35,872.00	1,08,01,245.00
110	31/01/2027		1,34,622.00			1,09,35,867.00
111	18/02/2027			1,02,808.00	1,34,622.00	1,06,98,437.00
112	28/02/2027		1,20,605.00			1,08,19,042.00
113	18/03/2027			1,16,825.00	1,20,605.00	1,05,81,612.00
114	31/03/2027		1,31,917.00			1,07,13,529.00
115	18/04/2027			1,05,513.00	1,31,917.00	1,04,76,099.00
116	30/04/2027		1,26,456.00			1,06,02,555.00
117	18/05/2027			1,10,974.00	1,26,456.00	1,03,65,125.00
118	31/05/2027		1,29,251.00			1,04,94,376.00
119	18/06/2027			1,08,179.00	1,29,251.00	1,02,56,946.00
120	30/06/2027		1,23,844.00			1,03,80,790.00
121	18/07/2027			1,13,586.00	1,23,844.00	1,01,43,360.00
122	31/07/2027		1,26,520.00			1,02,69,880.00
123	18/08/2027			1,10,910.00	1,26,520.00	1,00,32,450.00
124	31/08/2027		1,25,154.00			1,01,57,604.00
125	18/09/2027			1,12,276.00	1,25,154.00	99,20,174.00
126	30/09/2027		1,19,830.00			1,00,40,004.00
127	18/10/2027			1,17,600.00	1,19,830.00	98,02,574.00
128	31/10/2027		1,22,323.00			99,24,897.00
129	18/11/2027			1,15,107.00	1,22,323.00	96,87,467.00
130	30/11/2027		1,17,057.00			98,04,524.00
131	18/12/2027			1,20,373.00	1,17,057.00	95,67,094.00
132	31/12/2027		1,19,423.00			96,86,517.00
133	18/01/2028			1,18,007.00	1,19,423.00	94,49,087.00
134	31/01/2028		1,17,970.00			95,67,057.00
135	18/02/2028			1,19,460.00	1,17,970.00	93,29,627.00
136	29/02/2028		1,09,086.00			94,38,713.00
137	18/03/2028			1,28,344.00	1,09,086.00	92,01,283.00
138	31/03/2028		1,14,918.00			93,16,201.00
139	18/04/2028			1,22,512.00	1,14,918.00	90,78,771.00
140	30/04/2028		1,09,803.00			91,88,574.00
141	18/05/2028			1,27,627.00	1,09,803.00	89,51,144.00
142	31/05/2028		1,11,837.00			90,62,981.00
143	18/06/2028			1,25,593.00	1,11,837.00	88,25,551.00
144	30/06/2028		1,06,785.00			89,32,336.00
145	18/07/2028			1,30,645.00	1,06,785.00	86,94,906.00
146	31/07/2028		1,08,682.00			88,03,588.00
147	18/08/2028			1,28,748.00	1,08,682.00	85,66,158.00
148	31/08/2028		1,07,096.00			86,73,254.00
149	18/09/2028			1,30,334.00	1,07,096.00	84,35,824.00
150	30/09/2028		1,02,140.00			85,37,964.00
151	18/10/2028			1,35,290.00	1,02,140.00	83,00,534.00
152	31/10/2028		1,03,825.00			84,04,359.00
153	18/11/2028			1,33,605.00	1,03,825.00	81,66,929.00
154	30/11/2028		98,935.00			82,65,864.00
155	18/12/2028			1,38,495.00	98,935.00	80,28,434.00
156	31/12/2028		1,00,474.00			81,28,908.00
157	18/01/2029			1,36,956.00	1,00,474.00	78,91,478.00
158	31/01/2029		98,788.00			79,90,266.00
159	18/02/2029			1,38,642.00	98,788.00	77,52,836.00
160	28/02/2029		87,840.00			78,40,676.00
161	18/03/2029			1,49,590.00	87,840.00	76,03,246.00
162	31/03/2029		95,238.00			76,98,484.00
163	18/04/2029			1,42,192.00	95,238.00	74,61,054.00
164	30/04/2029		90,523.00			75,51,577.00
165	18/05/2029			1,46,907.00	90,523.00	73,14,147.00
166	31/05/2029		91,678.00			74,05,825.00
167	18/06/2029			1,45,752.00	91,678.00	71,68,395.00
168	30/06/2029		87,035.00			72,55,430.00
169	18/07/2029			1,50,395.00	87,035.00	70,18,000.00
170	31/07/2029		88,031.00			71,06,031.00
171	18/08/2029			1,49,399.00	88,031.00	68,68,601.00
172	31/08/2029		86,191.00			69,54,792.00
173	18/09/2029			1,51,239.00	86,191.00	67,17,362.00
174	30/09/2029		81,660.00			67,99,022.00
175	18/10/2029			1,55,770.00	81,660.00	65,61,592.00
176	31/10/2029		82,410.00			66,44,002.00
177	18/11/2029			1,55,020.00	82,410.00	64,06,572.00
178	30/11/2029		77,956.00			64,84,528.00
179	18/12/2029			1,59,474.00	77,956.00	62,47,098.00
180	31/12/2029		78,537.00			63,25,635.00
181	18/01/2030			1,58,893.00	78,537.00	60,88,205.00
182	31/01/2030		76,580.00			61,64,785.00
183	18/02/2030			1,60,850.00	76,580.00	59,27,355.00
184	28/02/2030		67,535.00			59,94,890.00
185	18/03/2030			1,69,895.00	67,535.00	57,57,460.00
186	31/03/2030		72,507.00			58,29,967.00
187	18/04/2030			1,64,923.00	72,507.00	55,92,537.00
188	30/04/2030		68,254.00			56,60,791.00
189	18/05/2030			1,69,176.00	68,254.00	54,23,361.00
190	31/05/2030		68,393.00			54,91,754.00
191	18/06/2030			1,69,037.00	68,393.00	52,54,324.00
192	30/06/2030		64,223.00			53,18,547.00
193	18/07/2030			1,73,207.00	64,223.00	50,81,117.00
194	31/07/2030		64,178.00			51,45,295.00
195	18/08/2030			1,73,252.00	64,178.00	49,07,865.00
196	31/08/2030		62,044.00			49,69,909.00
197	18/09/2030			1,75,386.00	62,044.00	47,32,479.00
198	30/09/2030		58,004.00			47,90,483.00
199	18/10/2030			1,79,426.00	58,004.00	45,53,053.00
200	31/10/2030		57,675.00			46,10,728.00
201	18/11/2030			1,79,755.00	57,675.00	43,73,298.00
202	30/11/2030		53,724.00			44,27,022.00
203	18/12/2030			1,83,706.00	53,724.00	41,89,592.00
204	31/12/2030		53,199.00			42,42,791.00
205	18/01/2031			1,84,231.00	53,199.00	40,05,361.00
206	31/01/2031		50,930.00			40,56,291.00
207	18/02/2031			1,86,500.00	50,930.00	38,18,861.00
208	28/02/2031		44,082.00			38,62,943.00
209	18/03/2031			1,93,348.00	44,082.00	36,25,513.00
210	31/03/2031		46,252.00			36,71,765.00
211	18/04/2031			1,91,178.00	46,252.00	34,34,335.00
212	30/04/2031		42,533.00			34,76,868.00
213	18/05/2031			1,94,897.00	42,533.00	32,39,438.00
214	31/05/2031		41,497.00			32,80,935.00
215	18/06/2031			1,95,933.00	41,497.00	30,43,505.00
216	30/06/2031		37,875.00			30,81,380.00
217	18/07/2031			1,99,555.00	37,875.00	28,43,950.00
218	31/07/2031		36,627.00			28,80,577.00
219	18/08/2031			2,00,803.00	36,627.00	26,43,147.00
220	31/08/2031		34,154.00			26,77,301.00
221	18/09/2031			2,03,276.00	34,154.00	24,39,871.00
222	30/09/2031		30,681.00			24,70,552.00
223	18/10/2031			2,06,749.00	30,681.00	22,33,122.00
224	31/10/2031		29,105.00			22,62,227.00
225	18/11/2031			2,08,325.00	29,105.00	20,24,797.00
226	30/11/2031		25,735.00			20,50,532.00
227	18/12/2031			2,11,695.00	25,735.00	18,13,102.00
228	31/12/2031		23,932.00			18,37,034.00
229	18/01/2032			2,13,498.00	23,932.00	15,99,604.00
230	31/01/2032		21,303.00			16,20,907.00
231	18/02/2032			2,16,127.00	21,303.00	13,83,477.00
232	29/02/2032		17,542.00			14,01,019.00
233	18/03/2032			2,19,888.00	17,542.00	11,63,589.00
234	31/03/2032		15,933.00			11,79,522.00
235	18/04/2032			2,21,497.00	15,933.00	9,42,092.00
236	30/04/2032		12,831.00			9,54,923.00
237	18/05/2032			2,24,599.00	12,831.00	7,17,493.00
238	31/05/2032		10,439.00			7,27,932.00
239	18/06/2032			2,26,991.00	10,439.00	4,90,502.00
240	30/06/2032		7,449.00			4,97,951.00
241	18/07/2032			2,29,981.00	7,449.00	2,60,521.00
242	31/07/2032		4,812.00			2,65,333.00
243	18/08/2032			2,32,618.00	4,812.00	27,903.00
244	31/08/2032		1,947.00			29,850.00
245				1,49,72,097.00	1,35,19,503.00
Sheet2

 

[Dave Patton]

What have you tried?
What is your actual question?
Can you explain your schedule?

Normal amortization

Loan 2022.xlsm
	A	B	C	D	E
1		Principal	15,000,000.00
2		Rate	14.5%
3		Term	10
4		Payment	237,430.18
5					Balance
6			Payments	Interest	15,000,000.00
7		1	237,430.18	181,250.00	14,943,819.82
8		2	237,430.18	180,571.16	14,886,960.79
9		3	237,430.18	179,884.11	14,829,414.71
10		4	237,430.18	179,188.76	14,771,173.29
11		5	237,430.18	178,485.01	14,712,228.12
12		6	237,430.18	177,772.76	14,652,570.69
1a

Cell Formulas
Range	Formula
C4	C4	=PMT(C2/12,C3*12,-C1)
E6	E6	=C1
C7:C12	C7	=$C$4
D7:D12	D7	=E6*$C$2/12
E7:E12	E7	=E6-C7+D7