Excel XIRR Function

By Ravindra Bagale · Last updated: 31 October 2026

Purpose

IRR when cash flows are not equally spaced — uses real dates.

Return value

The annualised IRR for dated cash flows.

Syntax

Arguments

Version

Excel. LibreOffice/Sheets: XIRR/XNPV available under same or similar names.

How to use — Why → What → How

Why: Projects rarely pay on perfect period grids. XIRR uses actual dates. Dated returns for Kochi project draws and New York investor schedules.

What: XIRR pairs each cash flow with a date. Result is an annualised rate. In practice, keep the rate and nper in the same unit — annual rate with monthly payments means rate/12 and nper×12. Mixing units is the most common silent error: Excel returns a number that looks plausible but is wrong for the loan. Sign convention: money you receive is positive; money you pay out is negative. For a borrower, PV is positive and PMT is negative. Build amortization with PMT for the installment, IPMT/PPMT for each period’s split, and CUMIPMT/CUMPRINC for year totals.

How — numbered steps:

Download practice file: practice-xirr.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

Common errors and fixes

Notes and tips

• Revisit the XIRR errors table after the quiz.
• Build example 1 from memory on a blank sheet.
• Check number vs text if a result looks wrong.
• Teach one tip in Marathi or Hindi this week.
• Keep currency symbols consistent inside one report column (practice mixes ₹ $ £ €).
• Drill: build a four-row dated cash-flow XIRR and verify in Excel. Uncertain: sample % are illustrative.
• XIRR vs IRR: use XIRR whenever gaps are uneven — common in real projects.
• Fictional sample data only — names and amounts are for learning.
• Print the XIRR revision card for one week of real worksheets.
• Ask juniors to predict before they press Enter.
• Track your top mistake with this function monthly.
• Dated returns for Kochi project draws and New York investor schedules.
• Interview tip: explain the business question before the formula.
• Classroom drill: invent three brand-new formulas from your city without the page open.
• If a result looks wrong, check blanks vs zeros before rewriting.
• When Excel versions differ, note the LibreOffice / Sheets fallback in a cell comment.
• Prefer clear ranges over whole-column refs on huge sheets if speed matters.
• Document units (₹, kg, days) in the header so totals stay honest.
• Print the revision card for XIRR for one week of real worksheets.
• When helping a junior, ask them to predict before Enter.
• Share one tip with a classmate in Marathi or Hindi.
• Keep currency symbols consistent inside a single report column.
• When Excel versions differ, write a LibreOffice / Sheets fallback under a comment.
• Keep rate and nper units matched (monthly with monthly) or the EMI will silently mislead.
• Document whether the sheet is borrower’s view (PV>0 → PMT<0) or lender’s view.
• Round displayed EMI with ROUND(...,2) for client letters; keep full precision in calc columns.
• For RATE/IRR #NUM!, supply a guess near 0.1 when cash-flow signs flip more than once.
• Pair NPV with a separate time-0 outflow cell — do not bury the initial invest inside values[1] by accident.
• Fictional EMI and cash-flow figures only — never paste real customer loan data into class files.
• Compare your EMI to a bank quote: if off by a lot, you forgot /12; if off by paisa, rounding.
• Teach one junior the sign convention in Marathi or Hindi this week — teaching locks it.

In real work

Drill: build a four-row dated cash-flow XIRR and verify in Excel. Uncertain: sample % are illustrative. Dated returns for Kochi project draws and New York investor schedules. Classroom drill: write three brand-new XIRR formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose and watch the error; recovering from that break is half of interview readiness. Always verify with a known case before trusting a long chain. Keep a personal notebook of the one mistake you make most often with this function; review it monthly. Excel’s financial engine uses the same closed-form annuity formulas taught in CFA Level I. PMT isolates the constant installment; IPMT the interest slice; PPMT the principal residual — and PMT = IPMT + PPMT for each period when FV and type match. NPV discounts values starting at period 1, so keep the time-0 investment outside the values array. XNPV/XIRR need actual dates; RRI inverts growth between two pots; EFFECT/NOMINAL bridge stated APR and true EAR under m compounding periods. Bank EMI sheets often round each installment to two decimals — expect ₹0.01–₹0.05 drift versus raw Excel PMT on long tenures.

How it compares

XIRR vs IRR: use XIRR whenever gaps are uneven — common in real projects.

Interview corner

Needs dates?

Yes.

vs IRR?

Calendar aware.

Uncertain?

Spot-check illustrative %.

Related functions

IRR, XNPV, NPV

Practice tasks (10)

1. Task 1. XIRR dated project.
   Kochi.
2. Task 2. Compare to IRR.
   Skew.
3. Task 3. Format %.
   Display.
4. Task 4. Force #NUM!.
   Bad.
5. Task 5. Use guess.
   0.1.
6. Task 6. Interview.
   Talk.
7. Task 7. Teach.
   Teach.
8. Task 8. $ schedule.
   NY.
9. Task 9. Fill.
   Fill.
10. Task 10. Verify in Excel.
    Check — uncertain illustrative %.

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.