I wanted to preview a new RSU Dashboard I have been working on. I’ll have a detailed post later this week but wanted to give subscribers a first look and opportunity to share any feedback before it fully goes live. WIP Google Sheet Template: https://docs.google.com/spreadsheets/d/1flqIgtN1GXTHNFR7Ks4GTMSTZhg1sYZCMRs8d2-qBm0/edit?usp=sharing
I had an issue where current value of total unvested RSUs are #N/A due to "Function GOOGLEFINANCE parameter 1 value is invalid." - looking at hidden "New Calculations (DONT EDIT)" sheet, it seemed one of the refresher grant is missing Stock Ticker. This was fixed by just changing the order of problematic refresher entries in "Insert Data Here" sheet (i.e., I originally had New Hire Grant, 2020 refresher, 2021 refresher in order; 2020 refresher was the problem, and when I deleted them the issue went away; so I just added 2020 after 2021 and everything worked as normal)
Interesting. So in column B in "New Calculations (DONT EDIT)" there was a row missing the stock ticker? I wonder why that would be since the full column E of "Insert Data Here" should map to C11 where you enter your company stock ticker (so every row should have the stock ticker). I wonder if there was an errant paste that ended up overridding the ticker in column e (no way you would know since it is hidden?).
> So in column B in "New Calculations (DONT EDIT)" there was a row missing the stock ticker?
Correct. It's so weird, I can actually reproduce the issue if I move the 2020 one back after the new hire grant, where it was originally - and issue fixes itself if I just move those 2020 grants after the 2021.
> I wonder if there was an errant paste that ended up overridding the ticker in column e (no way you would know since it is hidden?).
Ohhhh interesting - when I "reproduced" the issue as mentioned above, and unhid column E, these tickers in column E were missing - so as you mentioned, I'd assume this is the user error introduced by some weird pasting job I did.
Thanks for responding! Not sure if it'd be a good solution, but perhaps unhiding column E might be worthwhile so that users know if they mess up in copying&pasting :)
TY for digging into it! I could probably move the column I use further away so it is harder to paste over or as you say un-hide it. My worry about un-hiding it is that people are even more likely to paste over since it looks like placeholder text.
It was fine... until it wasn't! I am hopeful that most FAANGs should outperform the market. But I am perfectly fine lowering volatility and diversifying into index funds, particularly while still employed.
I treat every new vest the same way I treat any cash bonus. I use the proceeds to buy index funds to match my desired asset allocation. I keep it simple and I am consistent. Regardless of whether Meta is $100 or $350.
I am not selling because I want to beat the market. If anything I am selling because I want to reduce volatility and more closely match the overall market. By nature of my employment I am long Meta. All my unvested shares continue to ensure I have significant skin in the game.
Reality: Yes, still selling all META shares. Have not been as consistent with my partner's Uber shares in part do to some wash sale annoyance (they vest monthly and have an espp). But most of that shouldn't come into play if I just sold everything. So I'll admit I am not being as consistent as I should be (perhaps justifying it due to the current prices although not logical since I am not doing the same with Meta).
One issue I am trying to figure out if shares that vest pre-IPO. Uber as an example. They IPO for $45 in May 2019. Employees had a lock up period that started 180 days after that on November 6th 2019 with an opening price of $25.58. Taxes are based on the $45 but for "diversification vs hold" it makes sense to use the date and price after the lock up period.
Based on testing from y'all I updated the original sheet to no longer hide column "e" in the "Insert Data" sheet where the stock ticker was populated. This was getting accidentally getting pasted over without people realizing.
There was also a bug in the "Annual RSU Value by Grant" chart where the pivot table populating it was filtering out dates. I removed any filters. (If you don't want to create a new copy of the google sheet you can unhide the "Calculations 3 (DONT EDIT)" and edit the pivot table in column "K" and just delete the entire filter section at the bottom.
Each company does this different so I didn't want to include any projection out of the box. If you know the formulas you can utilize those. Another strategy would be to estimate based on the value of the prior years RSUs (this doesn't work for some companies who use RSU refreshers to match a target comp).
1. For people like me who've followed your advice of selling each vest only starting X date (say in 2021) and have held onto previous vests (New hire etc.), is there an option to show the Hold vs. Sell vs. Partial Sell?
2. I am actually thinking if there is value to show Grant Price vs. Vest Price vs. Today's price & and the associated values. Meta grants a certain # of RSUs based on a $ as part of PSC. Some times by the vest happens, the Value goes up (good conditions till 2021) and sometimes it drops much below (2022). It may be a good reminder to user that you were given certain $ at grant. and by the time it vests, it became this multiple and by today its this value.
Just my 2 cents. (not fully fleshed out on #2 above)
For #1 I would lazily make 2 version of the sheet to get the numbers :)
For #2 I do think that would be valuable. This is why I originally included the "Grant Date". But grant date ends up not being very useful since it isn't used to calculate out the actual share value during the grant. I would need to ask people to hard code in the value.
For Meta employees it is in their PSC sheet; ie "The number of RSUs was determined by dividing your refresher value of $XXX,XXX USD by $228.51 which was the average closing price of Meta stock for the 7 trading days between February 3rd, 2022 and February 11th, 2022, inclusive." So pulling out that $228.51 and adding that to a row to run the calculations. What do you think?
For META employees the steps to easily get the info that the sheet needs:
1. Go to Schwab > Equity Awards > Equity Details https://client.schwab.com/app/accounts/equityawards/#/equityTodayView
2. Check “Show All Historical Awards”
3. Click “Export” in the top right to access the raw csv
It could just be my naivety, but a quick terms guide of some sort for noobs might be very helpful!
Yes! Once everything is finalized i'll make a longer post going into detail and walking through how to update each section.
Thanks for this, it's a great start!
I had an issue where current value of total unvested RSUs are #N/A due to "Function GOOGLEFINANCE parameter 1 value is invalid." - looking at hidden "New Calculations (DONT EDIT)" sheet, it seemed one of the refresher grant is missing Stock Ticker. This was fixed by just changing the order of problematic refresher entries in "Insert Data Here" sheet (i.e., I originally had New Hire Grant, 2020 refresher, 2021 refresher in order; 2020 refresher was the problem, and when I deleted them the issue went away; so I just added 2020 after 2021 and everything worked as normal)
Not sure what the issue was but just flagging!
Interesting. So in column B in "New Calculations (DONT EDIT)" there was a row missing the stock ticker? I wonder why that would be since the full column E of "Insert Data Here" should map to C11 where you enter your company stock ticker (so every row should have the stock ticker). I wonder if there was an errant paste that ended up overridding the ticker in column e (no way you would know since it is hidden?).
> So in column B in "New Calculations (DONT EDIT)" there was a row missing the stock ticker?
Correct. It's so weird, I can actually reproduce the issue if I move the 2020 one back after the new hire grant, where it was originally - and issue fixes itself if I just move those 2020 grants after the 2021.
> I wonder if there was an errant paste that ended up overridding the ticker in column e (no way you would know since it is hidden?).
Ohhhh interesting - when I "reproduced" the issue as mentioned above, and unhid column E, these tickers in column E were missing - so as you mentioned, I'd assume this is the user error introduced by some weird pasting job I did.
Thanks for responding! Not sure if it'd be a good solution, but perhaps unhiding column E might be worthwhile so that users know if they mess up in copying&pasting :)
TY for digging into it! I could probably move the column I use further away so it is harder to paste over or as you say un-hide it. My worry about un-hiding it is that people are even more likely to paste over since it looks like placeholder text.
I like it. Although it's a painful reminder of how much I leaned into diamond hands mindset :).
It was fine... until it wasn't! I am hopeful that most FAANGs should outperform the market. But I am perfectly fine lowering volatility and diversifying into index funds, particularly while still employed.
Are you continuing to fully diversify even at these prices? Is there a price that will make you reconsider and hold the RSUs instead?
My pocket answer is:
I treat every new vest the same way I treat any cash bonus. I use the proceeds to buy index funds to match my desired asset allocation. I keep it simple and I am consistent. Regardless of whether Meta is $100 or $350.
I am not selling because I want to beat the market. If anything I am selling because I want to reduce volatility and more closely match the overall market. By nature of my employment I am long Meta. All my unvested shares continue to ensure I have significant skin in the game.
Reality: Yes, still selling all META shares. Have not been as consistent with my partner's Uber shares in part do to some wash sale annoyance (they vest monthly and have an espp). But most of that shouldn't come into play if I just sold everything. So I'll admit I am not being as consistent as I should be (perhaps justifying it due to the current prices although not logical since I am not doing the same with Meta).
One issue I am trying to figure out if shares that vest pre-IPO. Uber as an example. They IPO for $45 in May 2019. Employees had a lock up period that started 180 days after that on November 6th 2019 with an opening price of $25.58. Taxes are based on the $45 but for "diversification vs hold" it makes sense to use the date and price after the lock up period.
Tricky.
Based on feedback I duplicated the two dashboards for now with the ability to enter a single "tax %". Might try to see if there is a way to combine the visuals into a single version. You can copy the updated version here: https://docs.google.com/spreadsheets/d/1A13a88nEgoEk6bDYj9NEsjIeDilbLH1BsqZ8Ci0mlOo/edit?usp=sharing
Really good feedback and bug catching!
Based on testing from y'all I updated the original sheet to no longer hide column "e" in the "Insert Data" sheet where the stock ticker was populated. This was getting accidentally getting pasted over without people realizing.
There was also a bug in the "Annual RSU Value by Grant" chart where the pivot table populating it was filtering out dates. I removed any filters. (If you don't want to create a new copy of the google sheet you can unhide the "Calculations 3 (DONT EDIT)" and edit the pivot table in column "K" and just delete the entire filter section at the bottom.
This is great work Andre. How did you come up with 2023 Projected Refresher? I mean did you had a formula based on rating?
Each company does this different so I didn't want to include any projection out of the box. If you know the formulas you can utilize those. Another strategy would be to estimate based on the value of the prior years RSUs (this doesn't work for some companies who use RSU refreshers to match a target comp).
1. For people like me who've followed your advice of selling each vest only starting X date (say in 2021) and have held onto previous vests (New hire etc.), is there an option to show the Hold vs. Sell vs. Partial Sell?
2. I am actually thinking if there is value to show Grant Price vs. Vest Price vs. Today's price & and the associated values. Meta grants a certain # of RSUs based on a $ as part of PSC. Some times by the vest happens, the Value goes up (good conditions till 2021) and sometimes it drops much below (2022). It may be a good reminder to user that you were given certain $ at grant. and by the time it vests, it became this multiple and by today its this value.
Just my 2 cents. (not fully fleshed out on #2 above)
For #1 I would lazily make 2 version of the sheet to get the numbers :)
For #2 I do think that would be valuable. This is why I originally included the "Grant Date". But grant date ends up not being very useful since it isn't used to calculate out the actual share value during the grant. I would need to ask people to hard code in the value.
For Meta employees it is in their PSC sheet; ie "The number of RSUs was determined by dividing your refresher value of $XXX,XXX USD by $228.51 which was the average closing price of Meta stock for the 7 trading days between February 3rd, 2022 and February 11th, 2022, inclusive." So pulling out that $228.51 and adding that to a row to run the calculations. What do you think?
Thank you for putting this together, worked fine for me.
I also support the partial sell request from earlier, My desired allocation involves keeping x$ in META.
I added my vest details and it works great!
I've always sold my RSUs like you have, and it's vindicating to see my total value of diversification has never gone below the total value of holding!
This is great Andre! Thank you very much.
For some reason, not all categories of "Vest description" are showing up for me in the "Annual RSU Value by Grant" chart.
Only 2 out of 5 categories are showing up. I edited the description for the ones showing up to remove spaces etc but didn't work.
Interesting. Might send you an email to get an example to debug.
Sure!
Can you email a copy of your doc with the issue to andre@avocadofire.com (feel free to randomize the numbers as long as the issue reproduces).
This is fixed now. Thanks Andre!
+1 I only see one of the 4 grants I have.
I think Vikram helped me solve it. Try creating a new copy. There were dates being filtered out in one of the pivot tables.
Can you email a copy of your doc with the issue to andre@avocadofire.com (feel free to randomize the numbers as long as the issue reproduces).