Formula to calculate debits and credits for personal finance

[Ashley_Jackson]

Hey guys, I've got a new challenge in my quest to create a base for tracking personal finances.  A quick review:

Tables

1. Categories (I call them buckets)
2. Transactions
3. Designations

Transactions are either entered manually or bulk imported via csv from my bank.  Each transaction is assigned at least one category via the designations join table.

In the Categories table, I have assigned each category a monthly budget.  Then I roll up the designations amounts filtered by month (this is my current setup - I'm working on a different option).  Now, you would think that a simple third formula field for [ budget - total spent (roll-up) ] would give me the balance for each of my budgets for the month.  Should tell me whether I've received all my salary for the month or if I've gone over budget for eating out, for example.

Here's my confusion:  there are situations in which categories may have counter-intuitive balances.  For instance, we have a category for gifts received like birthday money.  We categorize all related income AND expenses to the same income category so we can see at all times how much "birthday" money we have left to spend.  $100 birthday gift goes in, category balance is $100.  Spend $50 and now the balance is $50, right?  My kids, however, spent some gift money before we had a chance to deposit it in the bank which created an interesting scenario in which the balance of the gift category (an income category) was negative.  Here's what the record looked like:

So as you can see, the bucket total calculates correctly:  a negative total because the transactions are debits, even though it's an income account.  But then when I calculate the bucket balance, things go awry.  The bucket balance should be -$71 because we've overspent the $0 estimated budget.  Then once we deposit the kids gift money, the bucket total and bucket balance should both be +$29.  And there are similarly complicated issues on the expense category side, haha.

So, what's the correct formula here?  Am I looking at long series of nested IF statements???

[Micki_O_Neil]

Why don't you just add it as two records? The first is the -71. The next is +100. That will give you the balance of $29.

(I would have done $100 of income originally but then 71 of expenses for whatever they bought. Or if I'm understanding you correctly, $100 of gift income and then $71 against that bucket, so "negative income", meaning you still have $29 to spend.)

[Ashley_Jackson]

Hi Micki, Thanks for chiming in - I think I understand your suggestion:  enter two transaction records for -$71 and another for +$100.  If I understand correctly, that is how it will ultimately end up.  But I would still need a solution for the meantime because we can't always get to the bank to make the deposit that would correct the calculation, yk?

[Micki_O_Neil]

Can you have a cash on hand account? Or "deposits in transit" is what it might be called in an accounting reconciliation. 

[Ashley_Jackson]

I had not thought of that.  At the moment, I don't have it structured for multiple accounts... just different categories... and I can't even work out the formula for that lol!

[Micki_O_Neil]

I'd just do the two transactions and add a note that the money has yet to be deposited.l@ (I don't think there's any formula that would work universally for this)