Calculate Compound Interest

[philippsathasivam]

Hello dear Experts !

I have the following Problem: I need to calculate the compound interest of an amount, so please look at the table below:

i want to calculate for instance 100*1,1=110, then 110*1,2=132, then 132*1,3 and so on. does s.o know how to calculate this in an easy fashion? maybe with the iterative view function (advanced) ? or can i use the line item function in valuation ? i also saw the answer of samyasay for the following question:

https://answers.sap.com/questions/13226085/papm-calculation-between-rows-of-a-table.html?childToView...

But when i did what she suggested, i couldnt exactly get the correct results.

Best Regards, Philipp

[ZYT · Accepted solution]

Hi @Philipp Sathasivam,

besides iterative view, you may also first expand the recursive formulas to remove the iteration, then use window function to calculate the compounding amounts:

FIRST_VALUE(ZE_AMT) OVER (ROWS BETWEEN UNBOUNDED PROCEDING AND UNBOUNDED FOLLOWING) * EXP (SUM (LOG(IFNULL(NULLIF(ZE_MULT,0),1))) OVER (ROWS BETWEEN UNBOUNDED PROCEDING AND CURRENT ROW)).

Please note that: we may use following formula to calculate the product of numbers a * b = exp(ln(a) + ln(b)).

It would be helpful to order the number series by an identifer.

[philippsathasivam · Accepted solution]

Hi Tang big thx for your great help again, youre the best! I tried it out, it works with the following formula:

(FIRST_VALUE(ZE_AMT) OVER (ORDER BY IDENT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))*EXP(SUM(LOG(2.71828,IFNULL(NULLIF(ZE_MULT,0),1))) OVER (ORDER BY IDENT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))

(you need unbounded PRECEDING, and fill the LOG function with the exponential number)

The result:

I also try to use the LIV-Function with Valuation function. But are the settings i use here correct? I wanna write the results into the newly added field TOTAL_AMOUNT?

Im multiplying ZE_MULT with the lagged Amount, so it would be 100*1,1*1,2*1,3*1,4*1,5. How should the formula be. I tried it with the following formulas, but couldnt get exactly the result:

case when ZE_MULT =0 then ZE_AMT else ZE_MULT*lag(ZE_AMT,1,0) end

or

case when ZE_MULT!=0 then ZE_MULT*(lag(ZE_AMT,1,0) over (order by IDENT)) else ZE_AMT end

Best Regards, Philipp

[akki0785]

Hi Philip,

Try to use SQL WINDOW function to perform Row level results.

Regards,

Akshay

[ZYT]

Hi @Philipp Sathasivam,

yes, line item valuation is also a good option. It may implicitly iteratively execute a calculation formula. You for instance might try it out with LIV - Formula or LAG. I hope this hint would be helpful.

Best regards,

Zhiyi Tang