Calculation of intrests

[D1480LU5]

I need to make an Access form which looks up in a Table different percentages, based on 2 dates.

The table has 2 fields: Date From and Percentage.

Date From Percentage
01/01/2007 8%
01/01/2008 9%
01/01/2010 7,5%



Etc...

When a date is selected, it needs to calculate the amount of days in the date range per year.

If e.g. you select from 01/01/2012 through 31/01/2013 it needs to split up per (calendar) year, like this:

Period from 01/01/2012 through 01/01/2013 ( = 366 days) at 6.25 %
Period from 01/01/2013 through 31/01/2013 ( = 30 days) at 4.75 %

For 2012, an entire year, the result should be 6.25 and for 2013 it needs to be 4.75/(number of days per year)*(amount of days in that year)

How do I start? I'm quite new to Access 2010. Any help is much appreciated!!

[June7]

The date range could be any two dates between 2007 and 2013? You don't show 2009 in the example.

This will require a VBA procedure.

[alcapps]

so are you saying that.. if you had a date say June 3, 2009 and today Feb 17, 2013

you need
June 3, 2009 number of days to dec 31, 2009 = Interest Rate for FY2009 / (211 left * number of days in FY2009)

so are you trying to get the compound interest calculation?

you can get this by writing one function to get the number of days left and calling it when you need it or using date serial addition.
pseudo code..
dim intYr as long
dim intStartYr as long
dim intEndYr as long
dim intNumDays as long

intStartYr = Year(start date)
intEndYr = Year(end date)
intYr = intEndYr
loop until intYr > intEndYr
if intYr = Year(start date) or IntYr = Year(end date) then
if intYr = Year(start date) then
if cdate("1/1/" & intYr) = stardate then
intNumDays = GiveNumDaysInYear(intYr)
else
IntNumDays = DateDiff("d",startDate,DateSerial(Year(startdate), 12,31 ))
end if
else
if cdate("1/1/" & intYr) = end date then
intNumDays = GiveNumDaysInYear(intYr)
else
IntNumDays = DateDiff("d",startDate,DateSerial(Year(enddate),12 ,31 ))
end if
end if
else
intNumDays = GiveNumDaysInYear(intYr)
end if
intYr = intYr + 1
end loop


Also found this code..
Public Function GiveNumDaysInYear(Year As Long) As Integer
GiveNumDaysInYear = IIf(Year Mod 4, 365, 366)
End Function

so depending on what you want to do with the intNumdays.. you can add that to the mix..