 |
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.
I have always been looking for a calculator which will calculate my time weighted returns.
Thanks for sharing!
Thanks for the formula – this will come in handy.
I’ve been keeping track of my investments as part of a total annual return. I subtract the beginning value on Jan 1st from the ending value of the portfolio on Dec 31st, and then factor out all deposits (subtract them) and withdrawals (add them back) made throughout the year. My total % return is that number divided by the beginning balance.
Obviously, your approach will paint a more accurate picture – thanks for sharing.
I also found problems with Quicken’s calculations especially with my investments in US dollars. Good luck trying to get Quicken to calculate the performance of a USD investment in Canadian dollar terms.
It would be nice if there was a general way to figure out the performance of each individual investment INCLUDING dividends and interest. Unfortunately I’ve thought about this and I don’t think there is a general way to do it.
Very helpful tool, thank you. And just an FYI, if it keeps coming up with #NAME? in your formula, it means you do not have the Analysis Toolpak add-in. Simply go to Tools->Add-Ins, and select the box for the Analysis TookPak. It will install it, then restart Excel and you’re good to go.
http://office.microsoft.com/en-us/excel/HP052093411033.aspx
TDG,
Not including dividends or interest is good only when it is reinvested back into the stocks (and gets accounted in final value). In cases where they are not ploughed back, then dividends and interests need to be included. Is my understanding correct ?
Warm Regards,
DT
Can I keep cash in this calculation ? So if I sell a stock that goes into cash that is not a withdrawal, right? And if I use cash to buy another stock that is not a contribution?