# Formula that compares value owed against value on hand

#### Tap13

##### New Member
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
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Exc123

##### New Member
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
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
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
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.

Replies
0
Views
145
Replies
3
Views
570
Replies
0
Views
619
Replies
0
Views
359
Replies
0
Views
637

1,170,935
Messages
5,872,805
Members
432,948
Latest member
Yordi

### 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.

### Which adblocker are you using?

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

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