locked
Need Help with CTE for Compound Interest Calculation RRS feed

  • Question

  • Hi

    I need some help with a simple CTE query to calculate the Compound Interest +Principle 

    Calculated Value=Compound Interest +Principle 

    Row 2= 1000*0.05+1000=1050

    Row 3=1050*0.05+1050=1102.50

    value row_num rate Calculated Amount(Compound Interest +Principle )
    1000.00 1 0.05 1000.0000
    1000.00 2 0.05 1050.0000
    1000.00 3 0.05 1102.5000
    1000.00 4 0.05 1157.6250
    1000.00 5 0.07 1238.6588
    1000.00 6 0.07 1325.3649
    1000.00 7 0.08 1431.3941
    1000.00 8 0.08 1545.9056

    --Dummy Data

    CREATE TABLE #test (
    [value] [decimal](18, 4) ,
    [num_count] [int] ,
    [rate] [decimal](18, 4)


    insert into #test values (1000,1,0.05)
    insert into #test values (1000,2,0.05)
    insert into #test values (1000,3,0.05)
    insert into #test values (1000,4,0.05)
    insert into #test values (1000,5,0.07)
    insert into #test values (1000,6,0.07)
    insert into #test values (1000,7,0.08)
    insert into #test values (1000,8,0.08)

    Tuesday, August 12, 2014 10:04 AM

Answers

  • Hi ,

    Please find the below query.

    ;WITH cte
    AS
    (
       select value,num_count,rate,cast(a.value as float) as tot
       from #test a
       where num_count=1
       union all
       select b.value,b.num_count,b.rate,cast(cte.tot+(cte.tot*b.rate) as float)
       from #test b
       inner join cte on b.num_count=cte.num_count+1
    )

    select * from cte 


    Thanks & Regards Rajesh

    Tuesday, August 12, 2014 11:16 AM

All replies

  • Hi ,

    Please find the below query.

    ;WITH cte
    AS
    (
       select value,num_count,rate,cast(a.value as float) as tot
       from #test a
       where num_count=1
       union all
       select b.value,b.num_count,b.rate,cast(cte.tot+(cte.tot*b.rate) as float)
       from #test b
       inner join cte on b.num_count=cte.num_count+1
    )

    select * from cte 


    Thanks & Regards Rajesh

    Tuesday, August 12, 2014 11:16 AM
  • Have a look at this

    use tempdb;
    drop table #test
    CREATE TABLE #test (
    [value] [decimal](18, 4) ,
    [num_count] [int] ,
    [rate] [decimal](18, 4)
    ) 
    
    insert into #test 
    values 
    (1000,1,0.05),
    (1000,2,0.05),
    (1000,3,0.05),
    (1000,4,0.05),
    (1000,5,0.07),
    (1000,6,0.07),
    (1000,7,0.08),
    (1000,8,0.08);
    
    
    with cte as
    (select t.num_count,t.value,t.rate, convert(money,Value) as Amt from #test t where num_count=1
    union all 
    	select t.num_count,t.value,t.rate,convert(money,c.amt + (t.value*t.rate)) from cte c join #test t on c.num_count+1=t.num_count)
    select * from cte


    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, August 12, 2014 1:38 PM
  • Why use a CTE and not use the closed formula in a column? Here is one pace to cut and paste it. User the4 POWER() function : 

    https://qrc.depaul.edu/StudyGuide2009/Notes/Savings%20Accounts/Compound%20Interest.htm



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, August 12, 2014 5:58 PM
  • Thanks for the suggestion.

    However, the interest rate is not a fixed interest rate. it will change every month. I have to loop through the balance by day to find use the right rate

    That's Why I cannot use this formula. Unless, you have any other ideas.

    By using CTE, I am also having a limit of not exceeding 32,767 loops, which I think is enough for my case.

    Tuesday, August 12, 2014 10:00 PM