We pay our auto insurance bi-annually, but we save for the payments monthly. If I categorize the monthly transfers to savings as “auto insurance,” I have both an outgoing-to-savings transaction and an incoming-to-savings transaction. These cancel each other out. The same thing happens when I transfer the money back every six months. Despite saving ahead, the big deduction only happens every six months in our spreadsheet in the insurance payment transaction.
I need the big payment to be reflected in my “actual” annual budget because that amount is specific, but I need the monthly payments to be reflected in my monthly budget, because we save slightly more than needed each month to cover the payment. Can anyone give me an idea of how to make this work in Excel?
I think I used to have a similar situation with my trash provider that only billed quarterly but I wanted to reflect the monthly expense. I don’t how you will solve the monthly vs actual annual budget since the annual budget is made up of the monthly budgets. But for the other situation of the cancelling out issue, create two categories and hide the incoming one(the second category) into the savings so that the outgoing one will mark the monthly expense.
That’s such an awesome spreadsheet, and I read all about it when I was searching for answers before I posted my question here on the Excel side of the community. Any idea if/when the Savings Budget sheet will be available to Excel users?
Hmm, not sure I follow 100%, but I would handle this by categorizing the savings deposit as “Savings Contribution” and only categorize the actual insurance payment as “Auto Insurance.” Your monthly budget will reflect the Savings Contribution “expense”, and the Auto Insurance payment will be reflected in the two months that you make your payments (and in the Yearly Budget report).
If that works for your situation, here is more detail:
Steps
For the monthly outgoing-to-savings transaction, enter a category of “Savings Contribution.” Add a Description of “Auto Insurance” so you know this money is intended for your auto insurance payment later.
For the monthly incoming-to-savings transaction in the savings account, enter the category of “Transfer In,” which hides the transaction from reporting.
When you need to make the transfers from Savings to Checking to pay for your auto insurance, use the Transfer-In and Transfer-out categories.
When you pay your Auto Insurance from checking, categorize that as “Auto Insurance.”
Honestly, @jaggy732, I’m sorry to say that there are no immediate plans. I use it and love it too.
(It wouldn’t be too heavy a lift to port the in-sheet calculations. The challenge is that it needs a small bit of code in the add-in/add-on to write the changes and we don’t have a handle on how we’d want to do that in Excel right now.)
Thank you so much! I think you and bradb3605 gave me enough information and advice that I have a plan for how to make this work. I want the monthly budgets to be relatively flat so that we’re not hammered when the auto insurance bill comes. But I also want the actual bill to be reflected in the annual budget accurately. I’m not sure this is even possible, so I worked out the following:
Monthly payments to savings are categorized “auto payment,” and the opposite transaction into savings are called “transfer”
Every six months, when I transfer the larger amount back, the positive transfer will be “auto payment,” and the negative transfer out of savings will be “transfer” (thereby making all of the transfers cancel over the course of six months)
The positive “auto payment” transfer from savings will land the same month as the actual bill, also categorized as “auto payment”, thereby canceling each other out, mostly
I still won’t have the exact amounts for payment in the annual budget, but it smooths out our payments over time, but I can always search by the vendor or just focus on the “auto payment” category for transactions. The annual budget will be “pretty close,” and that’s just going to have to be close enough.
I handle this as follows. I manage two separate sheets. One is my monthly budgeting sheet. On that sheet, I include my checking account where I transfer cash out of into a separate savings account. On this sheet, I do not include my savings account so there is no offseting transaction to savings within this sheet. When I transfer the money back to make the annual payment, I see the funds coming back into my checking account offset by the actual expense - but those two cancel, so no impact to my monthly or yearly budget on that sheet.
I have a separate sheet that links to my savings account - but not my checking account. I’m using the savings budget template here so the balance accrues month to month and I can track balances. This sheet is linked to my savings account, but not my checking account. So here I just see the cash coming in and building up, and then transferring out when its time to make the payment.