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 ReturnTo 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.
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.
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.
Calculating Multiple Year Portfolio ReturnIf 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.