Code question date, year days ...

  • Thread starter Thread starter Joseph Meehan
  • Start date Start date
J

Joseph Meehan

I am feeling lazy today and my mind just does not want to work.

I need to compute the total interest for various time periods.

By law this interest is set each year for that year. It is chargeable
by day. A period that extends from one year to another will have a
different interest rate for the number of days in each year and that number
of days must be charged for the rate for that year. No compounding, just
simple interest.

Lets say interest rate of 10% for 1999; 15% for 2000 and 20% for 2001

So if the period was December 1, 1999 to January 31, 2001, there would
be three interest groups:

12-1-99 to 12-31-99
31(days) * 10%/365 = 0.85%

1-1-00 to 12-31-00
365 * 15%/365 - 15%

1-1-01 to 1-31-01
31* 20%/365 = 1.70%

Total = 17.55%

A special thanks to the one who comes up with the code. Bonus points
for code that uses a table as the source for the interest rates to allow
easy annual updates.
 
By law this interest is set each year for that year. It is chargeable
by day. A period that extends from one year to another will have a
different interest rate for the number of days in each year and that number
of days must be charged for the rate for that year. No compounding, just
simple interest.

Lets say interest rate of 10% for 1999; 15% for 2000 and 20% for 2001

So if the period was December 1, 1999 to January 31, 2001, there would
be three interest groups:

12-1-99 to 12-31-99
31(days) * 10%/365 = 0.85%

1-1-00 to 12-31-00
365 * 15%/365 - 15%

1-1-01 to 1-31-01
31* 20%/365 = 1.70%

Total = 17.55%
Click to expand...

Interesting!

I'd suggest relaxing the assumption that the rate changes on January
1: have a table Rates with three fields, EffectiveDate, EndDate, and
Rate. I'll assume the loan starts on StartDate and ends on FinalDate.
Use a Non Equi Join query:

SELECT Sum([Rates].[Rate] * DateDiff("d", IIF([StartDate] <
[EffectiveDate], [EffectiveDate], [StartDate]), IIF([FinalDate] >
[EndDate], [EndDate], [FinalDate])) / 365. AS Interest
FROM yourtable INNER JOIN Rates
ON yourtable.StartDate <= Rates.EffectiveDate AND yourtable.FinalDate
= Rates.EndDate;
Click to expand...

You'll probably want to Group By an accountID or the like.

Untested code... but it ought to be a start!
 
Sorry. This is a real application for the application of interest
charges to taxpayers who pay their tax bills late. The law is specific on
how the interest must be applied.

A quick reading of what you are suggesting will not work as needed.

Right now the solution is the spreadsheet from hell coupled with manual
calculation.

Thanks for taking the time for a suggestion.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math



....
Interesting!

I'd suggest relaxing the assumption that the rate changes on January
1: have a table Rates with three fields, Effective, EndDate, and
Rate. I'll assume the loan starts on StartDate and ends on FinalDate.
Use a Non Equi Join query:

SELECT Sum([Rates].[Rate] * DateDiff("d", IIF([StartDate] <
[EffectiveDate], [EffectiveDate], [StartDate]), IIF([FinalDate] >
[EndDate], [EndDate], [FinalDate])) / 365. AS Interest
FROM yourtable INNER JOIN Rates
ON yourtable.StartDate <= Rates.EffectiveDate AND yourtable.FinalDate
= Rates.EndDate;
Click to expand...

You'll probably want to Group By an accountID or the like.

Untested code... but it ought to be a start!
Click to expand...
 
Sorry. This is a real application for the application of interest
charges to taxpayers who pay their tax bills late. The law is specific on
how the interest must be applied.
Click to expand...

ummm... that's what I was trying to do: to solve the problem as
stated.
A quick reading of what you are suggesting will not work as needed.
Click to expand...

I'll set up some tables and try it out, but if it doesn't work as is,
I'm sure it can be made to do so. My comment about the "relax the
assumption" was misleading - if you put in January 1 as the start and
end date, it will use that date in accordance with the law; it's just
that the query itself does not make any restrictions on which date you
enter in the Rates table.
 
It needs to work for any date to any other date not just whole years.
So you may have three days in one year followed by 97 days in the following
year. The first three days would be at one rate and the 97 at another, or
am I still hung up on that "relax the assumption" thing?

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math
 
It needs to work for any date to any other date not just whole years.
So you may have three days in one year followed by 97 days in the following
year. The first three days would be at one rate and the 97 at another, or
am I still hung up on that "relax the assumption" thing?
Click to expand...

Ok... here's what I came up with; it's a bit different than my first
go.

First, calculating the "rate" (fraction of year times rate for that
year) for each year:

SELECT Clients.ClientID, Year([RateStart]) AS TheYear,
Clients.StartDate, Clients.FinalDate,Rates.Rate,
IIf([StartDate]<[RateStart],[RateStart],[StartDate]) AS SDate,
IIf([FinalDate]>[RateEnd],[RateEnd],[FinalDate]) AS Edate,
DateDiff("d",IIf([StartDate]<[RateStart],[RateStart],[StartDate]),IIf([FinalDate]>[RateEnd],[RateEnd],[FinalDate]))
AS NDays,
[Rates].[Rate]*DateDiff("d",IIf([StartDate]<[RateStart],[RateStart],[StartDate]),IIf([FinalDate]>[RateEnd],[RateEnd],[FinalDate]))/365
AS Interest
FROM Clients INNER JOIN Rates
ON Clients.StartDate<=Rates.[RateEnd] AND
Clients.FinalDate>=Rates.[RateStart]
ORDER BY Clients.ClientID, Rates.RateStart;

For two clients - one starting 12/1/1999 and ending 1/31/2001, the
other starting 2/1/2001 and ending 12/1/2001 - this gives (trimmed):

ClientID TheYear NDays Interest
1 1999 30 8.21917808219178E-03
1 2000 365 0.15
1 2001 30 1.64383561643836E-02
2 2001 303 0.166027397260274

Summing grouped by Client, the SQL becomes

SELECT Clients.ClientID, Clients.StartDate, Clients.FinalDate,
Sum([Rates].[Rate]*DateDiff("d",IIf([StartDate]<[RateStart],[RateStart],[StartDate]),IIf([FinalDate]>[RateEnd],[RateEnd],[FinalDate])))/365
AS Interest
FROM Clients INNER JOIN Rates
ON Clients.StartDate<=Rates.[RateEnd] AND
Clients.FinalDate>=Rates.[RateStart]
GROUP BY Clients.ClientID, Clients.StartDate,Clients.FinalDate;

and the summed rates come out to 0.174657534246575 and
0.166027397260274. I think that the calculation is correct (aside from
the fact that 2000 had 366 days not 365).

This was fun!
 
Back
Top