New Excel tools help investors see true value of funds
Spreadsheet data extraction enables better fund holdings analysis – what you find might surprise you.
When you buy an investment fund, do you know exactly what you are buying and whether it is cheap or expensive? It is seductive to see a graph of good recent performance and assume that will continue. But this method is like sticking your finger in the air – it lacks the necessary detailed analysis and often leads to poor decisions.
Due to the increasing popularity of self-invested personal pensions and do-it-yourself investment platforms, this is a critical challenge for millions of investors. But the launch of some terrific new tools in Microsoft Excel has made the data and analysis they need much more accessible.
The main way to judge the value of a fund is to look at the price-to-earnings ratio (PE) of all its holdings and compare them to the current and long-term averages in that sector. PE – the ratio of a company’s share price to its earnings per share (EPS) – is the most widely-used measure of whether a share price is over or undervalued.
Before last year, it was laborious work for small investors to get PE figures for fund holdings and analyse them – you had to find all the figures from the web yourself and enter them manually in a spreadsheet. But Excel’s ‘linked data types’ tools – launched in Windows 365 in 2019 – make it much easier for anyone to get under the bonnet of a fund by automatically importing PE data – and lots of other relevant information too.
When you do this, you might be surprised at what you find.
Analysing top funds
We can demonstrate how to do this analysis by looking at two of this year’s best performing funds – Baillie Gifford US Growth Trust and Polar Capital Technology Trust. The funds have similar performances having risen almost 80% over the last year. This begs the question, are they very similar?
Start by locating the latest full list of shares in the fund. Many funds publish updated lists regularly on their website – if not, try their annual report. Highlight the list together with the weightings, which show what percentage of the portfolio each holding occupies. Copy and paste this into an Excel document.
Next, highlight the company names column and use Excel’s magic data types tool to convert them into stock codes. Some may fail to recognise – if so, click on the adjacent question mark and rephrase until Excel recognises them.
Once you have as many of the stock codes as possible, use this simple formula to extract the top company’s PE ratio from the web, https://insider.office.com/en-us/blog/new-data-types-in-excel. Use the fill handle to copy the formula down, then calculate the average for all the holdings. For the most accurate picture, use the weightings data to the calculate weighted average.
Finally, compare this to current and long-term averages. For example, the long-term PE average for the S&P 500 index of large US companies is 17. For the Nasdaq, which holds mostly US technology stocks, it is around 21.
Next time you analyse the fund, check for any holdings it has bought or sold; follow the steps above for any new ones; then click data/refresh all and Excel will fill in the latest data automatically.
Surprise fund holdings
The holdings and weightings in the Baillie Gifford fund show that such a large proportion of its stocks are technology and internet-based companies. Already, we can see this is largely a technology growth fund, in which stocks happen to be US listed, and so perhaps not what we expected.
Using the technique above, I managed to get PE information for 71% of holdings in the fund by weighting. Of those, over a third have no PE ratio, which means they have negative earnings per share, and are therefore loss-making.
Buying a loss-making company is not always a problem if its expected future earnings growth justifies the potential. Some of the loss-making companies in Baillie Gifford’s list are hugely valuable businesses. For example, Shopify – one of the portfolio’s biggest holdings – is losing over $120 million a year. But it is busy grabbing market share, so has potential to turn a huge profit at some point. Equally, it is scary to see the valuation of the company – almost 100 times its $1.5 billion of sales – yes you read that right, 100 times its sales. But it is still loss-making.
How can we calculate an overall PE for a fund when many its stocks are losing money? One way is to allocate a loss-making stock a high PE of, say, 100 to reflect the risk and speculative nature of the investment. I did this to my analysis, and also limited stocks with actual PEs to 100 – because , even though some are higher than that, I believe a company just maturing and turning a profit is less risky than a loss maker. This is arbitrary, but it gives a feel for what is happening in the fund.
In this way, I calculated that the overall PE, based on a weighted average of the fund, is 90 times. Comparing that to the Nasdaq and S&P averages, we can see the Baillie Gifford fund’s constituents are very expensive.
Finding better value
Let’s compare this with Polar Capital Technology Trust, the equally high-performing technology specialist fund.
Polar’s portfolio has about one tenth of its stocks in common with the Baillie Gifford fund, but it has a much broader group of more mature, profitable technology manufacturing companies rather than internet stocks.
Linking the stock data for Polar was a bit harder because it has more Asian stocks, for which Microsoft often cannot find data. But it can be pulled manually from sources like Stockopedia. I calculated that the PE for that fund, using the same method, is 53 times. That is still expensive, but the stocks in the fund are much cheaper compared to the BG fund.
This is not investment advice. It is just to help you get some perspective on funds and see that any investor with some Excel knowledge can now get a great insight into fund values. This can help them stop the finger-in-the-air approach to investing and get much closer to answering that key question: what price growth?
For those who would like to learn more, our online financial training course Introduction to Valuation looks at how to value companies in more detail. Topics include the role of valuation in markets; the core valuation metrics; adjusting for normalisation and comparison; and other types of analysis.
