How to Calculate the Average Annual Rate of Return in Excel

agawa288/iStock/Getty Images

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.



About the Author

Based in San Diego, Calif., Madison Garcia is a writer specializing in business topics. Garcia received her Master of Science in accountancy from San Diego State University.

Photo Credits

  • agawa288/iStock/Getty Images