How to Calculate XIRR

Calculating the Formula for XIRR

XIRR is a function in Microsoft Excel to determine the internal rate of return with scattered cash flows. IRR determines internal rate of return for periodic cash flows. XIRR requires three sets of data: values, dates and guess. Value is a cash flow with a corresponding date. Date is when a cash flow arrives. Guess is a guess of the internal rate of return. This article describes a hypothetical spreadsheet; the numbers and cell addresses will differ based on each individual spreadsheet.

Things You'll Need

• Microsoft Excel

Instructions

1. Open Excel and type:

   =XIRR(

2. Highlight the value set, then type a comma. The formula will look something like:

   =XIRR(b2:b10,

3. Highlight the date set, then type a comma. The formula will look something like:

   =XIRR(b2:b10,a2:a10,

4. Type a general guess of where the internal rate of return should be, then close the parenthesis. The final formula will look something like this:

   =XIRR(b2:b10,a2:a10,4)

References

• Microsoft: XIRR - Excel
• Experigot: How To Use XIRR In Excel To Calculate Annualized Returns