thread705-1624516 Hi all, I a 1

[ongke0711]

thread705-1624516

Hi all,

I am from Vietnam so my English is not so good. Please understand me if having something not clear in my express below.
I needs help from you about calculate the bank interest and days as in the thread705-1624516 (from 2010). Sorry to dig it again because it correct with my case .
I have a table of bank Interest by period of time. Ex: from (mm/dd/yyyy) 10/12/2009 - 12/31/2009: 10%; 1/1/2010 - 06/27/2010: 12%;...
I have a Amount of loan: 150,000,000 from 12/01/2010 until 03/02/2013 (mm/dd/yyyy).
My table as MajP suggest and I need the output as picture attached.

My datebase like this:

As MajP said, we can do faster by SQL so please guide me how to do this.
Thank you very much.

My demo file: Link

 

[MajP]

SELECT 
   tblLoanAmount.LoanAmount, 
   tblLoanRates.RateStartRange, 
  tblLoanRates.RateEndRange, 
  tblLoanRates.RangeRate, 
  RateDays([LoanStartDate],[loanEndDate],[RateStartRange],[RateEndRange]) AS Rate_Days,
   [Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
  WHERE 
(((tblLoanRates.RateStartRange) Between [LoanStartDate] And [LoanEndDate])) OR (((tblLoanRates.RateEndRange) Between [LoanStartDate] And [LoanEndDate]));

Public Function RateDays(LoanStart, LoanEnd, RateStart, RateEnd) As Long
  ' The loan range covers entire rate range
  If RateStart > LoanStart And RateEnd <= LoanEnd Then
    RateDays = DateDiff("d", RateStart, RateEnd)
  ElseIf RateStart <= LoanStart And RateEnd <= LoanEnd Then
    'Rate range covers begining of the loan but not whole loan
     RateDays = DateDiff("d", LoanStart, RateEnd)
  ElseIf RateStart >= LoanStart And RateEnd >= LoanEnd Then
     'Rate covers end of loan but not whole loan
     RateDays = DateDiff("d", RateStart, LoanEnd)
  ElseIf RateStart <= LoanStart And RateEnd >= LoanEnd Then
    'the rate covers the entire loan
    RateDays = DateDiff("d", LoanStart, LoanEnd)
  End If
End Function

 

[MajP]

Graphically to show this where LS - LE loan sart to loan end
RS - RE rate start to rate end

1)  LS---------------LE
          RS----RE
    = rateend - ratestart


2)            LS---------------LE
        RS---------RE
    = rateend - loanstart


3)  LS---------------LE
                 RS--------RE
    = LoanEnd - ratestart


4)  The other two possible cases are taken care of by the query and do not show up so I do not test for that in the function.
           
                                      LS---------------LE
                 RS------RE                                        RS------RE

 

[ongke0711]

Thanks much MajP.
This work great as my expectation. Your graphic make it easy to understand.

 

[MajP]

To do this in a pure SQL solution without the function, you can make seperate queries for each case and then do a union query. Here is the first two cases.

(the entire loan range falls within a single rate range)
(the entire rate range falls within the loan range)


SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
CLng([LoanEndDate]-[LoanStartDate]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)<=[LoanStartDate]) AND ((tblLoanRates.RateEndRange)>=[LoanEndDate]));

UNION

SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
CLng([RateEndRange]-[rateStartRange]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)>=[LoanStartDate]) AND ((tblLoanRates.RateEndRange)<=[loanEndDate]));


Notice that only the calculation and the where statement changes as described in the graphic. You then just need to do the two cases where part of the rate covers the loan period.

My original graphic was missing the first case.

Loan Covered by a single Rate
               LS------------LE
          RS---------------------RE
    = LoanStart - LoanEnd


Entire Rate Range covered by loan range
    LS-------------------LE
          RS----RE
    = rateend - ratestart


Rate starts before loan and stops before end of loan

            LS---------------LE
        RS---------RE
    = rateend - loanstart


Rate Starts after loan start and goes beyone loan end

       LS---------------LE
                   RS--------RE
    = LoanEnd - ratestart

 

[ongke0711]

Thanks MajP.
The SQL is so powerful. I thought we have to write some code to get this result but not, pure SQL also enough to do this.
I follow you and complete the Union query for my case as below:
...................
SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
CLng([RateEndRange]-[LoanStartDate]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)<=[LoanStartDate]) AND ((tblLoanRates.RateEndRange)<=[LoanEndDate])) AND (CLng([RateEndRange]-[LoanStartDate]))>=0;

UNION

SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
CLng([LoanEndDate]-[LoanStartDate]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)<=[LoanStartDate]) AND ((tblLoanRates.RateEndRange)>=[LoanEndDate]));

UNION

SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
CLng([RateEndRange]-[rateStartRange]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)>=[LoanStartDate]) AND ((tblLoanRates.RateEndRange)<=[LoanEndDate]));

UNION SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
CLng([LoanEndDate]-[RateStartRange]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)>=[LoanStartDate]) AND ((tblLoanRates.RateEndRange)>=[loanEndDate])) AND (CLng([CalcEndDate]-[RateStartRange]))>=0;
..................

- I add "AND (CLng([RateEndRange]-[LoanStartDate]))>=0" in order to remove the minus value from query.
So far is good but sorry that I was lack 1 more term in this case.
- Another term I lacked is the Interest Calculating Date beside the Loan Start/End Date. The Date Start calculates interest maybe applied before the LoanStartDate and the Date End for calculating maybe after the LoanEndDate (Over due loan) . Here I call "CalcStartDate | CalcEndDate". The number of day Over Due Loan is [CalcEndDate]-[LoanEndDate] and these days will bear 50% of Interest from the RangeRate in its time range.
Ex: the Loan 1: 150,000,000; over due interest is 9% of range 08/17/2012 - 03/07/2013 for 62 days (from 03/03/2013 - 05/04/2013)

- I modified your query as below but still not get the result. So please help if you have free time.
Thank much for your spending time.
...........
SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
IIF([RateStartRange]<[CalcStartDate],[CalcStartDate],[RateStartRange]) AS FromDate,
IIF([RateEndRange]>[CalcEndDate],[CalcEndDate],[RateEndRange]) AS ToDate,
CLng([RateEndRange]-[CalcStartDate]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)<=[CalcStartDate]) AND ((tblLoanRates.RateEndRange)<=[CalcEndDate])) AND (CLng([RateEndRange]-[CalcStartDate]))>=0;

UNION

SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
IIF([RateStartRange]<[CalcStartDate],[CalcStartDate],[RateStartRange]) AS FromDate,
IIF([RateEndRange]>[CalcEndDate],[CalcEndDate],[RateEndRange]) AS ToDate,
CLng([CalcEndDate]-[CalcStartDate]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)<=[CalcStartDate]) AND ((tblLoanRates.RateEndRange)>=[CalcEndDate]));

UNION

SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
IIF([RateStartRange]<[CalcStartDate],[CalcStartDate],[RateStartRange]) AS FromDate,
IIF([RateEndRange]>[CalcEndDate],[CalcEndDate],[RateEndRange]) AS ToDate,
CLng([RateEndRange]-[rateStartRange]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)>=[CalcStartDate]) AND ((tblLoanRates.RateEndRange)<=[CalcEndDate]));

UNION SELECT
tblLoanAmount.LoanAmount, tblLoanRates.RateStartRange, tblLoanRates.RateEndRange, tblLoanRates.RangeRate,
IIF([RateStartRange]<[CalcStartDate],[CalcStartDate],[RateStartRange]) AS FromDate,
IIF([RateEndRange]>[CalcEndDate],[CalcEndDate],[RateEndRange]) AS ToDate,
CLng([CalcEndDate]-[RateStartRange]) AS Rate_Days,
[Rate_Days]/365*[RangeRate]*[LoanAmount] AS Interest
FROM tblLoanAmount, tblLoanRates
WHERE
(((tblLoanRates.RateStartRange)>=[CalcStartDate]) AND ((tblLoanRates.RateEndRange)>=[CalcEndDate])) AND (CLng([CalcEndDate]-[RateStartRange]))>=0;

...........
Sample database: Link