Database
Highlighted

Calculating compound interest in SQL

I've been trying to figure out a way to calculate compound interest in SQL and I think that I can only do it using recursive SQL or actually return the rows using a cursor and calculate it in the calling program. Here is an example of my table:

Row_Nbr                        Principle                      Int_Rate

1                             1000                              .03

2                              NULL                             .03

3                              NULL                             .02

4                                200                             .02

The Principle column above would only contain an amount if additional principle was deposited or withdrawn. Note that the interest rate can change on each row. With the above table, I'd like to write SQL that gave the following results:

Row_Nbr                 Start_Bal            Principle          Interest     End_Bal

1                             0                 1000.00             30.00      1030.00

2                        1030.00            NULL                 30.90      1060.90

3                        1060.90            NULL                 21.22      1082.12

4                        1082.12            200.00               25.64      1037.76

Is there a way to do this without using recursive SQL? I'm not sure that it can be done in recursive SQL, but that would where I would go next.

Thanks,

Barry

Re: Calculating compound interest in SQL

The recursive SQL can handle this, an example is below.   I wonder whether a more simple way exists.

create table vlad.test (n integer, principle decimal(18,2), int_rate decimal(18,2));

insert into vlad.test values (1, 1000, .03);

insert into vlad.test values (2, NULL, .03);

insert into vlad.test values (3, NULL, .02);

insert into vlad.test values (4, 200, .02);

with recursive recur (n, start_bal, principle, interest, end_bal) as

(

select 1, cast(0 as decimal(18,2)) as start_bal, principle, cast(principle * int_rate as decimal(18,2)) as interest, principle + interest as end_bal

where n=1

union all

select b.n, a.end_bal as start_bal_NEW,

b.principle,

(start_bal_NEW + coalesce(b.principle,0)) * b.int_rate as interest_NEW,

start_bal_NEW + coalesce(b.principle,0)+ interest_NEW as end_bal_NEW

from recur as a, vlad.test as b

where  b.n = a.n+1

) select * from recur

n start_bal principle interest end_bal

1 1 0.00 1,000.00 30.00 1,030.00

2 2 1,030.00 ? 30.90 1,060.90

3 3 1,060.90 ? 21.22 1,082.12

4 4 1,082.12 200.00 25.64 1,307.76

With regards,