How to Create a Budget Spreadsheet

I down load my expenses from Mint in a CSV file and at the end of every month I cut and paste the months transactions into a spreadsheet from the CSV file. To down load the CSV look for this link at the bottom of the transaction page after everything has updated.

The CSV file looks something like this

This is a partial readout from May. Had a lot of Amazon activity this May because of supplies following my surgery

My spread sheet looks like this

It is simply month after month of the year out to Dec using the same format as the CVS file so I cant cut and paste each month’s data into its place in the spread sheet

I set it up so each year next to a month is linked to the $c$1 value which is the year. So “Spending Feb” has as it’s year as $c$1 Spending Mar has $c$1 etc. In other words wherever I want the year to show up I type =$C$1 in the cell (the = is important. The entire spreadsheet changes to whatever value I put in cell C1. By doing this I can create a new year simply by cutting and pasting

Here is spending year 2021. All I have to do is cut and paste and put the right number in C1 and the whole year changes. I have out to year 2022 created and each year has its own sheet

to populate a given month with data I cut and paste that data Here is a partial of the month from May

I just cut and paste from “date” on down to the bottom of the month. I have to do a slight data massage. Notice how most everything is a debit but there is one credit. On credits I change the sign of the value

Next I merely Autosum the column

and voila the partial sum of May 2019 spending. I can update the month as often as I like till the next month starts. It’s just cut paste change credits to a negative autosum, takes only a couple minutes to have an accurate readout of the month’s spending. I some times transfer money between accounts using command account which is my checkbook. Those transactions I simply set the transfer “value” to 0 in the data column and write the transfer amount out to the side under “notes” to track that transaction. I keep track of tax payments like this also since I consider taxes a transfer and not a monthly expense and it makes it easy to track. taxes paid for the year. My credit card gets paid off on the first of every month and results in a debit and credit of equal value being generated so I simply set credit to negative and that along with debit results in a 0 transaction but any given month I can easily spot my credit card bill and when it was paid.

A bit complicated to set up but EASY to use. I also can track yearly expenses and ask “what if” questions of the data. Since each month is accurate each year is accurate and a “whole year” is merely the sum of each month. Once I have a year’s data it’s trivial to understand how expenses are varying year to year and you can create your own personal inflation index if you like.

Here is a shot of multiple years I have created but not yet populated. I retired is 2017 so the first “year” is actually 17 mos long

I keep my spreadsheet auto-saved in the cloud so if my computer blows up my data doesn’t and I can access my data across computers on my network. Just before completing this post, my power glitched but I was auto-saved so I lost nothing. My wife pays her credit cards using command so I get a readout of her credit card expenses but I DO NOT track her specific spending. If you want to track multiple credit cards just set that up in Mint

Funny thing I thought May 2019 would stand out as an expensive month and it did, but I checked May 2018 and it was nearly as expensive since my kid graduated college in May 2018 which required a lot of plane trips and motel expense and celebration. Looking over May 2018 was a nice walk down memory lane

4 Replies to “How to Create a Budget Spreadsheet”

  1. I read your prior post about how you use Mint.
    I really wanted to do it but was going to ask you to include some screenshots.

    You read my mind. Thanks for doing this.
    I bookmarked the page and plan to do this.
    I never budget and never used Mint, but I think I need to.

    Especially as I cut back more at work, I need to stop just guessing about my expenses.

    1. I wouldn’t be much of a host to scold people about budgets and with hold the goods on what I do to make my budget knowledge happen! Once you get the sheet up and running you’ll be checking every other day, but eventually you’ll convince yourself “about what you spend” per month plus or minus a little. I now check twice a month, good enough to know if things are on track. Good luck WD!

  2. I am unsure if you are concerned about your name being on the website, but Lee A Crocker is in the spreadsheets.

    1. Hi Gene and thanks for the heads up. Eventually every “anonymous” blogger’s cover gets blown. In my case I decided the authenticity of the actual spread sheet creation was more important than my anonymity. I barely exist on the internet anyway. Creating a spreadsheet if you’re not familiar can be a bit daunting so I wanted the creation to be able to be done by anybody interested, so I decided fidelity to the process was important. The “account” column is important so I can figure out which account a transaction came from. For example my credit card payment comes from the command account and shows as a debit and an exactly equal amount is shown as a “credit” in the credit card statement. To get the exactly correct sum I have to change the sign of the “credit” part of that pair or it looks like I spent more money than I actually did. One small sign change can make a huge difference the accuracy of the monthly “total” and I want the result absolutely accurate. If my wife shops and then sends something back to say Amazon it also shows up as a credit on the CC bill and I can adjust the monthly total accordingly using this technique. I don’t let the robots do my payment but do the payment every month using the CC website linked to my bank. That way I for sure know exactly what is going on. I make my Medicare payments the same way. I set up that payment to happen early in the month so medicare is always paid a bit early and I can track it better. Medicare was sending a snail mail bill but sometimes it wouldn’t get here and then I’d be playing catch-up. Hated it!

Leave a Reply

Your email address will not be published.