XIRR calculation in excel is malfunctioning

Question

I am using excel 365 and 2021 professional, where I had sheet to calculate IRR and XIRR, the IRR can work well and provide me the true value, however XIRR (cell I10) cannot function, and it returns value of zero.

I opened this same file on Google sheet, and WPS, they can both calculate and provide me the correct value of XIRR (cell I10).

can Microsoft please look into this and explain or fix the issue within excel?

Thanks,

Answer 1

If the XIRR function in Excel is returning a value of zero while the IRR function works correctly, there are a few potential reasons for this issue:

1. Cash Flow and Dates: Ensure that your cash flow values (the first argument of XIRR) contain at least one positive and one negative value. If this condition is not met, XIRR will return an error or zero. Additionally, check that the dates (the second argument) correspond correctly to the cash flows and that they are in the correct format.
2. Guess Parameter: If you are using the optional guess parameter (the third argument), try omitting it or using a different value. The default guess is 0.1 (10 percent). If the guess is too far from the actual result, it may lead to incorrect calculations.
3. Date Format: Make sure that the dates are not entered as text. You can use the DATE function to ensure they are recognized as valid dates. For example, use DATE(2021, 1, 1) for January 1, 2021.
4. Excel Version Issues: Since you mentioned that the same file works in Google Sheets and WPS, there might be a compatibility issue or a bug in the specific version of Excel you are using. Ensure that your Excel is updated to the latest version.
5. Error Handling: If there are any errors in the data (like invalid dates or mismatched lengths of cash flows and dates), XIRR may return an error or zero. Check for any inconsistencies in your data.

If you have checked all these factors and the issue persists, consider reaching out to Microsoft support for further assistance, as it may be a specific issue with your Excel installation.

---

References:

• WorksheetFunction.Xirr(Object, Object, Object) Method
• IWorksheetFunction.Xirr(Object, Object, Object) Method

Answer 2

this is my sheet, where the first 4 months have zero for principal, balance....may this be the issue for XIRR result? however, I opened this file in Google sheet, and WPS, both of them have no problem to treat this sheet and provide me the correct XIRR value.

Why...?

Answer 3

Hi, Bella Li

Welcome to Microsoft Q&A forum.

Thanks for your question. What you're encountering is a data/format issue rather than a product bug. Here are some workarounds you can try to resolve it:

Fix XIRR returning 0% (Excel 365 / 2021)

What’s likely happening: Excel’s XIRR is iterative and very sensitive to how dates and the values/dates ranges are passed. If dates are text, ranges are misaligned, or errors are being masked (e.g., by IFERROR), Excel can converge to 0 or show #NUM! - which sometimes gets converted to 0% by a wrapper formula or cell formatting. Zeros in early months are fine; XIRR only requires at least one negative and one positive cash flow.

Quick, single solution (Excel on Windows/Mac/Web)

Confirm the formula:

• In I10, select the cell, press F2, and make sure it is exactly XIRR(values_range, dates_range, [guess]) without wrappers like IFERROR(...,0) or N(...). If you do see IFERROR, temporarily remove it to surface the real error.

Ensure dates are true Excel dates:

• Select the Date column > change format to Short Date.
• If they don’t flip visually, they’re text. Convert with =DATEVALUE(A2) (then copy-paste Values). Or rebuild with =DATE(2025,1,31) style formulas. Text dates cause #VALUE!/#NUM! which wrappers often turn into 0.

Match range lengths exactly:

• values_range and dates_range must have the same number of rows - no extra blank cells on either side and no headers included. Misalignment yields #NUM!.

Check sign pattern:

• Verify there’s ≥1 negative and ≥1 positive cash flow (your last row looks positive and some mid-rows are negative - good). If all are ≥0 or ≤0, XIRR can’t solve.

Remove blanks/errors; keep zeros:

• Blanks or errors inside the ranges can derail the solver. Replace blanks with 0. (Zeros are allowed and are not the root cause.)

Try a different guess:

• Add a third argument, e.g., =XIRR(values, dates, 0.1) or 0.2 if returns are high. Multiple solutions (or tough shapes) may need a nudge.

Sanity-check with XNPV:

• Pick a candidate rate (say, 8% and 25%) and evaluate =XNPV(0.08, values, dates) and =XNPV(0.25, values, dates). You should see the sign flip across the true IRR; if both are positive (or both negative), revisit steps 2–5. XIRR is the rate where XNPV = 0.

Recalc mode:

• Ensure Formulas > Calculation Options > Automatic (manual calc can leave stale 0%). Then press Ctrl+Alt+F9 to force a full recalc.

Why Sheets/WPS worked:

They often auto-parse ISO-style text dates (e.g., “2025-01-31”) more leniently than Excel. In Excel, text dates don’t become serials unless converted - causing the solver to fail.

Thank you for your patience in reading, we hope this information has been helpful to you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.