Hey Tiller peeps. Anyone out there have a system for projecting upcoming credit card payments? As in, I spent $900 on my credit card in July and know that I’ll have to pay off $900 to the credit card in August. So even though I “spent” $900 in July, it won’t actually be reflected in my checking account balance until August.
For example, I buy groceries on July 1 for $100 and buy a bike on July 3 for $800. I pay for both with my credit card. My credit card billing cycle always ends on the 25th of every month. So In theory I could do some formula that sums all credit card transactions during a billing cycle, and then create a future transaction to account for the cash flow, I think something like this would also help predict bank account balances, So in August, I know my bank account should go down by $900 (the credit card payment for the stuff I bought in July), rather than going down by whatever credit card purchases I made in August. Tagging you @matt since I know you are into bank account balance tracking too,…
@Ngb12347 you’re right that it’s something that would interest me! Personally I don’t think I would use the system you’re describing very much, but I can definitely see the utility. I haven’t build anything like it, but it should be doable. I would start by using the SUMIFS or QUERY function to filter and sum the appropriate numbers from the Transactions sheet. I’d try to build something like that for you but honestly I just don’t think I’ll have time for it anytime soon.
I’ll definitely have to think about it some more though, it could be very useful.
Thanks Matt. Just to be clear, I have no expectation of you or anyone building this for me, just curious if it might work - so I appreciate the reply!
Regarding the SUMIFS of the transactions tab… do you have any idea if that is a “heavy” calculation? Like will it slow down the spreadsheets moreso than other formulas? I’m just debating whether I should go crazy on SUMIFS for some other dashboards I’m making or whether it will make the spreadsheet super slow.
I pull credit card balances from the Business Dashboard and sum them up in a big master dashboard I created. It doesn’t “project” in the sense that it tries to estimate where I will be at the end of the month, but it does let me know where I am.
With regards to the July-bill…August-payment dilemma, I pay off 100% of my credit card balances on the last day of the month. Sometimes I have charges that don’t post until a couple days into the next month. Some companies won’t let you pay more than a certain percentage current balance. If that’s the cast I just pay those as soon as they post. This does require a little creative accounting on my part because my checking account doesn’t get debited for a couple days after I make the payment. What I do in the case is back-date the payments from checking as well as the credits on the charge card account to the last day of the month. I am sure to put a little note in the transaction that the date has been changed in case I need to do a reconciliation against the statement.
Then I go into the Balance History worksheet and manually set the balance for all my cards at $0 on the last day of the month and adjust my checking balance there to reflect the payments. It’s a lot of work, but all my charges are made and paid in the same month. I keep a running realtime reconciliation of every spending account against my Rollover in the Budget Dashboard (credit cards, bank accounts, and cash investments) that I like to have perfectly balanced. That way I know every dollar I have socked-away in a budget actually exists somewhere in an account.
I think I would find an inactive cell on the Balances tab [either a new row at the top or an unused column like column E] and put the sum of your checking and credit account balances in a box labeled “liquidity” or “spendable balance” or “OMG”.
@brewer.05 I appreciate the reply. What you describe is a lot of work, but it is good to see how you make it work. I can understand it being worthwhile in the end in order to start with that 0 balance every month and be able to accurately project cash flow. Thanks for sharing!
I accomplish this by breaking up my expenses under “Credit” and “Debit” groups. My credit card bill should equal $1,761.90 since that is what I am budgeting for the expenses I pay for using my credit card.
@catpatriciamorgan Interesting. Does that mean that your statement ends exactly on the end of the month? This seems like it wouldn’t work if your statement ends on the 27th. So a purchase made on the January 28th, 29th, 30th, or 31st would show in your tiller budget for January but in reality wouldn’t be a part of the statement cycle ending january 27… right? Am I missing something?
@Ngb12347 I actually do have my statement ending on the 27th of the month. I pay CC bills off on the 1st of the next month so if I have spending in days the 28-31, I include in the current month’s spending and add it to my CC payment even though that spending is on the next statement. So it’s not a perfect system but it is usually close and at least ballpark what I should expect.
Grouping along the Credit/Debit lines is interesting, @catpatriciamorgan. I use Discretionary/Living. I can see that most recurring, non-discretionary expenses— mortgage, utilities, insurance, etc— could be moved to debit payment while budgetable and discretionary expenses could be paid by credit card.
Hi @Ngb12347 ,
I don’t think any existing Tiller Labs solution exactly matches what you are looking for.
Here’s a possible method to figuring out your Upcoming Credit Card payments. This could be a separate sheet on your spreadsheet and the results could be added to a dashboard, rather than adding something to Transactions or Accounts sheets.
List each of your credit card account names and the day of the month that the statement period ends for each.
This would be easier if all credit card statements ended at the end of the month. But that’s probably not reality. Also, each card likely has a different end date.
You could then write a SUMIFS() formula for each of the accounts to calculate the net sum of the transactions for each credit card account from 1 day after the statement ended last time until the end of the current statement.
You would likely use the DATE() function (more info here) to get the correct date range.
You would need to exclude any Transfer payments that paid off the prior month amount, since they wouldn’t be included in the payments due amount.
This method might assume you are paying off your entire balance each month.
This could give you the current upcoming credit card payments due for each account. Adding all the accounts up would give you a total of credit card payments due. You could subtract that amount from your current checking balances to get a net balance that includes your upcoming credit card payments due.
Another method would be to use the current Accounts balance values rather than Transactions amounts. But I’ve found that the balances don’t always match up with the transactions sheet due to minor timing differences. And a current balance might include transactions that will be paid from a prior payment.
@jono wow that write up was on point! Yes I think that would do the trick. I would just exclude any “Credit Card Payment” Transfer Category.
One thing that might be tricky is credit card rewards. If you redeem them to statement credit then they probably should be included in the SUMIFs. If they are directly deposited into your checking then I think the rewards should be excluded from the SUMIFs.
When creating a separate sheet for the credit cards and their due dates… is there anyway to do this where that sheet would automatically list all of the credit card names by pulling from the “Accounts” tab?
@Ngb12347 ,
I’m glad you found my comments helpful. Yes, exclude those Credit Card Payment transfers.
If the credit card rewards are a set 2% perhaps for all transactions, you could calculate the 2% credit total on this sheet and include it as a credit in the amount due. Or you could manually enter the changing rewards value, but that might be a lot of work.
This formula would give you a list of all your credit card accounts: =FILTER(Accounts!H2:H,Accounts!M2:M="Credit")
It assumes you use the Type = “Credit” for your credit card accounts. If you use a different type name, substitute that at the end of the formula instead of the word Credit.
I’m curious if anyone implemented any of the solutions in this thread.
I found myself asking a similar question and posted about it here. If anyone has anything along these lines, are you willing to share it? I’m happy to make contributions too if I can see helpful ways to build on it.
Looking at it again, one of the differences is that I’m interested in projecting future balances across a year, based on budget, rather than current payments based on transactions
@soitmake, I’m currently working on a solution in Google Sheets that might be interesting to you, but it’s pretty involved and not quite ready for release just yet.
My solution takes a list of recurring multi-grain expenses and plots them against a series of paychecks (past, present, or future), to show when those expenses are due. It shows an expected (avg.) paycheck amount, budgeted expense amount, and the amount you should have left over from your check once those expenses are paid. There’s a whole other slew of cool stuff it does, though!
There is also an optional parameter to add in credit card totals to the equation. When selected, the current amount due on your credit card is split into (1) what is due on your next due date (prior statement expenses), and (2) what is due on the due date after that(current statement expenses). Those totals are then added to the appropriate pay period “lane” and calculation (shown in blue).
But that looks at projected paychecks, not checking account totals. In addition to the above, I created a ‘Home Page’ sheet that displays my current checking account totals, along with the amount that is available after taking into account all recurring expenses and credit card totals that are due by my next paycheck.
I’m not sure if it’ll be 100% what you’re looking for, but it’s been helping me with planning upcoming expense/credit card payments and keeping on track. There’s a few more things I have to do before my eventual “Show & Tell” of it, but I’m excited to eventually get there, release it, and hear from the community!
