Printable Debt Tracker
cost of debt calculator
This detailed debt management spreadsheet gives you a comprehensive view of all your debt and credit in one place. Our excel downloadable version has all the necessary calculations in it, so if you’d rather have that, please click here to take a look.
This download is available now, just add your email to the box to your left or click below, and we will send you the link over by return email.
This tool is for you if you want to:
- See ALL your debts and total assets in one place
- 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 credits cards, loans, mortgages AND assets and/or equity on a single balance sheet
- 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.
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.
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. and amount
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.
Add up each column so that the totals row reflects the total of all your balances owed, the sum of all your minimum payments and the sum of monthly interest charged.
Now transfer these totals to the summary box for use later.
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.
if you want some help to work this out, download the excel versio of this sheet as, there is a second tab in the workbook 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.
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.
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.
Calculate the “cash in asset” 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, 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 need to be 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!
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.
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.
Debt to Asset Ratio (Debt Equity Ratio)
The debt to asset or debt to equity ratio at the top of the debt calculator spreadsheet uses the following formula
total liabilities / total assets
In this sheet; that is the totals from the credit cards and loans section PLUS the total from the mortgage and secured loans divided by property value plus current accounts, savings and investments.
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.
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.
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.
Second point would be to restructure what you have, either through 0% or lower interest cards or a consolidation loan. Take a look at our article on consolidation options first though.
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.
A debt management plan can be expensive, try this for free, we won’t even send you loads of emails and it could save you a serious amount of cash, not to mention the satisfaction from sorting your debts out by yourself – for free, double bonus!