summing interest across time with variable rates

[astro]

Hi:

I need to build a query that sums (simple) interest for a deposit over time
for each client. The interest rate changes at distinct points along the
way.

for example:

Date Rate
1/1/91 2.4%
3/15/94 3.5%
6/1/96 5.4%
7/1/99 7.2%
5/14/2002 1.2%

So if a client has a deposit from 2/1/91 to 7/1/96 I need to figure out
interest for 3 different rates (2.4%, 3.5%, & 5.4%) on their deposit and sum
them together to get a total 'dividend'. Interest is calculated in 1-month
intervals.

Is there any built-in SQL features in Access (or SQL-Server) that expediate
this math?


Any advice greatly appreciated.

))

 

[Michel Walsh]

HI,



With the table

Rates
rDate Rate
1991-01-01 2.40%
1994-03-15 3.50%
1996-06-01 5.40%
1999-07-01 7.20%
2002-05-14 1.20%



I make a first query that clip the data to just the relevant records:

----------------------
SELECT *
FROM Rates
WHERE Rates.rDate>=(SELECT Max(rdate)
FROM Rates
WHERE Rates.rDate<=starting
)
AND Rates.rDate <=ending ;
-----------------------

where starting and ending are my parameters. I saved the query under the
name QrateK.


The second query computes the number of month the interest rate is
applicable:

-----------------------------
SELECT iif(a.rDate>starting, a.rDate, starting) AS OfficialStart,
iif( b.rDate<=ending, b.rDate, ending) AS OfficialEnd,
LAST(a.rate) AS MensualRate,
DateDiff("m", OfficialStart, OfficialEnd) As NumberOfMonth,
(1+MensualRate) ^NumberOfMonth As CompoundInterestForOneUnit

FROM (qrateK AS a INNER JOIN qrateK AS b ON a.rDate < b.rDate)
INNER JOIN qratek AS c ON a.rDAte < c.rDate

GROUP BY a.rDate, b.rDate
HAVING b.rDate=MIN(c.rDate);
--------------------------


Note that we just really need the last column, the compound interest from an
amount of 1.00 Currency unit


You will have to check if the Rate is indeed a mensual rate (or a year rate,
in the last case, you need to divide by 12 the MensualRate. You also have
to check that DateDiff("m", ..., ... ) returns the right number of month
(change), accordingly to the policy of the company. You may have to subtract
one, or add one to the NumberOfMonth, dependently.



QGetRatesAndDates
OfficialStart OfficialEnd MensualRate NumberOfMonth
CompoundInterestForOneUnit
1991-02-01 1994-03-15 0.024 37 2.40490760476041
1994-03-15 1996-06-01 0.035 27 2.53156710827146



Note that I assumed the interest rate was 2.4% per MONTH (33% per year)...
which can be a credit card rate. So, for 37 months, at that rate, one dollar
is now at 2.40 dollar. Same, 27 months at 3.5% a month, one dollar, the
1994-march-15, is now, at the 1996-june-01, 2.53 dollar.

What we need is the multiplication of values under the last column:


------------------
SELECT Exp(Sum(Log(CompoundInterestForOneUnit))) AS TotalnterestForOneUnit
FROM QGetRatesAndDates;
-----------------

which returns 6.088, ie, the initial, #2/1/91#, one dollar value is the,
#7/1/96#, at 6.09 dollar.


If the interest is per year rather than per month, the result is 1.1648
instead of 6.088.



Technically, just call the third query, it will automatically call the other
two queries and prompt you for the parameters starting and ending.




Hoping it may help,
Vanderghast, Access MVP

 

[astro]

Thanks -- I'll have a look at this...

HI,



With the table

Rates
rDate Rate
1991-01-01 2.40%
1994-03-15 3.50%
1996-06-01 5.40%
1999-07-01 7.20%
2002-05-14 1.20%



I make a first query that clip the data to just the relevant records:

----------------------
SELECT *
FROM Rates
WHERE Rates.rDate>=(SELECT Max(rdate)
FROM Rates
WHERE Rates.rDate<=starting
)
AND Rates.rDate <=ending ;
-----------------------

where starting and ending are my parameters. I saved the query under the
name QrateK.


The second query computes the number of month the interest rate is
applicable:

-----------------------------
SELECT iif(a.rDate>starting, a.rDate, starting) AS OfficialStart,
iif( b.rDate<=ending, b.rDate, ending) AS OfficialEnd,
LAST(a.rate) AS MensualRate,
DateDiff("m", OfficialStart, OfficialEnd) As NumberOfMonth,
(1+MensualRate) ^NumberOfMonth As CompoundInterestForOneUnit

FROM (qrateK AS a INNER JOIN qrateK AS b ON a.rDate < b.rDate)
INNER JOIN qratek AS c ON a.rDAte < c.rDate

GROUP BY a.rDate, b.rDate
HAVING b.rDate=MIN(c.rDate);
--------------------------


Note that we just really need the last column, the compound interest from an
amount of 1.00 Currency unit


You will have to check if the Rate is indeed a mensual rate (or a year rate,
in the last case, you need to divide by 12 the MensualRate. You also have
to check that DateDiff("m", ..., ... ) returns the right number of month
(change), accordingly to the policy of the company. You may have to subtract
one, or add one to the NumberOfMonth, dependently.



QGetRatesAndDates
OfficialStart OfficialEnd MensualRate NumberOfMonth
CompoundInterestForOneUnit
1991-02-01 1994-03-15 0.024 37 2.40490760476041
1994-03-15 1996-06-01 0.035 27 2.53156710827146



Note that I assumed the interest rate was 2.4% per MONTH (33% per year)...
which can be a credit card rate. So, for 37 months, at that rate, one dollar
is now at 2.40 dollar. Same, 27 months at 3.5% a month, one dollar, the
1994-march-15, is now, at the 1996-june-01, 2.53 dollar.

What we need is the multiplication of values under the last column:


------------------
SELECT Exp(Sum(Log(CompoundInterestForOneUnit))) AS TotalnterestForOneUnit
FROM QGetRatesAndDates;
-----------------

which returns 6.088, ie, the initial, #2/1/91#, one dollar value is the,
#7/1/96#, at 6.09 dollar.


If the interest is per year rather than per month, the result is 1.1648
instead of 6.088.



Technically, just call the third query, it will automatically call the other
two queries and prompt you for the parameters starting and ending.




Hoping it may help,
Vanderghast, Access MVP