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 in any given month; the ending balance (row 11) in any given month cannot go negative).
- A loan draw (row 10) for an expenditure (row 4) must be lagged by a defined number of months (Cell H10) and loan proceeds must also be available in a given month in order to be reimbursed.
As always, any help is much appreciated.
HTML TABLE
- The loan cannot be overdrawn (i.e. cannot draw more from the loan than what is available in any given month; the ending balance (row 11) in any given month cannot go negative).
- A loan draw (row 10) for an expenditure (row 4) must be lagged by a defined number of months (Cell H10) and loan proceeds must also be available in a given month in order to be reimbursed.
As always, any help is much appreciated.
HTML TABLE
| Book1 | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
| 2 | Month # | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | Totals | ||||
| 3 | ||||||||||||||||||||||
| 4 | Expenditures | -50 | 0 | -50 | -25 | -50 | -50 | -25 | -250 | |||||||||||||
| 5 | Loan Proceeds | 100 | 100 | 200 | ||||||||||||||||||
| 6 | ||||||||||||||||||||||
| 7 | ||||||||||||||||||||||
| 8 | Beginning Balance | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 50 | 0 | 0 | 25 | 0 | 0 | 0 | |||||
| 9 | Loan Proceeds Available | Loan Draw Lag (Mos.) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 100 | 0 | 0 | 100 | 0 | 0 | 0 | 0 | 200 | |||
| 10 | Loan Draws | 4 | -50 | -50 | 0 | -75 | -25 | 0 | 0 | 0 | -200 | |||||||||||
| 11 | Ending Balance | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 50 | 0 | 0 | 25 | 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 | |
| R8 | =Q11 | |
| R9 | =R5 | |
| R10 | =M4+N4 | |
| R11 | =SUM(R8:R10) | |
| S2 | =R2+1 | |
| S8 | =R11 | |
| S9 | =S5 | |
| S10 | =O4 | |
| S11 | =SUM(S8:S10) | |
| T2 | =S2+1 | |
| T8 | =S11 | |
| T9 | =T5 | |
| T11 | =SUM(T8:T10) | |
| U2 | =T2+1 | |
| U8 | =T11 | |
| U9 | =U5 | |
| U10 | =P4+Q4 | |
| U11 | =SUM(U8:U10) | |
| V2 | =U2+1 | |
| V8 | =U11 | |
| V9 | =V5 | |
| V10 | =MIN(SUM(V8:V9,R4)) | |
| 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) | |
