Calculating Interest and Days 2

[Knicks]

Access 2007 (DAO preferred)

I have a table of records on the many side of an account where interest rates change over a given time. I want to be able to have a user input a START DATE and END DATE and have the system calculate interest. An account can have many rows of data with changing dates and rates.

Example:
INPUT DATE: 3/1/2006
INPUT DATE: 2/1/2008
(in this situation I know some of the days would be calculated from change date (on) 1-1-2008 to 2-1-2008 at 8%, 1-1-2007 to 1-1-2008 (-1) at 7%, 3-1-2006 through 1-1-2007 (-1) at 6%

All loans have an original date started and a last change date. The Input date will never be more than 31 days after last change date. The Input start needs to not starting calculating interest till after the original date.

I need to loop through the records or possibly create an array (which I am a bit foggier on). Essentially I need to calculate the amount of days from the INPUT as they pertain to the orig date, change date, rate after change, and prev rate (which has been conviently supplied). I would then add up all the various interest from the created table. Some loans change monthly or yearly and could be any number of records associated.

Orig Loan change rate date rate prev rate
1/1/2004 101 1/1/2005 5% 4%
1/1/2004 101 1/1/2006 6% 5%
1/1/2004 101 1/1/2007 7% 6%
1/1/2004 101 1/1/2008 8% 7%

Any help on creating a looping system that can take into account the various scenerios that happen (no interest at all for loans with orig date later than inputted end date)

 

[SimonSellick]

A few questions:

- Is it a rate or an amount that you are after? You need to know the original / outstanding loan amount if you want amounts.

- Do you want just the interest due for each day, or the cumulative amount (compounded)?

- Is the loan interest actually calculated and 'credited' daily, monthly, annually...?

The easy bit is iterating the date field: if you add 1 to a VBA date, that adds a day. So, something like:

v = amount_at_start_date
d = start_date
while d < end_date
  .
  . (calc i = interest for day d)
  .
  v = v + i
  d = d + 1
wend

Hope this helps

Simon

 

[Knicks]

Each line item will supply the loan amount

Example (continued from original), so loan number 101 will always show the original loan amount, called UPB.

UPB
$125,000



Also,
YearDays = 365

Interest Rate = will be from the date of Change forard. So if end date of input equals change date that is 1 day of interest at that rate.

I am basically trying to gather the 'days' using a date diff and then apply the correct amount of days of the input to the correct interest rate and ultimately add up all the interest for each loan. Just getting each loan interest line item in a table would make it easy to sum each loan in a query or report. The interest does not needed to compounded on the principal, which should be simpler

UpDinterest = rs!upb * ((InterestRate / YearDays)) * ((Days))

 

[MajP]

If your rate data was normalized this would be easy to do with a pure sql solution. It still can be done that way but takes too much work to explain. However, that is the recommended solution.

Your tbl for rate data should be
tblLoanRates
loan
orig (not really necessary)
startRange
EndRange
rateForRange

so your records would look like
StartRange EndRange
101 1/1/2004 1/1/2005 4%
101 1/1/2005 1/1/2006 5%
...

Here is a function to return the info

Public Function getRate(loanID As Variant, dtmDate As Variant) As Variant
  'your name here
  Dim strSql As String
  Dim rsRates As DAO.Recordset
  Dim blnFound As Boolean
  Dim orig As Date
  Dim origRate As Double
  Dim firstChange As Date
  Dim firstRate As Double
  Dim lastChange As Date
  Dim lastRate As Double
  If Not IsNull(loanID) And IsDate(dtmDate) Then
    strSql = "Select * from tblLoanRates where loan = " & loanID
    Set rsRates = CurrentDb.OpenRecordset(strSql, dbReadOnly)
    rsRates.MoveFirst
    'Origin Date
    orig = rsRates!orig
    'Rate from origin to first change
    origRate = rsRates![Prev Rate]
    'Rate from First change to second change
    firstRate = rsRates![Rate]
    'Date of first change
    firstChange = rsRates![Change Rate Date]
    'Move last to keep from busting the loop
    rsRates.MoveLast
    'Date of last change
    lastChange = rsRates![Change Rate Date]
    'Last Rate
    lastRate = rsRates!Rate
    'if date before loan origin
    If rsRates!orig > dtmDate Then
      Exit Function
    'From origin to first change
    ElseIf dtmDate >= orig And dtmDate < firstChange Then
      getRate = origRate
    'Past the last change
    ElseIf dtmDate >= lastChange Then
       getRate = lastRate
    Else
      rsRates.MoveFirst
      Do Until blnFound
        If dtmDate > rsRates![Change Rate Date] Then
          blnFound = True
          getRate = rsRates!Rate
          Exit Function
        End If
        rsRates.MoveNext
      Loop
    End If
  End If
End Function

The above could have been written shorter but this is logically simpler.

in a sql

Select loan, someDate, otherFields, getrate(loan,someDate) as DailyRate from someTable

If your willing to normalize the rate table then this can be done far more efficiently in sql. Normalizing the data can be done with update queries..