Great, let me know if you run into any issues in the future.
@chris.oneill619 @Rocketghost Wanted to tag you 2 in case you are still looking for an investment tracker. Happy to help you set this up or help you with Cowboyâs one if you prefer that one.
@purujit.saha Thanks!! You guys are so helpful in here! However, just like in cowboys sheet the instructions are written with the expectation it is working but i think we need stuff when or why it doesnât. So i will comment for what its worth in the event it helps?
After copying template to foundations:
- Populate the accounts to track (column A): Fine, I see my investment accounts
- investment categories (column B) and periods to track (column C). Fine, categoryâs are linked.
- âThe rest should auto-populateâ
Right away we have issue. Nothing happens
Reading further:
" It requires Transactions <ok!>, Accounts and Balance History sheets in the foundation template. <ok!> âDonât modify the hidden section that contains external references forINDIRECTand some other computed columns but everything else should be fair game.â <ok!>
Right from the âstartâ a question i would have is âwhere does it get the starting balance?â The formula in there is not able to be read by non technical folks. I also assume from looking it only tracks the main accounts, (e.g. a total brokerage) and is not able to bring in specific holdings within that account?
I wonder on that because I actually maintain an excel sheet that does that using the Data>Stocks function so wondering if that may be incorporated, but of course lacks the banking uploads. I might take a stab at combining the foundations and that, although I am new to Google sheets
@Rocketghost Thanks for trying this out. If you are up for it, we can debug this.
- In your screenshot, if you hover your mouse over the â#NUM!â cells, does it show an error message? If so, what is the error message.
- In the template, columns D through AA are hidden. If you expand it (see my screenshot below for how to expand it), columns M through P should have the transactions of the categories that you listed in Column B populated. Is that coming through?
- What period did you put in?
Forgot the screenshot. Here it is. Note the two arrows between columns D and AB and click on it to expand the hidden columns.
AH, well came back after a few hours and many the errors in the screenshot i provided resolved themselves. Weird, as I did nothing since i took the shot. Anyway the remaining #NUM errors say: " Error XIRR attempted to compute the internal rate of return for a series of cash flows, but it was not able to."
My current return shows as several million %, (i wish haha) and all the starting balances remain @ $0.0.
I expanded the columns and see all the background data. (Column M through P have my transactions).
How does it determine starting balances? Is there a way to manually add this to get it to show more realistic returns?
Wow, thatâs strange.
I have only seen temporary errors from the cells that use GoogleFinance calls - those calls are sometimes flaky. Anyways, if it happens frequently let me know
Regarding starting balance, it uses the âBalance Historyâ sheet. Letâs say you enter â2023-01-01:2024-02-29â as your period. The formula will find the latest balance with a date prior to 2023-01-01 and use that as the starting balance. If there is no such balance, itâd use 0. So if you donât have any balance in Tiller prior to that start date, your returns will be overestimated.
You have 2 options.
- Select a period such that your investment accounts have a balance in Tiller Balance History sheet prior to that date i.e. a date after you started using Tiller. This is useful for people who have always used Tiller or donât need that long of a history. I used this option since I did not import transactions from Mint.
- Or, if you imported transactions from another finance app like Mint, you can enter your balance for those accounts manually in the Balance History sheet (add new rows on the bottom since Tiller adds rows on the top) with a date on or before the start date of the period you want to track. I am assuming you have transactions already imported - so cash flow should be pulled from there as long as you populate the Account ID column in the Transactions sheet. Tiller assigns a unique Account ID for all of your accounts. It is in âAccountsâ sheet hidden column G and transactions sheet hidden column L and should look something like
655e9909fcecd2002f8095b6. Imported transactions wonât have this populated and @RedNell above had to populate that column to get the cash flow data imported.
Let me know if you have any questions or run into other issues. The XIRR issue will go away once your balances and cash flows are fixed. The calculation of XIRR uses a numerical method (there is no closed formula) and relies on an initial guess. So returns of millions of percent often wonât compute.