Loan Draw - Dyanamic Formula

C

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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You must log in or register to reply here.

Similar threads

P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P
MrDB4Excel
  • Question Question
XLOOKUP Issue
Replies
8
Views
668
MrDB4Excel
MrDB4Excel
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
641
MrDB4Excel
MrDB4Excel
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor
W
  • Question Question
Macro Looping for 'n' counts of rows
Replies
0
Views
413
whitefeather
W

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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