INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

thread705-1624516 Hi all, I a

thread705-1624516 Hi all, I a

(OP)
thread705-1624516: Calculating Interest and Days

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: Calculating Interest and Days (from 2010). Sorry to dig it again because it correct with my case dazed.
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

RE: thread705-1624516 Hi all, I a

CODE

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])); 

CODE

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 

RE: thread705-1624516 Hi all, I a

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

CODE -->

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 


RE: thread705-1624516 Hi all, I a

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

RE: thread705-1624516 Hi all, I a

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.

CODE -->

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 

RE: thread705-1624516 Hi all, I a

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close