Loan Draw - Dyanamic Formula

[chet645]

Hi All, I am wondering if I can enlist this forum's help. Can someone help me create a dynamic loan draw formula (highlighted row 10 in HTML table below) with the following constraints:

- The loan cannot be overdrawn (i.e. cannot draw more from the loan than what is available). The ending balance in each month cannot be negative.

- A reimbursement for an expenditure (row 4) is lagged by a define number of months (Cell H10). Therefore, debt proceeds must be available, and the timing of the expenditure in each month must be greater than the defined number of months (Cell H10).

As always, any help is much appreciated. This forum is a life saver!​

HTML TABLE

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	Time Period			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	Totals
3
4	Expenditures						-50	-50	-50	-25	-50	-25	-25							-275
5	Loan Proceeds											100			100					200
6
7
8	Beginning Balance			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	Debt Proceeds Available	Draw Lag (Mos.)		0	0	0	0	0	0	0	0	100	0	0	100	0	0	0	0	200
10	Loan Draws	4										-100	0	0	-100	0	0	0	0	-200
11	Ending Balance			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
K2	=J2+1
K8	=J11
K9	=K5
K11	=SUM(K8:K10)
L2	=K2+1
L8	=K11
L9	=L5
L11	=SUM(L8:L10)
M2	=L2+1
M8	=L11
M9	=M5
M11	=SUM(M8:M10)
N2	=M2+1
N8	=M11
N9	=N5
N11	=SUM(N8:N10)
O2	=N2+1
O8	=N11
O9	=O5
O11	=SUM(O8:O10)
P2	=O2+1
P8	=O11
P9	=P5
P11	=SUM(P8:P10)
Q2	=P2+1
Q8	=P11
Q9	=Q5
Q11	=SUM(Q8:Q10)
R2	=Q2+1
R10	=M4+N4
R8	=Q11
R9	=R5
R11	=SUM(R8:R10)
S2	=R2+1
S8	=R11
S9	=S5
S11	=SUM(S8:S10)
T2	=S2+1
T8	=S11
T9	=T5
T11	=SUM(T8:T10)
U2	=T2+1
U8	=T11
U9	=U5
U10	=O4+P4-25
U11	=SUM(U8:U10)
V2	=U2+1
V8	=U11
V9	=V5
V11	=SUM(V8:V10)
W2	=V2+1
W8	=V11
W9	=W5
W11	=SUM(W8:W10)
X2	=W2+1
X8	=W11
X9	=X5
X11	=SUM(X8:X10)
Y2	=X2+1
Y8	=X11
Y9	=Y5
Y11	=SUM(Y8:Y10)
Z4	=SUM(J4:Y4)
Z5	=SUM(J5:Y5)
Z9	=SUM(J9:Y9)
Z10	=SUM(J10:Y10)
J8	=I11
J9	=J5
J11	=SUM(J8:J10)

<tbody> </tbody>

<tbody>
</tbody>