Hi @ramji241, thanks for the kind words and welcome to the community.
Regarding questions 1 and 2, can you share screenshots (feel free to redact any sensitive data) or copy and paste the data youâve entered for the transactions that arenât working? It would help to see what youâve entered in columns A through I, whether Q is checked, as well as whatâs in cells BJ1, BL1, BN1, and BO1.
Regarding question 3, if you enter a value in the Actual Amount column, the sheet should use that value when calculating the balance for that particular transaction. (The actual amount should appear in column CC for the transaction with an Actual Amount value, and the recurring amount should appear in column CC for future generated copies.) If that doesnât clear it up, some screenshots would be helpful.
In the first one you can see that the first date in column G is 7/16/2024, but in the third one you can see that the first generated forecast date is 7/23/2024. The rental income entries are in lines 12-14 in the first screenshot, with a date of 8/1/2024 in column G; they donât appear until 9/1/2024 in the third screenshot. The solar bill appears in row 18 in the first screenshot, but this actual entry is skipped in the third screenshot, with only the 9/1/2024 forecast appearing.
Hi @ramji241, try unchecking the box in BO1. When itâs checked, the blue table shows generated transactions only â useful for copying them over to the green table. When itâs unchecked, itâll show the transactions you entered as well as the generated ones. (The balance calculation doesnât change, just which transactions are visible.)
I think this will solve all 3 questions. Let me know.
I see, I was thrown off by the balance not changing; I figured the generated transactions would start with what I entered and show me a rolling balance from today. Got it, thank you!
Hi rhowell, I REALLY appreciate you putting all of this together. I just signed up for Tiller a couple weeks ago and added your template yesterday. Itâs the closest thing Iâve found to the functionality of an obscure personal finance tracking app called Calendar Budget, which is the only one Iâve found until now that allows for forecasting of future account balances based on recurring and projected transactions.
Would it be possible to allow for any account to be used in Column A? Essentially, making it agnostic to which side of the ledger the account comes from?
I realize that the main purpose of tracking asset accounts is to avoid overdrafts and shortfalls of real money, and that credit cards by contrast only impact our real money once the bill is paid. But for the purposes of monitoring all of my recurring âfixed costs,â it would be great to forecast credit card accounts as well since many recurring items (e.g., gym memberships, streaming services, utility bills) may be autopaid from these accounts in order to maximize cashback, rewards points, miles, etc.
Totally understand if this creates too much complexity within what you have already built, which is a fantastic tool. But figured it was worth asking about, given the existing capability to support/toggle multiple accounts, match and reconcile actuals automatically within each respective account, and sort/filter/move rows and columns.
Hey @ericmcintire1 and welcome! Always glad to hear that people find the sheet useful.
The change to allow you to select any account is pretty easy. I think the sheet should work if you select a credit card account, but I havenât tried it, so YMMV.
To make the change, unhide the columns to the far right of the sheet. Find cell DX1, labeled Asset Accounts ($BE$1-> Showing âŚ, and change the formula to:
=let(this_result_,
query({DS:DS,DT:DT,DU:DU,DV:DV}, "select Col1 where Col1 is not null label Col1 'Accounts'", 1),
{this_result_;MAKEARRAY(rows(B:B)-rows(this_result_),columns(this_result_),lambda(r_,c_,IFERROR(1/0)))})
You should now be able to select any of your accounts in column A. Let me know how it works!
For reference, the original formula in cell DX1:
=let(this_result_,
query({DS:DS,DT:DT,DU:DU,DV:DV}, "select Col1 where Col3 = 'Asset' "&if(BE1="Showing checking and savings accounts only"," and (lower(Col4) = 'checking' or lower(Col4) = 'savings')","")&" label Col1 '"&CHAR(10)&"Asset Accounts"&CHAR(10)&"("&cell("address",BE1)&"-> "&BE1&")'", 1),
{this_result_;MAKEARRAY(rows(B:B)-rows(this_result_),columns(this_result_),lambda(r_,c_,IFERROR(1/0)))})
You are the GOAT! Thank you for sharing your awesome work. This is exactly what I was looking for but I thought I would have to do it myself until I came across this.
I am also trying to make it automatically fill the âActual Amountâ but any cell or array formula I put in the table breaks the sheet. Can you point me in the right direction to adding this functionality?
It looks like credit card bill projections are based on total balance payments. How can I change it to be statement balance?
Iâm not sure what youâre trying to do â can you provide an example?
Credit card amounts due are calculated by subtracting transactions that occur after the closing date from the most recent balance reported to Tiller. Tiller doesnât download statement balances, so this is the best we can do. The calculated amounts due should be accurate unless youâve had credits applied after the closing date.
Iâve entered data for a Credit Card. The row is in bold, so it has linked to the liability account. Current statement has 10/20/24 closing date and 11/23/24 payment due date
Column L says âClosing Date invalidâ
Incredible template! Thank you so much. I had my own for the last two years but was manually bringing in transactions in general and this is SO helpful and does 85% of what I need and will keep me using Tiller for sure.
Hi there @rhowell. Love this sheet, especially as we pay 99% of our expenses by card (which of course we pay off every month so we get the points (maximize those) but do not pay a penny of interest).
Still having a couple of issues after starting over a couple of times.
One is the closing date invalid notification. Some of our cards have auto pay set up for as close to 15 days after the transaction date. This solution looks like it narrows it to 21. Not a great coder so would love some guidance as to what would need to be changed to grab these payments.
Also, when my husband pays off a balance well before it is due, the account seems to get âorphanedâ, it never clears, and I canât even see the payments in the override section to manually match them. Not sure if this is again âtime relatedâ issue. I end up deleting them and reentering them for the next month, but of course the goal is to try to automate the estimates here.
The calcs for current balance are sometimes off. Not sure why, as if I go into the transactions sheet and do totals for a CC account from a certain date to today it generally matches my CC statement (accounting for pending transactions), but the calculation on the sheet is sometimes off. And there arenât necessarily unexpected credits or anything out of the ordinary. Even seeing instances when there is an accruing balance due but the generate calcs showing a credit. Would be happy to assist here.
Dream: Would be to somehow add in capability to project recurring expenses that will be paid by CC (thus impacting cash flow on CC due date, not expense date). This would be a very sophisticate addition I understand, and maybe best left to another sheet, but for those of us maximizing cards as a payment method this would be an awesome addition. Again, happy to give my thoughts.
Let me reiterate that the work is amazing. I wish I had the skills! If there is any further input or answers to questions that would help, please let me know.
I think youâre right that these problems are related. The sheet assumes that youâre paying your credit card close to or on the due date. The validation calculation relies on the Starts Recurring date being set to the credit card statement due date. The reconciliation calculation looks for transactions close to the Transaction Date and Transaction Amount.
The intended workflow is to set the Starts Recurring date to the due date shown on the credit card statement, and, if needed, manually change the Transaction Date to the date you actually pay the bill. This should address both problems.
If you (understandably) want to avoid manually updating the Transaction Date each time you copy over a new transaction, you can try updating the formula in hidden cell EJ1 to:
={"Liability Account Entered With âŚ"&CHAR(10),"⌠Valid Closing Date"&CHAR(10);
let(this_result_,
ifna(sort(filter({C:C,J:J;CH:CH,CO:CO},match({C:C;CH:CH},DS:DS,0),{J:J;CO:CO}<>"",{J:J;CO:CO}<={G:G;CL:CL}),2,TRUE),{"No liability accounts entered with valid closing dates",""}),
{this_result_;MAKEARRAY(rows(B:B)-rows(this_result_),columns(this_result_),lambda(r_,c_,IFERROR(1/0)))})}
This should let you set the Starts Recurring date to the date you actually pay the bill and eliminate the Closing date invalid error. Note that I havenât tested this change: I think it should work, but let me know if you run into any problems.
If the calculation is sometimes showing a credit, it may be a problem related to reconciling the previous monthâs payment. What dates are you using for the affected card for Transaction Date, Starts Recurring, and Credit Card Closing Date? Can you provide examples of these dates?
Also, is the closing date for the affected card always the same day of the month, or do you need to update it each month? If you update it, do you also typically delete the row with the recurring transaction for the previous month right away?
