Earlier this week, a reader asked about adding some functionality to the debt payoff spreadsheets I posted a few weeks back.
One of the things he wanted was a way to enter base income and subtract expenses, rather than having to calculate it separately. That was fairly easy, and I went ahead and explained how to do it.
The other thing he wanted was a way to spread the extra payment evenly across all debts, rather than applying it all to a single debt until that debt is paid. That’s certainly an option, but seemed a little difficult to explain in a comment. So instead I’ve developed a set of spreadsheets that have both of these options.
In addition to the previously existing pages, I’ve added an “Expenses” page, a “Plan” page, a line on the “Income” page, and a column in the “Payments” page. I’ve also made some of the improvements I noted when talking about the original spreadsheet.
You can download the new versions using the links below.
– Excel 97 (.xls)
– Excel 2007 (.xlsx)
– Open Office (.ods)
Once again there are instructions in the spreadsheet, but here’s a general overview of what to do in the new worksheets.
New Worksheet: Expenses
Just list your estimated expenses in the blue spaces on this sheet. There’s a couple of examples, but feel free to delete them. You don’t need to transform frequent expenses like grocery bills into monthly expenses — just enter them where they’d naturally fall, and the spreadsheet will figure it out.
If you want to see how I’m accomplishing the shifting, just look down at rows 42 through 44. On row 42 I’m shifting any uncovered expenses up to larger time periods, until they get covered. In the process I’m multiplying them as appropriate. For example, weekly expenses get multiplied by 2 when being covered by biweekly income. On row 43 I’m doing the reverse — shifting uncovered payments towards shorter time periods until they reach an income that will cover them. Yearly expenses, for example, get divided by twelve and applied to monthly incomes. Then on row 44 I figure out which approach actually covers all the expenses, and use that in later calculations.
New Row: Income
Instead of just being able to enter your available money after expenses, you can enter your income, and let it calculate how much you have left. You still have the option, however, of entering the available money directly. If you do you’ll be overwriting the formula, so only do it that way if you’re sure! If you’re not sure, you can get the same effect by entering your available money in the “Income” row, and not listing any expenses.
New Worksheet: Plan
If you want the payments spread evenly, put an “x” in the blue space below the “Even” header. If you want the payments focused on a single debut until it’s done, put an “x” in the blue space below the “Rolling” column. (It really can be any character, including a space! But if you use a space, you won’t be able to see which you’ve marked easily, so I don’t recommend it.)
New column: Payments
In between the “extra” column and the information for the individual debts I’ve added a column called “debts”. It just counts the number of active debts for that row. This is so, in the “even” scenario, the extra money can be divided evenly.
I also modified each of the “Extra” columns under individual debts so that they’d give one value if the “Rolling” plan was in place, and a different one if it was the “Even” plan.
That’s everything! If you have any questions or want to see additional functionality, just let me know!