 |
I use Microsoft Money to manage my portfolio. I find it very useful to enter and keep track of all the various transactions that occur in a dividend investing portfolio. Through the years I have relied upon the software program to calculate the various returns my portfolio has achieved. However, when I recently upgraded to Microsoft Money Plus Deluxe I saw something strange happen – both my annualized return and year-to-date numbers changed. Upon some further investigation I have found that there have been various issues through the years with the way Money calculates returns. This did not leave a warm fuzzy feeling in my gut! An investor needs to know exactly how they are doing compared to the market to effectively judge how their strategy is performing.
My lost confidence in the program’s ability to calculate returns has lead me to take things into my own hands and set up a spreadsheet to help me track my portfolio’s annualized return using Excel. This way I will know have two systems going to gauge my returns, and frankly I will trust my own system more because I will know exactly what it is calculating and where the numbers are coming from. Even after a few hours of research I still cannot figure out how Money does its calculations.
Here is a similar version of the spreadsheet I set up to calculate my return. It is published via Google Docs and this is the first time I have used it so I hope it works for you…
A couple of things to point out:
1. The formula for calculating the return is xirr(C6:C9,D6:D9), where C6:C9 equals the values and D6:D9 equals the dates.
2. You do not enter portfolio transactions such as dividends earned or interest on cash. These transactions are part of the current value. Only enter transactions where you personally deposit or withdrawal money from your portfolio.
3. Current Portfolio Value is entered as a negative number – the spreadsheet assumes that on the current date you liquidate your portfolio.
The best way to learn about this process is to play with it yourself. By calculating your own return you will know exactly how your portfolio is performing, which can provide a great deal of value to the individual investor.