Dynamic Loan Drawdowns in Amortization Table

[smittymj]

Good day!

Hoping for some help on this problem. I'm trying to make a dynamic loan amortization table. Is it possible to make an amortization table that adjusts its principal and interest payments based on dynamic loan drawdowns instead of one lump-sum drawdown at the start? Rough idea below. Would appreciate any help!

Amortization.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
3									4,480,000	(5,072,588)	(4,480,000)	(592,588)	(0)
4								Month	Loan Actually Required	Payments	Principal	Interest	Balance
5								-					4,480,000
6	Payments p.a.	#	12					1	100,000	(84,543)	(65,876)	(18,667)	4,414,124
7								2	100,000	(84,543)	(66,151)	(18,392)	4,347,973
8	Loan Amount	A$	4,480,000					3	100,000	(84,543)	(66,427)	(18,117)	4,281,546
9	Loan Term	Years	5.0 Yr(s)					4	100,000	(84,543)	(66,703)	(17,840)	4,214,843
10	Interest Rate p.a.	% p.a.	5.0%					5	100,000	(84,543)	(66,981)	(17,562)	4,147,861
11								6	100,000	(84,543)	(67,260)	(17,283)	4,080,601
12								7	100,000	(84,543)	(67,541)	(17,003)	4,013,060
13								8	100,000	(84,543)	(67,822)	(16,721)	3,945,238
14								9	100,000	(84,543)	(68,105)	(16,438)	3,877,134
15								10	100,000	(84,543)	(68,388)	(16,155)	3,808,745
16								11	100,000	(84,543)	(68,673)	(15,870)	3,740,072
17								12	100,000	(84,543)	(68,959)	(15,584)	3,671,112
18								13	100,000	(84,543)	(69,247)	(15,296)	3,601,866
19								14	100,000	(84,543)	(69,535)	(15,008)	3,532,330
20								15	100,000	(84,543)	(69,825)	(14,718)	3,462,505
21								16	200,000	(84,543)	(70,116)	(14,427)	3,392,389
22								17	200,000	(84,543)	(70,408)	(14,135)	3,321,981
23								18	200,000	(84,543)	(70,702)	(13,842)	3,251,279
24								19	200,000	(84,543)	(70,996)	(13,547)	3,180,283
25								20	200,000	(84,543)	(71,292)	(13,251)	3,108,991
26								21	200,000	(84,543)	(71,589)	(12,954)	3,037,402
27								22	200,000	(84,543)	(71,887)	(12,656)	2,965,515
28								23	200,000	(84,543)	(72,187)	(12,356)	2,893,328
29								24	200,000	(84,543)	(72,488)	(12,056)	2,820,841
30								25	300,000	(84,543)	(72,790)	(11,754)	2,748,051
31								26	400,000	(84,543)	(73,093)	(11,450)	2,674,958
32								27	480,000	(84,543)	(73,397)	(11,146)	2,601,561
33								28		(84,543)	(73,703)	(10,840)	2,527,857
34								29		(84,543)	(74,010)	(10,533)	2,453,847
35								30		(84,543)	(74,319)	(10,224)	2,379,528
36								31		(84,543)	(74,628)	(9,915)	2,304,900
37								32		(84,543)	(74,939)	(9,604)	2,229,960
38								33		(84,543)	(75,252)	(9,292)	2,154,709
39								34		(84,543)	(75,565)	(8,978)	2,079,144
40								35		(84,543)	(75,880)	(8,663)	2,003,264
41								36		(84,543)	(76,196)	(8,347)	1,927,067
42								37		(84,543)	(76,514)	(8,029)	1,850,554
43								38		(84,543)	(76,832)	(7,711)	1,773,721
44								39		(84,543)	(77,153)	(7,391)	1,696,569
45								40		(84,543)	(77,474)	(7,069)	1,619,095
46								41		(84,543)	(77,797)	(6,746)	1,541,298
47								42		(84,543)	(78,121)	(6,422)	1,463,177
48								43		(84,543)	(78,447)	(6,097)	1,384,730
49								44		(84,543)	(78,773)	(5,770)	1,305,957
50								45		(84,543)	(79,102)	(5,441)	1,226,855
51								46		(84,543)	(79,431)	(5,112)	1,147,424
52								47		(84,543)	(79,762)	(4,781)	1,067,662
53								48		(84,543)	(80,095)	(4,449)	987,567
54								49		(84,543)	(80,428)	(4,115)	907,139
55								50		(84,543)	(80,763)	(3,780)	826,375
56								51		(84,543)	(81,100)	(3,443)	745,275
57								52		(84,543)	(81,438)	(3,105)	663,838
58								53		(84,543)	(81,777)	(2,766)	582,061
59								54		(84,543)	(82,118)	(2,425)	499,943
60								55		(84,543)	(82,460)	(2,083)	417,483
61								56		(84,543)	(82,804)	(1,740)	334,679
62								57		(84,543)	(83,149)	(1,394)	251,530
63								58		(84,543)	(83,495)	(1,048)	168,035
64								59		(84,543)	(83,843)	(700)	84,192
65								60		(84,543)	(84,192)	(351)	(0)
66								-		-	-	-	(0)
67								-		-	-	-	(0)
68								-		-	-	-	(0)
69								-		-	-	-	(0)
70								-		-	-	-	(0)
71									4,480,000	(5,072,588)	(4,480,000)	(592,588)
Amortization

Cell Formulas
Range	Formula
J3:M3	J3	=J71
N3	N3	=INDEX($N$6:$N$70,MATCH($D$9*12,$I$6:$I$70,0))
N5	N5	=D8
K6	K6	=PMT($D$10/$D$6,$D$9*$D$6,$D$8)
L6:L70	L6	=IF($I6=0,0,PPMT($D$10/$D$6,I6,$D$9*$D$6,$D$8))
M6:M70	M6	=IF(I6=0,0,IPMT($D$10/$D$6,I6,$D$9*$D$6,$D$8))
N6:N70	N6	=N5+L6
K7:K70	K7	=IF($I7=0,0,$K$6)
D8	D8	=5600000*0.8
I7:I70	I7	=IF(I6=0,0,IF((I6+1)<=($D$9*12),I6+1,0))
J71:M71	J71	=SUM(J6:J70)

 

[awoohaw]

What do you mean by drawdowns? Taking an advance on a line of credit, or an additional payment to principal? It has been a long time since I've worked on AM Scheds. But, I am pretty sure what you're asking is entirely possible. Is the payment recalculated on the drawdown date or the anniversary date? or the first of the month the drawdown occurs in? What does column J do in your xl2bb worksheet (is it the drawdown you mention?).

 

[awoohaw]

Is this something that you were looking for? As I look at it, i think you mean draw downs are advances, like a line of credit. But, it seems you have a maturity date that needs to be met regardless of when the draw down occurs. Likewise you could put interest rate changes in this mix as well.

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H							O	P	Q	R	S	T	U
1
2
3
4																Month	Advance At start of this month	Payments	Interest acrued thru start of this month	Principal	Balance
5																0	100000	0	0	0	100000
6		Payments p.a.	#	12												1	0	$1,887.12	416.67	$1,470.45	$98,529.55
7																2	0	$1,887.12	410.54	$1,476.58	$97,052.97
8		Loan Amount	A$	4,480,000.0000												3	0	$1,887.12	404.39	$1,482.73	$95,570.24
9		Loan Term	Years	5												4	0	$1,887.12	398.21	$1,488.91	$94,081.33
10		Interest Rate p.a.	% p.a.	0.05												5	0	$1,887.12	392.01	$1,495.11	$92,586.22
11																6	0	$1,887.12	385.78	$1,501.34	$91,084.88
12																7	0	$1,887.12	379.52	$1,507.60	$89,577.28
13		Payment:		$84,543.130												8	0	$1,887.12	373.24	$1,513.88	$88,063.40
14																9	0	$1,887.12	366.93	$1,520.19	$86,543.21
15		Advances:	Pd	Amt												10	0	$1,887.12	360.6	$1,526.52	$85,016.69
16			0	100000												11	0	$1,887.12	354.24	$1,532.88	$83,483.81
17			16	100000												12	0	$1,887.12	347.85	$1,539.27	$81,944.54
18			25	100000												13	0	$1,887.12	341.44	$1,545.68	$80,398.86
19			26	100000												14	0	$1,887.13	335	$1,552.13	$78,846.73
20			27	80000												15	0	$1,887.13	328.53	$1,558.60	$77,288.13
21																16	100000	$1,887.13	322.03	$1,565.10	$175,723.03
22																17	0	$4,379.27	732.18	$3,647.09	$172,075.94
23																18	0	$4,379.27	716.98	$3,662.29	$168,413.65
24																19	0	$4,379.27	701.72	$3,677.55	$164,736.10
25																20	0	$4,379.27	686.4	$3,692.87	$161,043.23
26																21	0	$4,379.27	671.01	$3,708.26	$157,334.97
27																22	0	$4,379.27	655.56	$3,723.71	$153,611.26
28																23	0	$4,379.26	640.05	$3,739.21	$149,872.05
29																24	0	$4,379.27	624.47	$3,754.80	$146,117.25
30																25	100000	$4,379.27	608.82	$3,770.45	$242,346.80
31																26	100000	$7,455.74	1009.78	$6,445.96	$335,900.84
32																27	80000	$10,616.28	1399.59	$9,216.69	$406,684.15
33																28	0	$13,216.05	1694.52	$11,521.53	$395,162.62
34																29	0	$13,216.05	1646.51	$11,569.54	$383,593.08
35																30	0	$13,216.05	1598.3	$11,617.75	$371,975.33
36																31	0	$13,216.05	1549.9	$11,666.15	$360,309.18
37																32	0	$13,216.05	1501.29	$11,714.76	$348,594.42
38																33	0	$13,216.05	1452.48	$11,763.57	$336,830.85
39																34	0	$13,216.05	1403.46	$11,812.59	$325,018.26
40																35	0	$13,216.05	1354.24	$11,861.81	$313,156.45
41																36	0	$13,216.05	1304.82	$11,911.23	$301,245.22
42																37	0	$13,216.05	1255.19	$11,960.86	$289,284.36
43																38	0	$13,216.05	1205.35	$12,010.70	$277,273.66
44																39	0	$13,216.05	1155.31	$12,060.74	$265,212.92
45																40	0	$13,216.05	1105.05	$12,111.00	$253,101.92
46																41	0	$13,216.05	1054.59	$12,161.46	$240,940.46
47																42	0	$13,216.05	1003.92	$12,212.13	$228,728.33
48																43	0	$13,216.05	953.03	$12,263.02	$216,465.31
49																44	0	$13,216.04	901.94	$12,314.10	$204,151.21
50																45	0	$13,216.04	850.63	$12,365.41	$191,785.80
51																46	0	$13,216.05	799.11	$12,416.94	$179,368.86
52																47	0	$13,216.05	747.37	$12,468.68	$166,900.18
53																48	0	$13,216.04	695.42	$12,520.62	$154,379.56
54																49	0	$13,216.05	643.25	$12,572.80	$141,806.76
55																50	0	$13,216.05	590.86	$12,625.19	$129,181.57
56																51	0	$13,216.04	538.26	$12,677.78	$116,503.79
57																52	0	$13,216.05	485.43	$12,730.62	$103,773.17
58																53	0	$13,216.04	432.39	$12,783.65	$90,989.52
59																54	0	$13,216.05	379.12	$12,836.93	$78,152.59
60																55	0	$13,216.04	325.64	$12,890.40	$65,262.19
61																56	0	$13,216.05	271.93	$12,944.12	$52,318.07
62																57	0	$13,216.05	217.99	$12,998.06	$39,320.01
63																58	0	$13,216.04	163.83	$13,052.21	$26,267.80
64																59	0	$13,216.04	109.45	$13,106.59	$13,161.21
65																60	0	$13,216.05	54.84	$13,161.21	$0.00
smittymj

Cell Formulas
Range	Formula
P5:P65	P5	=SEQUENCE(61,1,0,1)
Q5:Q65	Q5	=IFNA(INDEX($D$16:$D$20,MATCH(P5,$C$16:$C$20,0)),0)
U5	U5	=Q5+S5-T5
R6:R65	R6	=ROUND(PMT($D$10/12,($D$9*12)-P5,-U5),2)
S6:S65	S6	=ROUND(U5*$D$10/12,2)
T6:T65	T6	=R6-S6
U6:U65	U6	=U5-T6+Q6
D8	D8	=5600000*0.8
D13	D13	=ROUND(PMT(D10/12,D9*12,-D8),2)
Dynamic array formulas.

 

[awoohaw]

Is this something that you were looking for? As I look at it, i think you mean draw downs are advances, like a line of credit. But, it seems you have a maturity date that needs to be met regardless of when the draw down occurs. Likewise you could put interest rate changes in this mix as well.

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H							O	P	Q	R	S	T	U
4																Month	Advance At start of this month	Payments	Interest acrued thru start of this month	Principal	Balance
5																0	100000	0	0	0	100000
6		Payments p.a.	#	12												1	0	$1,887.12	416.67	$1,470.45	$98,529.55
7																2	0	$1,887.12	410.54	$1,476.58	$97,052.97
8		Loan Amount	A$	4,480,000.0000												3	0	$1,887.12	404.39	$1,482.73	$95,570.24
9		Loan Term	Years	5												4	0	$1,887.12	398.21	$1,488.91	$94,081.33
10		Interest Rate p.a.	% p.a.	0.05												5	0	$1,887.12	392.01	$1,495.11	$92,586.22
11																6	0	$1,887.12	385.78	$1,501.34	$91,084.88
12																7	0	$1,887.12	379.52	$1,507.60	$89,577.28
13		Payment:		$84,543.130												8	0	$1,887.12	373.24	$1,513.88	$88,063.40
14																9	0	$1,887.12	366.93	$1,520.19	$86,543.21
15		Advances:	Pd	Amt												10	0	$1,887.12	360.6	$1,526.52	$85,016.69
16			0	100000												11	0	$1,887.12	354.24	$1,532.88	$83,483.81
17			16	100000												12	0	$1,887.12	347.85	$1,539.27	$81,944.54
18			25	100000												13	0	$1,887.12	341.44	$1,545.68	$80,398.86
19			26	100000												14	0	$1,887.13	335	$1,552.13	$78,846.73
20			27	80000												15	0	$1,887.13	328.53	$1,558.60	$77,288.13
21																16	100000	$1,887.13	322.03	$1,565.10	$175,723.03
22																17	0	$4,379.27	732.18	$3,647.09	$172,075.94
23																18	0	$4,379.27	716.98	$3,662.29	$168,413.65
24																19	0	$4,379.27	701.72	$3,677.55	$164,736.10
25																20	0	$4,379.27	686.4	$3,692.87	$161,043.23
26																21	0	$4,379.27	671.01	$3,708.26	$157,334.97
27																22	0	$4,379.27	655.56	$3,723.71	$153,611.26
28																23	0	$4,379.26	640.05	$3,739.21	$149,872.05
29																24	0	$4,379.27	624.47	$3,754.80	$146,117.25
30																25	100000	$4,379.27	608.82	$3,770.45	$242,346.80
31																26	100000	$7,455.74	1009.78	$6,445.96	$335,900.84
32																27	80000	$10,616.28	1399.59	$9,216.69	$406,684.15
33																28	0	$13,216.05	1694.52	$11,521.53	$395,162.62
34																29	0	$13,216.05	1646.51	$11,569.54	$383,593.08
35																30	0	$13,216.05	1598.3	$11,617.75	$371,975.33
36																31	0	$13,216.05	1549.9	$11,666.15	$360,309.18
37																32	0	$13,216.05	1501.29	$11,714.76	$348,594.42
38																33	0	$13,216.05	1452.48	$11,763.57	$336,830.85
39																34	0	$13,216.05	1403.46	$11,812.59	$325,018.26
40																35	0	$13,216.05	1354.24	$11,861.81	$313,156.45
41																36	0	$13,216.05	1304.82	$11,911.23	$301,245.22
42																37	0	$13,216.05	1255.19	$11,960.86	$289,284.36
43																38	0	$13,216.05	1205.35	$12,010.70	$277,273.66
44																39	0	$13,216.05	1155.31	$12,060.74	$265,212.92
45																40	0	$13,216.05	1105.05	$12,111.00	$253,101.92
46																41	0	$13,216.05	1054.59	$12,161.46	$240,940.46
47																42	0	$13,216.05	1003.92	$12,212.13	$228,728.33
48																43	0	$13,216.05	953.03	$12,263.02	$216,465.31
49																44	0	$13,216.04	901.94	$12,314.10	$204,151.21
50																45	0	$13,216.04	850.63	$12,365.41	$191,785.80
51																46	0	$13,216.05	799.11	$12,416.94	$179,368.86
52																47	0	$13,216.05	747.37	$12,468.68	$166,900.18
53																48	0	$13,216.04	695.42	$12,520.62	$154,379.56
54																49	0	$13,216.05	643.25	$12,572.80	$141,806.76
55																50	0	$13,216.05	590.86	$12,625.19	$129,181.57
56																51	0	$13,216.04	538.26	$12,677.78	$116,503.79
57																52	0	$13,216.05	485.43	$12,730.62	$103,773.17
58																53	0	$13,216.04	432.39	$12,783.65	$90,989.52
59																54	0	$13,216.05	379.12	$12,836.93	$78,152.59
60																55	0	$13,216.04	325.64	$12,890.40	$65,262.19
61																56	0	$13,216.05	271.93	$12,944.12	$52,318.07
62																57	0	$13,216.05	217.99	$12,998.06	$39,320.01
63																58	0	$13,216.04	163.83	$13,052.21	$26,267.80
64																59	0	$13,216.04	109.45	$13,106.59	$13,161.21
65																60	0	$13,216.05	54.84	$13,161.21	$0.00
smittymj

Cell Formulas
Range	Formula
P5:P65	P5	=ROW(P5)-5
Q5:Q65	Q5	=IFNA(INDEX($D$16:$D$20,MATCH(P5,$C$16:$C$20,0)),0)
U5	U5	=Q5+S5-T5
R6:R65	R6	=ROUND(PMT($D$10/12,($D$9*12)-P5,-U5),2)
S6:S65	S6	=ROUND(U5*$D$10/12,2)
T6:T65	T6	=R6-S6
U6:U65	U6	=U5-T6+Q6
D8	D8	=5600000*0.8
D13	D13	=ROUND(PMT(D10/12,D9*12,-D8),2)

 

[smittymj]

Is this something that you were looking for? As I look at it, i think you mean draw downs are advances, like a line of credit. But, it seems you have a maturity date that needs to be met regardless of when the draw down occurs. Likewise you could put interest rate changes in this mix as well.

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H							O	P	Q	R	S	T	U
4																Month	Advance At start of this month	Payments	Interest acrued thru start of this month	Principal	Balance
5																0	100000	0	0	0	100000
6		Payments p.a.	#	12												1	0	$1,887.12	416.67	$1,470.45	$98,529.55
7																2	0	$1,887.12	410.54	$1,476.58	$97,052.97
8		Loan Amount	A$	4,480,000.0000												3	0	$1,887.12	404.39	$1,482.73	$95,570.24
9		Loan Term	Years	5												4	0	$1,887.12	398.21	$1,488.91	$94,081.33
10		Interest Rate p.a.	% p.a.	0.05												5	0	$1,887.12	392.01	$1,495.11	$92,586.22
11																6	0	$1,887.12	385.78	$1,501.34	$91,084.88
12																7	0	$1,887.12	379.52	$1,507.60	$89,577.28
13		Payment:		$84,543.130												8	0	$1,887.12	373.24	$1,513.88	$88,063.40
14																9	0	$1,887.12	366.93	$1,520.19	$86,543.21
15		Advances:	Pd	Amt												10	0	$1,887.12	360.6	$1,526.52	$85,016.69
16			0	100000												11	0	$1,887.12	354.24	$1,532.88	$83,483.81
17			16	100000												12	0	$1,887.12	347.85	$1,539.27	$81,944.54
18			25	100000												13	0	$1,887.12	341.44	$1,545.68	$80,398.86
19			26	100000												14	0	$1,887.13	335	$1,552.13	$78,846.73
20			27	80000												15	0	$1,887.13	328.53	$1,558.60	$77,288.13
21																16	100000	$1,887.13	322.03	$1,565.10	$175,723.03
22																17	0	$4,379.27	732.18	$3,647.09	$172,075.94
23																18	0	$4,379.27	716.98	$3,662.29	$168,413.65
24																19	0	$4,379.27	701.72	$3,677.55	$164,736.10
25																20	0	$4,379.27	686.4	$3,692.87	$161,043.23
26																21	0	$4,379.27	671.01	$3,708.26	$157,334.97
27																22	0	$4,379.27	655.56	$3,723.71	$153,611.26
28																23	0	$4,379.26	640.05	$3,739.21	$149,872.05
29																24	0	$4,379.27	624.47	$3,754.80	$146,117.25
30																25	100000	$4,379.27	608.82	$3,770.45	$242,346.80
31																26	100000	$7,455.74	1009.78	$6,445.96	$335,900.84
32																27	80000	$10,616.28	1399.59	$9,216.69	$406,684.15
33																28	0	$13,216.05	1694.52	$11,521.53	$395,162.62
34																29	0	$13,216.05	1646.51	$11,569.54	$383,593.08
35																30	0	$13,216.05	1598.3	$11,617.75	$371,975.33
36																31	0	$13,216.05	1549.9	$11,666.15	$360,309.18
37																32	0	$13,216.05	1501.29	$11,714.76	$348,594.42
38																33	0	$13,216.05	1452.48	$11,763.57	$336,830.85
39																34	0	$13,216.05	1403.46	$11,812.59	$325,018.26
40																35	0	$13,216.05	1354.24	$11,861.81	$313,156.45
41																36	0	$13,216.05	1304.82	$11,911.23	$301,245.22
42																37	0	$13,216.05	1255.19	$11,960.86	$289,284.36
43																38	0	$13,216.05	1205.35	$12,010.70	$277,273.66
44																39	0	$13,216.05	1155.31	$12,060.74	$265,212.92
45																40	0	$13,216.05	1105.05	$12,111.00	$253,101.92
46																41	0	$13,216.05	1054.59	$12,161.46	$240,940.46
47																42	0	$13,216.05	1003.92	$12,212.13	$228,728.33
48																43	0	$13,216.05	953.03	$12,263.02	$216,465.31
49																44	0	$13,216.04	901.94	$12,314.10	$204,151.21
50																45	0	$13,216.04	850.63	$12,365.41	$191,785.80
51																46	0	$13,216.05	799.11	$12,416.94	$179,368.86
52																47	0	$13,216.05	747.37	$12,468.68	$166,900.18
53																48	0	$13,216.04	695.42	$12,520.62	$154,379.56
54																49	0	$13,216.05	643.25	$12,572.80	$141,806.76
55																50	0	$13,216.05	590.86	$12,625.19	$129,181.57
56																51	0	$13,216.04	538.26	$12,677.78	$116,503.79
57																52	0	$13,216.05	485.43	$12,730.62	$103,773.17
58																53	0	$13,216.04	432.39	$12,783.65	$90,989.52
59																54	0	$13,216.05	379.12	$12,836.93	$78,152.59
60																55	0	$13,216.04	325.64	$12,890.40	$65,262.19
61																56	0	$13,216.05	271.93	$12,944.12	$52,318.07
62																57	0	$13,216.05	217.99	$12,998.06	$39,320.01
63																58	0	$13,216.04	163.83	$13,052.21	$26,267.80
64																59	0	$13,216.04	109.45	$13,106.59	$13,161.21
65																60	0	$13,216.05	54.84	$13,161.21	$0.00
smittymj

Cell Formulas
Range	Formula
P5:P65	P5	=ROW(P5)-5
Q5:Q65	Q5	=IFNA(INDEX($D$16:$D$20,MATCH(P5,$C$16:$C$20,0)),0)
U5	U5	=Q5+S5-T5
R6:R65	R6	=ROUND(PMT($D$10/12,($D$9*12)-P5,-U5),2)
S6:S65	S6	=ROUND(U5*$D$10/12,2)
T6:T65	T6	=R6-S6
U6:U65	U6	=U5-T6+Q6
D8	D8	=5600000*0.8
D13	D13	=ROUND(PMT(D10/12,D9*12,-D8),2)

Thank you so much! I think I can work with this. Sorry we call advances/taking from the credit line as drawdowns from where I work at :D

I'll let you know if I make it work!