CODEWITHSUNDEEP
excelexcel-formulapivot-tablexirr
graffit
graffit

Reputation: 1

XIRR function in pivot table

I'm trying to add XIRR formula to pivot table but either I'm doing something wrong, either it's impossible.

Here on the picture is sample data, formula I've put and XIRR calculated in single cell with the same data (so It works outside pivot table)

enter image description here

Upvotes: 0

Views: 1034

Answers (1)

QHarr
QHarr

Reputation: 84465

I don't think so as the calculated field cannot work with arrays and will sum across the underlying data.

enter image description here

Look at how SUMPRODUCT misbehaves due to summing underlying data:

SUMPRODUCT in pivottable calculated field click image to enlarge

Pivottable calculated fields will operate on the underlying data and the XIRR function needs at least one negative and one positive value, which it wouldn't have with the summed underlying data.

As per Microsoft XIRR guidance:

XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value.

Upvotes: 0

Related Questions