Excel XIRR fails in Desktop & Online — ATP not binding, cloud profile corruption

What you’ve described suggests a likely cloud-profile-level mismatch or corruption in the Analysis ToolPak (ATP) registry mapping for XIRR, persisting across environments. 

Before addressing the escalation steps, I would like to point out that, in my estimation, your formula contains inconsistencies.

Your formula =XIRR({-100,110},{1,2}) is mathematically invalid for calculating a 10% annual return because:

• Excel serial number 1 = January 1, 1900
• Excel serial number 2 = January 2, 1900
• These dates are only 1 day apart, not 1 year

For a true 10% annual return, you need dates approximately 365 days apart, such as:

=XIRR({-100,110},{DATE(2023,1,1),DATE(2024,1,1)})

or using serial numbers:

=XIRR({-100,110},{44927,45292})  // Jan 1, 2023 to Jan 1, 2024

The value you're seeing (likely 2.98E-09) is a known XIRR behavior when the algorithm fails to converge on a solution

 With cash flows of -100/+110 separated by only 1 day, XIRR would need to calculate an astronomically large annualized rate (~350 quadrillion percent), causing the iterative solver to fail and return this tiny fallback value.

This is not profile corruption—it's expected behavior when:

• Dates are invalid or too close together
• The solver cannot find a mathematically valid solution within iteration limits

Test with valid dates (365 days apart):

=XIRR({-100,110},{44927,45292})  // Returns ~0.10 (10%)

1. Confirm ATP isn't required: XIRR is a native Excel financial function (not ATP-dependent). ANALYS32.XLL contains ATP tools like Regression/ANOVA—not XIRR.
2. Check GET.CELL: XIRR has always been a native function. GET.CELL reporting it as "invalid" likely reflects your malformed inputs, not binding corruption.

If this not solve your Issue here an escalation path

What you maybe can do additional…

• Open a Microsoft 365 support ticket with: “XIRR returns a fallback constant in both Desktop and Online; ATP function binding appears corrupted at cloud profile level; requires cloud profile/ATP map rebuild.”
• Include: reproducible steps, environment details, minimal workbook, and evidence that local remedies (uninstall/reinstall, new profile) do not resolve it.
• Request a cloud-profile refresh or ATP function-map rebuild for your tenant/account, including a targeted reset of the XIRR/XIRR-related registrations for ATP in the cloud.
• If possible, include a request to involve Excel service engineering and the appropriate product group (Office/365 engineering) with visibility into any service-side logs or telemetry that could indicate a registry or mapping mismatch at the cloud profile level.

Workarounds and validation checks you can attempt (non-destructive)

Validate with a simple online-only test

Create a small XIRR test in Excel Online using a freshly created test workbook in a new tenant or a new user account (if you have a test tenant) to confirm if the behavior is strictly tied to your tenant/account.

Compare with alternate environments

If you have access to a different Microsoft 365 tenant or a guest account, test XIRR there to distinguish tenant vs. user profile scope.

Confirm function availability

In Online, ensure that the XIRR function is not being shadowed by any newer function or ARP mapping changes in your region; verify in a clean workbook that only standard XIRR is used.

I cannot directly escalate to Microsoft engineering teams, I’m only a simply user, but you can:

Microsoft 365 Admin Center → Support → "New service request" (for tenant admins)

Before escalating, verify the issue occurs with valid date inputs (≥30 days apart). Microsoft engineering teams will reject tickets based on invalid formula usage.

To check and list all possible causes of errors, some AI is also consulted. However, this might not be sufficient, as certain basic information is missing from your message.

For example...

Your Office 365 build/channel (e.g., Monthly Enterprise, Current Channel, etc.).

Your OS version and locale.

A minimal XIRR example (values ​​for dates and cash flows) and the exact incorrect result you're seeing.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.