## 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 .

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

My datebase like this:

As

Thank you very much.

My demo file: Link

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 .

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

## CODE

## RE: thread705-1624516 Hi all, I a

RS - RE rate start to rate end

## CODE -->

## RE: thread705-1624516 Hi all, I a

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

## RE: thread705-1624516 Hi all, I a

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

## RE: thread705-1624516 Hi all, I a

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

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

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

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 Datebeside 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

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

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

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

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