DIY Personal Finance - Tracking Long-Term Debt
Last week we discussed how to use a Google Sheet to track credit card debt. This week we will discuss how to use the same Sheet to track long-term debt such as student loans, car notes, mortgages, etc.
Similar to credit card tracking, your starting point is the debt’s current balance and annual percentage rate (APR). You can obtain this information from the most recent statement that you received. Let’s say that you have the following debt balances at 1/1/2020:
Car note - $17,567; APR - 4.7%
Student loan - $$32,354; APR - 6.9%
You would list the above on the Debt tab of the Sheet as follows:
Once you enter the balance and APR, amounts will appear on the Interest Charge and Balance columns on the rest of the Sheet. This is because the Sheet was set up to automatically calculate the monthly interest amount based on the APR that you entered.
Update the titles of the debt rows on each of the monthly budget tabs of the Sheet to match the titles that you entered on the Debt tab. In this example, since the pastel green column heading in the image above was changed from Debt #1 to Car Note, the name of pastel green row on the January (and all other months) tab of the Sheet should also be changed from Debt #1 to Car Note.
This is important because the columns on the Debt tab are formulated to pull the payment amount for its respective color from each of the monthly budget tabs. For example, if you entered a $250 payment on the pastel green/debt #1 row of the January tab, the $250 will also appear on the January Payments row of the pastel green/debt #1 column on the Debt tab. See the example images below.
The Debt tab summarizes the effect that your monthly debt payments have on your overall debt balance. Continuing with the example, as a result of making a payment of $250 on your car note in January, your car note would then have a balance of $17,476. As a result of making a payment of $310 on your student loan, your student loan would then have a balance of $32,230. Your total long-term debt would decrease from $50,011 to $49,706. Of the $560 that you paid towards your car note and student loan in January, $255 of that amount would be applied to interest.
When you create your annual plan, you can use the minimum payment owed for each debt as a starting point. I'll show you what I mean. Let's assume that the minimum payment for the car note is $250, and the minimum payment for the student loan is $310. Based on these amounts, the following monthly budget was created:
If you copied and pasted this January budget to the February through December tabs of the Sheet, the values below would appear on the Debt tab.
Per the image below, If only the minimum was paid on the car note and student loan during the year, then your total debt would decrease by $3,753 (see the Totals row at the bottom of the image below).
Now you can go back and play around with numbers on each month's budget tab. Maybe you may want to pay $250 per month on the car note from January through March, $350 per month from April through September, $400 in October, etc. Remember that If you increase the monthly debt payment, that means you have to decrease another budget line item (e.g., clothing, entertainment, etc.) or items by the same amount. As you play around with the debt repayment numbers on each month's budget tab, you can come back to the Debt tab to see the effect that those changes will have on your overall debt.
In the next and final post of the DIY Personal Finance series, we will go through an example from start to finish about how to incorporate your monthly budget, credit cards, long-term debt and savings goals in annual plan and things to consider.
The Sheet used in this article can be accessed at https://docs.google.com/spreadsheets/d/1t6vcO5_4XmA1WSYUzim-VwntWT7yW_BrB2C4vBMCxrs/edit?usp=sharing. It will be available at this link until January 31, 2020.
Zikora is a Certified Public Accountant (CPA) by day. When not assisting and advising clients, she can be found writing, singing, songwriting, exercising, cooking, and or plotting her next move. She is a sounding board for friends and family and enjoys showing people that they can indeed do things that they believe to be difficult or impossible. Send questions and/or topics that you would like Zikora to cover in future articles to naijacpa@gmail.com.