# How to Calculate Your Investment Returns Using Excel

gurufocus
Oct 04, 2010
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:

 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.

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.