Understanding your debt

Today, I was cleaning up some old files in my archive and came across a Excel 2003 spreadsheet I had created years ago. The reason I created this spreadsheet was to get a big picture understanding of my whole debt situation and get it paid off. At the time, I had credit card debt, student loan debt, and a car loan. I’m making it freely available for anyone to use who has Excel or OpenOffice.org Calc (I’ve tested it, and it works).

This was back in 2003-2004. and I had finished grad school. Some of the classes we had were in finance and investments, understanding time value of money, corporate structure, and the stock markets. I was determined to be debt free and now had the understanding & motivation to do it. So I went to work on a excel spreadsheet that I could plug my debt numbers into to see how I progressed and when all this would be done.

I’m glad to say that I’m relatively debt free today (still got a mortgage), thanks in part to school and the spreadsheet that provided a easy feedback mechanism for seeing my overall progress.

I’ve listened to the personal finance people over the years and based on my experience it is best to pay off the debt with the lowest balance first. Most tell you to pay the highest interest rate first since it will ultimately save you more money. What I found is that paying the lowest balance first allows you to feel good about getting something paid off quickly, but more importantly you free up your time, energy, stress, and money that was devoted to that one bill. Thinking about one less bill is a huge load off the mind. This is why my strategy was to pay the lowest balance off first, then roll the previous payment to the next bill.

Ok, here are some details about the spreadsheet:

The changeable fields are in blue (the black are calculated fields, so are the other tabs in the spreadsheet), basically plug in the relevant info and the spreadsheet does the rest. It can handle payoffs up to 30 years which will allow for some most mortgages also. For the summation rows, a weighted average calculation is done to see big picture. For the date column, it lets you put in today’s date and the spreadsheet will tell you when this part will be paid off, this does not account for variable rates it assumes a fixed rate throughout the payment which is not too realistic since credit card rates change (loans generally do not). But, then again, you can update the spreadsheet as your rates change so you are constantly on top of your debt picture.  The way I used this, was to revisit the spreadsheet once a month when all the statements came in and adjust the values that changed, typically the date, balance, & APR.  It also allows the flexibility to add increased payments and see what the payoff results would be.  Lastly since all this information is local to my computer it stayed private.

Here is a quick video demo of the spreadsheet (best viewed at 720p fullscreen):

Here is the 20 entry 30 year Credit Card Loan Calculator spreadsheet for you to download: CCLC30YR20.xls

And if you don’t have Excel, here is where you can download OpenOffice Calc for free so you can use the spreadsheet.

I hope this is useful to others as it was useful to me. Enjoy.

– Dom

* The youtube video was created using Camtasia Studio 7 and a Plantronics .Audio 470 USB Stereo Headset.