The ideal portfolio manager

However, to see all this you have to record it. The deals have to be logged, the dividends have to be monitored, the cash books have to be adjusted. For this, you need tools – portfolio management tools. And ‘management’ now goes into inverted commas because the word conveys maintenance of a portfolio both for the purposes of administration and to help maximise performance. So the tools must do the mundane stuff, such as recording the latest interim dividend from GlaxoSmithKline, popping the proceeds into the portfolio’s value when the shares go ‘ex-dividend’ and into the cash account when the payout is made. They must also be capable of a little sophistication, a little added value; for example, warning if a shareholding moves into ‘sell’ territory or appraising the whole portfolio for cheapness or dearness.

True, these tools don’t have to be that complex. For an investor with the time and inclination, analysis using paper, pen and ink can still do an adequate job. There is no getting away from it, though, computer spreadsheets can do a much better one, and in much less time.

So, with the hope that they may be helpful, here are the spreadsheets that Bearbull uses to log the performance of the Bearbull Income Portfolio. They have been smartened up for public appearance. A couple of worksheets that until recently really did exist only in a hand-written schedule have gone electronic and others have been made more logical. But, in essence, these are the spreadsheets that Bearbull has used to run his income portfolio for the past 14 years or so. And the hope is that – strip them of their details – the sheets can become a generic portfolio-management tool that can run almost any type of portfolio.

To maximise their usefulness, you will need passable skills on Microsoft Excel because, for example, you’ll be doing a fair bit of cutting and pasting of formulas. You must also be willing to commit a decent amount of time to inputting data. That’s because the extent to which the 12 worksheets ‘talk’ to each other is limited. That’s partly a comment on Bearbull’s spread-sheeting capability, but it’s partly deliberate. For example, manually inputting today’s prices and watching the effects ripple through a spreadsheet provides an opportunity to think about a holding or the state of a whole portfolio; ditto the effect of updating forecasts for a shareholding’s earnings and dividends in the ‘Ratings’ spreadsheet.

View this inputting and checking as an important part of running your capital. It may be portfolio management’s equivalent of cleaning the bathroom but, just like that chore, ignore it for too long and you’ll come to regret it. So appreciate the benefits of doing it. Besides, the Ratings sheet can also be duplicated and used to run ‘what if?’ scenarios – what happens to my portfolio’s rating if I swap a holding in Wm Morrison for one in Shell; what happens to the portfolio’s dividend yield if I swap it for one in ARM? You get the idea.

Anyway, to run through the ‘what’ and the ‘how’, the best place to start is at the beginning.

Step 1

If you want to build an investment portfolio, the first thing you’ll need is a chunk of cash – your starting capital – which will live in an account somewhere. Managing that is the function of the ‘Cash manager’ spreadsheet, which is about as simple and as commonsensical as a spreadsheet can get. Screengrab 1 shows the Bearbull Income Portfolio’s latest cash transactions. To update, define the transaction – for example, ‘Vodafone 7.47p fin’ – insert the amount, remembering to prefix a debit with a minus sign, and the spreadsheet automatically updates and drops the balance carried forward into the cash component of the ‘Portfolio’ spreadsheet (more of which, later).

Step 2

Next, you will deal – buy and sell securities – for which you will use the ‘Dealings’ spreadsheet (see screengrab 2). This, too, is fairly straightforward. For a new deal, fill in cells A to F in a new row. They should automatically calculate cells G to J. That said, brokers’ commission rates vary, which may affect the calculation in column I. Bearbull pays a flat rate of £25, but commission is often related to the size of the transaction. In which case, often it may be easier simply to type in the amount of commission; not forgetting that on every purchase of a quoted share there is also a £1 contract levy. Lastly, if you are buying a security, remember to note down the level of your benchmark index – most likely the FTSE All-Share index or the FTSE 100 – because you’ll need that information elsewhere.

Step 3

Now you are building that portfolio. You’ll have holdings, each of which will have a performance record since you dealt; you’ll have some cash, maybe some accrued interest and most likely some dividends owing. In short, your portfolio will have an up-to-date valuation, which you will want to compare with its cost and compare its performance with a benchmark.

This is what the ‘Portfolio’ worksheet does. It is the first sheet in the portfolio workbook because it’s the go-to reference that, at a glance, tells you what you’ve got and how it’s doing. You will use this sheet more than any other. It’s a bit more complex than the previous two, but hardly challenging.

To help explain, screengrab 3 shows a version of the Bearbull Income Portfolio. First, let’s assume that you have already put data into your equivalent of cells E25 and E26 in the Bearbull sheet, which show values for your benchmark indices at your portfolio’s launch date. Adding a holding is straightforward. In a new row, fill in columns A to F, then columns J, M and N. The spreadsheet will do the rest, calculating the absolute and relative performance of each holding and its weighting in the portfolio plus the portfolio’s aggregate value.

To get an up-to-date valuation, fill in latest prices in the occupied cells in column F. Your cash and ex-dividend positions will be automatically updated by the ‘Cash manager’ and ‘Dividends’ sheets. Filling in ‘interest accrued’ is probably optional – it’s unlikely to make much difference. Last, remember to fill in latest values for your benchmark indices (cells G25 and G26 in the income portfolio worksheet).

Your portfolio might just include short positions, where you have agreed to sell a security before you have bought it in order to profit from a falling price. If so, then the Portfolio sheet has a section that will manage this. The most likely scenario – as shown in screengrab 3 – is that you want some portfolio insurance against a falling market and therefore have bought put options in your benchmark index or something close to it. Complete the appropriate cells, just as you would have done for your conventional long positions, and the running profit or loss on the short positions will add or subtract value from your portfolio. In reality, short selling remains a minority activity so, in practical terms, you may simply want to delete the relevant cells – A18:G20 in the screengrab – from your Portfolio worksheet.

Step 4

We have already mentioned the ‘Dividends’ sheet, so let’s focus on that one. As the name indicates, it helps manage the portfolio’s dividends, a vital function. First, as shown in screengrab 4, it is sensible to manually log the dates when each of your holdings announces its dividends. The days rarely change from year to year and this will help remind you when a dividend – and results – are due.

When a dividend is announced, record the relevant data in an unused row in the yellow section of the worksheet (see screengrab 5). Fill in cells in columns G to K. The sheet will know when a share holding goes ex-dividend, which, effectively, is the date at which a shareholder becomes entitled to the dividend. It will calculate the amount and drop the aggregate amount of ex-dividend payments into the Portfolio sheet. But, sorry, it won’t add the dividend payment into the Cash manager sheet. For now at least, you will have to do that manually. When the payment has been made and recorded, delete the data from columns G to K to free up the row.

Step 5

A key part of portfolio management is being able to run profits and knowing when to sell. For this, we will use the ‘Stop-loss’ sheet. The stop-loss manager works on the logic that a holding should be sold if its price falls below a preset level. Alternatively, if the price rises above another level – the ‘reset’ price in column E (see screengrab 6) – then the ‘sell’ and ‘reset’ levels should both be adjusted upwards. Follow these rules and a holding will be run for as long as its price rises satisfactorily and will be sold before its falling price can cause too much damage.

As a subjective rule, when Bearbull buys a new holding he will tolerate a 20 per cent price swing either way. If the price falls 20 per cent, he sells. If it rises 20 per cent, he resets the parameters. Take shares in Carr’s Milling (CRM), currently the most successful holding in the income portfolio. The stake was bought at 440p per share back in January 2009 and since then the reset limits have been changed six times. That has allowed the portfolio to ride the rise in Carr’s share price, even though chunks of stock have been sold along the way as its weighting in the portfolio became too great. During the course of this rise, Bearbull also became more cautious, anxious not to lose too much profit should the share price go seriously into reverse. Thus he has tightened the limits. Now a sell is triggered after just a 10 per cent fall from the 1,682p level set a year ago. Simultaneously, the reset limits will be adjusted upwards if the price rises just 15 per cent to 1,934p.

So much for the principles. Operating the stop-loss manager is intuitively straightforward. For a new holding, insert a new row at the bottom of the yellow section of the sheet then, in column A, copy and paste the reference from the cell above. That should insert the name of the new holding from the ‘Portfolio’ sheet – but do check; changes to the ‘Portfolio’ sheet will affect the ‘Stop-loss’ sheet. Add the ‘base’ data in the cells in columns B and C. Cells in D and E should populate automatically. Check that the ‘sell’ and reset’ price limits are values that you want; for a new and possibly volatile holding you may want to tolerate a bigger price fall – say 25 per cent – before you sell. As you shift the limits upwards for a successful holding, it is sensible to note the date and price at which each previous reset was made. That helps to get a feel for where a share price has come from. And remember to enter the new base date and price in the cells in columns B and C.

Taken together, these five worksheets – Portfolio, Stop-loss, Dividends, Dealings and Cash manager – make up a standalone portfolio-management package. If you want to use only these, you will find that they can do a perfectly good job. However, there are seven more sheets – including two charts – that can add value.

Step 6

Of these seven, the most important is ‘Performance’, where you can log the portfolio’s monthly values and build a better picture of both its absolute and relative performance. The worksheet calculates the average monthly returns for both the portfolio and its benchmark plus the standard deviation of those returns (ie, the degree to which they bounce around the average). Typically, high returns and high standard deviation go together. However, that does not have to be the case and the less standard deviation for a unit of change in a portfolio’s value, the better.

That notion is packaged into the Sharpe ratio, which the worksheet calculates. The ratio – named after William Sharpe, a Nobel Prize-winning economist – is also called the ‘reward-to-variability’ ratio, which gives a big clue to its origins. It measures the relationship between a portfolio’s excess returns over a risk-free rate and the standard deviation – or risk – taken on to achieve those returns. As such, a portfolio’s Sharpe ratio can be neither good nor bad, but has merit only to the extent that it is higher than the ratio for a similar portfolio or a benchmark.

In the case of the Bearbull portfolio (see screengrab 7), its performance has been fine. It has a much higher average monthly return than its benchmark (the All-Share index) for about a quarter less volatility. But it’s not possible to compare the Sharpe ratio for the two sets of returns because the performance of the All-Share index over the portfolio’s 16-year life has been so moderate that its returns on average have been below the risk-free rate of interest. That means the All-Share’s Sharpe ratio is negative, which is meaningless – maybe the benchmark or the time series needs changing.

As to calculating these statistics, most of that is achieved simply by inputting the latest end-month data for the portfolio’s value and the benchmark in a new row in columns K and L of the worksheet (see screengrab 8). To calculate the Sharpe ratio you will also need a value for the risk-free rate of interest. In the work sheet the proxy used is the interest rate for Treasury bills. Data for these is available on www.bankofengland.co.uk/statistics/Pages/default.aspx.

Step 7

Closely linked to the ‘Performance’ worksheet are two charts, each of which illustrates a different aspect of the portfolio’s performance. First – see screengrab 9 – is a straightforward line chart that compares the portfolio’s performance with its benchmark. The scale is linear, although a logarithmic one will show the rate of change better at the cost of losing some detail. It is easy enough to adapt the chart to a log scale – hover the mouse over the vertical axis, right click, then left click ‘Format axis’ when the options box appears; go to the ‘scale’ tab, click on the ‘logarithmic scale’ box and adjust the values for the axes.

To update the chart for the latest monthly performance, left click on ‘Chart’ in the Excel ‘standard’ tool bar and go to ‘Source Data’. In the dialogue box – see screengrab 10 – update the value for each series of data – ‘Portfolio’ and ‘All-Share index’ in the screengrab; also, remember to update values for the ‘x’ axis at the foot of the box.

The second chart – see screengrab 11 – is a ‘scattergram’, or regression analysis, to give it a posh name. It shows the extent to which one factor drives the performance of another. In this case, the driving factor – or the independent variable – is the portfolio’s benchmark (ie, the All-Share index) and the driven one – or the dependent variable – is the portfolio. The more that the independent variable drives the variable, the steeper will be the slope of the diagonal line on the chart.

Arguably, however, the most illuminating bits of the chart are the little equations in the top right. The first line – y = 0.3652x + 0.526 – shows where the portfolio’s returns come from on average. Essentially it says that in an average month the portfolio makes 0.36 of whatever the market (or the independent variable) makes; go back to the ‘Performance’ sheet and you’ll see that’s 0.36 of 0.16 per cent (see screengrab 7). In addition, it makes 0.526 per cent. This ‘0.526’ is defined by the point at which the diagonal line crosses the vertical line, which shows the portfolio’s monthly returns. On screengrab 11 you can just about make out that must be around 0.5 on the ‘y’ axis.

More interesting, this is what City analysts call ‘alpha’ – the returns that are independent of the market’s; the returns that a fund manager gets through his or her own efforts and ability. True, we can debate whether alpha really exists. Statistically speaking – and we are speaking about statistics here – alpha is just a byproduct of the regression analysis that seeks to find a straight line that is the best possible fit for all the dots on the chart. In this case, as the chart shows, the dots are scattered about all over the place. So how good a fit is the straight line?

Answering that is the task of the lower line: R2 = 0.2478. Basically, the higher the value of R2 (it goes up to 1.0), the more the dependent variable’s returns are predicted by those of the independent variable. Here, just below a quarter of the returns of the income fund (the dependent variable) are predicted by the All-Share index’s performance. That’s consistent with a high alpha and it gives a warm feeling that Bearbull is doing something right. So it’s worthwhile keeping the beta chart up to date. But don’t get hung up about alpha. There is plenty of evidence to show that it is more statistical quirk than significant figure.

As to updating the beta chart, that’s done by the same procedure outlined for the performance chart.

Step 8

In addition to knowing how your portfolio’s value is changing, it’s vital to know both how highly its components and the portfolio in aggregate are rated by the stock market. Knowing that can provide useful clues about whether your fund and its holdings are cheap or dear. That’s the function of the ‘Ratings’ worksheet. It shows the price/earnings ratio, dividend yield (both in relation to cost and market value), dividend cover, price-to-sales ratio, profit margin, return on equity and debt-to-equity ratio for the portfolio and its holdings (see screengrab 12).

True, the usefulness of the ratios is restricted by the quality of the data and, in respect of equity and net debt in particular, historic figures, which have the benefit of accuracy, may be better than estimates. Even so, making the effort to maintain this worksheet, which also requires a fair quantity of data inputting, should be worthwhile. Much of that inputting should come through automatically from the ‘Portfolio’ sheet. That applies to columns A to D, then columns W and AA. Check that the feed is correct; some copying and pasting of formulas may be needed as your portfolio changes. You have to provide data for the cells in the green columns, most of which are shown in screengrab 13.

Last, column F shows data for ‘intrinsic value’. In a way, this can be any figure you want, but really it’s a stab at what you reckon is the underlying per-unit value of the security in question (most of the time that means value per share of a company’s equity). For Bearbull, this is important – estimating intrinsic value often plays a key part in determining the decision to invest. And having made the effort to estimate value, it makes sense to keep tabs on its changes. That means some guesstimating, which may require little more than annually updating intrinsic value by your ‘opportunity cost’ or the rate of return you seek (for Bearbull that would be 8.5 per cent). At the very least it’s another way of checking whether your holdings and your portfolio looks cheap or dear.

The final three worksheets – as we move towards the right-hand side of the tabs at the bottom of the screen – are the most optional. They log dividends received, track the sums that have been distributed and keep score of the annual results.

Step 9

For Bearbull, whose income portfolio distributes all the dividends it receives, it is important to tally up dividends and interest received during each six-month period. Then it is equally important to keep track of the aggregate amounts distributed and, from that, calculate the dividend yield that the fund has produced. For those running portfolios whose income is only being reinvested, the need will be less. However, those running mature funds whose function is to distribute their income, should use the ‘Dividends received’ and ‘Distributions’ worksheets.

True, these worksheets are rather hands-on. Dividend details must be inserted manually, although in the ‘Dividends received’ sheet a bit of copying and pasting from the ‘Dividends’ sheet means that the sheet can be updated in seconds (see screengrab 14).

Much the same is true of the ‘Distributions’ worksheet. Copy and paste total half-yearly dividends from ‘Dividends received’ to find the year-on-year change in the amount paid out plus the cumulative total of all distributions (screengrab 15).

To calculate the portfolio’s yield, you will need a value of the fund for the denominator. You get the most accurate measure by averaging the portfolio’s value over the period to which a payout relates. This is done easily enough by using a spare cell to apply Excel’s ‘average’ function to the portfolio’s end-month values in the ‘Performance’ worksheet (screengrab 16, which shows how the average value for the first half of 2014 can be calculated).

Step 10

The years will rush by quicker than you can imagine. Before you know it, your portfolio will have a three-year record, then it will be five years and so on. It helps to have the big picture to hand to know where you have come from, to get a clean-but-simple indicator of how your investment performance has been. Long-term results can point to lessons that need to be learned or can provide reassurance during the short spells of underperformance, which will inevitably come.

That is the purpose of the ‘Long-term performance’ worksheet (see screengrab 17). Because it is updated just once a year, it hardly needs automation. The source data comes from the ‘Performance’ and ‘Distributions’ worksheets. In addition, you will need data for the dividend yield of your benchmark index. Without a subscription to a database, that can be tricky to obtain. However, subscribers to the Financial Times, for example, can access the ‘Markets Data’ pages at markets.ft.com/research/Markets/Equity-Indices for information on the dividend yield of the All-Share index.

So there it is – Bearbull’s Portfolio Manager. You can download an Excel workbook of the tool at

This software will not help you choose investments. For instance, it won’t help you assess whether or not you are looking at a great company and only to a very limited degree will it indicate whether a security is cheap or dear (see the ‘Ratings’ worksheet). What it will do is help you manage the investments you do have. And that’s vital – if you did not know that before reading this, you should now.

Think of the software as a prototype. It works just fine, but only if you input the data and know your way around Excel. That’s good because it helps you mull over the state of your portfolio, but, obviously, it’s not cutting-edge software and could work much more smoothly. The extent to which that happens depends on readers’ response. Tell us what you think and how the product could be improved.

Not a subscriber? Click here to view all of our subscription packages.