Annualized rate of return measures the compound annual growth rate of an investment and can be tricky to calculate by hand. Users can calculate the annualized rate of return in Excel using the "XIRR" formula. To perform the calculation, you must have the Analysis ToolPak add-in installed.
Set up the Calculation
Type each return or investment you received in descending order in column one. In the adjacent column, type the corresponding year in which you received the return. For example, say you initially invested $200 in 2009, received $40 in 2010 and $50 in 2011. In an Excel spreadsheet, type "-$200" in cell A1, "$40" in cell A2 and "$50" in A3, "January 1, 2009" in cell B1, "January 1, 2010" in cell B2, and "January 1, 2011" in cell B3.
Find the Annualized Rate of Return
In an open cell, type "=XIRR(A1:A3, B1:B3)." The resulting figure is the compound annual growth rate, or annualized rate of return, for the period. If the formula results in "#NAME?," you probably don't have the Analysis Toolpak installed. To install the Toolpak, navigate to the Tools menu, click Add-Ins and select Analysis ToolPak.