Forum Discussion

TeunVerhagen's avatar
TeunVerhagen
Frequent Visitor
5 years ago

PRODUCTX calculation on total level

Hi all,

 

I am dealing with the following issue:

 

My table consists of investmentID's and for every investment I calculated the returns (Dietz method). In order to link the returns per period to get the returns over a longer period I use the Modified Dietz method. This basically works by multiplying the returns per period. This is working fine on investment level by using PRODUCTX calculation. But...

 

Now I want to do this on portfolio level and this is going wrong because the PRODUCTX function multiplies all the returns per investment in stead of the total return of x period. Example:

 
 

 

When I use the PRODUCTX fomula on the returns it will multiply all returns on investment level. What I am looking for is a formula that will multiply the returns on period level so: 2.4 * 3 * 11 * 3.2 (in stead of 2*3*5*0.2 etc.).

 

Many thanks in advance. I hope there is a solution for this.

 

Kind regards,

 

Teun Verhagen



3 Replies

  • TeunVerhagen's avatar
    TeunVerhagen
    Frequent Visitor
    5 years ago

    Thank you for your response!

     

    Returns is indeed a measure. I have tried your method but it does not work unfortunately.

     

    It has become a complex structure with a return calculation based on two tables: one table with start and end value per period and one table with transactions during the period. Those two tables are linked and the formula to measure return works fine.

     

    The problem is that it keeps calculating the product per investment return in stead of the product of the return of multiple investment within a period. 

    • stevedep's avatar
      stevedep
      Icon for Memorable Member rankMemorable Member
      5 years ago

      For readability, I would suggest to simply use SUMX(Values..