Okay, I don't see my original post, so I guess I'll have to post it again. Performance tab: Net return seems to be counting contributions as investment return. I have put contributions in transactions as investments. See spreadsheet. Graph tab: "total value" cell displays as ######. Apparently the total value is too big to display in the cell. Graph tab: there are two predefined categories which have a value of zero. They are still showing up on the graph, and they are crammed together (which looks very messy). How can I fix this?
The return calculations take into account of investments and divestment over the reporting period by adjusting the start value for amount and timing accordingly under the 'money-weighted' return.
Contributions should be added as positive investments and increase the start value thereby reducing the net return over the period.
The graph can be unprotected using the password released on purchase in order to modify cell sizes to accommodate large numbers, remove categories from the chart data source and other customizations such as styles.
In the original post you can see in the spreadsheet that start value + capital invested = current valuation. Of the capital invested, only 2.25 is return (reinvested mmkt dividends). The rest is new principal. Therefore, the net return over period should be microscopic, not 36%. Here are the transactions which make up the performance data in my original post: Excel Spreadsheet:
The difference is due to the timing of the investment transactions. We do not know the actual start and end dates of the reporting period; however it seems like the bulk on investment was made near the end. The start value is adjusted by the weighted average of investment amount and time between the start date and investment dates. In this case, under the money weighted return, the principle value was lower for the majority of the reporting period (until the capital injection) resulting in a higher return calculation for the entire period.
This is a money market yielding 0.15%. Bulk of the money was invested up front ($6789 out of $9969 total). $2318 came in right at the end of the reporting period (1/20/2010 thru 3/31/10), but that represents less than a third of the total. Regardless, the actual interest earned on the mmkt was only $2.25.
The calculation is based on the money weighted return which is commonly used to account for fluctuations of the principle over the reporting period.
To understand the theory behind the formula you can refer to the user guides here. You can also unprotect the template to view the components of the formula or modify it to create a straight return and ignoring the timing. If you are still using the trial version and do not yet have the unprotection password, you can reply to the notification email of this post and we will send it through to you.
In layman's terms: the money-weighted rate of return incorporates the size and timing of cash flows, so as to effectively measure for returns on a portfolio.
