Introducing YAAASS!

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Introducing YAAASS!

Post by SimpleStupid »

I've received so much value from this community in the past few months, the least I could do was (attempt) to give a little something back. So I created Yet Another Asset Allocation Spread Sheet, aka YAAASS! The goal is to provide a unified tool for folks to visualize and execute their asset allocation, asset location, and rebalancing strategies, with relative ease.

Please play around with it, and let me know if you find any issues, or areas for improvement.

There's a Read Me tab which explains everything in detail, but for those who prefer to jump right in, be aware that 1) only green cells are meant for editing and 2) you'll need to make an editable copy of your own by going to File > Make a Copy.

Thanks for everything, and enjoy!

SS
Last edited by SimpleStupid on Sat Nov 20, 2021 3:35 pm, edited 1 time in total.
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Re: Introducing YAAASS!

Post by SimpleStupid »

Fixed a couple bugs. Links above updated.
User avatar
LadyGeek
Site Admin
Posts: 95686
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Introducing YAAASS!

Post by LadyGeek »

This thread is now in the Investing - Theory, News & General forum (spreadsheet).

(This thread was temporarily removed for moderator review.)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Re: Introducing YAAASS!

Post by SimpleStupid »

LadyGeek wrote: Sun Nov 21, 2021 6:41 am This thread is now in the Investing - Theory, News & General forum (spreadsheet).

(This thread was temporarily removed for moderator review.)
Not sure if a link to this would be useful in the tools section of the wiki...but...for consideration.
User avatar
LadyGeek
Site Admin
Posts: 95686
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Introducing YAAASS!

Post by LadyGeek »

Sorry for the delay. Actually, it would be a good entry for the Using a spreadsheet to maintain a portfolio page.

It might be helpful for Rebalancing, but there's no easy way to use the formulas to help rebalance your portfolio. Compare to the spreadsheets listed at the bottom of the page.

Before it goes in the wiki, there are a few things to work on.

Administrative -
- Change the tab names to be sentence case, not all caps. Spell out abbreviations.
- "AA & ACCOUNTS" to "Asset Allocation & Accounts", "ASSETS" to "Assets", "ACCOUNTS BY CLASS" to "Accounts by Class", "READ ME" to "Read Me"
- Move the "Read Me" Tab to be first (left-side). It's the first thing users look for.
- In "Asset Allocation & Accounts", change the title in C1 from "ASSET ALLOCATION" to "Rebalancing". Indicate somewhere that negative is Sell, positive is Buy.

Expense ratio calculation:
There is an error in your average expense ratio calculation. In ASSETS!I38, the formula is =AVERAGE(I2:I36).

Expense ratio is a weighted average, meaning that you need to look at (total expense / total dollars). The formula instead should be =sum(J2:J36)/sum(F2:F36). You'll see the difference if you make one of the funds have a much higher expense ratio and dollar value. For example: change I1 to 0.100% and F1 to $100,000.

Background info: How to Calculate the Weighted Expense Ratio of a Portfolio

Read Me:
I don't know how you entered the documentation, but I can't click on any of the links. I can edit some of them, but that's not helpful. The link near the bottom titled "read this" does not appear to be a link.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
pascalwager
Posts: 2327
Joined: Mon Oct 31, 2011 8:36 pm

Re: Introducing YAAASS!

Post by pascalwager »

You seem to be manually entering fund Amounts from the investment companies and then calculating Shares on the ASSETS sheet (using downloaded Prices). I do the opposite so my sheet is accurate in real-time, at least for ETFs, and I seldom need to update the Shares. So do you enter new Amounts, say, once a month. Seems very tedious, but you may have a reason that just hasn't occurred to me.

But I'm picking up new ideas and getting exposed to some new sheet functions and might revise my own sheet, the product of a decade of evolution.

Thanks for sharing.
User avatar
celia
Posts: 16774
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Re: Introducing YAAASS!

Post by celia »

It’s not clear to me how you designate an account as being Taxable, Tax-Deferred, Roth, or something else (HSA or 529). I’ve seen people who post with the same AA in each account, which is not tax-efficient. Being able to demonstrate the same holdings but held in different tax categoies, could be useful.
A dollar in Roth is worth more than a dollar in a taxable account. A dollar in taxable is worth more than a dollar in a tax-deferred account.
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Re: Introducing YAAASS!

Post by SimpleStupid »

pascalwager wrote: Tue Nov 23, 2021 9:51 pm You seem to be manually entering fund Amounts from the investment companies and then calculating Shares on the ASSETS sheet (using downloaded Prices). I do the opposite so my sheet is accurate in real-time, at least for ETFs, and I seldom need to update the Shares. So do you enter new Amounts, say, once a month. Seems very tedious, but you may have a reason that just hasn't occurred to me.
Thanks for the feedback. I had originally done it the way you're suggesting but then realized that after reinvesting dividends the number of shares could change. So I went with amounts, knowing that bogleheads would seldom need to use the sheet, as it's not meant as a portfolio tracker, but more of a setup and rebalancing tool. Does that make sense?
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Re: Introducing YAAASS!

Post by SimpleStupid »

celia wrote: Tue Nov 23, 2021 10:03 pm It’s not clear to me how you designate an account as being Taxable, Tax-Deferred, Roth, or something else (HSA or 529). I’ve seen people who post with the same AA in each account, which is not tax-efficient. Being able to demonstrate the same holdings but held in different tax categoies, could be useful.
Thanks for the feedback. I had tried to show the asset classes by account in the pie chart tab, figuring people would know by looking at the account names whether it's taxable or advantaged. Then they can always look at the Assets tab for specific assets, by account. Let me know if you're looking for something else though.
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Re: Introducing YAAASS!

Post by SimpleStupid »

Thank you for the great suggestions. See comments below.
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm Sorry for the delay. Actually, it would be a good entry for the Using a spreadsheet to maintain a portfolio page.
Great.
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm It might be helpful for Rebalancing, but there's no easy way to use the formulas to help rebalance your portfolio. Compare to the spreadsheets listed at the bottom of the page.
It seems that the main update needed to my sheet would be rebalancing at the specific asset level?
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm - Change the tab names to be sentence case, not all caps. Spell out abbreviations.
Done
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm - "AA & ACCOUNTS" to "Asset Allocation & Accounts", "ASSETS" to "Assets", "ACCOUNTS BY CLASS" to "Accounts by Class", "READ ME" to "Read Me"
Done
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm - Move the "Read Me" Tab to be first (left-side). It's the first thing users look for.
I added a link on the first tab instead (like Hoppy's sheet) so that already-familiar users could cut to the chase.
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm - In "Asset Allocation & Accounts", change the title in C1 from "ASSET ALLOCATION" to "Rebalancing". Indicate somewhere that negative is Sell, positive is Buy.
Done
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm Expense ratio calculation:
There is an error in your average expense ratio calculation. In ASSETS!I38, the formula is =AVERAGE(I2:I36).

Expense ratio is a weighted average, meaning that you need to look at (total expense / total dollars). The formula instead should be =sum(J2:J36)/sum(F2:F36). You'll see the difference if you make one of the funds have a much higher expense ratio and dollar value. For example: change I1 to 0.100% and F1 to $100,000.

Background info: How to Calculate the Weighted Expense Ratio of a Portfolio
Excellent. Done.
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm Read Me:
I don't know how you entered the documentation, but I can't click on any of the links. I can edit some of them, but that's not helpful. The link near the bottom titled "read this" does not appear to be a link.
I added to the Read Me that users have to double click into the instructions in order to click the links. And i fixed that "read this" link.
User avatar
LadyGeek
Site Admin
Posts: 95686
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Introducing YAAASS!

Post by LadyGeek »

SimpleStupid wrote: Fri Nov 26, 2021 7:57 am
LadyGeek wrote: Tue Nov 23, 2021 6:28 pm It might be helpful for Rebalancing, but there's no easy way to use the formulas to help rebalance your portfolio. Compare to the spreadsheets listed at the bottom of the page.
It seems that the main update needed to my sheet would be rebalancing at the specific asset level?
In Rebalancing (Spreadsheets) I was thinking of my rebalancing spreadsheet that shows how to do this by fund.

Direct link: Rebalancing spreadsheet It's the "Rebalance by Desired Funds to Transfer" section. I wrote that spreadsheet because Vanguard doesn't support rebalancing on their website. I can't rebalance my IRA, for example. Instead, I use the spreadsheet and execute the transactions manually.

When I was working, it was a simple matter to login to my company account and rebalance my 401(k) to the nearest 5% allocation.

(This is a long-standing problem from several years ago. You could call Vanguard and get it done, but that's not the point. FYI - You can't rebalance on Fidelity's website, either.)

Each time I rebalance, I customize the spreadsheet to add rows and columns as needed. Looking at this again, perhaps it would be too difficult to do this for the general case (one-to-many, many-to-one fund transfers).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
pascalwager
Posts: 2327
Joined: Mon Oct 31, 2011 8:36 pm

Re: Introducing YAAASS!

Post by pascalwager »

SimpleStupid wrote: Fri Nov 26, 2021 6:15 am
pascalwager wrote: Tue Nov 23, 2021 9:51 pm You seem to be manually entering fund Amounts from the investment companies and then calculating Shares on the ASSETS sheet (using downloaded Prices). I do the opposite so my sheet is accurate in real-time, at least for ETFs, and I seldom need to update the Shares. So do you enter new Amounts, say, once a month. Seems very tedious, but you may have a reason that just hasn't occurred to me.
Thanks for the feedback. I had originally done it the way you're suggesting but then realized that after reinvesting dividends the number of shares could change. So I went with amounts, knowing that bogleheads would seldom need to use the sheet, as it's not meant as a portfolio tracker, but more of a setup and rebalancing tool. Does that make sense?
No, it doesn't make sense to me, because it's permanently labor intensive. You don't really know whether you even need to rebalance until you make the effort to manually enter the Amounts.

I want to be able to look at my sheet at literally any time and quickly know everything, including fund values, various percentages, % deviation from desired allocations, and rebalancing trade amounts. My sheet updates continuously for ETFs during market hours, and at around 11pm for mutual funds. I don't do contrarian (conventional) rebalancing, but follow the BH wiki real-time William F. Sharpe World Bond/Stock Portfolio Four-Fund Proxy allocations, so these various market values also download into my sheet via linking with the wiki spreadsheet.

I do need to occasionally confirm share quantities: end-of-quarter, monthly for some bond funds, after transactions. I also need to enter the fund value for three insurance company funds without tickers. Otherwise, I consider my sheet fully-automated and virtually ready-to-trade at all times.

My sheet also calculates account values to allow easy accuracy-checking against investment company Amounts before completing trades, etc.
Topic Author
SimpleStupid
Posts: 64
Joined: Wed May 05, 2021 6:38 am

Re: Introducing YAAASS!

Post by SimpleStupid »

pascalwager wrote: Fri Nov 26, 2021 2:52 pm
No, it doesn't make sense to me, because it's permanently labor intensive. You don't really know whether you even need to rebalance until you make the effort to manually enter the Amounts.

I want to be able to look at my sheet at literally any time and quickly know everything, including fund values, various percentages, % deviation from desired allocations, and rebalancing trade amounts. My sheet updates continuously for ETFs during market hours, and at around 11pm for mutual funds. I don't do contrarian (conventional) rebalancing, but follow the BH wiki real-time William F. Sharpe World Bond/Stock Portfolio Four-Fund Proxy allocations, so these various market values also download into my sheet via linking with the wiki spreadsheet.

I do need to occasionally confirm share quantities: end-of-quarter, monthly for some bond funds, after transactions. I also need to enter the fund value for three insurance company funds without tickers. Otherwise, I consider my sheet fully-automated and virtually ready-to-trade at all times.

My sheet also calculates account values to allow easy accuracy-checking against investment company Amounts before completing trades, etc.
After writing to you earlier, I went back and revisited the idea and agree with this approach. I have to change some fundamental things about how my sheet currently works, as it requires allocating $ amounts to each asset manually, rather than using percentages to automate the asset's target value. If I can automate the whole thing, then your approach can work. Thank you.
Post Reply