A Method to Calculate Historic Portfolio Performance

I find that in life if you want to succeed at something you must have a plan.  This plan in its most basic form will include a number of goals and a timeline detailing when you intend to meet those goals.  Once you have that plan in place you must then track progress against the plan and should you deviate you should put actions in place to get you back on track.  Personal Finance is no different.

Within the Invest Wisely portion of my strategy I have two distinct goals for my Low Charge Portfolio.  The first is to beat a Benchmark that I have set myself.  Everybody’s Benchmark will of course be different.  It could be to beat the FTSE100, the FTSE 250, the Barclays UK Government Inflation-Linked Float Adjusted Bond Index, a combination of these or something completely different.  Remember when you set your Benchmark you must ensure it has a similar risk profile to your investments and contains investments that are as close to yours as possible.  What good is it to spend time developing a Low Charge Portfolio and Strategy if you can’t at least match (for those of us where personal finance is a hobby) or beat a simple Benchmark (for those of us where personal finance is a hobby or chore).  If we can’t meet this goal then we’re probably better off just buying a Vanguard LifeStrategy Fund.

The second aim is for my portfolio to over the long term meet or exceed a Real Total Return goal that I have set myself.  This is defined as over the course of my investing career the sum of the capital gains within my portfolio and the dividends paid must exceed UK Inflation by a specific amount.  In the interests of full transparency I must point out that I am current not meeting my goal however by tracking progress I at least know why I am missing and have planned actions to recover.

Let’s look at the method I use to calculate the historic performance of my portfolio assuming I want to look at Total Return.  Calculating Real Total Return is then just a simple matter of subtracting your chosen inflation measure from the calculations for the period concerned.

Calculating Year to Date and Yearly Total Portfolio Return

To make this calculation you only need 4 things:

• Access to the XIRR function within Microsoft Excel.  This function is not typically part of the standard Excel install so if you have Excel and can’t find XIRR you may need to install what is called the Analysis Toolpak.  As every version of Excel is slightly different just type “Install Analysis Toolpak” into Excel’s Help and you should get the guidance you need for your version.
• The start date for the period you are interested in analysing and the value of your portfolio on that date.  This must be the earliest date entered into Excel.
• The end date for the period you are interested in analysing and the value of your portfolio on that date.  When running the calculation this value should be entered as a negative number.
• Any cashflows into or out of your portfolio.  Note that because I am calculating Total Return all of my dividends have been reinvested in my portfolio and so I don’t need to include any dividends within the cashflows.  Cash into the portfolio should be entered as a positive number and cash out should be entered as a negative number.

A worked example is shown in the image below.

Click to enlarge

A couple of important points:

• The first column entry into the XIRR formula is the cashflows, the second column is the dates and the third piece of data you have to enter is a guess as to what the return might be.
• It doesn’t matter what the period you are using is, whether 1 month, 1 year or 10 years, the return will always be an annualised return.  So in the example above, which is only a 3 month period, were the year continue at the current rate of return then you’d see a return of 64.7%.  You have not achieved a 64.7% return over that 3 month period.

At this point if you only want to calculate your total return for a whole year, say 31 December 2011 to 31 December 2012, then you are done.  If however you would like to know what your return is year to date then you have a little more work to do.  I do this because I don’t want to wait a whole year to understand if I’m going off plan.  I update my financial position weekly and then update my portfolio performance on the first Saturday of every month.   To do this you now need to calculate your Personal Rate of Return (PRR) which is represented by the formula:

Let’s continue with our worked example and now calculate the Personal Rate of Return.

Click to enlarge

Another important point:

• Note how even though we are looking at a 3 month period the PRR is not equal to the XIRR value divided by 4.

So that’s how to calculate year to date and yearly performance.  Let’s move onto calculating performance over a number of years.

Calculating Multiple Year Portfolio Return

If you want to Keep It Simple Stupid then you might just stay with using the XIRR function.  This will continue to give you an annualised return as you move into multiple years.  I don’t use this because I want to plot my progress year on year as I did in this post.

To plot year on year I setup another simple spreadsheet where I calculate the Portfolio Total Growth from my Starting Year to the Current Year.  This is calculated by starting with a nominal value in Year 0, which could be a value of 1 if you wanted to create an index and then multiplying the previous year’s value by the XIRR for the next year of interest.  This continues until you cover all years of interest.  From this point you can then calculate the Compound Annual Growth Rate (CAGR) by taking the very first value in your table, the very last value in your table and the number of years represented by your table.  This is represented by the formula:

A worked example is shown in the image below.

Click to enlarge

In conclusion to track my own Portfolios progress against plan I use these 3 main formulas – XIRR, PRR and CAGR.   By then bringing a Benchmark into the analysis and adjusting for inflation I am able to understand if I am really Investing Wisely.

As always do your own research.