To calculate your portfolio return you enter your cash flows like so.
Use all positive values for money being added to the portfolio and negative values for money actually leaving the portfolio. For the last cash flow, use a negative number of your current portfolio value. Dividends or sales proceeds as well as commission or other fees are not accounted for since they are taken care of in the final portfolio value. The XIRR function looks like this: =XIRR(Cash Flows, Dates, Guess of Return) or in this case =XIRR(B1:B7,A1:A7,0.1). I've assumed a 10% starting guess, you don't have to actually put in a guess value in most situations. Our annualized return is then calculated through Excel to be 21.49%. Great. What exactly does that mean? If you're not sure then you can always run a check on it by using that return to calculate the value of each cash flow. The formula to check is =Cash Flow1 * (1+R)^(End Date - Date 1) but since our return from XIRR is an annual return we have to convert the date into a annual value. The correct formula becomes =Cash Flow1 * (1+R)^[(End Date - Date 1)/365] or as in this spreadsheet =B1 * (1+F2)^[(A7 - A1)/365]. You then proceed to calculate the value for each cash flow and then sum them all up. The resulting sum should then add to your current portfolio value of $35,000.
Here is the same spreadsheet with the formulas shown.
Here's the returns that I've gotten across my different investment accounts as of close of trading on September 7, 2012, returns are calculated through Sunday, September 9, 2012.
|Roth IRA 1||11.16%|
|Roth IRA 2||6.14%|
My Rollover IRA has an awesome annual return at just under 19%. That's thanks to 66% of the money invested in it being put to work in April 2009 just after the March 2009 bottom. The last of my Rollover IRA money was invested in August 2010. My Roth IRA is doing well with a 11.16% annual return. I contributed money to my Roth IRA between November 2010 and December 2011 before I then decided to embark on investing on my own while focusing on dividend growth stocks. My 401k has given me a solid 11.27% return as well. I have invested in my 401k since August 2010 and continue to contribute 10% plus the 5% company match and 4% profit sharing that is paid out in February each year. My 401k, Rollover IRA and Roth IRA are currently invested in mutual funds. I have a lot of tax issues to check on but the plan is to move my Rollover IRA and Roth IRA 1 to a brokerage account and start purchasing more DG stocks. Depending on the tax consequences I might also move my Rollover IRA to a Roth IRA as long as it doesn't push us into the next tax bracket.
My Roth IRA 2 is clearly the standout laggard so far amassing a quite pitiful just a 6.14% annual return thus far. I only just started the Roth IRA in November 2011 but the market in general has returned much better over that time frame. The lower return is due to my purchase of 2 BAC calls that have gone bad and drug down the portfolio's return. There's still time for them to turn around since they don't expire until January 2014. If I had just left the money spent on the calls in cash my return thus far would be 14.51% which is much more inline with the rest of the returns.
The FI Portfolio is the one that I'm most concerned about since my plan is to live off the dividends that it spins off. I have achieved a 32.42% annual return for my FI Portfolio so far. I would much prefer to see the return be lower right now since I'm still accumulating shares and the lower the share price the more shares I can buy with new capital and dividend reinvestment. A big reason that the FI portfolio is so much further ahead is because I have my ESPP shares included which I get to buy at a 15% discount semi-annually. That juices up the returns pretty quickly. If you back out the ESPP shares then my annualized return is still leading the way at a 19.13% mark.
My hope is to compile returns of the S&P500 or SPY using the same corresponding dates and cash values to see how my returns compare to those. Now if only I can keep these returns up for several years and I could be set much earlier than planned. Wishful thinking I guess.
*Edited 9/13/12 - Please don't confuse the return from the XIRR function as a ROI value. The XIRR function takes into account the time value of money. Whereas the ROI is the actual return. IRR is still a great tool to use because it allows you to compare different investment opportunities to each other on a time basis. If both investments can return a ROI of 10% but one takes 5 years and the other 10 years, obviously the 5 year is the better investment because you the return occurs over a shorter time period.