Hi all,
I'm trying to automate interest calculation given the total charge amount, due date, and interest rates for each period.
The following table is the interest rates table for account code and time period:
Acc Code BEG END Low Rate% High Rate%
13 0001-01-01 1990-07-01 15.00 15.00
13 1990-07-01 2004-12-06 15.00 15.00
13 2004-12-06 2005-01-18 .00 .00
13 2005-01-18 2005-07-01 15.00 15.00
13 2005-07-01 9999-12-31 9.00 18.00
15 0001-01-01 1990-07-01 15.00 15.00
15 1990-07-01 2004-12-06 15.00 15.00
15 2004-12-06 2005-01-18 .00 .00
15 2005-01-18 2005-07-01 15.00 15.00
15 2005-07-01 9999-12-31 9.00 18.00
17 0001-01-01 1990-07-01 15.00 15.00
17 1990-07-01 2004-12-06 15.00 15.00
17 2004-12-06 2005-01-18 .00 .00
17 2005-01-18 2005-07-01 15.00 15.00
17 2005-07-01 9999-12-31 9.00 18.00
19 0001-01-01 1969-08-01 7.00 7.00
19 1969-08-01 1977-01-01 7.00 11.00
19 1977-01-01 1979-07-01 7.00 15.00
19 1979-07-01 1981-07-01 7.00 18.00
19 1981-07-01 1982-07-01 7.00 25.50
19 1982-07-01 1983-07-01 7.00 23.00
19 1983-07-01 1984-07-01 7.00 16.50
19 1984-07-01 1985-07-01 7.00 18.50
19 1985-07-01 1987-07-01 7.00 16.00
19 1987-07-01 1988-07-01 7.00 15.00
19 1988-07-01 1989-07-01 7.00 15.50
19 1989-07-01 1990-07-01 7.00 19.00
19 1990-07-01 1991-07-01 10.00 19.00
19 1991-07-01 2004-12-06 9.00 18.00
19 2004-12-06 2005-01-18 .00 .00
19 2005-01-18 2005-07-01 9.00 16.00
19 2005-07-01 2016-07-01 9.00 17.00
19 2016-07-16 9999-12-31 6.00 18.00
Interest rates given are all annul rate and there are 365 days in any year. If a charge is past due, always use the high rate, but use the low rate for the last
To give a specific example: As of today, Aug.3 2016, Sidewalk (Acc Code 19) has a total charge of $1,000 (including original amount and interest amount) was due on April 1, 2005. What's the interest amount and original amount?
The challenging parts are:
1. to locate the Acc Code 19 (for sidewalk) in the table
2. then find out which periods and which rates to use:
The charge was due on April 1, 2005, so rates are:
19 2005-01-18 2005-07-01 9.00 16.00
19 2005-07-01 2016-07-01 9.00 17.00
19 2016-07-16 9999-12-31 6.00 18.00
16% for period 4/1/05-7/1/05,
17% for period 7/1/05-7/1/16,
18% for peridod 7/1/16-Today.
How to automate the how calculation? Thanks for any thoughts/helps.
you can also email me at dl769@cornell.edu.
I'm trying to automate interest calculation given the total charge amount, due date, and interest rates for each period.
The following table is the interest rates table for account code and time period:
Acc Code BEG END Low Rate% High Rate%
13 0001-01-01 1990-07-01 15.00 15.00
13 1990-07-01 2004-12-06 15.00 15.00
13 2004-12-06 2005-01-18 .00 .00
13 2005-01-18 2005-07-01 15.00 15.00
13 2005-07-01 9999-12-31 9.00 18.00
15 0001-01-01 1990-07-01 15.00 15.00
15 1990-07-01 2004-12-06 15.00 15.00
15 2004-12-06 2005-01-18 .00 .00
15 2005-01-18 2005-07-01 15.00 15.00
15 2005-07-01 9999-12-31 9.00 18.00
17 0001-01-01 1990-07-01 15.00 15.00
17 1990-07-01 2004-12-06 15.00 15.00
17 2004-12-06 2005-01-18 .00 .00
17 2005-01-18 2005-07-01 15.00 15.00
17 2005-07-01 9999-12-31 9.00 18.00
19 0001-01-01 1969-08-01 7.00 7.00
19 1969-08-01 1977-01-01 7.00 11.00
19 1977-01-01 1979-07-01 7.00 15.00
19 1979-07-01 1981-07-01 7.00 18.00
19 1981-07-01 1982-07-01 7.00 25.50
19 1982-07-01 1983-07-01 7.00 23.00
19 1983-07-01 1984-07-01 7.00 16.50
19 1984-07-01 1985-07-01 7.00 18.50
19 1985-07-01 1987-07-01 7.00 16.00
19 1987-07-01 1988-07-01 7.00 15.00
19 1988-07-01 1989-07-01 7.00 15.50
19 1989-07-01 1990-07-01 7.00 19.00
19 1990-07-01 1991-07-01 10.00 19.00
19 1991-07-01 2004-12-06 9.00 18.00
19 2004-12-06 2005-01-18 .00 .00
19 2005-01-18 2005-07-01 9.00 16.00
19 2005-07-01 2016-07-01 9.00 17.00
19 2016-07-16 9999-12-31 6.00 18.00
Interest rates given are all annul rate and there are 365 days in any year. If a charge is past due, always use the high rate, but use the low rate for the last
To give a specific example: As of today, Aug.3 2016, Sidewalk (Acc Code 19) has a total charge of $1,000 (including original amount and interest amount) was due on April 1, 2005. What's the interest amount and original amount?
The challenging parts are:
1. to locate the Acc Code 19 (for sidewalk) in the table
2. then find out which periods and which rates to use:
The charge was due on April 1, 2005, so rates are:
19 2005-01-18 2005-07-01 9.00 16.00
19 2005-07-01 2016-07-01 9.00 17.00
19 2016-07-16 9999-12-31 6.00 18.00
16% for period 4/1/05-7/1/05,
17% for period 7/1/05-7/1/16,
18% for peridod 7/1/16-Today.
How to automate the how calculation? Thanks for any thoughts/helps.
you can also email me at dl769@cornell.edu.