Calculating a Line of Credit

[Drymr]

Hi There. I have a cash flow chart (see below) 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.

[TABLE="width: 1020"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Monthly Cost[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-50,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cumulative Cost[/TD]
[TD="align: right"]-50,000[/TD]
[TD="align: right"]-100,000[/TD]
[TD="align: right"]-150,000[/TD]
[TD="align: right"]-200,000[/TD]
[TD="align: right"]-250,000[/TD]
[TD="align: right"]-300,000[/TD]
[TD="align: right"]-350,000[/TD]
[TD="align: right"]-400,000[/TD]
[TD="align: right"]-450,000[/TD]
[TD="align: right"]-500,000[/TD]
[TD="align: right"]-550,000[/TD]
[TD="align: right"]-600,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Monthly Line of Credit[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cash[/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]200,000[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]300,000[/TD]
[TD="align: right"]350,000[/TD]
[TD="align: right"]400,000[/TD]
[TD="align: right"]450,000[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]550,000[/TD]
[TD="align: right"]600,000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Max Line of Credit[/TD]
[TD="align: right"]280000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Line of Credit starting Month[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try this in B5 and copy across

=IF(B2<$B$11,0,IF(-SUM(B$3:$E3)<$B$10,-B3,IF(AND($B$10>-SUM(A$3:$E3),$B$10<-SUM(B$3:$E3)),$B$10+SUM(A$3:$E3),0)))

 

[Drymr]

It worked! Thanks a million!!!

 

[AlanY]

It worked! Thanks a million!!!

you're welcome