Formula that compares value owed against value on hand

[Tap13]

There are 3 equations I am working on. 1 is my Net cash, this looks at a bunch of data then says how much money I have currently. It then should look and say, if I don't have at least 30,000 in net cash I need to borrow enough money to get to 30,000 (Add: Borrowing from bank). Then my next row is the "Less: Repayment to the bank", here the formula should say if I have more then 30,000 in net cash, AND I owe the bank money from previously borrowing to bring my net cash to the minimum of 30,000, then I need to pay as much of my debt as possible without going under the minimum needed value of 30,000. The problem I am running into logically at least is if I owe 10,000 and I currently have 37,000 how does my formula deduct that 7 from my net cash on hand as well as deduct it from my 10,000 owed so that my end of year cash shows as 30,000 and my end of year debt is 3,000? I attached the snippit of the spreadsheet below using excel jeanie, let me know if I did it incorrectly.

In the snippit it does not show any cells regarding previously owed debt, but the equation would need to take any existing debt into account.

Thanks guys.

Excel Workbook
	A	B	C
54	Net Cash Position before borrowings and repayment of debt	NA	$39,677
55	Add: Borrowing from bank	NA	$0
56	Less: Repayment to bank	NA	$0
57	Equals: End-of-year cash on hand	$30,000	$39,677
Sheet1

Excel Workbook
	B
67	Minimum Cash
68	$ 30,000.00
Sheet1

 

[Exc123]

Here's how I set it up, without seeing the complete sheet I'm not 100% sure this will do what you need. I also took the liberty of adding lines as necessary.

Row 54 - Net Cash Position - $39,677
Row 55 - Add: Borrowing from bank - $0 Formula =IF(C54>=30000,0,30000-C54)
Row 56 - Added Total Outstanding Debt Prior to Repayment - $0 Formula =C55+Any cell that contains outstanding debt balance
Row 57 - Less: Repayment to bank - $0 (where C56 denotes Outstanding Debt Prior to Repayment)
Formula =IF(AND(C54>=30000,C56>0),IF((C54-30000)>=C56,-C56,(30000-C54)),0)
Row 58 - Added Debt Balance - $0 =c56+c57

 

[Tap13]

Thanks for the reply. Unfortunately for this particular sheet I've been asked to adhere to the format strictly. Any idea how to solve the problem in the provided layout?

 

[Exc123]

Thanks for the reply. Unfortunately for this particular sheet I've been asked to adhere to the format strictly. Any idea how to solve the problem in the provided layout?

Gotcha. I'd remove the added lines I used and then substitute C56 within the presented formula with any outstanding debt balance (is that c10 in your sheet?). If your outstanding debt balance is $10,000 in this example, then this formula should return (9,677) into cell C56.

=IF(AND(C54>=30000,C56>0),IF((C54-30000)>=C56,-C56,(30000-C54)),0)

I may not be understanding what you need still though. Where are you housing the new outstanding debt balance (323 in this example)?

 

[Tap13]

This is what I ended up using:
=IF(C60=0,0,IF(C55>0,0,IF(C54-C10>C60,C60,C54-C10)))

This is where if there is no debt owed (C60) then value is zero.
If there is debt owed but we had to borrow money from the bank (C55) the value is zero.
If there is debt owed, no money borrowed and our cash on hand less minimum cash is more then debt owed, the value is debt owed, otherwise it is our cash on hand less minimum cash required.

Thanks for the help on this.