How to Track Savings SubAccounts in Excel

Some online banks let you create what they call sub accounts in your master savings account. Essentially, these allow you to make one deposit and then divide the money between several uses for easier tracking. If your online bank allows this, then simply label the subaccounts things like vacation, property tax, homeowner’s insurance, etc. Figure out how much you need to contribute to each account, then make one deposit and divide it.

Other online banks don’t allow subaccounts, but you can still allocate your money in your personal tracking.

Create a SubAccount Tracking Document in Excel
I use Excel to track our annual or semiannual bills, how much we’re allocating each month, and how much we have in the account total. If we’re able to pay a bill out of our checking without dipping into the subaccount, I just start that cell over at one month’s balance, or zero if I simply reduced that month’s savings deposit accordingly.

Here’s a sample account. You can see that I’ve labeled the months in the columns, and put the accounts in the rows. I also noted the current balance of the subaccounts after the month’s deposit is made and the available balance, which is the difference between the starting balance plus deposit, and the withdrawals and earmarked funds. At the bottom, I list the monthly contributions to each account and the month’s the bills are due.

How to Track SubAccounts

Here are step-by-step instructions for updating the sheet once a month.

1. After the interest is applied and you’ve made your monthly deposit, log into the account. You’ll see the starting balance and the interest. Add these two together and enter that number as the starting balance for the month.

2. Enter the amount of your deposit.

3. If you made any withdrawals (or expect to), either for bills that were due from subaccounts, or for items that weren’t in the subaccounts, note it in the withdrawal column.

4. Add this months’ contribution to last month’s balance for each subaccount. This is your new balance.

5. If you paid any of the annual bills this month, subtract the bill amount from the subaccount balance. If you were able to pay it without using the subaccount funds, simply add that money to your available balance. If you paid it from subaccount funds, subtract that amount from the subaccount balance and note it in the withdrawals column.

6. If you still made the full deposit, divide it accordingly among the accounts. If you reduced your deposit by this month’s contribution for the bill you paid, don’t add money to that account’s balance.

7. Repeat next month.

Here are April and May in action. Let’s say I had to pay the earthquake insurance in April. I noted the amount ($1150) in the withdrawals. However, I made the month’s full deposit, so I allocated a portion of that to the earthquake insurance subaccount ($115). Then in May, I made all the deposits as usual.

Once you get the hang of it, it’s a matter of minutes to update the sheet. Then you’ll never have to worry about using earmarked funds if an emergency comes up.

Leave a Comment

Your email address will not be published. Required fields are marked *