XIRR Function in Excel: Real Return on SIPs and Irregular Cash Flows

XIRR Function in Excel: Real Return on SIPs and Irregular Cash Flows featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

FinancialLevel: ExpertAvailable in: Excel 2007+

In this article
  1. Syntax
  2. Examples
  3. Example 1
  4. Common errors and fixes
  5. XIRR in practice
  6. Where you will use it
  7. Worked example
  8. Mistakes people make
  9. Related functions
  10. Continue learning

XIRR returns the annualised return for cash flows on specific dates — the right way to measure SIP and mutual-fund returns.

Advertisement

Syntax

=XIRR(values, dates, [guess])
Argument What it means
values Investments negative, redemptions/current value positive.
dates Matching dates.

Examples

Example 1

=XIRR(B2:B26, A2:A26)

Monthly SIP instalments as negatives and today’s value as the last positive entry.

Common errors and fixes

You see Why, and the fix
#NUM! All values have the same sign — you need at least one negative and one positive.

XIRR in practice

Where you will use it

  • Real return of a SIP with dates
  • Investments with irregular dates
  • Mutual fund returns

Worked example

CaseFormulaResult
1=TEXT(XIRR(INDEX($H$1:$K$3,A2,0),$H$5:$K$5),"0.00%")12.69%
2=TEXT(XIRR(INDEX($H$1:$K$3,A3,0),$H$5:$K$5),"0.00%")15.00%
3=TEXT(XIRR(INDEX($H$1:$K$3,A4,0),$H$5:$K$5),"0.00%")26.51%

Results calculated in Excel.

Mistakes people make

MistakeWhat to do instead
First flowMust be negative
Date orderFirst date must be the earliest

IRR · XNPV · RATE

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All Financial guides →
RECEIVED Function in Excel: Amount Received at MaturityFinancialLevel: ExpertAvailable in: Excel 2007+RECEIVED calculates what you get back at maturity for a…PDURATION Function in Excel: Years Needed to Reach a TargetPDURATION returns how many periods an investment needs to grow to a target at…INTRATE Function in Excel: Interest Rate of a Fully Invested SecurityINTRATE returns the annualised return when you invest an amount and receive a larger…NPER Function in Excel: How Many Payments Until Paid OffNPER returns the number of periods needed to repay a loan or reach a…

📎 Practice files for this article

  • 📗
    Practice workbook: Financial: EMI, SIP, NPV, IRR, depreciation23 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
    ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong