Kayla has shared the three spreadsheets we use to keep track of budget stuff, and while reading through her most recent post about them it occured to me that I’ve never shared the one that I use. While all of hers do a great job keeping track of where we are and where we’ve been, I like to look toward the future.
This spreadsheet allows me to put in how much debt we have in different departments and the normal payments we’re making, and project how long it will take to become 100% debt free at that rate. If I don’t like it, I change how much we pay each month by just little bit and project it again, then yell across the house, “Hey Kayla! If we increase how much we’re paying on our loans by $50/month we’ll be paid off 10 months sooner than the rate we’re going now!”
And she yells back, “How much!?”
And I shout, “Fifty bucks will save us ten months!” because I’m not sure if she meant “how much money” or “how much time”.
And the animals all look at us like “Why are you yelling? Is there a threat?”
Let’s get into the details of this thing. The picture below is the basic layout. Each row denotes a different month, which is listed in the left most column. All the other columns are different items we owe money on, like the cars, the house and student loans. Each of those is broken down into two columns, one for the payment being made that month (the left) and the other for how much is still owed at the end of that month (right). The value next to “Starting Amount” for each item isn’t value you took out the loan for originally, it’s the how much you owe when you start the spreadsheet. In this example you may have had $1500 in credit card debt, but you’ve been paying it down for a while and now, when you’re starting to keep track, you only owe $1000.
To show the progression into history I simply select a cell, click on the bottom right corner (a little plus sign will appear) and drag down to fill a bunch of cells. In this example you plan on paying $100 on your credit card each month, with the exception of February. February is your birthday, and you’ll have some extra money from your grandma to throw at it. At that rate the credit card will be paid off by November of this year (highlighted in yellow). Great! Now we have something to look forward to.
This next picture shows when you’ll be debt free (because that’s the dream, right?). I have the items organized based on what I want to pay off first. My setup is highest interest rate first, and if there’s two things with the same interest rate then I pay off the bigger one first. That system, generally, will result in paying the least amount of interest possible. If you want to set it up otherwise then have at it. Biggest loan first, smallest loan first, whatever, it’s all gotta get paid off one way or another. Once everything is in order I project into the future based on assumed monthly payments. I didn’t start doing this until after we started our budget spreadsheet, so I was able to look back at what our previous payments were on each item and used that for likely future payments. Now, the credit card is going to be paid off in November of this year, so the $100/month you were putting towards that can go to the next highest priority loan, Student Loan A. Notice in the left column for Student Loan A, the monthly payment for that item jumps from $500 to $600 after the credit card is paid off, and then again in December of 2020 from $600 to $800 when the other student loan gets paid off, because now we can put that $200 minimum payment you were making on that student loan towards this student loan. That’s snowballing! The further you roll the faster each thing gets paid off! Using that system, assuming you can keep making the same payments you’ve been making, your 30 year mortgage will be paid off by June of 2028! That’s only 10.5 years!
This last picture shows the formula I used to do all the math. You’ll have to change some things in here to make it accurate for your specific situation. If you’re not so great with math or Excel pay close attention, I’ll try to be as detailed as possible. If you’re as good at math and Excel as I am feel free to skip this paragraph, and if you’re better than me PLEASE tell me if my formula is wrong. The formula at the top of the picture “=((G2*1.01958)-F3)” is what is actually typed in the outlined box on the right side of the picture that reads “$4897.90”. Number 1 is pointing to “G2” and “$5000”. This means that the first thing the formula is referencing is the the cell above it, cell G2. The number 2 is pointing to the number with all the decimals, which represents the interest rate for this loan. To get this number take the interest rate on your loan, in this example 23.5%, and move the decimal place to the front so it reads 0.235. It’s important to note that if you have an interest rate that less than 10%, say 5.6%, you have to add a zero in front of the 5 then move the decimal point, 0.056. The interest rate is based on a full year, Annual Percentage Rate (APR), so in order to calculate what it is per month we have to divide it by twelve months. 0.235÷12=0.01958 The more decimal places you use the more accurate the calculation will be, but 5 units out is plenty. Add 1 to this number, 1.01958, and plug it into the formula at number 2. The number 3 in the picture, “F3” and “$200” is telling the formula to subtract that month’s payment from the total amount owed on the loan, the “G2*1.01958”. Just make sure that when you click and drag the cell to see how long it will take to pay off the loan that each cell is referencing the one above it (number 1, “G2”) and the one to the left of it (number 3, “$200”). Sometimes shit gets goofy when you click and drag in Excel. I know that may have been confusing, so if you’re having a hard time just send me a message and I’ll help you through it.
Being able to look into the future like this helps me make purchasing decisions. “I could get this new $50 video game,” I say to myself, “but that will be another three weeks that I’m in debt to the Man, playing his bullshit game called ‘You Don’t Own Your Own Life’. Guess I’ll just keep playing one of the 15 games I already own.” Using this spreadsheet I determined that Kayla and I will be 100% debt free in about 3 more years. I need to rerun the numbers because some things changed right at the end of 2017 (like Reliant needing knee surgery), but I still have a goal I can look at, a light at the end of the tunnel that keeps me from running away to New Zealand to hide in anonymity as sheep-herder. I am hoping you will find it equally useful.
You can find a copy of the spread sheet here. Just copy and paste the data into your own excel file, then alter to fit your reality.
-Seth