How To Use The Debt Tracker Spreadsheet
To Calculate your Debt to Income Ratio & Total Cost of Debt.
Before we start – this tool is for you if you want to:
- Know how much ALL your debt is costing in interest charges every month / year
- View your TOTAL monthly payment for ALL your debt in a single sheet and how much of that goes to pay the interest
- Analyse at ALL your credit cards, loans, mortgages AND assets and/or equity on a single balance sheet
- See ALL your debts and total assets in one place
- See which debt is costing you the most so that you can create a payoff schedule that minimises your interest charges and pays your total debt down faster
- Offset the interest or income the you EARN against the total COST of debt
- Calculate exactly how much money you will save every month by using your cash balances to pay off expensive debt.
- See how much $1 of debt costs you EVERY year in your current debt structure.
- See your mortgage loan to value percentage (LTV%)
- Calculate how much you can save by switching high interest debt onto 0% or lower debt vehicles (cards or loans)
- Evaluate the best way to restructure your debt so that you pay less for it, even if you can’t pay anything down.
- Get a snapshot view of your debt and credit situation right now
This tool is NOT for you if you want
- A loan amortization schedule
- A snowball or avalanche payoff schedule tracker
- A loan or credit card interest calculator
- A retirement planner
- A minimum payment calculator
- A budget management system / planner
This sheet has been designed to help you to answer a number of questions that you may have about your current debt situation, but I personally developed it for myself so that I can feel more organized with my debt. Quite often if we have a number of cards, a few loans, a mortgage (or two) and possibly the odd store card hanging around, it can feel too much of a mountain to climb. Who wants to start a journey when you don’t know how far you’re going or how long it will take you to get there?? I’d be willing to bet that not many people would sign up for that field trip!
If you are suffering from debt overwhelm, or you’ve had to take on more debt than you’re comfortable with during the current crisis, I really think that this tool will help. It might also benefit you to look into some form of counselling, or even just join a group online for some moral support, you really have nothing to lose and potentially a hell of a lot to gain from the experience.
How to Use The Total Cost of Debt Calculator
I am a very simple being at heart so this tool has been created to take in just the relevant information to enable it to calculate the outputs that we need, namely the total cost of debt, total of all minimum payments each month and the total outstanding balance over all your debt.
Step 1 – Credit Cards & Loans
Pick a card, any card……(preferably not the ace of spades though!)
Write a description of the card in the description column, in the example I’ve used here is “card 1”, not particularly imaginative I admit, but you get the idea.
Next add in the total balance that you owe on that card as at your last statement in the “balance owed” column
From that same statement add in the “minimum payment due” amount into the next column and the interest rate.
The sheet will now calculate your monthly interest amount so you don’t need to add this manually. Don’t worry if it’s a little out from your statement, for these purposes is just needs to be close enough.
Finally, if you have a promotion on this card, write in the end date so that you can easily see which cards might be falling out of their 0% introductory period soonest.
A quick side note here, in my experience, most credit card companies don’t like to make the “promo end date” clear on their statements, I guess because they want you to forget and end up paying interest. You might have to call or email them to get confirmation of the EXACT date you need to pay the card off before they start to charge you their standard interest rate. Also, don’t worry too much if this is a pain to find, you can always revisit it later.
Now pick another card…..and do the same again
Keep going until you have listed ALL your debts. If you need more lines, just right click on one of the rows, and insert a row.
When you have done this, copy the formula in column F to calculate the monthly interest charge :
The totals row will now reflect the total of all your balances owed, the sum of all your minimum payments and the sum of monthly interest charged. All these values transfer to the summary box at the side of the sheet.
The total interest rate is an AVERAGE interest rate. There is an additional hidden calculation that takes into account the balances owed on each card and the respective interest rate for each one to give you a more accurate average rate. You can test this by changing your interest rates and total balance value and watch what it does to the numbers. You will see that even a small balance at a high interest rate can have a large impact on your over cost of debt.
Step 2 – Mortgages and secured loans
If you don’t have any mortgages or secured loans, please move onto step 3, but if you do; take a look at your last mortgage statement for your interest rate, product end date and balance owed.
It might have been a while since your last mortgage statement, so you will need to work out an estimate for how much you owe now.
To do this, there is a second tab in the workbook (DELUX version only) where you can add in the balance owed at your last statement, the payments you have made since this statement was issued and the annual interest rate. There is an option to change the annual interest rate as you may have dropped off an introductory rate and onto a standard variable rate or even a new product rate within this time line.
Once you have an idea of your current amount owed, come back to the main sheet and add this number into the balance owed column.
Next add your monthly payment amount and your CURRENT annual interest rate. The monthly interest charge will then be calculated for you.
Step 3 - Assets & equity
For many of us, this is the simple bit. If you have more than 1 property or you have a number of assets that you wish to include, please add them up separately and put the total value into the “current property value” box.
The “cash in asset” will be automatically calculated by taking the total balance owed in the mortgages and secured loans column, from the current property value amount.
Step 4 - Current accounts, savings and investments
Many current accounts pay interest on positive balances up to a particular value. Its also interesting (pun intended!) to note that these rates are often higher than so called savings accounts so it’s worth looking into this if you have any cash lying around in zero interest accounts that could be earning you some money.
Write in here any current account balances that you have. These will of course fluctuate throughout the month but write a balance in here that you feel reflects your average for each account.
If you are lucky enough to have money in a current account OVER the amount that you receive interest on (i.e. they pay you interest on anything upto a $2k balance and you have $2.5k), list the account twice.
i.e. say you have an account that pays you 2% annually, calculated daily on any balance up to $2,000. This would mean that if you consistently have about $2,500 in the account, $2,000 of it will earn interest and $500 would not. List these as 2 accounts, one with a balance of $2,000 and an interest rate of 2% and the second with a balance of $500 and an interest rate of 0% – make sense?
Repeat this process for any savings and investment accounts that you have.
Step 5 – the summary
OK, so now you have accounted for ALL your debt and ALL your positive balances, equity and assets which are summarised in the blue section at the side of the sheet.
The top part of the summary includes any balances from property. This is especially useful if you are considering a re-mortgage. If this box is green and your mortgage LTV rate % (loan to value) is LOW then it could be worth looking a new mortgage product that would release capital from your property(s) and enable you to pay off some expensive debts. If this is something that you are considering READ more about your options here before you jump in.
The next part of the summary shows you the numbers excluding the property, i.e. no fixed assets or mortgage values are included here.
This will show you your total positive balances from all your accounts and investments and the total negative balances from the 1st step: credit cards and loans. The resultant balance, shown here in RED is basically your liquid (or illiquid) position. If you have more cash in your accounts and investments than you have debt, this number would show up GREEN.
Your total credit payments and charges are displayed next, then your total interest received.
The reason for this part of the summary is to show you that if you have investments or accounts that are EARNING you more than your debt is COSTING you then you’re still winning!
I’ve written a post on “How to use debt to make money” – you might want to check this out next!
If however your debt is costing you quite a bit every month and you have savings sitting in an account that are earning you next to nothing (or actually nothing!), you can see quite clearly that using those savings to pay off the debt would be the most cost effective course of action.
The last 2 rows here give you an annualised impact of your debt structure. These figures EXCLUDE property and mortgages but I think that it demonstrates quite well how a few $ in interest charges every month might not seem like an awful lot but when you see that sum over 12 months you can really see the damage that it could be doing and how you can make a HUGE impact with small changes.
Test this out
When you have completed your sheet, take a copy of it and in your new version, change some of your interest rates to 0%, then compare the outcome summary with your original copy, it will surprise you just how much some simple changes could save you.
Lets say, for example that I manage to switch “card 3” on the test sheet from its current apr of 22% to 0% card. It’s a high rate card and the balance on it accounts for 40% of my overall debt so you’d expect a decent size impact but in actual fact it drops my annual cost of debt from $ 4,057.74 to $ 1,963.34 !! that’s a massive 52% reduction in COSTS. Imagine how much faster you can pay down your over all balance when you are SAVING $2,094.40 in a single year.
The more astute amongst you might notice that this “annual cost of current debt” calculation is not strictly accurate as it is based on a the assumption that your balances and interest rates would not change over the course of the year. Whilst this of course would not be exactly true as you would be making the minimum monthly payments and receiving interest charges and fees, for the purpose of this exercise the difference that it would make is very small.
Just incase you are panicing a bit at this point, PLEASE remember – You don’t have to do this alone, there are a debt charities, councelling sevices and skilled therapists that can help you deal with this. It takes strength to ask for help, it’s not a weakness. Don’t ever think you have no options, there are always choices.
Step 6 - Using The Debt to Income Ratio Calculator
(or Debt to Asset / Debt to Equity Ratio)
In this sheet the TOTAL LIABILITIES is the totals from the credit cards and loans section PLUS the total from the mortgage and secured loans divided by TOTAL ASSETS; i.e. property value plus current accounts, savings and investments.
If you just want it to show the debt to INCOME and not include any assets, leave any asset values out of the sheet.
It may be that you are looking for a different debt to income calculation, one that takes into account your weekly, monthly or annual budget. If this is the case, take a look at our budget sheet HERE and consider opting in to our list as we are constantly developing new tools and we send them out for free to our existing subscribers before we make them accessable on line.
Many credit providers like to see that you have a healthy debt to asset ratio before they offer you any kind of credit facility. A low ratio implies that you have more assets either in cash or some form of saleable entity than you have debts or liabilities, therefore you are seen as a much lower risk and they will likely offer you a better rate.
However a lack of credit can also count against you as the provider can’t see your track record, they like to see debt that has been or is being paid off as this again makes you look like a nice low risk for them.
You might not think that your credit rating really means that much, especially if you rarely apply for credit or just leave your mortgage product with the same company year after year, but it could save you thousands. A good credit rating is like the best image filter you’ve ever seen, it makes you look so shiny and nice that companies will be falling over themselves to give you money at nice low rates.
Plug some test numbers into this sheet and see how much just getting a few % better rate on a mortgage, loan or credit card could save you in 1, 5, 10 or 20 years, it really does add up!
Now what?? - Should I consolidate my credit card debts?
So you’ve completed the spreadsheet and don’t know where to go from here. Well, depending on what surprises this has thrown up for you, you have a number of options. the posts listed below will help you to get some clarity on your next steps……however if you feel like your spreadsheet needs more umph….like your still missing some key parts to your debt story, please check out our shop for an ever increasing range of downloads, I’m sure you will find something that works for you!
If you are looking for a payoff strategy and have heard that the snowball method is best……please take a look at our case study before you make a decision. Now that you have all your debts organised in one place you’ll be able to test out the benefits of each method before you start down the wrong path
Your first point of call should always be to check your credit score and make sure that ALL 3 agencies hold the CORRECT data about you. It’s surprising how often this is out of date and it limits your choices for cheaper debt massively so please don’t apply for any new credit until you’ve checked this is all ok.
If you have high interest cards and can’t get any free or cheaper credit, then you might want to consolidate or re-mortgage. Don’t take the first offer on this, shop around as much as you can, use comparison sites and do your research. If you are taking a new loan over a much longer time, make sure you run the numbers, just because the rate is lower it might still be more expensive if you’re paying it for a longer period of time.
Make a few copies of the debt calculator spreadsheet and test a few different scenarios to see what the end result might look like.
If you have a mortgage with a low LTV rate and high interest cards, this again could be an option but look closely at the numbers and if possible, get some impartial advice before you sign on the dotted line.