Credit Card twelve month amortization schedule calculator
As an immigrant graduate student I did not have access to subsidized US government student loans. I had a pretty meager graduate assistantship of a little more than 10K. As a consequence I ended up charging a substantial amount of money on credit cards. It took me about 5 or 6 years to pay off these balances. It might have been worse if I had not done a little spreadsheet which gave me an idea about the true cost of the credit.
There are many reasons why one might end up with large credit card balances such as job loss, medical expenses, etc., or simply a period of indiscretion and in such a situation, if one wants to avoid going through bankruptcy and the associated pains, one probably should stop using all but one of the cards and pay off monthly balances as they are due on the remaining card. That takes discipline and some control over expenses.
To investigate why I was not making too much of dent in my balances after graduate school I did a spreadsheet and what I learnt helped me with the discipline and control I needed over my expenses.
The spreadsheet below provides an amortization schedule of payments and interest under the conservative assumption that you do not make any new charges on your card. It calculates the schedule for a starting balance, a desired percent reduction in the balance in 12 months and the annual credit card interest rate. As a default (you can input your own numbers in the blue cells) the spreadsheet has a starting balance of 10,000, a desired reduction of 30% and an annual interest rate of 12%.
In my example it appears that for a considerable monthly payment of 336.55 there is a relatively small reduction in balance or principal by 3000 in 12 months. So I calculated monthly payments on a separate simple interest amortization loan for an amount equal to the reduction. A 12% loan would require 266.55 per month in payments, 24% would require 283.68 and an astronomical 58.8% loan (see cell in red) would have the same payment of 336.55 as in the credit card amortization.
What is happening here?
It turns out that method for calculating the amortization schedule for the credit card is identical to that for a simple interest loan under the assumption of ‘no new charges’ that we had made earlier. So, why the difference?
The difference is that in the spreadsheet credit card example we pay off in about 35 months and the first portion (such as the 12 months period here) in that 35 month amortization schedule is when you pay more towards interest and less towards getting the your balance down. (This is similar to mortgage loans where you pay mostly towards interest and very little towards principal in the initial period of the loan). So a complete simple interest 12 month amortization of a loan equivalent to the reduction in balance of the credit card leads to much lower interest payments. Note that this (the simple interest rate of 58.8% for a 12 month loan with same reduction) is a contrived way of measuring the inflation in interest payments earlier in a loan. It also helps us see how that inflation relates to the desired rate of reduction of balances and interest rates.
One shouldn’t attempt to get a 12 month loan to pay down the balance as this example essentially does a little trick with numbers. In our example one might pay less for the 12 month loan of 3000 but would be left with very little for monthly payments for the 7000 remaining. In fact for 12% nominal rates for both the credit card and the simple interest loan in our example the amount remaining would exactly cover interest payments on the 7000 and make no dent on the 7000 balance over 12 months.
So how does the credit card amortization spreadsheet help?
It helps you assess the cost of the higher initial interest payments of any loan. Lower interest rates and a larger rate of reduction of balance reduce this measure. This spreadsheet scared me towards some control and discipline. Some things you should get from this spreadsheet are:
- You should determine the rate at which you want to reduce your balance and ensure that you will have the money for the corresponding payment every month.
- As your balance decreases the minimum amount due per your card issuer decreases as well. Don’t be tempted to reduce payments correspondingly and stick to the monthly payments you determined in step (1).
- Do not make additional charges on your credit card. If you do then add the amount of the charges to the monthly payment you determined in step (1). Making additional charges without a corresponding increase in payments has an adverse impact. For our example if we make charges of 100 every month and do not add this to the calculated payment then the payoff period of the credit card balance increases from 35 months to 58 months as the effective payment decreases from 336.55 to 226.55. You can create this payment schedule by entering a 16.05% derired reduction for the 10000 balance and the 12% interest rate. Note that the ‘cost’ of initial excessive payments to interest is now 110.7%.
- If you have trouble avoiding additional charges to your card then consider getting a bank loan with the same or lower interest rate, pay off the credit card and keep paying off any new charges each month thereafter. Credit cards provide flexible credit typically when no other forms of credit are available. However they are structured to keep customers perpetually in the high interest payment cycle of a loan by encouraging minimum payments and allowing new charges. Banks and credit unions offer signature loans (loans which do not require you to provide collateral i.e. something in exchange for the loan) and these can help when there is some control over credit charges thereafter. I used two such loans in the 6 years it took me to repay my credit cards.
- For United States employees paying down a credit card is like investing in a Roth IRA. In a Roth IRA you invest post-tax dollars and when you withdraw your withdrawal is tax-free. The reduced balance on your credit card is like income and you are not taxed for reducing your loan balance.
- If US readers find that their ‘cost’ for the higher payments to interest is very high then they may consider paying down credit card debt instead of investing in a 401-K. I personally had reduced my 401-K contributions for a few years to make larger payments on credit card debt. However most employers match the first 5 or 6 percent of your contribution and this compounds generating considerable income and one shouldn’t divert these matched investments to reduce credit card debt.
- As I had noted the initial higher interest payments are similar to those you would have for a fixed rate mortgage. The same advice that one gets about paying down the principal works for credit cards. Whenever you come by a large sum of money such as a tax return or a bonus apply a major portion of it to your high interest debt.
- It helps as well to consults a significant other or a friend, who are aware of your indebtedness, before making major purchases. Debate and discussion about purchases helps delay purchases and helps by getting you a better deal (this is particularly true about technology purchases).
Enter your balance, desired reduction and annual interest rate in the blue cells to refresh calculations. I am not a professional credit counsellor and you are advised to speak with one, if and when needed. However I speak from from my experiences and the calculations in this spreadsheet is accurate given my assumptions. If you appreciate our efforts and would like to make a voluntary contribution of any amount to help us maintain and improve this site please do so at this link.
The American University system has always fascinated me with it’s ability to mix science and football. A little picture inspired by that.