I put together a debt repayment calculator tonight. You can download it here (link).
I’m sure there are infinite calculators available on the internet, but I enjoyed the modelling exercise.
Yellow cells are inputs. Grey cells are calculations.
I’m not really sure the demographics of this blog’s readers, but I suspect that most of you aren’t carrying around a lot of non-mortgage debt. If I’m wrong, perhaps this tool will be useful to you. If I’m right, perhaps this tool will be useful to someone you know. Or perhaps it will be useful to nobody.
I set up the sheet to accommodate up to 9 different loans. The key input parameters for each loan are (obliviously) the interest rate, minimum payment, and balance. This will work for any type of debt (credit card and/or student loan and/or car and/or mortgage). Put the highest interest loan on the left as “loan 1”, the next highest interest loan as “loan 2”, etc. If you have fewer than 9 loans, then zero out the extra loan balances in row 10.
Given total monthly debt payments (Cell E2), the sheet pays off the highest interest loans first (while making minimum payments on all other loans). Columns AC:AK compute the size of the extra payment in excess of the minimum payment.
Columns M&N (on the right side of the below image) show how soon in years until you’re debt free at a given monthly repayment amount.
If you wanted to make extra payments in a given month (in excess of the baseline repayment in cell E2), you can input that manually in columns AC:AK.
It’s a lot more fun to earn interest than to pay interest, but before you earn interest it is helpful to get out of high-interest debt first.
4 thoughts on “Debt Repayment Calculator”
I do love a good spreadsheet. I find that even if you can’t find someone who can immediately use it (or understand it), it helps bring clarity of thought and a starting point for explaining what debt “looks” like. Thanks for sharing.
Glad to hear you like a good spreadsheet. Not sure I produced one, but I had fun doing it.
I created the sheet to help a friend who just graduated med school with a mountain of debt. This was intended to help them sort through it.
In the process of creating the sheet, I came to the realization that consolidating federal loans at the weighted average interest rate is a mistake because it precludes one from accelerating the repayment of a higher interest rate loan first. Without modelling the situation, that fact would never have occurred to me (though it obviously should have in hindsight).
I consolidated my loans back in 2004, and hadn’t thought about it that way. But I think one of the biggest reasons why I consolidated was to lock the interest rate in to 2.875% (1.875% after paying on time for 12 months; hard to not do with autopay turned on). I think they would have kept resetting to different rates if I hadn’t. It was so long ago I don’t recall. I finally dispatched the loans last December because I was tired of them. It would have made financial since to drag it out a little longer, but oh well.
1.875% after paying on time for 12 months!!! That’s less than inflation! Hopefully my Dr friend can work out some sort of refinancing deal like that.