As an investor you may always wonder how much your investment return is. It will easy to figure out what your investment returns are if you never add or withdraw from your accounts. Most of investors add and/or withdraw funds from their account consistently. In this case, how to calculate investment returns?
Microsoft Excel provides a function called XIRR, which can help you to figure out your returns. XIRR returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. This is a link to an Excel file which you can download (linked again later) and modify to calculate your own investment returns. Here is some simple guides for you to use the file.
What information will you need to calculate your investment return for a specific period?
1. Initial date and the investment balance on that date
2. The values and dates of deposits and withdraws
3. The date of the end of the period and the balance on that date.
In the example you can download here, we want to calculate the investment returns from Jan. 1, 2009 to Sept. 30, 2010. The initial balance on Jan. 1, 2009 is $25,453. There are non-periodic deposits and withdraws within the period. The balance on Sept. 30, 2010 is $68,654. The data in Excel is listed as this:
The investment return can be calculated with the XIRR function as this: =XIRR(B2:B16,C2:C16,0.1), which is equal to: 17.74% in this example. That is the annualized average return of your investment.
One thing we would like to point out is the final balance. A negative number must be used for the final balance, so it is like that you withdraw all funds at the end of the period. Otherwise XIRR will not calculate correctly.
Again this is the link to an Excel file which you can download and calculate your investment returns.
GuruFocus will develop an online version for you to do the calculation and generate charts and comparison tables. In the meantime, if you are not a Premium Member of ours, we invite you for a 7-day Free Trial.
Good luck with your investing!
Microsoft Excel provides a function called XIRR, which can help you to figure out your returns. XIRR returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. This is a link to an Excel file which you can download (linked again later) and modify to calculate your own investment returns. Here is some simple guides for you to use the file.
What information will you need to calculate your investment return for a specific period?
1. Initial date and the investment balance on that date
2. The values and dates of deposits and withdraws
3. The date of the end of the period and the balance on that date.
In the example you can download here, we want to calculate the investment returns from Jan. 1, 2009 to Sept. 30, 2010. The initial balance on Jan. 1, 2009 is $25,453. There are non-periodic deposits and withdraws within the period. The balance on Sept. 30, 2010 is $68,654. The data in Excel is listed as this:
Dollar Amount | Dates | |
Initial Balance and Date | 25453 | 1/1/2009 |
These are Deposits and withdraws. Use position numbers of deposits and Negative numbers for withdraws. Add Rows if needed | 5000 | 5/26/2009 |
4500 | 5/30/2009 | |
5000 | 6/7/2009 | |
5500 | 7/23/2009 | |
(6000) | 8/25/2009 | |
4000 | 11/17/2009 | |
2000 | 12/16/2009 | |
8000 | 12/19/2009 | |
8000 | 12/31/2009 | |
1000 | 1/7/2010 | |
500 | 1/26/2010 | |
(10000) | 6/29/2010 | |
1000 | 9/18/2010 | |
Final Balance and Date. * You must use negative value for yoru balance, just like you withdraw all the fund at the end of the period. | -68,654 | 9/30/2010 |
The investment return can be calculated with the XIRR function as this: =XIRR(B2:B16,C2:C16,0.1), which is equal to: 17.74% in this example. That is the annualized average return of your investment.
One thing we would like to point out is the final balance. A negative number must be used for the final balance, so it is like that you withdraw all funds at the end of the period. Otherwise XIRR will not calculate correctly.
Again this is the link to an Excel file which you can download and calculate your investment returns.
GuruFocus will develop an online version for you to do the calculation and generate charts and comparison tables. In the meantime, if you are not a Premium Member of ours, we invite you for a 7-day Free Trial.
Good luck with your investing!