@soitmake: The solution that I posted is really like 5 solutions in one. But the great part about it is that each component is pretty modular, and can be used independently. I’m currently in the process of posting them all as separate solutions. You might not be able to use the Paycheck report, but based on what you replied to @peter, I believe two of the underlying mechanics that build that report could potentially be of use to you.
For credit cards, I’m dynamically calculating what is due on the next due date, and what will be due on the due date after that. I don’t project any further into the future (though I’m sure there’s a way using parts of my solution). I do this using my Credit Card Settings sheet. This sheet is stand-alone, easy to use, available now, and has complete instructions in the Show & Tell.
I calculate the Upcoming Total Due and total due next time (Statement Purchases) by using the prior statement close day and total Balance. You enter the statement close day for each card on this sheet, and I generate the Prior Close Date from that. Any transactions after that date will not be due on your next due date, but the due date after that. This will be the Statement Purchases total. I then calculate what you owe on your next due date by subtracting that total from the overall total of the card.
- Unfortunately, a credit card’s close day can change from month to month by a few days. For this to be accurate, you have to make sure the close day is correct each time a statement closes. And it would only be accurate for the current cycle. Some credit cards have the same close day every month (a big THANK YOU to those cards…), but a lot fluctuate this date.
My Recurring Expenses and Generated Expenses sheets/solution, which I will be releasing sometime in the near future, would allow you to project future expenses out over any period that you desire, and might be helpful for getting those future projection numbers you are wanting.
You would enter your expenses, their frequency (from a list of 10), and requested information on the first sheet (above). One of the requested metrics is the credit or checking account typically used to pay the expense, which would be good to calculate what you are asking. A bonus is that with the Credit Card Settings sheet from above installed: when you select a credit card as the payment method, the Master Pay Source will pull in the checking account name that is set to pay that credit card.
On the second sheet, Generated Expenses, you feed dates to the View Start and View End cells, and a dynamic list of expenses that are due between those dates will generate, with the correct due dates that correspond to the frequency chosen for that expense.
- This list is generated a certain way, and cannot be ordered/filtered, so viewing this sheet directly is not the best experience.
- The best thing to do is to set up a report sheet (like I did with the paycheck report above) that queries this sheet for the expense list, ordered by date.
- You can set the dates you want to look at directly into the View Start and View End cells, or (more preferably) in cells on the report sheet you build. You can then reference those cells in the View Start and View End cells. This is the preferable way to do it.
I will be including a report example when I release this solution. It’s incredibly easy to use.
This would give you a full list of expenses for whatever period you want (the entire year if you choose). Then, you can pull in your pay sources into the report, and do any number of things to compare that data.