How to Use Excel to Project Your Savings - Part 2

This is Part 2 of the ‘How to Use Excel to Project Your Savings’ series.  If you have not viewed the first post, you can find it here.

Now that we know how much is possible to save, let’s talk about how to stay on track with savings, starting with a bank account. 

I’d recommend getting a checking account to serve as a place to hold enough money to pay all of your bills for one month.  Having a checking account with enough money in it for one month’s worth of bills will allow you to use auto-pay on many of your bills if you desire.  This will free up a lot of your time and as we all know, time is money.   While creating your checking account, I’d suggest creating a savings account that way each month you can transfer money from your checking account to your savings account.  

Once your accounts are up and running, decide how much needs to be in the checking account for you to safely pay all of your bills without over-drafting.   I recommend always steering on the high side.  

In our example, the total Expenses are $1050.  Keeping the checking account end of the month balance at $1500 should be more than enough to cover all automatic payments.

Now, let’s get this on the books.

I start by changing my ‘Monthly Total’ color to black for ease of navigation, add a row below, then change my ‘Accumulated Totals’ row’s name to ‘Checking Account’.  Notice that in April, we will be at our target of $1500 in our checking account.  This means we will need to begin transferring to the savings account.

I create two additional rows below ‘Checking Account’ and title them ‘Transfer to Savings’ and ‘Savings Account’. 

In my checking account row, I will determine how much needs to be transferred to remain at $1500.  To do this, I will subtract $1500 from the checking account.  Because the first three months are not at $1500, there would be a negative amount.  You can either delete the negative amounts or use the IF function as I did.  =IF(B18>1500,B18-1500,0).  Note that our numbers are growing exponentially.  This is because originally we had accumulated all of our savings into our checking account.  We will adjust this after we add our savings account functions.

For the savings account, we want to add the amount we are transferring into the savings account to the previous savings account total.  =C19+B21  This equation begins in February because January does not have a previous amount to add the transfer to.  For January, just copy the ‘Transfer to Savings’ amount.

Next, I add a row for the checking account balance after the transfer.  For this, I will subtract the amount transferred from the checking account.  =B18-B19  As decided earlier, the checking account should always have $1500 in it after the transfer to the savings account.  Because this is now the end of the month total, I change the title of the row to ‘Checking Account’ and I change the row that had been called ‘Checking Account’ to ‘Checking before transfer’.  This is all just preference and can be changed as desired.

Now that our accounts are set up, we return to the accumulated savings that has caused our numbers to be incorrect.  Previously, we had added the ‘Monthly Total’ to the monthly total from the previous month to create our total savings.  Because we are no longer adding these totals together, we need to create a new equation starting from our first transfer into our savings account. 

To get the correct amount in the checking account prior to the monthly transfer, we will add the ‘Checking Account’ total from the previous month to the ‘Monthly Total’ for the current month and fill across. =F16+E20

When we fill across, now we will notice that in the ‘December’ column, we show $1500 in the checking account, and $3900 in the savings account, totaling $5400 as we determined in Part 1.