The Permanent Portfolio allocation is 25% stocks, 25% bonds, 25% gold and 25% cash. In this series of posts we’re going to talk about how to implement each one of these components to take advantage of the economic cycles of Prosperity, Inflation, Recession and Deflation.
This FAQ is divided into two sections: Short Answers and Long Expanded Answers. If you don’t want to know the details then just read the Short section and skip the Long Expanded section. This page will be updated from time to time as more common questions and answers are needed.
We begin this series with discussing the 25% stock allocation and Prosperity.
Why own stocks for Prosperity?
Stocks are the #1 asset to have during times of prosperity. During these times the economy is sound and growing. Inflation is under control (less than 5% per annum) and not causing any rapidly rising prices. Market interest rates will be stable and perhaps slightly falling. People are happy.
1) Move your IRA to someplace like Vanguard that has index funds. 2) Ask your retirement plan administrator to make index funds available either by requesting them from the 401(k) custodian or if necessary moving the company 401(k) plan to a new custodian that does offer them. 3) Use the funds that you have to the best of your ability. NEWSLETTER Sign up for all the latest and greatest news in playing cards.
In prosperity it is not uncommon to see stock prices rise sharply as companies grow, expand and produce profits for investors. Annual stock returns (price increase + dividends) could be in the +-10% historic range. Times are good, employment is stable, people are spending money on products and services. Stocks are going to perform well.
What kind of stocks should I own in the Permanent Portfolio?
Stockfolio is the best stock and cryptocurrency investment app for mac that allows you to research and track interesting stocks. It is extremely easy to set up and the best way to manage and follow your investment portfolio. Stockfolio – Stocks, Real-Time Stock Portfolior 1.5. Stockfolio is an investment app for macOS that will help improve your investment returns by allowing you to research and track interesting stocks and cryptocurrencies. It is extremely easy to set up and the best way to manage and follow your investment portfolio.
You should own a broad-based stock index fund that captures the maximum returns offered by the stock market without trying to beat the market with speculative stock bets.
That means you want to use a stock index fund and only a stock index fund.
Why is an index fund the best choice for the Permanent Portfolio?
Because index funds are the average of the entire stock market. It represents the average returns an investor could expect that year without trying to beat the market. Over time it has been shown that index funds will beat almost all actively managed funds.
What stock index fund should I use?
There are many index funds available today. Some are good, some are mediocre and some are downright bad. You want to own the cheapest and most broadly-based stock fund available. That leaves two main choices:
Stockfolio 1 52 Inch
- Standard & Poors (S&P) 500 Index
- Total Stock Market Index
What index fund should I buy?
Here are the basic considerations:
- It should track the S&P 500 or Total Stock Market Index
- It should have an expense ratio below 0.50% a year
- It should be a passive index with no active management of the fund
- It should be from a well-established company with a track record for index investing
- It must be 100% invested in stocks at all times no matter what.
Here are some options that fit the above criteria:
S&P 500 Index
Total Stock Market Index
This list is far from complete. If you are at a brokerage or mutual fund company that offers their own index fund then you can use that as long as it meets the listed criteria.
* See question below about difference between an Exchange Traded Fund (ETF) and a Mutual Fund.
Should I use the S&P 500 or Total Stock Market index if I have the choice?
I prefer the Total Stock Market for the wider diversification and tax efficiency. Harry Browne suggested using the S&P 500. It doesn’t matter too much for performance except for some small amount.
Can I use an actively traded fund for the Permanent Portfolio instead of a broad based index fund?
No you can’t. You don’t want a fund manager making decisions to move between stocks, bonds, cash, commodities, etc. and disturbing the strategy. You must use a fund that is 100% invested in stocks at all times for the stock portion of the portfolio. The Permanent Portfolio holds assets that will protect you from big stock losses already. You don’t need a manager trying to out-guess the markets.
This rule is not flexible. Do not break it.
My retirement plan doesn’t offer any index funds. What can I do?
This is an unfortunate problem for many workers. Index funds are not as profitable for mutual fund companies who like making big fees on actively managed funds. As a result, many 401(k) and IRA plans don’t offer index funds.
In this case you have few choices:
1) Move your IRA to someplace like Vanguard that has index funds.
2) Ask your retirement plan administrator to make index funds available either by requesting them from the 401(k) custodian or if necessary moving the company 401(k) plan to a new custodian that does offer them.
Sqlpro studio 1 0 452 0. 3) Use the funds that you have to the best of your ability.
If you are forced into option (3) (and many are), then try to look for the following in your funds that you do have:
- Lowest expense ratio possible.
- Should track the broadest and largest stocks in the US market.
- Should be 100% in stocks at all times and not be moving in and out of assets like bonds.
- Should have low portfolio turnover.
What about owning International stocks and what index should I use?
International exposure is not so important to US investors. The US Economy and companies are already all over the world. There is also currency risk with international stock investing which can hurt performance in some cases. However, if you wanted to own some international exposure then perhaps 5% or so of the portfolio is fine (so 20% US index and 5% Intl. Index).
Again the international index should be cheap and broad-based. The EAFE international index, FTSE international ex-US index or what is sometimes called a Total International Index are good choices:
Harry Browne did not openly advocate international diversification in the Permanent Portfolio, but his associates have said a little is OK. Also, some international index funds charge slightly more because of the added expense of trading on foreign stock exchanges. Therefore, international stock index funds are more expensive but should not exceed 0.75% in expense ratio.
What’s the recap?
- Only buy index funds for the stock portion of the portfolio.
- The index fund should be either Total Stock Market or the S&P 500.
- The index fund expense ratio should be less than 0.50% a year (unless it is an international index and can be up to 0.75% a year).
- Never purchase an actively traded stock fund unless you have absolutely no other choice available to you.
- Do not try to beat the market with the funds. Your market protection is already built into the other asset classes you own. The stock asset class serves a specific purpose and you don’t want to tamper with it by trying to outguess the market.
- If you want to own some international exposure you should limit it to about 5% allocation (5% Intl + 20% Domestic = 25% total in stock)
What is a stock index fund?
An index fund is a way of passively tracking a pre-defined basket of stocks. Index funds typically own several hundred to several thousand company stocks. These stocks are usually owned in proportion to the size of the company in the market. For instance an index fund owning the US Stock market will own a much larger number of shares of General Electric (a huge multi-national company) compared to small regional publicly traded company like a power utility.
The advantage of this approach is the index doesn’t need to engage in expensive activities associated with actively traded investment funds (such as research, analysts, advisors, etc.). Because the stock index fund owns the entire market it is expected to earn the average performance of the market in any one year minus their small management fees to maintain the fund.
What is the S&P 500 Index?
The S&P 500 is an index of stocks compiled by Standard and Poor’s that comprise the 500 largest and most liquid publicly traded companies in the United States by market capitalization. These are the companies you rely on every day of your life for just about everything you do. General Electric. Wal-Mart, 3M, Microsoft, Johnson and Johnson, Google, Coca-Cola, IBM, Home Depot, McDonald’s, etc.
You can see the entire current list here:
The S&P 500 represents around 70% of the total value of the US Stock market.
What is the Total Stock Market Index?
The Total Stock Market (TSM) index includes all the companies of the S&P 500, but also includes all the other publicly traded companies that aren’t quite big enough to make it into the largest 500 list. These are called “mid-cap” and “small-cap” companies which means they have a market capitalization (size) that is smaller than the biggest (which are called “large-cap”).
The TSM index is commonly called other names such as the Wilshire 5000 or Russell 3000. A TSM index commonly holds thousands of companies (3000-7000+) in the composition as opposed to the 500 of the S&P 500. The TSM index easily covers 98%+ of the entire US publicly traded stock market. This is why it’s called the “Total Stock Market” Index. It owns just about everything except tiny low-volume stocks or penny stocks.
Why should I only use index funds for the Permanent Portfolio?
Indexing is the best and most efficient way to invest in stocks. Not only does it guarantee you maximum possible returns because you own all the companies all the time, but it’s also cheap. A typical index fund may have an expense ratio of less than 0.20% per year. This means for every $10,000 you have invested in the index the fund management company is going to take just $20 for handling all the operations.
Compare this to a non-index fund which can charge 1%, 2% or more each year. It doesn’t sound like much, but for each $10,000 invested you’re paying $100, $200 or more every year to the managers. Over the years it starts to really add up and hurt performance. It’s like driving a car dragging an anchor behind it where the index is like driving that car dragging just an empty soda can.
Not only this, but most actively managed funds don’t beat the index fund over time. So you are paying more in management fees and you’re paying this extra cost for them to underperform the market. Yes, it’s true.
Didn’t Harry Browne recommend owning the S&P 500 Index in his book Fail-Safe Investing and radio show? Why use the Total Stock Market Index instead?
It doesn’t matter that much except maybe for taxable investors. The Vanguard S&P 500 index has been available for 30 years and was the world’s first commercially successful index fund. It has a stellar track record.
In the early 1990’s though the Total Stock Market came on the scene. It held a much larger number of stocks and should have slightly better tax efficiency. The overall performance between the two is largely identical depending from year to year. Over the past several years, companies like Vanguard (the pioneer in index funds and world’s largest fund company) are recommending to their customers to use the Total Stock Market fund over the S&P 500 fund.
The reason why I personally prefer the TSM over the S&P 500 is the wider diversification and tax friendliness.The S&P 500 index holds 500 stocks and sometimes when a company shrinks it is removed from the index and replaced with a new one. This can generate unnecessary selling for the fund and those costs are passed onto the fund holders. If you are holding the fund in a taxable account this means you could incur capital gains taxes that you didn’t expect.
Because the TSM owns almost all publicly traded stocks at all times it tends to generate less capital gains vs. the S&P 500 because it isn’t required to sell a company when it gets too small and buy more of another when it gets too big. If a company gets too small in the TSM it is probably bankrupt and will be delisted. This is not a taxable event. Likewise if a company gets too big there is no need for the index to sell another company to make room for the new leader. The fund simply buys more of the new leader’s stock.
With the above said, we’re dancing on the head of a pin. Both types of index funds are quite tax-efficient and offer excellent performance. I simply prefer the TSM fund because I like the wider diversification and want to get any edge on tax efficiency I can. If you want to use the S&P 500 index, or have no other choice, then that is still an excellent decision and you will be in great shape for the Permanent Portfolio strategy. Don’t sweat it.
Shouldn’t I have a fund manager making stock decisions using their wisdom and insight?
NO! First of all the Permanent Portfolio has fixed allocation to stocks, bonds, cash and gold. You don’t want to own a fund for your stock portion and have that manager suddenly decide they don’t want to own stocks and go 100% cash or bonds. They can throw a real monkey wrench into the strategy if you have not only the markets moving around but now you have some fund manager trying to outguess what is going to happen next.
The Permanent Portfolio strategy has assets that will cover you if the markets are doing poorly or doing well. You don’t need a fund manager making decisions that could hurt performance.
But aren’t fund managers smarter than me at beating the market?
You aren’t trying to beat the market. Also, here’s a secret: Fund managers are the market.
Somewhere around 90% of all stock trades on the market are between institutional investors. That means mutual fund companies, pension plans, endowments, stock brokers, investment banks, etc. They are basically all trading against each other. Each has access to the same information, the same real-time news, the same hot tips, etc. Yet, one has decided to buy a stock and one has decided to sell that same stock.
What does this mean? It means they are both trading on virtually identical information and making decisions that are 180 degrees away from each other. How can that be? Simple: They are trading on random noise.
When you own the entire stock market you benefit from all the wisdom, research and money these other firms have spent analyzing the stocks of the companies in the index. The index holds all stocks. The stocks go up and down as earning outlooks adjust. You just sit back and collect the money without having to pay a bunch of MBAs to research everything and come to opposite conclusions about what to do. It’s the best deal going.
But can’t a good manager beat the market?
It’s no better than luck. Virtually every study performed on this area has shown it to be luck. It’s called the Random Walk because the market movements are equivalent to a drunk stumbling down the street. You can watch him and know he’s moving a particular direction, but at any moment he may change course, stumble, reverse or simply puke on your shoes. It’s not predictable and one of the core tenants of the Permanent Portfolio is not trying to predict the markets.
The important thing to remember is that each year the people who beat the market changes. One year you’ll have a hot fund manager and the next year they may rank near the bottom. Then a new winner will come up to beat the index only to find in a couple years they’ve faded away. And on and on.
Well as it turns out, over time as winners come and go the average annual return of the index funds just keeps climbing and climbing higher. After a decade or two the index fund owners find that their returns end up in the top quartile of performance without having to pay high fees to get it.
Think of it this way. Let’s say you’re a marathon runner in a group of 10 people. You’re not the fastest, but you have consistent performance and finish in the top half of all participants each time you race. Sometimes you’re 5th, sometimes 3rd, sometimes 4th, etc.
Now you enter a series of races against your nine competitors and you run each year for 20 years. Over that time the fastest runner the first year ends up dead last the next. The previous loser is now the winner. Then a new winner shows up and hurts his knee and drops out of racing entirely due to the injury. Etc. The years go by and you never win a race, but you’re consistent. You’re so consistent that you’ve racked up many 5th place, 4th place, 3rd place and maybe even a couple 2nd place finishes. You’ve never done really well each year, but you’ve also never done really badly. In fact you’ve managed to show up for each and every race and never missed one yet.
Well after 20 years of this consistent performance you will probably find that you’ve won the marathon series. Your rivals have either dropped out, burned out, or were never consistent enough to be in the top five. Your supposedly “average” performance pushed you into the well above average category because you are so consistent and reliable year in and year out.
That’s indexing. You’re not going to be the best each year, but over time you’re going to win. Promise.
What is a fair expense ratio for an index fund?
Anything 0.50% a year or below for a US index fund or 0.75% or below for an International fund.
Some companies offer S&P or Total Stock Market funds with outrageously high expense ratios of 1% or more. Do not use these funds unless you have no other choice available. If the index fund is charging more than 0.50% a year then you are being charged too much and need to find another option if you can.
Some international index funds charge slightly more because of the added expense of trading on foreign stock exchanges. Therefore, international stock index funds are more expensive but should not exceed 0.75% in expense ratio.
If your only choice is an expensive index fund or an expensive actively managed fund then choose the expensive index fund. The best choice though is a cheap index fund.
What is the difference between a Mutual Fund and an ETF?
An ETF is short for Exchange Traded Fund. This is a fund that can be traded intraday like any stock on the market. You can buy an ETF in the morning and sell it at lunch then buy it back again before you go home from work. This would be an incredibly bad idea, but you could do it if you wanted.
A typical mutual fund however allows redemptions and deposits on a fixed basis (usually at the end of the trading day). This means when you buy a fund you get the price of the fund after the market closes. You can’t trade in and out of it multiple times a day. Some companies (like Vanguard) won’t even let you buy back into a fund you just sold until you wait 60 days. This is done to keep the market timers and performance chasers from hurting the long-term holders of the fund and keep down costs.
The difference here doesn’t matter much for a buy-and-hold investment strategy like the Permanent Portfolio. The hourly or even daily fluctuations in price are irrelevant. However there is one major difference between ETFs and Mutual funds: Trading Costs.
When you buy a mutual fund you send your money to the your broker or fund custodian and make the purchase. Many times if the mutual fund is with the same company there is no transaction fee for this. You would, for example, send your money into Vanguard and tell them “Buy as many shares of the Total Stock Market Index as my deposit allows.” They say “Ok.” The sale is made, and the shares deposited into your account with no other fees involved.
Well with an ETF you need to deal with a brokerage. You have to place a market order, perhaps worry about a bid/ask spread, then pay a commission on the whole transaction. The commissions at a discount broker can be less than $10 or be hundreds of dollars at a “full-service” brokerage for each trade.
The problem is if you are making many small trades then the ETF can get expensive. If you are, for instance, depositing $100 a month into your portfolio you may spend $10 just to purchase the ETF. In other words, 10% of your savings that month went into transaction costs! Not good.
However if you sent that same $100 into a mutual fund company they simply would buy into the fund and not charge you commission. So you save money on the upfront purchase. Much better.
Now there are times where ETFs make sense and I will recommend some ETFs later on. But the important thing to remember is you should do the transactions in bulk and not a bunch of small trades with ETFs. If you follow that rule you can avoid the small commission charges that can really add up over time.
What about using an index fund that tracks a specialty index like small-cap stocks?
This is not advised. You want the broadest based index fund that captures the widest returns available from the US Stock market. That means the S&P 500 or TSM funds.
But haven’t small-cap stocks beaten large-cap stocks over the years?
Academics say “yes”. Reality says “it depends”. There are periods of time when large-cap companies dominate the market returns and times when small-cap companies dominate the market returns. It is unpredictable.
Let’s look at a big stock bull market to make our point. From 1980-1999 Small Cap stocks returned 14.29% CAGR but Large Cap stocks returned 17.11% CAGR. So for nearly 20 years the supposed higher returns of small-cap stocks over large-cap stocks didn’t exist. That’s a long time to wait for a theory of small-cap outperformance to show up isn’t it? How many people would have been sticking with that idea by year 17 or 18 of the strategy? Not many.
A broad based index will ensure you can grab extra returns from any stock asset class because you’ll own all of them all the time.
And before you start thinking that you’d rather have 100% stock portfolio to get those great returns all I can say is “Good Luck!” That was a wild ride and one of the greatest bull markets in stocks in US history. Not only that, but there are periods on both sides of that date range (1970’s and 2000’s) when stock performance was horrible!
If you still need more information about why the small cap advantage may not be that big of an advantage after all, please see this speech by Vanguard Founder Jack Bogle:
What about owning some international stocks?
Harry Browne didn’t advise this specifically. John Chandler, a close associate of Harry Browne and his former publisher says a little International exposure is OK but don’t go overboard.
My opinion is that 20% of your stock allocation percentage in International stocks is fine. That means 5% of your 25% allocation could be international stocks (5% – Intl and 20% Domestic).
The US is still about half the world’s economic output. It’s simply massive and not going away any time soon despite what some say. The fact is that American companies already have huge international presence and therefore international exposure already. Think about it and you’ll agree. General Electric has appliances, lightbulbs, generators, jet engines, etc. all over the world. You can’t hardly go anywhere on the planet without running into a McDonald’s or Starbucks. Microsoft and Apple Computer sell their products everywhere. Don’t forget food either. Archer Daniels Midland, Kraft, Campbells Soup, Coca-Cola, etc. export their products extensively. Caterpillar sells their construction equipment to everyone to build roads, bridges, buildings and everything else. Finally, you probably are flying on a Boeing plane to get to all of these places and rent a Ford or GM car to go to your Marriott hotel when you get there. Did you forget your running shoes for your morning jog? Not a problem. The store down the street probably sells Nikes.
Get my point?
So even if you think you have 0% international exposure by only owning American companies you actually don’t. It’s almost certain that the profits you receive from these stocks are generated from all over the world and not just from America.
What’s the recap?
- Only buy index funds for the stock portion of the portfolio.
- The index fund should be either Total Stock Market or the S&P 500.
- The index fund expense ratio should be less than 0.50% a year (unless it is an international index and can be up to 0.75% a year).
- Never purchase an actively traded stock fund unless you have absolutely no other choice available to you.
- Do not try to beat the market with the funds. Your market protection is already built into the other asset classes you own. The stock asset class serves a specific purpose and you don’t want to tamper with it by trying to outguess the market.
- If you want to own some international exposure you should limit it to about 5% allocation (5% Intl + 20% Domestic = 25% total in stock)
- Using Google Sheets or Excel, you can build a custom spreadsheet that will allow you to see the information about your investments that matters most
- There are many tools online for investors to monitor their portfolios
- They might not have information formatted in the way you want or illustrated in the way you want
- Spreadsheets allow you to make a portfolio analysis tool that is exactly the way you want it
Save some time and download a copy of the Portfolio Spreadsheet here!
Don’t feel like DIY-ing it?
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
Upon email confirmation, the workbook will open in a new tab.
How to make a stock portfolio in Excel, Google Sheets, or any other spreadsheet software
This “how-to” can be followed along in either Excel or Google Sheets. Really, any spreadsheet software will do. The formulas should be the same. Also, formatting and charting options should be very similar.
Would you rather watch a video than read a tutorial? Check out this post:
TRACK YOUR STOCK PORTFOLIO RETURNS USING GOOGLE SHEETS (OR EXCEL) – STEP BY STEP TUTORIAL
TRACK YOUR STOCK PORTFOLIO RETURNS USING GOOGLE SHEETS (OR EXCEL) – STEP BY STEP TUTORIAL
I will be using Google Sheets in this tutorial. I like Excel and use it often. Particularly with some of my more “intensive” models. In this case, however, I think that Google Sheets is a better option. First and foremost because of its GOOGLEFINANCE functionality which will automatically update certain fields for you (price, volume, PE, EPS, and on, and on…). Second, since Google Sheets is cloud-based, you can access it anywhere – including your mobile device.
Microsoft does have a cloud-based version of Office (Excel), but I would not recommend it. I am a Microsoft fan in general and a big Excel fan in particular. But, I thought Office 365 (or whatever it’s called) fell way short. Just my opinion though, use whatever you’re most comfortable with.
In order to follow along in Google Sheets, you’ll need a Google account. If you don’t already have one, click here for instruction on how to set one up.
Once you have your Google account set up, go to Google Drive and select “New” in the upper left-hand corner. Click on “Google Sheets > Blank spreadsheet”.
Okay, you should be ready to go, so let’s get into it.
First things first – enter your headers
Before you enter any information about your stocks or any formulas for calculations, you’ll want to lay the foundation of the spreadsheet by determining what information you want to see.
For this example, things were kept relatively simple. The amount of information you can glean from an investment’s current or historical data is almost limitless.
But, since this how-to is meant to serve as a starting point, I tried to keep things elementary. Here’s what we’ll analyze in this example:
- Company (fund) name
- Ticker symbol
- Purchase date
- Purchase price
- Shares
- Purchase cost
- Current price
- Gross current value
- Total dividends received
- Net current value
- Total gain or loss in dollars
- Total gain or loss percentage
- Annualized gain or loss
Enter these headers in the cells along the top of the spreadsheet. Personally, I like to leave a little space at the top and the left-hand side of my spreadsheets. So, I’ll start in B5 and enter across to N5.
Additionally, I like to know the as-of date for when I last updated a workbook such as this. Therefore, in B2, I’ll enter “Portfolio updated:” and I’ll bold everything I just entered (Ctrl+B). Here’s what that looks like:
Input some basic stock data
As mentioned earlier, this portfolio spreadsheet will consist of information you already know and information that you need to calculate.
The Name, Symbol, Purchase Date, Purchase Price, and Shares fields are all information you should already have. So, go ahead and enter that.
All of this information, in my example, was chosen at random. You may have more stocks or fewer stocks.
Google Sheets and Excel can certainly handle everything you have in your portfolio.
It might take some digging on your part to unearth this information – even if you have an online broker. Particularly the Purchase Date. But, if you want an accurate calculation of your Annualized Gain/Loss do your best to find it.
Want to know how to add quality stocks to your portfolio? Read this post:
DETAILED STOCK VALUATION SPREADSHEET WITH WALK-THROUGH
DETAILED STOCK VALUATION SPREADSHEET WITH WALK-THROUGH
What if I have different Purchase Dates for different lots of the same stock?
That’s a bit of a conundrum. To be completely honest, you might want a more sophisticated portfolio spreadsheet. But, there are a couple of ways you might work around it.
First, you could just list each lot separately. The only potential problem here is when it comes to allocating dividends (if any) to the different lots.
Second, you could group all of the lots together. But, unless you calculate a weighted-average Purchase Date and Purchase Price, a lot of your calculations are going to be erroneous.
Personally, I would separate the lots out and then group all of the dividend information under the lot you purchased first – for simplicity’s sake.
Here’s what we have so far:
Formula time! Purchase Cost
We’ll start off simple.
Purchase Cost = Purchase Price × Shares
At the risk of oversimplifying things – I’ll clarify for those of you who are completely new to spreadsheets…
Every formula begins with an equal sign (=). That’s how Google Sheets and Excel know to perform a calculation.
So, in cell G6, type “=E6*F6” and press Enter. The asterisk (*) means multiply.
In my example, for stock symbol ZF, the result is $1,990 ($15.31 Purchase Price times 130 Shares).
“The Simply Investing Portfolio has gained over 322% since 1999 versus the stock market return of 146% over the same period.
500+ happy investors in over 25 countries.”
Click here or click the image below ▼
500+ happy investors in over 25 countries.”
Click here or click the image below ▼
Don’t type more than you need to, copy down!
Now, you need to duplicate this formula for every stock in your portfolio.
But, don’t go to row 7 and type “=E7*F7”. Then row 8 and type “=E8*F8”. And so on…
Either copy the formula in G6 (Ctrl + C) and then paste (Ctrl + V) in cells G7 through G50 (or whatever row you end on).
Or, alternatively, Google Sheets and Excel give you the ability to duplicate what’s in a cell (the formula) by clicking on the little square in the lower right-hand of the active cell, holding the mouse button down, and dragging it where you want it to copy. This is the quickest and easiest way to do it.
Automate Current Price and Gross Current Value in Google Sheets
One of the reasons I elected to use Google Sheets for this tutorial is because of the GOOGLEFINANCE function. I know that Excel used to have MSN Money functionality. But, if they currently have something similar, I’m not familiar with it.
What is GOOGLEFINANCE?
It gives you the ability to connect to Google Finance through a formula and populate Google Sheets with information about an individual stock.
For instance, if, in cell H6, you type “=GOOGLEFINANCE(C6,”price”)” the Current Price of the stock entered on row 6 will be populated and automatically updated. Note the equal sign (=), and the quotes around “price”. Don’t type the very first and very last quotes, though. Only type what’s in bold.
With the GOOGLEFINANCE function, you don’t have to worry about looking up the price (and other basic information, if you wish) and then manually typing it into Google Sheets! Always accurate and always up-to-date (though not real-time).
Copy the GOOGLEFINANCE function down for all of your stocks. If you’re using Excel, or otherwise opt not to utilize the GOOGLEFINANCE function, you’ll have to enter the Current Price manually.
With the Current Price, you can now calculate a Gross Current Value. Why “Gross?” Because, later, we’ll add dividends in order to get a Net Current Value.
Gross Current Value = Shares × Current Price
In cell I6, type the following formula: “=F6*H6”. Copy that formula down for all of your stocks.
Here’s what it should look like thus far:
Dividends are an important part of your returns – be sure to include them!
Unfortunately, there’s no way (that I’m aware of ) to automatically import dividend data for the stocks you hold. Updating this information is by far the most labor-intensive step in this tutorial.
Yes, the GOOGLEFINANCE function can import the yield percentage for a given stock. But, since this stock portfolio spreadsheet is focused on total returns, that isn’t going to help much.
Want to identify stocks that pay (quality) high yields? Read this post:
HIGH DIVIDEND STOCKS – CHARACTERISTICS OF QUALITY YIELDS
HIGH DIVIDEND STOCKS – CHARACTERISTICS OF QUALITY YIELDS
If you own your shares through an online brokerage, as most people do, you should be able to access dividend payment history for the individual stocks you own.
For example, TD Ameritrade allows you to display dividends paid for a specific stock in your transaction history.
If this information can’t be easily retrieved from your brokerage, then you’ll have to do some homework. A good place to start would be the Nasdaq Dividend History page. Here, you can find the Payment Date and per share amounts of dividends paid for every stock. You’ll have to multiply the per share amounts by the number of shares owned to get the full dividend paid.
Total your dividends received for each of your stocks and enter that information under the Total Dividends Rec’d heading (column J).
For the sake of accuracy, make sure you only include dividends paid to you while you owned the stock. Also, be sure to update this information every time a stock pays a dividend.
It’s more trouble than it should be, for sure. But, as I said, dividends can make a huge contribution to the returns received for a particular stock. If this seems like too much trouble, you can forgo including dividends. Just know that your return numbers won’t be 100% accurate. Capturegrid 4 14 commentary.
Totaling all of your returns
With dividend information gathered, you can now calculate the Net Current Value. This will total your returns from capital gains and from dividends and give you an accurate picture of the stock’s performance.
Net Current Value = Gross Current Value + Total Dividends Rec’d
In cell K6, enter the following: “=I6+J6”. Then, copy that formula down for the rest of your stocks.
Only a few more columns to go! Here’s what your stock portfolio should look like now:
How are your stocks really doing?
The last three columns will be used to calculate the returns of each stock. Let’s focus on the first two columns first.
Total Gain/Loss $ = Net Current Value – Purchase Cost
This is the difference between the value of the stock now (including dividends received) and what you paid for it.
In cell L6, enter the following formula: “=K6-G6”. As usual, copy that down for the rest of the stocks.
Total Gain/Loss % = Total Gain/Loss $ ÷ Purchase Cost
In cell M6, type the following formula: “=L6/G6”. Copy it down…
This formula compares your Gain/Loss in dollars to what you paid for the shares of stock you own. The result is a percentage and it tells you what the total performance of your stock has been – thus far.
But, what’s a good Total Gain/Loss %? That’s difficult to answer. A 5% gain is good over the course of one day or one week. A 50% gain isn’t so good if it’s over the course of twenty years.
Looking to analyze your investment portfolio? Read this post:
ARE YOU SATISFIED WITH YOUR INVESTMENT PORTFOLIO?
ARE YOU SATISFIED WITH YOUR INVESTMENT PORTFOLIO?
How to compare gains and losses – apples to apples
That’s why I think you should calculate the Annualized Gain/Loss.
I’ll warn ya, the formula is a little complicated. But, when you pull it off, it’ll provide valuable insight into the true performance of each stock. It will allow you to better compare stocks against one another.
Type the following in cell N6: “=(K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1”.
Yikes!
Stockfolio 1 52 Sailboat
You don’t have to necessarily know why it works. Just know that it does. Don’t let it scare you off, this will make more sense if I break it down.
Before we do that, make sure you have a Portfolio updated date in cell C2. This date is used to determine the amount of time that has passed since the stock was purchased and is critical for calculating Annualized Gain/Loss.
The first part of the formula, “(K6/G6)”, compares the Net Current Value to the Purchase Cost. It calculates the relationship between the value now and when you bought the stock.
The next part, “(1/(YEARFRAC(D6,$C$2)))” looks complicated, but, for the most part, all it’s doing is figuring the amount of time that has passed since you purchased the stock. What’s critical, though, is that you include the dollar signs ($) in front of the “C” and the “2”. This ensures that the formula is always comparing a stock’s Purchase Date to the same Portfolio updated date. The dollar signs ($) lock that cell into the formula so it doesn’t change when you copy it down.
Finally, the “-1” at the end turns the result of the equation into a percentage that makes sense.
That’s it! Again, the formula for cell N6 is: “=(K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1”. Type that in. Copy it down. And, that nasty bit of business is over!
Here’s what it should look like with all of the information completed for each stock:
We’ve analyzed each stock – what about the overall portfolio?
While you could quit here and (with a little formatting) have a perfectly good stock portfolio spreadsheet – let’s push on just a bit further and put the final touches on this thing.
For most of our fields (except Current Price), we can analyze the portfolio in aggregate. That way you can compare your individual stocks against each other and understand how they contribute to the overall portfolio’s returns.
For most fields, this is pretty easy. It’s simply a matter of totaling the entire column. Others will use the exact same formula you used for individual stocks. One last field is a bit tricky to calculate for the entire portfolio – Purchase Date. But, let’s not get ahead of ourselves, let’s start with the easy ones…
Fields that are summarized with SUM
Purchase Cost, Gross Current Value,Total Dividends Rec’d, and Net Current Value amounts for the whole portfolio are simply sums of the amounts for each stock.
In order to sum these amounts, we’ll use the SUM function in Google Sheets/Excel. Start in cell G4 (above Purchase Cost) and enter the following: “=SUM(G6:G50)”.
You don’t have to stop on cell G50. If your list of stocks goes beyond row 50, then you can go to G100, G500, G1000, or however far down you need to. Just make sure that you are including the Purchase Cost for every stock in the equation. It will not hurt to include blank cells in the formula.
Enter the SUM function for Gross Current Value, Total Dividends Rec’d, and Net Current Value in row 4. E.g. “=SUM(I6:I50)”, “=SUM(J6:J50)”, and “=SUM(K6:K50)”.
Fields that are summarized with an equation
The Total Gain/Loss $, Total Gain/Loss %, and Annualized Gain/Loss fields use the same equations to calculate for the whole portfolio as they did for the individual stocks within.
Remember how you copied those formulas down rather than re-entering them for each stock? The same thing can be done with the portfolio calculations to save time.
Click and drag (highlight) across cells L6, M6, and N6, then press Ctrl+C (copy) on your keyboard. Then click on cell L4 and press Ctrl+V (paste). Viola! The spreadsheet will automatically use the previously calculated totals to determine Gain/Loss amounts for the entire stock portfolio.
One last formula…
Maybe you’re wondering how an Annualized Gain/Loss can be calculated for the entire portfolio if there’s no Purchase Date for the portfolio as a whole?
How the hell would you go about settling on onePurchase Date for a bunch of different stocks?
In order to pull this off, and get an accurate Annualized Gain/Loss for the entire stock portfolio, we need to calculate a weighted-average Purchase Date for the entire stock portfolio.
What will the Purchase Dates be weighted by? By Purchase Cost.
This is done by using a function in Google Sheets/Excel called SUMPRODUCT. No need to get into the particulars about how/why this works. Just know that it does. The result is a single Purchase Date for your entire portfolio.
In cell D4, enter the following formula: “=SUMPRODUCT(D6:D50,G6:G50)/G4”. Remember – if your list of stocks goes beyond row 50, change D50 and G50 to D100/G100, D150/G150…whatever you need to.
Don’t be alarmed if the result of that formula is a big number and not a date. That’s simply an issue of formatting and we’ll get into that next.
Here is what your (almost completed) stock portfolio spreadsheet should look like now:
This seems like good information, but it looks like crap!
Yep, you are correct. Time to spruce things up a little bit and make this information more readable.
Formatting is all a matter of preference. There’s no single right way to do it.
I’ll walk you through some of the things I like to do in terms of formatting and you can copy what you like. Also, feel free to explore on your own. Most of the formatting options are available below the main menu at the top of the spreadsheet. Mouse over the little icons, and you’ll see text pop up explaining what each option does.
I’d definitely say you want to format your Purchase Dates as dates – if they’re not already. Select those cells and click on the icon that has “123▼” on it. Lower down on the list, you should see a couple of options for a date format.
Another thing I almost always do when formatting a spreadsheet is to color in the cells that have formulas. That serves as a visual reminder, for me, not to type in them. The cells that are left white are the variables that can be changed. The little paint can icon is what you use to change cell color.
You can format the numerical data as numbers or currency. Show more or fewer decimals with the icons that have “.0←” or “.00→” on them.
I’d also change the Total Gain/Loss % and Annualized Gain/Loss to a percentage format. Use the “%” button on the menu.
Lastly, I like to bold my headers, and, usually, my totals.
Again, formatting is a matter of preference, so there are no wrong answers. Play around with it and make it look how you want. If you ever want to wipe the slate clean – select the cells you want to change, click on “Format” in the main menu, and then “Clear formatting.”
Below is how I opted to format my stock portfolio spreadsheet. You’ll notice the charts on the right-hand side. Which, we’ll get into next…
Use charts to better understand your portfolio and returns
You can chart anything on your spreadsheet. You could even create a bar chart comparing Purchase Prices or Shares if you wanted. Though, I’m not sure why you would?
In my spreadsheet, as you can see above, I opted to chart two things.
- A comparison of the Annualized Gain/Loss among all of my stocks using a simple column chart
- A pie chart that shows the composition of my portfolio – by stock
Creating these charts was quick and easy in Google Sheets. Doing so in Excel is also easy (“Insert”, then “Charts”). But, there will be some differences between the two visually.
To make the first chart, simply highlight all of your stock Symbols (not the header, C5). Then, holding down the Ctrl button, select all of the Annualized Gain/Loss percentages (again, not the header, N5). The Ctrl button allows you to select multiple cells that are not next to each other.
From the “Insert” menu, select “Chart.” You should see a column chart pop up with the Symbols in the x-axis and the percentages in the y-axis.
Click and drag the chart where you want it. If you double click it, you’ll see the Chart editor. After selecting “Customize” and then “Chart & axis titles” you can enter a title in the “Title text field.” As you can see, I just named mine Annualized Gain/Loss.
If you select the Symbols and Net Current Values and insert a chart, you’ll probably get another column chart. I think a pie graph better represents this information. Fortunately, changing the chart type is easy. Double click this new chart and in the Chart editor click “Setup” and change the “Chart type” by selecting a pie chart from the drop-down menu. It might be under the “SUGGESTED” heading.
There we go. A portfolio spreadsheet with well-formatted, useful information.
How to make a stock portfolio in Excel (or Sheets)
Spreadsheets allow users to analyze their portfolios and returns in just about any way imaginable. The GOOGLEFINANCE function in Sheets automates updates for a lot of frequently referenced information about stocks and mutual funds.
I tried to explain how my example stock portfolio was made in detail. What (if anything) did you get hung up on, though?
Is there additional information you would want to have in your stock portfolio spreadsheet? What would you want to analyze on an ongoing basis?