Return Calculation Comparison
An example of the spreadsheet used to perform the return calculations in the “Calculating Investment Returns” post is shown here:
The fields in blue can be edited to compare how the three return methods perform with various starting balances, monthly contributions, and monthly returns. The full spreadsheet can be viewed at this link. If you wish to edit the spreadsheet, just go to “File” and click on “Create a Copy”, you can then edit the fields in blue to compare the return calculation methods for any scenario you wish. The S&P500 returns used to calculate the post examples are included in the second tab of the spreadsheet.
For more detail on mechanics of the three types of return calculations, try the following links:
Money-Weighted Returns (also known as the Dollar Weighted or Internal Rate of Return)
- Wikipedia Entry
- Calculating Money Weighted Return in Excel (the same commands can be used in Google Docs)
Time-Weighted Returns
Modified-Dietz Returns
- Wikipedia Entry
- USA Today Article – This article has a great description of how to calculate the Modified-Dietz Return in Excel or Google Docs, but it incorrectly states that the Modified-Dietz is a time-weighted return.
Hi
Thanks for posting the Spreadsheet and explaing Portfolio Performance. I like what you did in your Spreadsheet which I’ve downloaded and use a bit.
I’m hoping to be able to connect with you to calculate my True Time Weighted Rate of Return (TWRR) using the Daily Valuation method.
I hav a sample of my data from a Brokerage house in a Spreadsheet. I’d like to be able to calculate the TWRR for each period. The data as shown represents a typical IRA or 401K contributions.
I found various scenarios on the web where Time Weighted Rate of Return (TWRR) using the Daily Valuation method is calculated; but none that I have seen really fit my data. And I also find the TWRR formula most confusing and intimidating.
Can I send you my Spreadsheet so you can take a stab at it? If so, let me know how I can get it to you.
Thanks.