Docs: Simple Investment Calculator for Google Sheets

[morgan]

Overview

The Simple Investment Calculator can help you find out if you’re on track with your investment goals and what changes you can make to reach them.

My personal finance goals revolve around how much I can save, but I wanted to know how impactful my savings contributions were. Should I prioritize one investment account over the other? How much does my spouse need to increase his 401K contributions by to reach our goals?

Previously I would fill my sheet to get the latest balances, then navigate to another website to use an investment calculator, then back and forth between that site and my sheet to input how much I was saving in each account and what my future values would be.

I eventually got tired of this and wanted to just have the future value calculations directly in my sheet. I started with some formulas and wanted to keep it dynamic so when sharing the sheet with my spouse, he can input some “what if” numbers without the fear of breaking anything.

I enlisted @randy to help me connect the sheet to my accounts and balances then a zoom call later it has a graph and is ready to share with the Tiller Community!

Installation

The Simple Investment Calculator is now available for free in the Tiller Community Solutions add-on for Google Sheets.

Usage

Use the Years to Chart drop down to select the amount of years you want to see your values calculated through.

In the Tiller Linked Accounts section, you can select one of your Tiller accounts from the drop down in the Account section to pull in your most recent balance.

If you want to see the future value of a manual account, you can enter those balance amounts below in the Manually-entered Accounts section.

Then, enter your monthly contribution amounts, the year you expect to contribute these amounts through, your average rate of return (or you can use the min and max to create a range), and there’s your future value!

The graph shows the average future value amounts for each of the accounts you’ve entered alongside a Total line.

Notes

Future value calculations are based on payments made at the end of the period.

The graph requires an entry in the “Avg RoR” for an account to be displayed.

This template can only compute positive amounts entered for the rate (RoR).

[diogo6]

@morgan this sounds great, and I can’t wait to start using it soon! I did want to call out that I just “tried” installing it twice, and both times I got the following error:

I then realized at least 30 mins later that the sheet had in fact been installed (twice, see below)

Posting this here in case others run into the same, and so your team can hopefully see error logs that’d help you prevent it. Again, quite excited about this add-on!

[MarcC]

I saw the same message as well.

[diogo6]

and I trust, like me, that the sheet itself was actually present?

[randy]

Thanks for flagging this, @diogo6 and @MarcC.

I think one of the last steps in installing a template (for the add-on) is to rename it from “Copy of…” to its template name. My GUESS is that the install process is crashing somewhere toward the end, leaving the sheet un-renamed and possibly also leaving out the metadata that helps the add-on track the install status and version.

Can I ask you a few questions about what you’re seeing?

• Does the content of the “Copy of…” versions appear to be fully functioning?
• Do you have any sheets in your spreadsheet called “Simple Investment Calculator” (i.e. without the “Copy of” prefix)?
• If you reload the browser tab and open the TCS add-on, does the Simple Investment Calculator appear under the Manage tab (as an installed template) or only in the Explore tab (as an available template)?
• Could you try opening the Developer Console window (right click in the add-on then select Inspect or something) and then re-run the install process? I assume you will see a red error toward the end of the process, could you screenshot and DM that to me?

Thanks for your help on this. We’ll get it fixed.
Randy

[MarcC]

@randy Sent you a message with the log.

[diogo6]

Hi @randy

• Does the content of the “Copy of…” versions appear to be fully functioning?

Yes, seemingly as intended.   On a related note, I do think it should handle negative growth projections differently, even if just **gracefully** refusing to do so.  See here: https://i.imgur.com/ZNlpxDQ.png

• Do you have any sheets in your spreadsheet called “Simple Investment Calculator” (i.e. without the “Copy of” prefix)?

I do now since manually renaming the sheet to "Simple Investment Calculator".  I've also deleted the duplicate one.

• If you reload the browser tab and open the TCS add-on, does the Simple Investment Calculator appear under the Manage tab (as an installed template) or only in the Explore tab (as an available template)?

https://i.imgur.com/WNpBFPq.png

• Could you try opening the Developer Console window (right click in the add-on then select Inspect or something) and then re-run the install process? I assume you will see a red error toward the end of the process, could you screenshot and DM that to me?

Alright, I've just deleted my “Simple Investment Calculator” sheet and now I get the expected "Add to Spreadsheet" button.  DM'ing as requested.

[diogo6]

@morgan wondering how you feel about my likely-buried comment in the above reply to randy, regarding the handling of negative growth projections?

IMO, it should either:

1. show how long that runway would be if it were to keep going down by x% annually
2. NOT accept negative values, and gracefully warn the user.

I hope we do agree that a -3% annual growth is different than 2% annual growth.

Separately, I think the default add-on should allow for far more space than just 15 accounts. Those of us who manage family finances can easily blow past that limit. https://i.imgur.com/bXqU06H.png

Again, I love this add-on and think it’s 99% there!

[dtchura]

I am getting the same error. It does add a copy of the sheet, but it doesn’t show up in the console under manage even after renaming to get rid of the “copy of”. Thoughts on work arounds?

[diogo6]

thanks for the confirmation @dtchura but it seems @randy and team are aware and on it now. I’ll tell you what I’m doing:

1. Renamed it to just “Simple Investment Calculator” (as you have)
2. Use it as intended
3. Bookmarked and set this thread to “Watching” for when it’s fixed.
   
   

@randy and @morgan, you may want to consider un-featuring and removing the “NEW” flag from the solution as it seems this installation issue happens to all/most users.

[dtchura]

Thank you - will do re: renaming and watching.

[morgan]

Hi @diogo6 thanks for this feedback. I updated the Notes to reflect this limitation. The future value calculation we used won’t work with negative rates, only positive. We tried to keep this pretty simple but I’m interested to see how popular having both negative & positive rates would be. Maybe a more complex version of this could be built to accommodate…

[randy]

Thanks for the feedback everyone.

I still don’t have enough information to fix the install issue. I’ve tried installing with the add-on in using both my work and personal logins and I’ve had no issues. @diogo6 sent me a console log and screenshot of the “Service Spreadsheets failed…” message. I did not recognize the spreadsheet ID in the message. It definitely was NOT our template-master. Is it possible that the spreadsheet ID in the message is your personal (destination) spreadsheet?

• I took @diogo6’s advice and removed the New flag from the template until we resolve the install issue.
• When we discussed the template, @morgan and I hadn’t anticipated negative RoRs, @diogo6. That is interesting. We will chat about that.
• As for the suggestion about adding rows for extra accounts… I think that is pretty easy to implement. Try just inserting rows between row 13 and row 25 and then expanding down the formulas in B11 and H11:J11. Let me know if anything is amiss.

[yossiea]

I really like this, it’s simple and effective. A couple of quick suggestions, in column bd12, you might want to filter out the hidden accounts. You already have hide as a column reference. Also, what do the numbers on the graph represent, the contribution, the min/avg/max, etc. also, it might be good to have the chart displayed in candlestick formula, especially with the different return percentages available

[diogo6]

Yep yep, I’m pretty comfortable editing solutions to fit my needs (and have already done so). Just stating that not all may be as comfortable, but then again, maybe they’re not the ones with a ridiculous number of accounts to begin with haha

And yes, @randy, just PM’d you a response now… that long ID is definitely my spreadsheet ID (ie: the one you’d find as part of the URL)

[randy]

Thanks for the suggestions, @yossiea and @diogo6. Will sync with @morgan on them.

[morgan]

@morgan

The graph shows the average future value amounts for each of the accounts you’ve entered alongside a Total line.

@yossiea Thanks for checking it out! The graph shows the average future value amounts for each of the accounts you’ve entered alongside a Total line. The graph requires an entry in the “Avg RoR” for an account to be displayed.

I do like the look of the candlestick chart, I just went with this graph to track solely the average rate in case some users don’t know the RoR range or don’t want to enter more than one rate for every account. Wanted to keep it as simple as possible.

Good eye on the hidden account filter, we’ll update the template shortly with that fix.

[morgan]

@diogo6 @yossiea We’ve implemented some changes in the latest version 1.01 - filtered out hidden accounts, the RoR columns will only accept positive amounts now, and there are some more (hidden) account slots.

Thanks again for the great feedback!

[yossiea]

Looks good, but on my end, the formula stops at B14. After three accounts, you manually have to drag down the formula.

[morgan]

Thanks @yossiea fix is implemented in 1.02 which is live now.