How to Calculate the Average Annual Rate of Return in Excel

Whether you're doing a what-if analysis to determine how to invest your company's money or you're looking backwards to see how an investment performed, calculating an average annual rate of return lets you do apples-to-apples comparison against different potential investments with different lives. Because of compounding, it isn't as simple as just taking your total return and dividing it by the number of years, though. Excel's XIRR function not only calculates your average annual return, but also lets you do it with cash flows that come at irregular times.

Open Excel by double-clicking its icon. Press "File," then "New," then "Blank workbook" to open a new spreadsheet if it doesn't open with one by default.

Put all of your cash flows in column A. Enter money you spend as a positive number and money that you receive as a negative number.

Enter the date of each cash flow in the same row as the cash flow in column B using Excel's DATE function. For instance, if your first activity was to spend $100,000 on January 1, 2012, you would put "100000" in cell A1 and "=DATE(2012,1,1)" in cell B1. If your next cash flow was the receipt of $25,000 cash flow on April 15, 2013, you would put "-25000" in cell B1 and "=DATE(2013,4,15) in cell B2.

Below your two tables of cash flows and dates, type "=XIRR(" without the quotation marks. Use your mouse pointer to select the cash flows so that the range fills into the formula, type a comma, use your mouse pointer again to select the dates, type a close parenthesis, and press "Enter." For example, if you had five rows of cash flows and dates, starting in cell A1, your command should say "=XIRR(A1:A5,B1:B5)." The cell shows the average annual rate of return after Excel finishes calculating it.

Click the cell, then click the "%" button in the "Number" section of the "Home" toolbar. Excel converts the decimal return to a percentage.