How to get compound YTD returns in pivot table

[navrak]

I created a pivot table with a custom calculation in pivot tables' field settings to derive the YTD return rate on monthly return performance of different individual securities, using the formula =Product(1+ MTD %) - 1.

However, the pivot table still gives me the sum of the monthly percentage instead of the product formula.

Can anyone help?

Greatly appreciate if anyone can reply.

Best regards

 

[steve the fish]

Im not quite sure what you ask for but your this:

=Product(1+ MTD %) - 1

is equal to:

MTD %

 

[navrak]

Thanks Steve for the quick reply.

Sorry I wasn't clear.

Within my pivot table I will have a list of stocks and their monthly returns %

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD %
Stocks A
Stock B
Stock C

I would like to create a custom calculation to get the last column, which is the total year to date % number. Normally within in excel I would put in the formula {=Product(1+Jan:Dec range)-1} to get the YTD %. In the custom calculation, that is the formula I have put in, and have tried FVSchedule as well. However, instead of giving the product of that formula, it just gives me a sum instead.

Your help is greatly appreciated

 

[steve the fish]

Cant you add a column in the data that makes the calc then add that to the pivot?

 

[navrak]

ideally no, the system where the raw data comes from cannot calculate out this formula.

would just like to download the data and then have the pivot table refreshed and the last row updated