Loan Draw - Dyanamic Formula

chet645

Board Regular
Joined
Nov 10, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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
GHIJKLMNOPQRSTUVWXYZ
2Time Period12345678910111213141516Totals
3
4Expenditures-50-50-50-25-50-25-25-275
5Loan Proceeds100100200
6
7
8Beginning Balance0000000000000000
9Debt Proceeds AvailableDraw Lag (Mos.)00000000100001000000200
10Loan Draws4-10000-1000000-200
11Ending Balance0000000000000000
Sheet1
Cell Formulas
RangeFormula
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)

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"][/TH]
[TH="align: left"][/TH]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top