Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

[retiringwhen]

Also you might have missed in it my update, but Vanguard USGO % Estimate seems to have some extra data at the bottom of VUSXX and I Wasn't sure if that was intentional? It's different than the other funds and different than 14.0 which has zero's on those cells (F42:F45 and K42:K45)

Yes, sorry that was my attempt to project to the end of the year to get a good idea of end of year percentages. I just straight lined August's numbers. My theory is that the Feds Reverse Repo jujitsu is winding down and Vanguard will be keeping mostly traditional Treasuries in their portfolio going forward. If they start lowering rates, there will be even less likelihood they'll keep the Repos around. This is an estimate/projection that has to have assumptions.

For the other two funds, their relative rate changes have been much narrower so I am just being conservative. I expect they'll keep a significant amount of Repos around since they greatly increase liquidity with minimal downside risk.

[mouth]

Also you might have missed in it my update, but Vanguard USGO % Estimate seems to have some extra data at the bottom of VUSXX and I Wasn't sure if that was intentional? It's different than the other funds and different than 14.0 which has zero's on those cells (F42:F45 and K42:K45)

Yes, sorry that was my attempt to project to the end of the year to get a good idea of end of year percentages. I just straight lined August's numbers. My theory is that the Feds Reverse Repo jujitsu is winding down and Vanguard will be keeping mostly traditional Treasuries in their portfolio going forward. If they start lowering rates, there will be even less likelihood they'll keep the Repos around. This is an estimate/projection that has to have assumptions.

For the other two funds, their relative rate changes have been much narrower so I am just being conservative. I expect they'll keep a significant amount of Repos around since they greatly increase liquidity with minimal downside risk.

Gotcha. Makes sense.

[billthecat]

Not sure what happened here.

Friday a.m. I received the email, which said:

** Fidelity - 9/7/2023 **

Best Today is FTEXX, Best Yesterday was FTEXX

After Tax Yields
FDRXX = 2.37%
FZFXX = 2.38%
FDLXX = 2.92%
FTEXX = 2.69%
SPAXX = 2.38%
SPRXX = 2.42%

FDLXX moved into first today, if it stays a second day, a swap will be recommended.

Report Date: Thu Sep 07 2023
Trigger Time: Fri Sep 08 2023 03:30:53 GMT-0400 (Eastern Daylight Time)

Then, today, Saturday, I received this:

(Subject: No Swap Triggered (Sat Sep 09 2023))

** Fidelity - 9/9/2023 **

Best Today is FDLXX, Best Yesterday was FDLXX

After Tax Yields
FDRXX = 2.37%
FZFXX = 2.38%
FDLXX = 2.92%
FTEXX = 2.68%
SPAXX = 2.38%
SPRXX = 2.42%

Report Date: Sat Sep 09 2023
Trigger Time: Sat Sep 09 2023 03:30:58 GMT-0400 (Eastern Daylight Time)

I would have expected today's report to have a report date of Friday Sep 8, and to indicate swap triggered. Somehow something seems to have been lost between Friday's email (Thursday report) and Saturday's email (which should have been the Friday report). And I would have expected Saturday's email (Friday report) to say "Best Today is FDLXX, Best Yesterday was FTEXX". I'm still learning how this works so what am I misunderstanding?

[retiringwhen]

Fidelity funds are reported daily, so this is the rate reported on Saturday. It is the same as Friday. Different than Vanguard.


As to Friday, I think there may have been an issue at fidelity.com as I did not get an email either, but I am traveling and not able to research just now.

Their site has had lots of overnight outages recently.

[scoothome]

Minor typos, feel free to ignore.
Instructions tab, cell C7:
"...complete set of Vanguard fund," -> "...complete set of Vanguard funds,"
"...from a customizable set of Fidelity funds..." -> "...for a customizable set of Fidelity funds..."
cell C9:
"...for you personal copy..." -> "...for your personal copy..."
cell C10:
"Details on each tab is provided" -> "Details on each tab are provided"
cell C167:
" Tax-Equivalent Yield (YTEY)" -> " Tax-Equivalent Yield (TEY)"

Vg BestNow tab, cell M33:
"...you state and funds," -> "...your state and funds,"

Optimize Study tab, cell D21:
"...used to analysis a back test..." -> "...used to analyze a backtest..."
cell D23:
"back test" -> "backtest" (to be consistent with usage on other tabs)

CurrentTreasuryYield tab, cell A64:
"Invetment" -> "Investment"

Vanguard USGO % Estimate tab, cell A1:
"This tab is use by me" -> "This tab is used by me" (or better yet, just say "This tab records the monthly USGO % reported by Vanguard during the current year to develop an estimate for the value that will be provided for the tax year.")

[retiringwhen]

Minor typos, feel free to ignore.

I can't ignore someone who finds all my hopeless typos and malapropisms! Updated.

I also added tax rates for the State of Michigan as provided by a reader. Thanks for all the input. I revised the versions to 14.3 for the Vanguard/Fidelity sheet and v1.1 for the All Funds sheet.

[retiringwhen]

I want to put out a word of warning to investors from California, Connecticut, and New York. This month the Vanguard funds VMFXX and VMRXX both show USGO% greater than 50%. Because the report based USGO% rely on only the most recent EDGAR report, the tool will incorrectly tag those funds as meeting the 50% threshold even though the fund failed the test in at least 1 quarter this year already.

This situation was the reason I added the manual overrides for the USGO% for VUSXX, VMFXX, VMRXX. My annualized estimates already keep the funds under the 50% threshold.

The following warning has been added to the Instructions page of both sheets. As noted, I have a working solution for the Vanguard funds, but all other funds have no solution at this time. If you are looking at Schwab, Fidelity, or other funds, please be cognizant of the potential mismatch to your tax situation.

Warning about EDGAR percentages for residents of CA, NY, and CT: Use of the EDGAR reports (for any fund) may result in the report applying state tax advantages that will not qualify. When using funds that have a marginal portion of USGO that fluctuates, due diligence is required by the investor as the Optimizer tool cannot do the full test necessary to validate the 50% threshold rule at this time.

Note, this caution also applies to other investors if the variation in the USGO% is great over the year. The current report may over or under-estimate the tax benefit of a specific fund.

I have been working on an approach to address this gap in the analysis, but it will take some coding and long-term data collection. In the mean-time keep your eyes open when analyzing money market funds that have mid-rage USGO% reported, especially Government Money Market funds.

[jamesbogle]

A note on code, security and permissions. This sheet uses Google Apps Script to implement much of the functionality. These scripts are the root cause of the need to approve permissions for execution. This is similar to the old Excel Macros problem and potentially a risk vector.

You do not have to give permissions to view the sheets, but some of the updates and the email feature will definitely not work.

If you are skeptical, all of the code is visible and documented for the reader. It is accessible via the App Script editor that you can reach via the menu item Extensions -> Apps Script

Besides the email function (in emailTrigger.gs), the code mostly does http requests for retrieving data from vanguard.com and treasury.gov. The rest of the code is just book keeping, caching and massaging of that data for use in the spread sheet.

I am game for peer review input on the code. Much of this started off a sheet shared by @KevinM a couple years ago, but I have massively customized the code and extended over time.The big thing I added is caching to improve performance on repeated use of the data. I also found a prototype of the Treasury access code somewhere on the web that was posted by a long dormant BH poster a couple years ago. All bugs are mine.

This is great work!

Is there way to grant only the email permissions?

On the grant screen it requests all of these:

Code: Select all

This will allow MM Optimizer v14 to:

- See, edit, create, and delete all your Google Sheets spreadsheets
- Connect to an external service
- Send email as you
- Allow this application to run when you are not present

All I see is an allow button.

Is there anyway to scope it to this spreadsheet only?

[retiringwhen]

Is there way to grant only the email permissions?

On the grant screen it requests all of these:

Code: Select all

This will allow MM Optimizer v14 to:

- See, edit, create, and delete all your Google Sheets spreadsheets
- Connect to an external service
- Send email as you
- Allow this application to run when you are not present

All I see is an allow button.

Is there anyway to scope it to this spreadsheet only?

To update any daily yields, the 'connect to external service' is required, I cannot disable that, it would essentially make the the sheet static.

'Allow the application to run where you are not present' is the daily email trigger. You could delete those triggers and the activation code, but there is no configurable way to do that just now. You could still run the emailTrigger manually as documented though.

Interestingly, I believe I can scope the see, edit, create and delete functions to just the current document. There is a directive I can add to the script code that is supposed to make that happen. I will make a new version and share with you for a test.

[retiringwhen]

I have just updated the Main sheet MM Optimizer v14.4 to limit Spreadsheet access to only the local document. That should address @jamesbogle's concern.

Thanks for the feedback, that is an excellent risk reduction configuration change. I did not know it was possible to change that setting until I was prodded to look for what was possible. It was easy too!

I have also updated All MM Funds Optimizer v1.3 as well.

[rgolds88]

A bit of a niche question/topic, but how would you advise Philadelphia residents to adjust this spreadsheet? In addition to the flat 3.07% income tax rate for Pennsylvania, Philadelphia residents also have to pay a 3.75% School Income Tax.

As expected, USGO is excluded from that tax, but additionally, per Section 206(b) of the SIT regulations, the following are also excluded:

Interest and dividends on savings deposits, savings certificates, Certificates of Deposit, checking accounts and Money Market accounts issued by a:

• Private Bank
• Building & Loan Association
• Savings & Loan Association
• Credit Union
• Savings Bank
• Bank, Bank & Trust Co., or Trust Company

In my interpretation, that would mean that at least the Compound TEY would need to be adjusted for an accurate after-tax yield comparison with a bank's published APY.

Up to now, I've kept my state marginal tax rate in My Parameters set to 6.82% (3.07% PA + 3.75% SIT) for the ATY and TEY. However, to compare with a bank's APY (Compound TEY), I change the state marginal tax rate to 3.07%.

Does that seem right? Any other suggested changes/corrections for Philadelphians?

Thanks!

[retiringwhen]

Interesting question, and after a bit of thinking, I haven't actually got a provable (to me) answer.

I suggest you create a new thread and enlist some help from folks like Kevin M who seems to understand the math better than me.

I will keep thinking about it and try to derive formulas that make sense.

[rgolds88]

Interesting question, and after a bit of thinking, I haven't actually got a provable (to me) answer.

I suggest you create a new thread and enlist some help from folks like Kevin M who seems to understand the math better than me.

I will keep thinking about it and try to derive formulas that make sense.

Thanks for the suggestion; done! I've created this thread: viewtopic.php?t=412977

[jamesbogle]

I have just updated the Main sheet MM Optimizer v14.4 to limit Spreadsheet access to only the local document. That should address @jamesbogle's concern.

Thanks for the feedback, that is an excellent risk reduction configuration change. I did not know it was possible to change that setting until I was prodded to look for what was possible. It was easy too!

I have also updated All MM Funds Optimizer v1.3 as well.

Thanks! This is much safer now.

Curious where is fintools? Not familiar with GAS. It looks like all wrapper calls to this library.

[retiringwhen]

Curious where is fintools? Not familiar with GAS. It looks like all wrapper calls to this library.

Thanks for the pointers to improve!

You can see the fintools library by following the link in the bottom section of the Instructions tab in the section fintools Apps Script API library.

There are essentially two libraries:
1.) the core functionality fintools API library
2.) a stub library for calling the those functions fintools Wrapper Library.

Clicking through those links will get you access to the code in a Google App Script editor and test environment. you will need to make a copy of the project to edit them as I share them read only as you would expect. Copies are made on the Project details (i) panel using the copy icon in the top right corner.

[retiringwhen]

Announcing Version 2 of the MM Optimizer All Funds Edition. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing

This is a new separate sheet from the original sheet. It follows the same basic model of a BestNow, Optimize and T-bills tabs with associated graphs, but instead of being limited to just the Vanguard funds, this sheet allows you to choose any fund ticker supported by the https://moneymarket.fun tool. See this thread for details on the tool.

New Features in release 2:

- Changed to add a presentation of the date of latest EDGAR report that sets USGO% values are reported.
- I also added a new entry for Bank Deposits on the BestNow tab. Simply enter a reference APY on the My Parameters tab, and its yield values will be shown on the BestNow tab.

I plan to maintain both this version and the original Vanguard focused sheets going forward.

As always, feedback, error reports, and feature requests are desired.

[retiringwhen]

Announcing Version 15 of the MM Optimizer Vanguard/Fidelity Edition. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing

Version 15 updates:
- Changed how the manual USGO% values are stored, reported and maintained. This makes the feature more transparent and allows end-users to more easily put their own values in the worksheets. The USGO% values can be entered by any user on the My Parameters tab.
- Changed to add a presentation of the date of latest EDGAR report that sets USGO% values are reported.
- Added a new entry for Bank Deposits on the * BestNow tabs. Simply enter a reference APY on the My Parameters tab, and its yield values will be shown on the Vg and Fidelity BestNow tabs.

Edit: Note, going forward with the release of the All Funds Edition, I will be calling this version the MM Optimizer Vanguard/Fidelity Edition.

[bbrock]

Thx retiringwhen!