How to Calculate Your Investment Returns Using Excel

Author's Avatar
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.


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!