Upcoming one-time/long term expense calculator?

June 15, 2009 11:36 AM

How to calculate how much to set aside each paycheck for multiple upcoming one-time/long term expenses?

I keep a list of upcoming one-time expenses and their dates, for example:

car insurance, $500, Sept 09
winter tires, $600, Nov 09
dentist, $300, Dec 09

I'd like to have a simple calculator that tells me how much I should be putting aside each paycheck (I get paid twice a month) so that as each date comes up I can just take that money out knowing that I'll still eventually have enough for the next payment. Bonus points if it can tell me how my deposits will vary as I work my way down the list. Extra bonus points it it can be done in Excel.

So, in the above example:
-my total expenses is $1400
-my dentist bill is split over 12 payments of $25
-my tires are split over 10 payments of $60
-my insurance is split over 6 payments of $84
-until the end of Sept I'd set aside $169 a paycheck
-then until the end of Nov I'd set aside $85 a paycheck
-then until the end of Dec I'd set aside $25 a paycheck, with the numbers changing as I add additional long term expenses.

posted by furtive to work & money (6 comments total)

See this or this. I like the latter; it's uncomplicated.
posted by jgirl at 11:47 AM on June 15 [1 favorite has favorites]

Sorry, I should make it clear that I already keep a budget (have been doing so for a good eight years) and usually have money set aside for expenses outlined like the above, but I'm in a position where I'd like to be able to set aside near exact amounts for these sorts of things.
posted by furtive at 11:50 AM on June 15

Well, see the first link.
posted by jgirl at 11:55 AM on June 15

I use a spreadsheet to track long-term expenses and understand how large my cash-flow buffer needs to be at any given point. I might have $x in my checking account with nothing outstanding, but I rely on the spreadsheet to tell me what part of that money is untouchable. Right now, for example, I know that $387 of my checking account balance is already spoken for, even though the bills it will help to pay may be months away. It probably works something like jgirl's first link, except it's not online and the money stays in the same bank account. Is that the sort of thing you want to accomplish?
posted by jon1270 at 12:18 PM on June 15

Actually, I was able to figure out the following in Excel:

=PRICE/((IF(DAY(LDATE)>=DAY(NOW()),0,-1)+(YEAR(LDATE)-YEAR(NOW()))*12+MONTH(LDATE)-MONTH(NOW()))*2)

where:
PRICE = the cell that has the cost of whatever the expense is.
LDATE = is cell that contains the due date (in month, year for me)
NOW() = needs to be replaced with the date you start setting aside the money.

It tells me how much I would have to set aside each paycheck, and I just need to replace NOW() with the date that I started saving for each item or else it will recalculate the payment schedule to the full cost each month.

It's worth noting the above doesn't work with odd number of payments since it calculates the number of months times two.
posted by furtive at 12:19 PM on June 15

I will have a play with your formula and see if it improves my method. I just do the crunch on monthly payments manually. My 'bill' spreadsheet has months across the top (I get paid monthly) and the list of bills running down.

Each bill line has a running total (=previous cell + amount for that month) so that I can add up how much should be in my account along the bottom. When it gets to the month where the bill is due, I set the cell to zero (and then start adding again the next month if it is a regular bill).

I have also automated most of these payments, so that the money is automatically transferred into my bill account (except for one, which isn't really a bill, but savings for a car, and the amount varies). Otherwise I would sometimes get a bit confused.
posted by AnnaRat at 4:47 PM on June 15