In Excel, what is the function or formula for calculating annualized return for an investment?

I purchase $10,000 of a stock on 1/1/05 and it's valued at $10,500 on 1/1/06. That's a $500 increase for a 5% gain (500/10,000). 1 year exactly is pretty easy to calculate; so is 1/2 year, 1 1/2 years or 2 years. But what is the annual increase if I've held it for 75 days, 400 days, 749 days? I'm looking for the function/formula to enter into Excel that would give me an annual interest rate.

Calculate a compound annual growth rate (CAGR)

A compound annual growth rate (CAGR) measures the rate of return for an investment 鈥?such as a mutual fund or bond 鈥?over an investment period, such as 5 or 10 years. The CAGR is also called a "smoothed" rate of return because it measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis. To calculate a CAGR, use the XIRR function.


If the XIRR function is not available and returns the #NAME? error, install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak check box, and then click OK.
If necessary, follow the instructions in the setup program.


Example
The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.



Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
6
A B
Values Dates
-10,000 January 1, 2008
2,750 March 1, 2008
4,250 October 30, 2008
3,250 February 15, 2009
2,750 April 1, 2009
Formula Description (Result)
=XIRR(A2:A6,B2:B6) The compound annual growth rate (0.373362535 or 37.34%)


Notes

When you compare the CAGRs of different investments, make sure that each rate is calculated over the same investment period.
To view the number as a percentage, select the cell and then click Cells on the Format menu. Click the Number tab, and then click Percentage in the Category box.

Function details
XIRR