Last week I shared a debt payment calculator spreadsheet, and promised that this week I’d discuss how I did it. I’m not going to write out the formulas here, but my explanations here are designed to help you read through the formulas in the spreadsheet.
Is the way I did it the only way to do it? Heck no. Everyone will have their own style. It’s not even the best, because I was trying to be as flexible as possible, while keeping it simple enough to actually illustrate things. Plus I was trying to do it quickly, and did some things which would probably have been better done another way. (I make notes of the things that I realized while typing this up, but there are probably more improvements that could be done.)
On the first worksheet, “Debts”, the only formula I used was in the first column. It just made it easy to get the numbers 1-20. If I had pasted the results as values before publishing it, everything would have still worked the same. I named the area with all the debt information, for ease of use in later formulas. (I should have added a blank column between the numbers and the values, or else used a formula that automatically figured out which the topmost value was, to make it easier to sort.)
On the second worksheet, “Income”, I didn’t have any formulas, but I named all of the input fields for easy use in later formulas.
On the third worksheet, “Payments”, almost every cell has some sort of formula.
- There’s a formula in row 1 (it’s hidden) that calculates the length of time between payments. If there’s weekly income, it’s every 7 days. Biweekly income means payments every 14 days, etc. I named the cell with this formula “span”, short for “time span”.
- In the first column, labeled “Date”, the first cell pulls the starting date, which was specified on the “Income” page. Thereafter the cells increase by 7 days, 14 days, 1 month, or 1 year, depending on the value in “span”.
- In the second column, labeled “Previous Date”, the first cell calculates the starting date minus “span”. The remaining cells all simply contain the contents of the “Date” column, from one row above. This gives us the date that the previous payment was made.
- The third column (weekly) contains the weekly income (if any).
- The fourth column (biweekly) contains the biweekly income, but only on the correct weeks. If the span is 14, then every entry has the biweekly income. However, if the span is 7 then it checks whether the previous week had any income. If the previous week had biweekly income, then the current week would have no biweekly income, and vice versa.
- The fifth column (monthly) contains the monthly income, if and only if the month of the current date is not the same as the month of the previous date.
- The sixth column (yearly) contains the annual income, if and only if the year of the current date is not the same as the year of the previous date.
- The seventh column (total) contains the sum of all the incomes for that date.
- The eighth column (special) is for one-off input — irregular income, or birthday money, or tax returns, or what have you.
- The ninth column (extra) is how much money is available for that date, after all the minimum payments are made. It’s calculated by taking the amount in “total”, adding the amount in “special”, and subtracting all of the minimum payments. It has to recalculate ever row, because some minimum payments will disappear as the debt gets paid off.
- The tenth column is the balance of the first debt. It starts with the original balance (if there is a first debt), and from then on it adds the interest, subtracts the minimum payment, and subtracts any additional funds assigned to that debt.
- The eleventh column is the interest accrued on the debt for that time period. It’d calculated by multiplying the previous balance by the interest rate, then dividing the result by 365 and multiplying it by the number of days that passed since the last payment (span).
- The twelfth column is the minimum payment., but it only appears in the first payment of the month. It’s always the same until the balance reaches a point where the previous balance plus the interest accrued would be less than the minimum payment. It then drops to match that amount.
- The thirteenth column is the extra payment. So long as the debt and interest accrued are greater than the minimum payment, the “extra” column with contain either the full available “extra” money, or the difference between the debt+interest and the minimum payment — whichever is less.
- The fourteenth through sixteenth columns are the same as the 10th through 12th, except that they deal with the 2nd debt, not the 1st. Likewise, the 18th-20th deal with the 3rd, and so on.
- The seventeenth column is the same as the 13th, except that it subtracts whatever “extra” was used by the first debt when figuring out how much extra is still available to be used. The 21st does the same for the third debt, but it subtracts both the extra used by the first and the extra used by the second.
…and so on, for all 20 debts.
The first three worksheets would be a functional debt payment calculator by themselves, but it could get hard to read if there were too many payments. So the remaining worksheets existed solely for the purpose of simplifying the results down to 30 (roughly) evenly spaced payments. That restricts it to 30 rows, which is easier to see on the average screen, or to print out at a reasonable resolution.
On the fourth worksheet, “Summary”, I’m mostly using formulas to look up values in a table of balances (defined in a later worksheet), using the “vlookup” function. The row numbers are defined in a hidden column, and the column numbers are defined in a hidden row.
- In the “Date” column, I’m first checking to see whether there’s a value in the table for this row. If there is, I’m using vlookup to pull the value from the 2nd column, which is the date column.
- In the “Total Debt” column I’m first checking to see whether there’s anything in this row, then simply adding up the balances of the all the debts for this date.
- In the “Total Paid” column I’m first checking to see whether there’s anything in this row, then looking up the cumulative total amount paid for this date, from a table of payments.
- In the remaining columns I’m checking to see if there’s anything in this row, and then pull the balance for the correct column in row.
- In the 34th row, immediately below the table and directly under the “Total Paid” column, I’m calculating how much interest was paid over the course of the repayment, by subtracting the initial balance from the final amount paid.
On the fifth worksheet, “Balances”, I’m mostly using very simple formulas to pull all of the dates, debt names, and debt balances from the “Payments” page. However, the four hidden columns contain the formulas critical to simplifying all this information into only 30 rows.
- In column A, I have the value 1 in cell a2, and thereafter I’m increasing by 1 each row where the total balance is greater than 0.
- In cell A1 I’m calculating the space necessary between entries in the summarized table, to get them to come out evenly.
- In column B I’m calculating which rows should get displayed. Essentially, it calculates the value of the previous value in column B times the necessary space, and compares that to the current and previous values in column A. If the current value in A is greater than it, and the previous value is less than it, then the value in B increments. (Note: I didn’t actually have to check it against the previous value, but I didn’t realize that until just now.)
- In column C I’m just pulling the values in B, if they’re different from the previous one. That lets me set up a table with column C as the index column, and use vlookup to look only at the rows with values in column C.
In the sixth and seventh worksheets, “ExtraPayments” and “MinPayments” I’m again just using very simple formulas to pull all of the dates, debt names, and extra / minimum payments from the “Payments” page.
In the eighth worksheet I’m combining information.
- In column A I’m pulling the values from column B in the Balances sheet. (I could have deleted this column, but for some reason I didn’t.)
- In column B I’m pulling the values from column C in the Balances sheet, which was the index column for the condensed table. This allows me to create a table for payments, with the information lining up correctly.
- In column C (Date) I’m simply pulling the date from the Balances sheet.
- In column D (Total) I’m adding columns E through X, to get the total amount paid so far.
- In columns E through X I’m taking the values from “ExtraPayments” and “MinPayments”, and adding them to value in the previous row for that column. This gives a running tally of the total which has been paid towards each debt.
That’s it! As always, feel free to contact me if anything doesn’t make sense.