using if formulas to calculate short term loan payments, cumulative loan, and closing cash

[crscrs5]

Hey all,

I am trying figure out a formula so that the cells c58,c 59, and c60 fill themselves in automatically but i cant quite wrap my head around how many if formulas i need... or if another formula would be needed in conjunction. i know an easy way to make it do it using another cell but everything needs to be self contained.

"closing cash" has to have a min value of 6000. if "cumulative cash" shows a loss, then "closing cash" will drop. "closing cash" cant drop below 6000 though, so if "closing cash" drops below 6000, then it needs to be brought up to 6000 and the difference of the two numbers needs to be applied to "short term loan requirements". the "short term loan requirement" is then applied to the "cumulative loan".

now if "cumulative cash" is a positive, we then made money. if there is a balance in the "cumulative loan" in the prev month, then a payment needs to be made against it. the difference between "closing cash"(cant go below $6000) and "cumulative loan" needs to be applied to "short term loan requirements" and then that number is applied against "cumulative loan" in the current month. *cumulative loan" cannot go below $0 and "closing cash cant go below $6000. once the loan is paid off, then any excess cash remains in the closing cash.

... did that make sense? i apologize if it didn't, once i get some sleep i can write it out clearer.

Our projects need to use as much cell referencing and formulas as possible, and i can guarantee none of the other students will use a formula to do this math. The picture is from a past assignment which only had 3 months in it, our projects that were working on now have 12 months.

Thanks for your time guys

[snick45]

Hey there,

First off, I would recommend adding in a named value for the closing cash requirement. That way, if you ever want to increase or decrease this amount, it can be done so easily. To do this, press crtl-F3 to open up the name manager, and then hit new. Name your value something like "ClosingCashRequirement", and set it equal to 6000. If you ever want to change this amount, you can go back to the name manager.

For the formulas, try:
c58: =IF(C57+B60<=ClosingCashRequirement,ClosingCashRequirement-C57,-B60)
C59: =C57+C58
C60: =C58+B58

[crscrs5]

it looked good at first but it didn't quite work, there is nothing to stop the cumulative loan from going into the negatives, and nothing stopping the beginning cash from going under 6000... this is way over my head

[snick45]

Hm, mine is working fine when I substituted these formulas. Could you upload the example that you have? Here is the example I put together based off the numbers you had in your image.