Automated Draw Schedule

[Hydestone]

I am trying to automate this draw schedule, and am having a tough time figuring out how to have the "Draw Amounts" table and "Draw Schedule" chart values adjust based on the number of payment periods.

The percentages I included in the table are just thrown in there to shape the curve....custom, front loaded, tail loaded, or bell.

Ideally, the user enters the contract amount and number of pay periods, then the tables and chart adjust accordingly. If there were 20 pay periods instead of 24, periods 21-24 would not show up and the percentages would get reallocated, so that the total when all periods are added up = 100%.

Draw Schedule - 24 mos.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	project info										12345 - TEST JOB
2	name		Test Job	change blue fields							Period	Draw		Retainage		Pay Amount
3	number		12345									Percentage	Amount	Percentage	Withheld
4	budget date		6/3/2020								1	3%	$ 75,000	5.0%	$ 3,750	$ 71,250
5											2	3%	$ 75,000	5.0%	$ 3,750	$ 71,250
6	project start		1/1/2020								3	5%	$ 125,000	5.0%	$ 6,250	$ 118,750
7	project finish		12/31/2021								4	5%	$ 125,000	5.0%	$ 6,250	$ 118,750
8	pay periods		24								5	8%	$ 200,000	5.0%	$ 10,000	$ 190,000
9											6	8%	$ 200,000	5.0%	$ 10,000	$ 190,000
10	contract total		2,500,000	numbers are good							7	9%	$ 225,000	5.0%	$ 11,250	$ 213,750
11	retainage (%)		5.00%								8	9%	$ 225,000	5.0%	$ 11,250	$ 213,750
12	reduce at 50%?		N	Y or N							9	8%	$ 200,000	5.0%	$ 10,000	$ 190,000
13	option		F	C, F, B, or T from chart below							10	8%	$ 200,000	5.0%	$ 10,000	$ 190,000
14				change custom percentages as needed							11	5%	$ 125,000	5.0%	$ 6,250	$ 118,750
15											12	5%	$ 125,000	5.0%	$ 6,250	$ 118,750
16	options	custom	front load	bell	tail load		Cumulative Billing				13	4%	$ 100,000	5.0%	$ 5,000	$ 95,000
17	period	C	F	B	T						14	4%	$ 100,000	5.0%	$ 5,000	$ 95,000
18	1	2.0%	3.0%	1.0%	1.0%		$ 75,000				15	3%	$ 75,000	5.0%	$ 3,750	$ 71,250
19	2	2.0%	3.0%	1.0%	1.0%		$ 75,000				16	3%	$ 75,000	5.0%	$ 3,750	$ 71,250
20	3	4.0%	5.0%	2.0%	2.0%		$ 125,000				17	2%	$ 50,000	5.0%	$ 2,500	$ 47,500
21	4	4.0%	5.0%	2.0%	2.0%		$ 125,000				18	2%	$ 50,000	5.0%	$ 2,500	$ 47,500
22	5	7.0%	8.0%	4.0%	3.0%		$ 200,000				19	2%	$ 37,500	5.0%	$ 1,875	$ 35,625
23	6	7.0%	8.0%	4.0%	3.0%		$ 200,000				20	2%	$ 37,500	5.0%	$ 1,875	$ 35,625
24	7	8.0%	9.0%	5.0%	4.0%		$ 225,000				21	1%	$ 25,000	5.0%	$ 1,250	$ 23,750
25	8	8.0%	9.0%	5.0%	4.0%		$ 225,000				22	1%	$ 25,000	5.0%	$ 1,250	$ 23,750
26	9	7.0%	8.0%	6.0%	5.0%		$ 200,000				23	1%	$ 12,500	5.0%	$ 625	$ 11,875
27	10	7.0%	8.0%	6.0%	5.0%		$ 200,000				24	1%	$ 12,500	5.0%	$ 625	$ 11,875
28	11	6.0%	5.0%	7.0%	6.0%		$ 125,000				RET	100%			retainage release	$ 125,000
29	12	6.0%	5.0%	7.0%	6.0%		$ 125,000					Totals	2,500,000			$ 2,500,000
30	13	5.0%	4.0%	7.0%	7.0%		$ 100,000
31	14	5.0%	4.0%	7.0%	7.0%		$ 100,000
32	15	4.0%	3.0%	6.0%	8.0%		$ 75,000
33	16	4.0%	3.0%	6.0%	8.0%		$ 75,000
34	17	3.0%	2.0%	5.0%	7.0%		$ 50,000
35	18	3.0%	2.0%	5.0%	7.0%		$ 50,000
36	19	2.0%	1.5%	4.0%	4.0%		$ 37,500
37	20	2.0%	1.5%	4.0%	4.0%		$ 37,500
38	21	1.0%	1.0%	2.0%	2.0%		$ 25,000
39	22	1.0%	1.0%	2.0%	2.0%		$ 25,000
40	23	1.0%	0.5%	1.0%	1.0%		$ 12,500
41	24	1.0%	0.5%	1.0%	1.0%		$ 12,500
42	total	100%	100%	100%	100%		don't edit this
43	% off by	0%	0%	0%	0%
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62											* Projection based on budget dated 06/03/20
63
draw schedule

Cell Formulas
Range	Formula
K1	K1	=UPPER(CONCATENATE(C3," - ",name))
M4:M27	M4	=contract*L4
N4:N27	N4	=IF((AND(G18>(contract/2),($C$12="y"))),,retainage)
O4:O27	O4	=M4*N4
D10	D10	=IF((contract<>M29),"numbers are off!","numbers are good")
K4:K27	K4	=A18
L4:L28	L4	=IF(($C$13=$B$17),B18,(IF(($C$13=$C$17),C18,(IF(($C$13=$D$17),D18,(IF(($C$13=$E$17),E18,"X")))))))
M29	M29	=SUM(M4:M27)
P4:P27	P4	=M4-O4
P28	P28	=SUM(O4:O27)
P29	P29	=SUM(P4:P28)
A19:A41	A19	=IF((AND(A18<$C$8,A18<>0)),A18+1,0)
G18:G41	G18	=SUM(M4)
B42:E42	B42	=SUM(B18:B41)
B43:E43	B43	=1-B42
K62	K62	=CONCATENATE("* Projection based on budget dated ",(TEXT(C4,"mm/dd/yy")))

Named Ranges
Name	Refers To	Cells
contract	='draw schedule'!$C$10	D10, M4:N27
retainage	='draw schedule'!$C$11	N4:N27

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A18:A41	Cell Value	<1	text	NO

 

[Hydestone]

Quick follow up on this. Any ideas on how to have the periods adjust? Eg, the 2 tables and 1 chart update to reflect the correct number of periods?

I can manually change the percentages if it is unrealistic for it to be automated.

Thanks again in advance.