# Need Help with CTE for Compound Interest Calculation • ### 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

• 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 &amp; 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 &amp; 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```

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