Excel DebtTracker Spreadsheet

Yes, you can track your debts, and have fun doing it.

If you've spent much time at my web site, you'll know that I offer quite a few Excel financial spreadsheets to the public. The most downloaded of all my Excel spreadsheets, by far, has been the DebtTracker.

I'm not sure why this might be. It has, I suppose, something of a catchy and intriguing name. But the fact that so many people are downloading it means that I can invest a bit of time making it better ... and feel like that time has paid off.

I created (and recently renovated!) the DebtTracker mainly for those folks who are just now embarking on a debt-paydown journey — or who are, at the very least, now taking debt reduction seriously. Get your copy of the DebtTracker from the download link below.

With the DebtTracker, you can see all your debts laid out in front of you in one place. You can easily sort your debts in a large variety of ways:   by type, by current balance, by interest rate, and more. You can get an idea of just how long it will take to pay off each debt. You can figure out your Credit Card Utilization percentage, your Debt-to-Income ratios, and various other financial metrics.

When I sit down with friends and family and attempt to draw up reasonable debt-reduction plans, the DebtTracker is the very first spreadsheet I utilize. It takes away the "abstractness" of personal debt ... and combines all the numbers and accounts to make that debt crystal-clear. So very many people have never overcome the fear and/or denial that inevitably accompanies significant debt. Which leads to an expected obstacle:   They've never tallied up their liabilities to see what they're really up against.

There's one clear truth to apply here:   If you don't know your enemy, you'll not only never defeat him — you wouldn't even know it if you did.

Fill out and use the DebtTracker, and your enemy steps out into the light.

And you can begin battle.

The DebtTracker spreadsheet contains seven worksheets in total, but uses three main sheets to do its thing:

1. Debt Types & Income
2. Debt Listing
3. Summary

You'll want to start off by opening the DebtTracker spreadsheet (obviously!) and enabling macros in your version of Excel. If you're not sure how to do this, just visit my instructional page at:

Enabling Excel Macros

Your next step is clicking on and opening the "Debt Types & Income" worksheet tab. The worksheet will look similar to the image at the right.

In the section labeled DEBT TYPES, you'll see a total of 15 spaces, 5 of which already include the types of debt most families have. You can't override these five primary types, so if there's a type or two that you don't have, simply ignore them.

However, if you're like most families and have other (more!) types of debt, just enter these other types in the remaining white spaces. Keep your TYPES as general in nature as are the five that are already included. Examples of other debt types would include:

• Payday Loan
• Checking Overdraft
• Investment
• Business
• Medical
• Family/Friend Loan

In the next section, labeled HOUSEHOLD INCOME, you'll need to enter your income data in the four white spaces provided. (The figures that are already there are for example only. Just write over them with your own data.)

If you're not sure of your household's exact income figures, then estimates will work fine. Enter your gross (pre-tax) annual income, your net (after-tax) annual income, your gross monthly income, and your net monthly income in their respective cells.

Once that's done, you're finished with the "Debt Types & Income" worksheet page.

[Return to Top]

The next worksheet page, titled "Debt Listing," is where you list ALL of your debts. Really. ALL means ALL! No fudging!

You'll see a lot of white space here — enough to enter up to forty (40) separate debts and the details relating to each.

Space shouldn't be an issue (one would hope!), so just start the dirty work. Delete all the figures that are already there, as they're examples only. Start inputting your own.

In the first column, give each of your debts a name that's meaningful to you.

In the second column, classify each debt with a TYPE. Refer back to the TYPES you entered on the "Debt Types & Income" worksheet, and use those. Clicking cells in the TYPE column will open a drop-down menu from which you can select each debt's TYPE, like so:

The spreadsheet is set up this way, with user-created standardized categories, so that sorting and calculations are easier to implement on later worksheets.

Enter the remaining columns as follows:

The next three columns (light gray) are calculated for you:

Once you've listed ALL your debts — even the $120 loan from your Aunt Maureen last year — you're ready to proceed.

Update Your Data As Time Passes

We're in this to make things better, right?

Absolutely.

So as time passes, we'll want to make sure our data on the "Debt Listing" worksheet is kept up-to-date.

Balances change all the time, obviously. But so do credit limits, rates, and minimum payments. If you want the DebtTracker to reflect your true money reality, you'll need to make sure that the data it holds is current and correct.

[Return to Top]

Perhaps the most useful feature of the revised DebtTracker is its new ability to sort all your debts — by any of the columns, either ascending or descending — with just three clicks. Shortly, you'll see how handy this can be for paydown planning. That easy sorting is where the two drop-down cells and the "GO!" button at the top of the worksheet page come in.

In the SORT BY box, select the column by which you wish to sort all your data. Clicking here will bring up a drop-down box which contains all the column headings listed above (Name, Rate, Limit, etc.). Choose the heading by which you wish to sort your data.

Next, in the SORT FROM cell, the spreadsheet offers you another drop-down choice. Choose to sort your data by either "Lowest to Highest" or "Highest to Lowest."

Once you've made these two selections, click the "GO!" button. The spreadsheet sorts the data as you instructed.

So now, whether you plan to follow Dave Ramsey's "Debt Snowball" approach (pay off your debts from lowest- to highest-balance) or Mary Hunt's "Rapid Debt Repayment Plan" (pay off from shortest payoff term to longest) or Jean Chatzky's "Boost Your Credit Score" plan (pay debts in order from the highest percent-of-limit-used to lowest), the DebtTracker can set you up. Or, even if you simply prefer to do it the old-fashioned way — by hacking away at your debt in order of highest- to lowest-interest rate — you can still line up all your debts in a snap.

[Return to Top]

The third page in the DebtTracker workbook, titled "Summary," is where you'll find just that — an overview, birds-eye look at your current debts, arranged by type.

In the first section (shown above), each Debt Type has its own row. You'll see your current balance for each Debt Type, as well as the total amount of principal you've paid so far on all debts of that type. Then the spreadsheet computes each Debt Type's total as a percentage of all your non-mortgage debt. Finally, you can see exactly how much of your monthly income you're contributing to each Debt Type; that's in the column headed "Total Monthly Payments."

At the bottom, the spreadsheets tallies up both your "Total Debt (excluding mortgage)" and your "Total Debt (including mortgage)."

The second section of the "Summary" page shows a few common metrics for measuring your financial debt-related health:

• Debt-to-Income Ratio: This ratio shows how much of your gross (pre-tax) and net (after-tax) incomes (both annual and monthly) are committed to your total non-mortgage debts.
  
  The figures beneath "Annual Income, Gross" and "Annual Income, Net" tell you how much of your gross and net annual incomes, respectively, would be required to pay off your non-mortgage debts.
  
  The figures beneath "Monthly Income, Gross" and "Monthly Income, Net" tell you how much of your gross and net monthly incomes are required to service (make payments on) your non-mortgage debts.

• Monthly Housing Ratio: Lenders often look at how much of one's monthly income is appropriated for mortgage payments. Most lenders use gross (pre-tax) income for this calculation, and they prefer to see this number no higher than 25 or 30 percent.

I've left four blank rows in this section so that you can add your own custom calculations if you wish.

The third and final section of the "Summary" page looks like this:

This is where the spreadsheet calculates your overall "Credit Card Utilization Ratio," using current limits and balances from all your cards. (You listed ALL of them on the "Debt Listing" page, right?)

Your overall Credit Card Utilization Ratio is computed by taking your (Total Current Balances on All Cards) and dividing it by your (Credit Limit Total From All Cards). Credit-scoring agencies place a large amount of importance on your overall Credit Utilization Ratio when they compute your FICO and other credit scores. The lower your Utilization Ratio is, the better.

Once more, I've left five blank rows in this section so that you can add more calculations if you wish.

[Return to Top]

The final three worksheet pages, "Paydown #1," "Paydown #2," and "Paydown #3," were added at the request of several IYM readers. They asked for a way to track their debt payoffs over time, and to see their progress charted visually. I hope that the Paydown worksheets fill this need.

To get a look at the "Paydown" worksheet pages (they're all duplicates of the same page), click the following image:

The data already shown on the "Paydown #1" page are merely for example. In this example, I'm tracking a single debt. I've named it "FirstBank Visa." Note that I've entered that Debt Name at the top of the page:

The big chart in the middle of the page will give you a visual representation of your debt as you pay it down over time:

Toward the bottom of the page is the area in which you'll enter your debt's balance as it changes over time:

In the example, I input the starting date (1/28/06) and starting balance ($10,000) of my FirstBank Visa debt in the first row (Row 24) of the data-entry section. In the example, I've made six entries, one per month. However, you could make entries at whatever time interval you like. They don't have to be evenly spaced, necessarily.

The example shows me tracking a single debt — a credit card account. You could, however, use a Paydown worksheet to track your total non-mortgage debt, or your total credit-card debt, or pretty much whatever debt you like. That's why there are three Paydown worksheets — so you can track more than one debt item if you wish. Just give each debt item its own Paydown worksheet.

[Return to Top]

Can I add more Paydown worksheets?

Sure you can. Just copy a blank Paydown worksheet — or copy one you're using, and then delete all the data from it to start fresh. If you're unsure how to do this, here's a walk-through:

1. Right-click the bottom tab of the Paydown worksheet you wish to copy. Select "Move or Copy."
   
2. In the "Move or Copy" window, scroll down in the "Before Sheet" section. Select "Move to End."
3. Place a checkmark in the "Create a Copy" option.
   
4. Click the "OK" button.

You'll now see another Paydown worksheet at the end of your workbook. Right-click its tab, and rename it as you wish.

[Return to Top]

Can I rename the Paydown worksheets?

Absolutely. In fact, I'd recommend it.

Just right-click the tab of the worksheet you wish to rename, select "Rename," and type whatever you want the name to be. It's just that easy.

[Return to Top]

Can I make a donation?

Like its predecessors, Version 3.0 of the DebtTracker spreadsheet is absolutely free to the public. I truly hope that readers download it even more often, and find it even more useful, than the earlier versions.

If you use the DebtTracker and think it worthy of a donation to the upkeep of IYM, then simply click the button below to make a donation via Paypal.

NOTE: I would ask, though, that if you're someone who's in substantial debt, and working like crazy to get out, that you please refrain from making a donation to IYM. Instead, use your intended donation amount as an extra contribution toward your highest-interest debt ... and then drop me an email to tell about how my site or my spreadsheets have helped in some way. That, I assure you, is more than enough payment for me.

I am, as always, grateful and appreciative of any and all donations.

[Return to Top]

Want to know when I add to and update my spreadsheets? Subscribe to IYM's feed!