Hi There. I have a cash flow chart (see attached) showing monthly costs over a 12 month period in row 3 and the cumulative costs below that in row 4. I have a line of credit in row 5 that kicks in in month 4 which will cover part of the cost from month 4-12 but not all of it. I’m looking for a formula I can drag from B5-M5 that has the following conditions:
-Only start using the line of credit in month 4, so B5-D5 would show 0.
-From month 4 the line of credit should cover all of the monthly cost until the line of credit runs out.
-In the month that the line of credit runs out, if it can cover part of the cost for the month show the amount it can cover. For example, if I have a $280,000 line of credit and $50,000/month costs, months 4-8 will would show $50,000 being drawn from the line of credit per month but in month 9 the line of credit will only be able to cover $30,000.
-In the following months after the line of credit is completely depleted, the cells in row 5 (K5-M5) should show 0.
PS. Row 6 just shows the cash required when the line of credit doesn't cover the cost so cells E6-J6 will change once the formula is input into row 5.
Bookmarks