Free Investment Tracking Spreadsheet

a freedom fighters my name is Rob burger in this video I'm going to walk through a free investment tracking spreadsheet it's extremely easy to use it'll help you track your investments and as you'll see it's a great tool when it comes to rebalancing your portfolio now if you like these kind of videos please subscribe below and let's get to it so here's this spreadsheet I actually first wrote about it I think back in 2014 on my site Doral or net personal finance site I found it at no seven actually sold it a couple of years ago it's still in good hands in fact if you if you google investment tracking spreadsheet I think this article is the first to appear in Google but you can find a copy of it here at dough roller net I'll also leave a link to it in the in the discussions below this video now it's only got two sheets the one you see here and what I call Holdings will be looking at both of them before you start though this is so important you see this note in red please do not request access to this spreadsheet instead to make a copy anyway all you do is go to file make a copy name it whatever you want to name it and you're good to go so what's the first step it's a simple spreadsheet but there's a lot of complexity underneath the hood so let's begin with the holdings page this is where you should start now that the data that I have in here is just demo data this is not my actual portfolio although I do own many of the mutual funds that are listed here but this is where you start and you want to enter your portfolio and you could include mutual funds ETFs stocks whatever you'd like now what I've done here as you can see is color-coded certain cells those are the cells where you need to enter information all of the other cells get calculated or filled in automatically so let me just show you how that works take this cell for example if we click on it you can see we use a function it's the Google finance function it references cell b3 which is the ticker symbol and then pulls in the name for that fund so when you enter a symbol for a mutual fund or ETF it should pull in the name of that fund automatically now for each fund we want to give it a category and as you can see from the drop down I have five categories you can change this you can be more specific you could for example have a category for REITs real estate investment trusts or you could have a category for emerging markets or for perhaps small caps or small cap value whatever you whatever you'd like and the way you do that is if you simply right click on this cell you'll see data validation and that's where you'll see a list of the drop-down items now if you want to change it for the entire column you could just highlight the whole thing do the same thing right click on it and we could add for example REITs and let's add emerging markets and then when we see the drop down you'll see that they get added for now for this demo we'll just keep the categories that we have then you enter the number of shares that you own for the particular investment price as you see gets calculated with the Google Finance function as well again using this ticker symbol value is just a simple mathematical evaluation of the shares in the price portfolio again is again just a simple mathematical calculation fund expense ratio again uses Google Finance and then the weighted expense ratio is just a simple formula so what you can do is actually just overwrite each of these rows with each of your investments you'll notice I organize them by account which means you could have the same investment in multiple times on the spreadsheet so we have in this example the Vanguard total stock market index is here and in a rollover our IRA but it's also here in a Roth IRA and it's even here in a 401k now you may have more investments and you may need to add more rows it's easy to do but there are a few things to keep in mind so let's actually add one now we'll call it let's just say you have a 403b and we'll throw some emerging market in that's VM a X and you'll notice nothing happens in the next cell so just go to the cell above it ctrl C to copy ctrl V to paste and what it's actually pasting in there is the Google Finance function that we looked at a minute ago but this time it's using this ticker symbol and so it brings in the name of that fund we could call it emerging markets since that's really what it is but for reasons you'll see in a minute I'm just gonna stick with the international stocks designation and we'll assume we'll assume we've owned a thousand shares and again here the price we can just copy from the cell above and it brings in the price the value we can copy from above same with the rest now this one's going to return an error I'll explain that in a minute 14 basis points is the fund expense ratio and then the weighted average will return an error as well so here's what's going on here you'll notice that it uses holding value and total value the problem is holding value as you can see is orange and it doesn't include the the row we just added so how do we add it to this holding value well if we double click here we can see it again and again we can see that it doesn't include that last row it's easy to add if we go to data and name ranges will see all the name ranges that are used in this spreadsheet we can find holding value here just click the pencil icon to edit it and we want to include row 16 when we do that our total goes up we can see that holding value now includes the last row and this now has a value represents 5% of the portfolio now for this one the issue is portfolio percent which is this column in purple again doesn't include the last row so we can come over here find portfolio percent click the pencil icon add the last row and it fills in the information so what I would recommend if you have more investments than the rows that are on the spreadsheet go ahead and add them all as I just did and then you can go in once you're done and change the value for holding value and portfolio percentage I should add and in fact we're gonna do just one more real quick this works just fine with individual stock so let's imagine you have a taxable account and you own shares of will say Citibank it should bring in the name of the company we can call that although it's an international company I would call it US stock since it's headquartered in the US let's assume you have we'll say 2,000 shares bring in the price which is again copy and pasting oops oh it doesn't have a fund expense ratio so for something that doesn't have a fund expense ratio or it's not loading correctly you can just put it in by hand this one obviously since it's an individual stock it doesn't have an expense ratio so we can just put in zero point zero zero and you'll notice this one here is in yellow because the Google Finance function was not pulling in the expense ratio for vti so I just looked it up and put it in by hand you can do that with any of these fields if for some reason the Google Finance function is not working and then here we see again our two value errors so we can come over as we did a minute ago change these to add the row 17 which we just added and now it all works so I'm going to delete these two if I can here we go so it's back to the original portfolio so here's the deal once you have all of your investments into the holdings sheet and you've adjusted the holding value and the portfolio percent we can then now go to the asset class tab and this is kind of where the magic happens there's a lot going on here and I want to walk through it for you what we've done is this is the target asset allocation that we're using in the spreadsheet and obviously what you'll want to do is put in your own target asset allocation if you add other categories like REITs or merging markets you'll want to simply add rows to add those here what we've got here Rick ferry and personal capital Rick ferry is a good friend of mine a financial advisor he's written some great books including all about asset allocation a book I highly recommend particularly if you're new to investing this was his recommended portfolio going back all five or six years ago he may have changed it since then same things personal capital again they may have changed what they're recommending but they take all of this with a grain of salt the best asset allocation for you is going to depend on your age your debt your investment goals how close you are to retirement so this was just to give folks a general idea the same is true with this target asset allocation I can tell you for example that this does not represent my current asset allocation the point isn't that any of these numbers are correct it's just to give you an idea of how the spreadsheet works what you'll want to do is put in your asset allocation in this column now we'll leave it where it is for now but I will show you some things about it so once you have that in here this is a calculated number we can open it up and see it's a calculated number in part based on all of the data we entered into the holdings spreadsheet and here's what it shows us it shows us our actual allocation again if we go back to the holdings tab all that's really doing is in this case picking up all of the investments that we've labeled is US stocks totaling them here and then simply doing the math based on the total value of our portfolio so what it says is for all of the investments that we've labeled US stocks on our holdings page they total three hundred and ninety thousand dollars and that represents about seventy one percent of our total total portfolio value now the next thing it does is it shows us the difference the difference between our actual portfolio value and what our target was so for example if we changed our target to 65 percent this number changes I'll put it back now and it does that for each asset class now you'll notice that some of these cells are red and one of them's white why is that well what this does is it takes the difference and it compares it to a threshold and if it's more than the threshold so if it's if the difference is greater than 5% in this case it turns the cell red just as a way to tell us hey this asset allocation it's it's because the market has changed its it's gone beyond our threshold in fact if we made this threshold 17% it would turn this white now 17% it's much higher than I would ever use I generally like the thresholds that are here sometimes I'll make them as you can see smaller if I'm dealing with an asset class that has a much lower allocation in my portfolio again though there's no right or wrong here your thresholds could be different and and understand to that in the spreadsheet the only significance of the threshold is that once an asset class exceeds that threshold it turns this cell red that's the only thing it does of course it doesn't rebalance your portfolio now that's up to you so how would we use this to actually rebalance our portfolio so to rebalance our portfolio I actually find it helpful to make a change to this spreadsheet I'm gonna do that now for you when you buy the time you see the link in the spreadsheet you will see this column when I'm going to build it right now and I just call the column rebalance I'm gonna copy the madding and this will just call amount and all I'm gonna do is multiply the sixteen point one nine percent times the total portfolio value so that I know how much of in this case US stock mutual funds I need to sell to bring the actual allocation back in line with the target so to do that we can just take our total portfolio times this difference and then we just need to make sure it's in dollars and there we go now we can copy this on down for each of them to do that let's make sure that this number always references the same cell so now when we cop ups there we go now when we copy it it should work there we are now if we actually totaled these they should equal zero and we see down here the sum is zero so the idea is if we took and sold out of our US stock mutual funds eighty eight thousand dollars and then we took fifty six of that and invested it in our international stock mutual fund 19 thousand and our alternatives in this case REITs 20 almost twenty three hundred into our bond fund if we wanted to rebalance this one it's not off by much but why not and then we could keep just about eleven thousand dollars of it in cash and it's really that simple I hope you find that investment tracking spreadsheet to be useful in your own investing journey and if you have any questions just leave leave them in the comments below I will be responding to them and remember you can get links to the free spreadsheet in the discussion below until next time remember the best thing money can buy is financial freedom

Channel: Rob Berger

Views: 12,884

Rating: 4.9504132 out of 5

Keywords: investing, investment tracking, spreadsheet

Id: l8vrmBIzICE

Length: 15min 3sec (903 seconds)

Published: Tue Mar 31 2020