Using Your Excel Spreadsheet to Build Rainy Day Savings

5f4b1-capture

Now that we have our savings account created and have determined our saving schedule, we can use our spreadsheet to predict how long it will take to build a Rainy Day Savings.

It is good practice to always have six months worth of expenses in your savings account just in case something unexpected happens.  We could also include the money in the checking account, but I would always prefer to air on the side of caution because my motto is ‘better safe than sorry’.  Because of this, I will only include the savings account total.

In our example, we know that the monthly expenses are roughly $1050.  Six months of expenses would be $6300.  By looking at the savings account total in column M: December, we see that the total amount in the savings account is $3900.  This is not enough to last six months.

To project further, we will need to add another year to our example.

Using the techniques from before, we will highlight all of the the December column (M1:M21) and drag across to column Y.  Highlight all cells in the sheet by pressing the Triangle in the top left corner then double click on  line between the column headers to widen the fields and remove the pound signs.  *If you have trouble with this step, please refer to the first tutorial’s video.

abedf-1capture

Next, add a column between December and January to separate the years.  You can add the year in this column if you would like.  Example: 2016.

518f0-3capture

Now that we have our projections extended for another year, we can see that the savings account will reach $6300 in June of year two.

If we were including the checking account, the savings account will reach $6300 in February.  None the less, the more savings the better because you never know what will happen next.

Like what you’ve seen so far?
Join the mailing list to be the first to know when a new post is published!

Working with Your Excel Budgeting Spreadsheet

38ebb-002765

Last week we made our spreadsheet a bit more robust by adding in a Checking account and Savings account rows.  If you need to review, you can find the post here.

This week, I will explain how to use this spreadsheet on an ongoing basis as your expense tracking tool in addition to being a budgeting and projecting tool.

Let’s start with the most fool-proof way of sticking to your budget and meeting your projections. Each month, in our example, remove $300 (the incidentals) from your checking account.  You can do this all at once at the beginning of the month or split it up.  Maybe $150 on the 1st and $150 on the 15th.  This all depends on your comfort level with managing your spending.  Here’s the kicker:  DO NOT TAKE ANY ADDITIONAL MONEY OUT OF YOUR ACCOUNT.  Period.  Of course, occasionally there are emergencies such as a blown tire or a medical bill but, if it’s not an emergency, don’t do it!

This money is yours for the month.  Use it however you please remembering that you will need to buy gas and groceries out of this money at some point. If you do not use all of the money in the month, decide what is best for you.  Would you rather hold it as your spending money just in case, save for a larger treat, or put it in your savings account.

If you are trying to build credit but can’t get approved, consider opening a secure credit card where you deposit your money into the account and you can only use how much you have.  With this option, you can have an automatic transfer that takes the money out of your checking account for you and deposits it to the secure account.

3f53e-capture

Tracking your expenses is more than entering in about what you think your expenses will be for the month.  While this is a great tool for predicting, it only acts as a placeholder when it comes to utilization of your spreadsheet.

My suggestion for tracking your expenses is to color code your cells.  For all income and expenses where the amounts change each time they occur, I change the text to Blue.  For all of the expenses with set amounts, I keep the text Black.

As I get my bills and paychecks, I add the correct amount over the blue estimated amount and change the color to black.  This shows me that I have already accounted for the item and helps me track where I’m at in the spreadsheet so I don’t have to look up to the months each time.  Once the month is complete, there should be no blue words left.

1. Take the incidentals money out of your checking account.
2. Update your spreadsheet with every new income or expense.

Like what you’ve seen so far?
Join the mailing list to be the first to know when a new post is published!

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.

c869b-000322

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

305ec-000500

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.

fce93-000750

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.

97b42-001045

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.

0f8b1-001495

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

fba0b-002729

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.

38ebb-002765

Like what you’ve seen so far?
Join the mailing list to be the first to know when a new post is published!