Compound Interest?

[#1]

Can Qlikview deal with compounding percentages? I want to create a line chart of an index using % changes which multiplies by all the previous values (compounding performance).

Example:

Day     Daily returns     Compound Returns

1          +5%                   +5%

2          +3%                   +8.15% (this is 1.05 * 1.03 -1)

3          +7%                   +15.72% (this is 1.05 * 1.03 * 1.07 -1)

4          -2%                    +13.41%

5          +1%                   +14.54%

On my expression I can Full Accumulate, which adds all the previous dates but I'm not sure how to multiply all the previous results? I am looking for a formula which I can use in the expression which achieves the compound returns.

Thanks

Max

[Oleg_Troyansky · Accepted solution]

Hi Max,

It requires a bit of tinkering, but it can be done.

First, you need to implement a kind of an "As of Date" table - in your case, it needs to be linked to your "product", or an individual loan, etc... In this table, you associate each "display date" with all the "actual dates" that are lower than the
"display date" - this way, you achieve the "compound" effect for each date.

Then, you need to implement an aggregation formula that can multiply instead of summarizing. This formula involves Logarithms and exponentials (didn't think you needed them in your math class, huh?):

=exp(sum(log(1+Interest))) - 1

I'm attaching a simplified "straw man" model.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[#3]

Thanks Oleg,

I am trying to get my head round what you have done there but it looks good! The only issue is that I was hoping to be able to adjust the start date of the compounding using selections (so if i selected days 3-10 it would start the compounding at period 3). Is this possible?

Thanks

Max

[Oleg_Troyansky]

Yes, it's absolutely possible, because the compound interest is calculated on the fly. In order to facilitate that, you should either offer a separate selection for the field Date, in addition to Display Date, or add a set analysis condition that limits the filed Date to the available values of the field Display Date. You have the flexibility to choose two separate selections for the two fields or one synchronized selection.

[#5]

Thanks Oleg that's working great now!

[pibarguren]

Hi Oleg,

I am trying to use these but expression to calculate de compound interest in the object seems not to work if instead of the field "interest" i use a calculated filed (ex:avg(interest)).

Please advice

Thks!

[Oleg_Troyansky]

HI Pamela,

I'm not sure what do you mean by using a calculated field - is that referring to another expression by its label? If you simply replaced the field Interest in my calculation by an aggregated function, then you have a nested aggregation, and those require the AGGR() function, to determine the level of the inner aggregation.

I could give you a more specific example if I could see a sample app with the problem.

best,

Oleg Troyansky

Check out my new book QlikView Your Business - An Expert Guide for QlikView and Qlik Sense