Posted on January 2, 2023 by fritz@theretirementmanifesto.com
I just created a free new tool for you to kick off the New Year and today I’m sharing it with you, along with screenshots and detailed instructions on how to use the tool to manage your asset allocation.
If you’re like me, you’re diligent in conducting a Year-End Financial Review. (If not, you should, and I encourage you to check out A Step-By-Step Guide For Your Annual Financial Update). That review includes analyzing your Asset Allocation and determining appropriate rebalancing actions.
I encourage folks to use Personal Capital (affiliate link) for that task. I’ve used their automated asset allocation dashboard for years and find it helpful as a part of my annual review. I previously built this Guide To Asset Allocation spreadsheet for folks who use Personal Capital to calculate their Asset Allocation.
But what if you’d prefer to not use Personal Capital?
Today’s post is focused on a major change I just made to a popular free spreadsheet on my site, which will now allow you to calculate your asset allocation without the need for Personal Capital. Even better, the modifications will automatically link to your Net Worth figures and determine what rebalancing you need to do in your portfolio. I actually do the exact same process every year-end as a comparison to the Personal Capital results.
The change to the free spreadsheet came at the request of a reader. Pamela, I hope you’re pleased with the result.
Today, a free new tool I just developed to help you manage your asset allocation. Click To Tweet
PLEASE READ THIS FIRST: I get hundreds of emails asking for editing permission on my free spreadsheets. Please don’t do that. To use the spreadsheet I’m demonstrating below, you need to click “File,” and then “Make A Copy” before you can enter your personal data into the spreadsheet. Alternatively, you can click “File,” then “Download” then “Excel” to save it as an Excel spreadsheet. This protects the original template for other readers and ensures your data isn’t accidentally shared with thousands of other readers.
Introducing The New Tool
The free Net Worth template I created years ago has been downloaded by thousands of readers, but I just made a major improvement. Now, when you open that spreadsheet, you’ll see a new tab titled “Asset Allocation” that is linked to all of the figures in the net worth spreadsheet. When you update the net worth figures, you can click on the Asset Allocation tab to also determine your asset allocation. Finally, the revision also allows you to enter your targeted asset allocation and automatically determines rebalancing requirements to meet your target.
With that introduction, let’s walk through the steps:
Update Your Net Worth
The Net Worth spreadsheet opens to the Net Worth tab. After making a copy of the file for your personal use, simply fill in the net worth spreadsheet (screenshot example below), changing the rows as required to suit your investment holdings.
Enter Asset Allocation Data
After entering your net worth data, toggle over to the Asset Allocation tab and follow the directions. After updating links for any changes you made on the net worth page, enter the allocation for each investment owned. For example, if you have a money market fund, you’d enter 100% in the cash column. For mutual funds, you can check on your financial institution’s website (or Yahoo! Finance) to find the allocation for each fund. You may find it necessary to go back to your Net Worth tab to break holdings down into more granular detail (e.g., if you hold a bond and an equity fund in your IRA, you’ll want a row for each).
Review Asset Allocation Result
After entering your data, scroll to the right to see your personalized pie chart showing your current asset allocation:
Determine Your Targeted Asset Allocation
Scroll down to the next section in the spreadsheet and enter your targeted asset allocation. To read more about how to determine your targeted asset allocation, read A Simple Guide To Targeted Asset Allocation.
Compare Your Actual Vs. Target Asset Allocation
Scroll down a few lines to see the automatic comparison of your actual asset allocation vs. the target you’ve established:
Review Your Rebalancing Actions
Based on the results above, you can determine what action should be taken. Add personal notes to establish your action plan:
Conclusion
For those who prefer the manual approach overPersonalCapital (affiliate link) to manage your asset allocation, I trust you’ll find value in the modifications I made to the free Net Worth template. I appreciate Pamela for asking the question and hope you’ll all find value in the thinking.
Even if you have your own way of tracking your net worth, I believe there’s merit in adding the asset allocation analysis to your net worth spreadsheet. While it takes a bit of manual work to research the asset allocation for each investment you own, it’s a reliable way to know your actual asset allocation.
Comparing your actual asset allocation to your target helps define actions required to rebalance your investment portfolio to match your risk profile. It eliminates the emotion and helps to identify areas where you may have over/under exposure to asset classes.
I hope you find value in the spreadsheet. Apologies in advance that I won’t be able to answer everyone’s specific questions on formulas, etc., and trust you understand. I have faith you’ll be able to figure it out or modify the system you’re already using to incorporate a method of tracking your asset allocation that works for you.
Happy New Year – looking forward to all the new year has in store!
Fritz,
Thanks for sharing the new and improved version of your spreadsheet. We were just talking about reviewing our asset allocation mix of retirement funds yesterday so this is very timely. After 2022, it’s hard to look at the balances and not want to make changes to the asset allocation mix. Always good to have another tool in the tool shed but also enjoy conversations with others to bounce ideas and thoughts before making the change if any.
All the best in 2023!
BobBob, pleased to hear the timing worked out well for you. It’s definitely “that time of year,” so I thought it would be a good time to made the modifications to the Net Worth spreadsheet. As for making changes to your Asset Allocation, always good to have your strategy in place before the bear market hits to avoid making emotional decisions. Good luck working through your numbers.
Fritz, thanks for sharing your tools. I just read your overview so have not tapped into the spreadsheet yet. I think I know the answer to my question, but can the assets be broken up and visualized by type? Eg Large Value, Large Growth as opposed to just Stocks?
None the less, really appreciate your sharing here.
Thanks.
Dave, you can absolutely do that. I considered making it a more “granual” template, but decided to keep it simple for the masses. To add detail, simply add additional columns as you allocate asset classes by holding.
Perfect! It sounds like you can effectively emulate the Personal Capital Visualizer… but without the sales pitch! 🙂
Thanks! This is how I will spend my day today. i will start with the Step by step, however, I am retired and do not use twitter. Can you tell me the additional info for being retired (and single if t matters)
Wendy, there’s no additional info required. Net Worth and Asset Allocation are both areas that need to be monitored whether your working or retired, married or single.
Nice Fritz, thanks for sharing. I’ve used the same spreadsheet since 1997 and it’s pretty similar to this, although your organization skills seem a bit better than mine, haha. Happy new year!
Don’t let my template fool you – my personal spreadsheet isn’t nearly as well organized as the templates I make for The Retirement Manifesto! And, I’ve also been using my personal spreadsheet since the mid-90’s…
I use your spreadsheet every month when I roll our net worth and have for a few years. I think it’s a great layout and really highlighted how I needed to focus on where I was investing my 401(k) to balance out the allocation between tax deferred and after tax assets. The only addition I did to it was on the bottom of the net worth statement. I added a section for life insurance. It helps me to see the various policies we have though work, personal, etc. and make some adjustments. Keeping it all on one page helps me. Thanks for the updated spreadsheet and I look forward to using it.
Sarah, thanks for the kind words about my spreadsheet, I’m pleased to hear you’ve used it every month for a few years, made my day. I like your addition with life insurance, and agree it’s good to keep all of the important financial items on “one page”.
Thanks Fritz, this is fantastic. I know you put a lot of time into this, so thank you for sharing! Happy 2023!
Happy New Year, Jim. It’s always worth putting my time into something I think will be of value to the readers. I know you do the same. Thanks for stopping by!
Thank you SO much for doing this! It is better than personal capital because I can include amounts that are in “cash” accounts, like CD’s and hi yield money market accounts etc. Personal Capital and quicken exclude those categories from their asset allocation charts so the one you made will be more accurate. Thank you!
Pamela – the star of the show! Thanks for leaving your comment, I’m pleased to hear the spreadsheet met your needs. And…you’re welcomed!
FI – tip…
If your income is less than $539,900/Year or net worth is less than $12,920,000…
Learn to use Excel to manage your own personal finance.By sharing the spreadsheet, Fritz is demonstrating to all of us…
Manage your finance down to the very, VERY and VERY detail level is a necessary skill…
To bring on the journey toward Financial Independence.Good luck!
Very timely! I have my own Excel file that I’ve used for 15 years but since my layoff this year I thought to tweak my spreadsheet to make it look nicer and get more detail on my AA. But I’ll peruse your file and might just switch to your file going forward. We’ll see. I also wish to keep my brain active 😉 and maybe instead borrow your ideas to tweak my existing file.
I’m with Pamela. I visit PC website to annually to manually update my NW and check on AA, but other than that I just don’t feel comfortable giving it all my PW’s to our accounts. If hackers can break into the government sites and banks, I don’t doubt they can crack PC’s walls too if they choose so.Hmmm… Is there a best way to get Quicken data in there? Yeah I know, play with Quicken reports, then enter the data manually. Right now, their Net Worth reports works well. Also, Fidelity and Vanguard can show asset allocation pretty well but not as well as what I’m seeing here. BTW, Personal Capital does not handle international investments, and I did not want to leave my login information in their hands. But getting rid of their marketing people was very difficult, like sticky stuff on your hands.
Comments are closed.
