Savings & Debt not populating balance etc

[1Email2RuleThemAll]

So this issue seems to also be multi-faceted. What I believe is going on from your description is that:

• You are possibly double counting entries
• Prior savings budgets vs. actuals are out of alignment

Brief explanation of how the Savings & Debt sheet works

In the Categories sheet, you set up an Expense account (We’ll say Emergency Fund), set the Track to Savings, and enter a budget for each month.

The Savings & Debt sheet then keeps track of how much you should have by:

• A - Adding together all the budgets of the Emergency Fund in the Categories sheet for the prior months together ('Savings & Debt'!AC column)
• B- Adding all the amounts in Transactions tagged with Emergency Fund that have occurred since the first month present in the Categories sheet ('Savings & Debt'!AD column)
• C - Adding together all the balances entered into the Budget Journal for the Emergency Fund ('Savings & Debt'!AE column)
• Available amount = A + B + C

Since money spent should always be negative, it’s really A + -B + C. This calculation works as a balancing act: (money budgeted) - (money spent) + (modifiers).

What might be causing your issue:

When you transfer money to or from the accounts you have set up for the Emergency Fund, are you choosing Emergency Fund for the category of those transactions?

If so, this is the first problem. If you transfer $500 to a savings account, and categorize it as Emergency Fund, then part B of the formula above will take that positive amount into account, when it’s already being accounted for in part A, essentially double counting what you have available.

Any movement of funds between accounts should be categorized as a Transfer. I personally have two transfer categories: Transfer In and Transfer Out. They are marked as hidden so they don’t show up on reports. Any movement of funds between accounts get categorized under one of those.

• Moving $500 from checking to savings
  • -$500 transaction for checking account gets categorized Transfer Out
  • +$500 transaction for savings account gets categorized Transfer In
• Paying a $200 credit card bill
  • -$200 transaction for checking account gets categorized Transfer Out
  • +$200 transaction for credit card account gets categorized Transfer In

The only thing that should be categorized as Emergency Fund on the Transactions sheet are transactions where you purchased something using the money you allocated toward that expense. So if your toilet broke and you needed another one, you go to Lowes and buy another one using your Chase card. The transaction that comes in from Lowes would get the Emergency Fund category. If you transfer money out of your savings to your checking to cover the Chase bill, those transactions get categorized as Transfers.

How to fix it

The first thing to do is make sure that you are not categorizing transfers between accounts under the category in question, and only expenses you wanted paid using those funds.

Once that’s done, you will need to get your historical budgets in order. You can try to finesse the data to give you the right number in the end, but it’s hard to do.

What I would suggest is to:

• Zero out the budget by setting all the prior month budgets to the amount of that month’s actuals. This will set the available balance to zero on Savings & Debt.
  • This can be best achieved by using the Savings Budget sheet, starting at the first month, and entering the actual amount reported as the budget for that month in the Categories sheet, then moving on until every month has been done.
• After it’s zeroed out, create a record in the Budget Journal for the current amount you have set aside for that category.
  • On the Savings Budget sheet, change column I4 to Savings, enter the amount that should be showing as available in the green box to the right of the Emergency Fund, then click on Extensions>Tiller Community Solutions>Tools>Update Savings Budget.
    • You can also just manually enter a row in the Budget Journal, if you know what it should look like.
      
      

This will enter a record in the Budget Journal that will basically be your “seed value” going forward.

Other stuff

1. It sounds like you’re using @jpfieber’s Budget Plan. If that’s the case, I wanted to point out that using the Update Savings Budget functionality when I4 is set to Budget will break his solution, as it writes to the Categories sheet, and conflicts with formulas he placed there. It is perfectly fine to use if set to Savings like described above.

2. If you ever remove past budget columns from your Categories sheet, it will affect the amount available in Savings & Debt. This is because it’s removing data from parts A (prior budgets) and B (prior transactions against those budgets) of the calculation, causing the picture to be incomplete. If you ever want to do this, remember you will need to “reseed” these balances by re-adding what fell out of the equation into part C, the Budget Journal balances.

   • So say you have $10,000 in you Emergency Fund, then delete old budget columns:
     • If afterward, the available balance is now $8,000, you would need to create a row in Budget Journal giving the account +$2,000.
     • If afterward, the available balance is now $13,000, you would need to create a row in Budget Journal giving the account -$3,000.