🏆 Scheduled Transactions template: Project future account balances; calculate credit card payments; auto-reconcile transactions

[rhowell]

@jamie1

The calcs for current balance are sometimes off.

Just occurred to me that this may be related to a bug identified above. See if either of the solutions mentioned in this post works for you:

Scheduled Transactions template: Project future account balances; calculate credit card payments; auto-reconcile transactions

Hey @dylan, Thank you! I thought there was a bug in the current activity calculation, but I hadn’t found it. For my cards, the difference between the due date and the next closing date is only 3 days, and I guess I haven’t look at the current activity in that window. I’d previously accounted for the possibility that a payment would throw off the current activity calculation, but I then changed the balance calculation to use the current card activity when no other values (actual, card payment, …

[dmetiller]

Hi, @rhowell . Hoping you can help me troubleshoot. I’ve been using this sheet religiously and getting a lot of out of it. This morning, when I went in to check it, all of the transactions for the past several months that had been processed and appropriately struck through were all live again. That is, they no longer were struck through as if no corresponding transaction had been found for them. I haven’t changed a thing since when I checked it yesterday morning and everything was working as expected and checked it this morning and nothing was working as expected. Any suggestions? Thanks.

[brettanicus]

Hello: I’d first check that you still have the conditional formatting rule:

f

And that cell V1 has the following formula:

t=map(A:A,B:B,P:P,O:O,AH:AH,M:M,lambda(account_,date_,manual_reconcile_,override_,reconciled_id_,amt_,
  if(row(account_)=1,"Reconciled",if(date_<>"",
    if((manual_reconcile_)+((amt_=0)*(amt_<>"")*(date_<=filter(EB:EB,DZ:DZ=account_)))+(reconciled_id_<>""),TRUE),
    iferror(1/0)))))

That should result in some TRUE values in column V.

[rhowell]

@dmetiller

they no longer were struck through as if no corresponding transaction had been found for them

That’s very strange. @brettanicus may be on to something regarding conditional formatting.

What happens if you manually reconcile a transaction by checking the box in column P? Does column V show TRUE and is the transaction struck through?

Also check that the dates of the corresponding transactions on your Transactions sheet are correct – maybe a fill/Autocat went awry?

If the problem still isn’t apparent, what happens if you start with a fresh copy of the sheet and copy over your data from the green cells on the old copy?

[dmetiller]

Thanks, @rhowell and @brettanicus. The conditional formatting seems to be in place, and the formula in V1 is correct. BUT when I manually reconcile a transaction by checking the box in Column P, it does not show True in Column V, so it seems like that’s the problem. Any insight on what’s broken? I swear I didn’t change anything. At least I didn’t change anything intentionally. It’s very odd, and I appreciate the help.

[jamie1]

Hi @rhowell. Sorry for the delay, just back from vacation.

For reference, here is the set up for a single credit card with a new copy of the Scheduled Transactions sheet. Columns B and G are set to the payment date and J is set to the closing date from my latest statement. The amounts in columns L Credit Card Pmt. Amount and M Transaction Amount both match the amount due from the statement. Notice the positive balance in Credit Card Current Activity Column K. This is not correct. The value should be -57.70 based on my transactions sheet.

I have tried both of the suggested linked solutions, and neither worked.

Option 1 results in a Credit Card Current Activity value in column K equal to the total charges going back to one month prior to the Credit Card Closing Date (so in this case it totals charges going back to 2/15, not 3/15).

Option 2 (after changing the formulas in M1 and K1) results in a correct Transaction Amount and Credit Card Pmt. Amount in columns L and M, but now Credit Card Current Activity is blank.

Any thoughts?

I will follow-up on my previous question about Closing dat invalid messages next!

Thanks for all your help Rob!

[jamie1]

@rhowell I should add that in the first instance, the $976.52 under Credit Card Current Activity is actually the total of all transactions in then account from 2/15, the previous closing dat, including the payment made on the account which is why it is positive.

[portamaker3d]

It’s been so long since I asked this that I don’t remember the exact details of the first question. I wanted to retrieve the amount from the transaction and autofill the “Actual Amount” column with it since lots of mine need correcting.

I understand it doesn’t know the statement balance, what I meant was it seems to keep adding transactions to the balance due even after the closing date. The projections keep being too high. This seems to be the bug discovered below if I’m understanding correctly.

[jamie1]

Hi @rhowell I think I may have found the change that’s necessary. If you change line 6 of the formula in K1 from:

ifna(filter(ER:ER,EJ:EJ=descr_,EK:EK=closing_date_),iferror(1/0))),

to

ifna(filter(EP:EP,EJ:EJ=descr_,EK:EK=closing_date_),iferror(1/0))),

it seems to fix it with the activity since last statement lining up with the charges in transactions since the closing date specified in column J. Specifically the first argument of the filter function needs to be EP:EP not ER:ER.

I will have to see if it works in the next cycle, but am hoping that’s addressed the issue. Any thoughts?

[rhowell]

@dmetiller

Any insight on what’s broken?

I’m stumped. Did you try re-installing a fresh copy of the sheet and copying your data over?

[rhowell]

@jamie1

Any thoughts?

Thanks for digging into this. I still haven’t had time for additional troubleshooting, but I suspect your fix will only work for part of the billing cycle. Let me know how it goes.

[dmetiller]

I never did figure it out, and whatever went wrong was seriously wrong. It started degrading the performance of other sheets because it seemed to be constantly running. Copying my data over to a new sheet didn’t work, so I wound up just starting from scratch. Not ideal, but it took less time to recreate things than I feared. Thanks for following up.

[jamie1]

@rhowell So far for the May cycle of generated balances which were populated today, the credit cards seem to be pulling the correct balances forward for the future payments (the May payment is showing the charges accumulated since the April payment). I will keep you posted as the month progresses. Thanks again for all your help and for a great addition to the ecosystem. By far one of my favorite sheets.

[northrop]

Please forgive this newbie question – I’m trying to learn the system.
I’m entering my scheduled transactions, and want to follow the directions for calculating credit card payments. However, my linked credit card accounts are not showing up in the “asset account” dropdown list (column A). This is the case even after I unchecked the box in column BE. With that box unchecked, the dropdown shows checking, savings, investment, and manual asset accounts – however, none of my credit card accounts show up. Am I missing another setting to tweak? Thank you.

[northrop]

The Tiller Helper says that this section won’t work as written. It says: "The “Asset Account” dropdown in the Scheduled Transactions template is built to pull only from the hidden “asset” side of the Accounts sheet (the hidden area on the right, fed by Balance History).

So credit cards, which are correctly classified as “Liability,” will never appear in that dropdown, even if they’re fully linked and showing in the Accounts sheet.

That means there’s no setting or value in the Accounts sheet you can change to make credit cards show up there. The template’s “calculate credit card payments” instructions are describing a workflow that depends on the credit card being selectable in that dropdown, which won’t happen with the current setup."

@rhowell

The sheet can automatically calculate credit card payment amounts for any credit card accounts you’ve linked to Tiller. To calculate a credit card payment:

• Enter the credit card account name in the Description column exactly as it appears in the dropdowns in column A (the first Account column) on the Accounts sheet. It usually looks like the account name followed by the partially-redacted account number and an identifier in parentheses, e.g., Inspirus Savings - xxxx0001 (F538).
• The transaction’s description will appear in italics if it matches a liability account.
• In the Starts Recurring column, enter the date the credit card payment is due as shown on the statement.
• In the Credit Card Closing Date column, enter the closing date as shown on the same statement. Note that the closing date may vary from month to month; update it when you copy generated transactions to the entry area as detailed in the Workflow section, below.
• The amount due will appear in the Credit Card Pmt. Amount column and will reduce the projected account balance. Note that if credits, such as returns or redeemed points, are applied after the closing date the calculated payment amount will be higher than the eventual amount you owe.
• The sum of transactions made after the current statement closing date but before the next closing date will appear in the Credit Card Current Activity column. This column is shown for information; the amount doesn’t affect any other calculations. Note that this amount may be lower than the amount due on the next statement, once it closes, if credits were applied to the account.

[Lillypad]

@northrop Good question! I’ve been using this template for a while now and can verify that it does work very well! Based on the problem you are having, I think you may be looking for the account name in the wrong location.

If you look closely at the first bullet point in the instructions you copied, the instructions tell you to:

@rhowell

Enter the credit card account name in the Description column exactly as it appears in the dropdowns in column A (the first Account column) on the Accounts sheet.

Follow these steps:

Step 1: Go to the Accounts sheet (tab at the bottom) in your Tiller spreadsheet. Then, copy the name of the credit card account from the Accounts column on the Accounts sheet.

Step 2: After you copy the name of the credit card, go back to the Scheduled Transactions sheet in your Tiller spreadsheet. Paste the credit card account name in the Description column.

Step 3: Continue following the instructions for setting up the credit card payment calculations.

In the Asset Account column, you will select the bank account (an asset) that the payment will be made from.

I hope this helps you get the template working!

[northrop]

THANK YOU! That’s perfect, exactly what I needed. I very much appreciate your time and assistance!

[emery.olson]

I’ve just copied this into my Tiller spreadsheet and I’m not sure that it’s doing what I’d like it to do. My credit card gets me cashback, so I put my recurring charges on my credit card then pay it off (or make payments). Is there a setting that allows me to choose a liability account? And also plan for transfers? My primary goal is to make sure I have enough money in the appropriate accounts at the appropriate time. I’m thinking of something like - https://calendarbudget.com/

[rhowell]

Hi @emery.olson and welcome!

This sheet should do what you’d like it to. Make sure you’re following the instructions to calculate credit card payments exactly:

• the credit card account name goes in the Description column (copy the name from column A on the Accounts sheet to make sure the sheet makes the match correctly) , and
• the account from which you’re paying the credit card goes in the Asset Account column. This will debit the calculated credit card payment from the asset account you select; no transfer needed.

@Lillypad’s recent explanation may also be helpful: #97 by Lillpad

[emery.olson]

@rhowell Thanks for directing me to that explanation. Unfortunately, I’m trying to note when I charge something onto my credit card - increasing the balance, not paying it down. For example, I have regular massages and I put the tip on my credit card every month. I want to track that expense that is going onto my card, rather than being paid from my bank (or any asset) account.

I also specifically want transfers because I pay odd amounts - rounding up from the minimum payment, or adding a second payment.

Do you know if that’s possible? Thanks for your help!