Pivot Tables - Compound Interest/Growth

[Henrique Carvalho]

Hi guys!


I´ve been trying to figure out a way for calculate compound interest in pivot tables, but haven´t found anything that works.


In this link below i´ve written some observations inside the spreadsheet (v.2007).


http://www.megaupload.com/?d=PYOBGPH0


Thanks in advance.

 

[Hui]

Is there any particular reason you have to do it inside a Pivot Table?

 

[Henrique Carvalho]

Yes Hui!


My main goal is to make graphs from any asset from my data base.


This way I thought I could make some pivot charts to simplify all data.


Is there another possibility?


Do you guys, that works with finance and has to publish some data in graphs, would recomend something diferent?

 

[Hui]

Are you after yearly totals for the 5 categories ?

 

[Henrique Carvalho]

Hi Hui!


I've made some changes in my spreadsheet to cover all the things i wish i could acomplish.


I believe this can help others to understand in details what's the point of it.


Spreadsheet in the link below:


http://www.megaupload.com/?d=JH1PD3K4

 

[Hui]

Henrique

I don't think you can do what you are trying to do in a Pivot Table in one pass


The problem is that you can make a dummy field in the data table "Test" which will be IPCA -1

and then Summarise (using Product)the Dummy Field in the Pivot Table so that you get the answer 233.33% but you then can't do maths within the Piviot Table on the Grand Total to get 113.33%


You can of course put a cell just below it using

=GETPIVOTDATA("Product of Test",$A$3)-1

Which will give you the answer you seek.

 

[Henrique Carvalho]

Thanks Hui!


I guess i don´t really need to display this kind of data via a pivot table.


Altough, your solution was very clear and simple. I appreciate your help!