Debt payoff spreadsheets

By the time this post goes up it’ll be 2012, and a lot of people will be starting on their new year’s resolutions.

Any resolution that’s done in stages (like weight loss, or debt reduction) can be tracked in a spreadsheet. So this seems like the ideal time to post some spreadsheets that are fully prepared, and ready for use.

This year I’ve made a spreadsheet for calculating debt payoffs. You can download an appropriate version using the links below.
Excel 97 (.xls)
Excel 2007 (.xlsx)
Open Office (.ods)

I didn’t do a version for Google Docs or Microsoft Works because neither one of them would allow me to simply convert the file I’d created. Google Docs rejected some of the formulas as “too complex”, and Works would only import a single worksheet. I’m happy to make versions for these programs, but I figured I’d wait and see if anyone actually wanted them.

Also, anyone using the Excel 97 version will probably get a message whenever they save, saying there may be some loss of functionality if they safe in the older version. Don’t worry — no functionality will be lost! I didn’t use anything that’s not available in 97, but I had to build it in 2007 since the computer I use for 97 recently died.

There are instructions in the spreadsheet, but here’s a general overview of what to do in the various worksheets.

Worksheet: Debts
Just list your debts in the blue spaces on this sheet. There’s a couple of examples, but feel free to delete them.

If you have a particular priority for repaying your debts then you should enter the list in that order. If you want to make building an emergency fund a priority, for example, just treat it as a “debt” to yourself, and enter it in the first row. You’d enter your goal in the “balance” column, the interest rate on your savings account (if any) in the APR column, and make the monthly minimum payment $0 or whatever other amount you want to commit to.

If you don’t have a priority like that you can enter them in any order, and sort them later if desired. If you want the most financially effective order, sort the list of debts by interest rate, in descending order. If you prefer the snowball approach then you would just need to sort the list of debts by balance, in ascending order.

Worksheet: Income
Enter your regular income in the blue cells on this worksheet. If you get paid weekly, put it in the “per week” column. Biweekly income goes in the “per 2 weeks” column, etc. The 100 in the monthly column is just there as an example.

I’ve also entered January 1st as the default starting date, but feel free to change it.

Worksheet: Payments
The spreadsheet will automatically calculate this. The only thing you need to do on this page is enter any one-time income in the appropriate row, in the yellow column marked “Special”. That would be things like tax returns, special overtime pay, or whatever — things that don’t happen regularly. If you need to put less towards debt than you normally do, you can enter a negative number in this column.

Worksheet: Summary
This worksheet just summarizes your projected progress, showing up to 30 (approximately) equally spaced snapshots of what is expected to happen.

Other worksheet
The rest of the worksheets derive and summarize information, to make the Summary page possible. You don’t have to do anything with any of them.

I’ll discuss the various tools and techniques I used in making these spreadsheets next week. And as always, if anyone has questions, please ask!