Chris Hughes

# How to Find Out Your Annualized Returns of Investment

January 25, 2006

by Chris Hughes

If you only bought one stock a year and bought it on the 1 st of the year and sold it on the 1 st the next year, it'd be really easy to track your gains using 4 th grade math (or 3 rd grade math if you went to a really foofy pre-school). But since we don't trade that way, simple math doesn't work. Calculating gains may sound easy until you start to think about all the factors involved:

- How do you value stocks you still own, compared to stocks you sold six months ago, but must still include in your gain calculations?

- How do you weight 10 shares of BRKA to 100 shares of MSFT?

- How do you weight a stock you owned for 18 months to a stock you've owned for two weeks?

- How do you account for dividends?

- Why is my head starting to hurt?

Actually there is a very easy way to tie all of this together using two things:

1. A common denominator: Cash (also known as moolah, dinero, lucre, etc. etc. etc.)

2. An excel formula that takes care of the complicated, and tedious calculation: XIRR

Cash is the common denominator to all the head hurting factors listed above. It's easy to weight 10 shares of BRKB to 100 shares of MSFT because of the obvious difference in cash required to purchase them, but what about time weighting?

XIRR takes care of the time weighting for you once you have broken all the above into cash. XIRR is an add-in Excel function that returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

Let's run through an example:

 Date Type # of Shares Symbol Price Cash Jan 15, 2004 Buy 10 BRKB 2,880.00 28,800.00 Mar 1, 2005 Buy 400 APA 61.47 24,588.00 Aug 8, 2005 Buy 100 MSFT 27.80 2,780.00 Aug 23, 2005 Div 400 APA .057 23.00 Sep 5 , 2005 Div 100 MSFT .08 8.00 Nov 23, 2005 Div 400 APA .10 40.00 Dec 19, 2005 Sell 5 BRKA 2,966.00 14,830.00

Jan 25 2006 Value of current holdings: 45,548.00

(5 BRKB @2947, 100 MSFT @26.29, 400 APA @70.46)

To calculate your annualized gains (or losses) from 1/15/2004 through 1/25/2006, all you need is the date of each transaction and the cash flows. Cash flows out (to buy something, or to pay a fee) are negative, and cash flows in (sales and dividends) are positive:

 A B 1 1/15/2004 -28,800.00 2 3/1/2005 -24,588.00 3 8/8/2005 -2,780.00 4 8/23/2005 23.00 5 9/5/2005 8.00 6 11/23/2005 40.00 7 12/19/2005 14,830.00 8 1/25/2006 45,548.00

The formula you would use is:

=XIRR(B1:B8,A1:A8)

and your result would be 0.0522, or just over 5%, hmmmmm, at least you didn't lose any money, right?

XIRR isn't installed by default on Excel. To install it, have your Office CD ready, launch Excel, and go to Tools - Add-Ins and select "Analysis ToolPak". It will prompt you for the Office CD and install XIRR.

Of course there are other factors to count into your calculation depending on what you are trying to find. If you want to calculate like the funds do, count your uninvested cash into your calculation and make it count against you. Or if you are an active trader, take the short term capital gains tax out of your gain and see what your real returns are.

Have fun!

 Currently 3.20/512345 Rating: 3.2/5 (5 votes)

Vooch - 11 years ago    Report SPAM
Great article!

I even own all the stocks mentioned in his example: APA BRKB and MSFT

hehe