Formula that compares value owed against value on hand

Tap13

New Member
Joined
Apr 13, 2010
Messages
35
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
ABC
54Net Cash Position before borrowings and repayment of debtNA$39,677
55Add: Borrowing from bankNA$0
56Less: Repayment to bankNA$0
57Equals: End-of-year cash on hand$30,000$39,677
Sheet1
Excel Workbook
B
67Minimum Cash
68$ 30,000.00
Sheet1
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Exc123

New Member
Joined
Apr 5, 2006
Messages
38
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

New Member
Joined
Apr 13, 2010
Messages
35
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

New Member
Joined
Apr 5, 2006
Messages
38
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.

Code:
=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

New Member
Joined
Apr 13, 2010
Messages
35
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top