How to Calculate the Average Annual Rate of Return in Excel. ... Enter the XIRR function at the bottom of column A, as follows:=XIRR ... Click "OK" to save the changes. Advertisement.