Add IRR & NPV to Pivot Table as Calculated Field

[JonesyUK]

Dear Experts,

Can you please tell me how to add IRR and NPV to a Pivot Table as a Calculated Field.

I am using Excel 2010

Many thanks,
JonesyUK

 

[Siraj.Samsudeen]

Using CALCULATE and time intelligence functions might help making your model more maintainable, provided you have a date table, since you would not have to referrence p or any other column in your table (except Y and C).

Hi Laurent, How are you doing? I need to use PowerPivot to calculate IRR for a dynamic number of investments. My table is structured like this: Project Name, Event Date, Cash Flow. In Excel, I would XIRR as the cashflows are not regular. Do you have any ideas on how this can be done? The difficulty here is that IRR is an iterative calculation and there is no way to reproduce it without using Excel function.

 

[Jacob Barnett]

Siraj,

Ultimately IRR is just mathematics and DAX is full of iterative formulas so it must be possible! Even in standard Excel, I wouldn't trust the NPV/IRR formulas to get it right (because they often don't).

Maybe post the full problem with an explanation of what your output looks like and the structure of your data in a new post.

Jacob

 

[Siraj.Samsudeen]

Thanks Jacob for your offer to help. Yes, that is what I am also thinking - whether there is a way to do an user-defined iteration in PowerPivot! Let me create a sample file which gives the essence of the problem and I will post it as a separate thread as per your suggestion.