0

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

marko-36
  • 1,309
  • 3
  • 23
  • 38
graffit
  • 1
  • 1

1 Answers1

0

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.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • I've tried with only one negative flow at the beginning and one positive and still doesn't work. But I've solved the problem with power pivot. I've just add XIRR function and in power pivot it works. – graffit Jan 22 '21 at 16:32
  • What I am saying @graffit is that you cannot solve it this way as the negative and positive get summed so you will only ever have a single scalar from each input series/array. – QHarr Jan 22 '21 at 17:12