Calculating Interest with Various Rates and Time Periods

[dacheng]

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.

 

[oldbrewer]

where does the $1000 come from

can you sort out the formatting and put dates as mm dd yyyy

you have a 0001-01-01 in there !!!

 

[dacheng]

Hi Oldbrewer,

It's difficult to have it formatted as I cannot attached the excel. If possible, can you provide your email add and I'll email the workbook to you?

0001-01-01 is not a typo. It simply means the first day of the first month of the first year. As as 9999-12-31.

Thanks,

 

[dacheng]

The total charge amount (including original charge and interest) is given.

 

[oldbrewer]

0001-01-01 is not a typo. It simply means the first day of the first month of the first year. As as 9999-12-31.

when is year 9999, then ?

 

[dacheng]

Year 9999 simply means keep using the rate end with year 9999. For example, the last row Acc Code: 19, always use rate 18% for charges whose due dates are after 2016-07-16.
The rates will be modified by NYC Dept. of Finance occassionaly. Year 9999 is just a place holder or a indifinite distant year into the future.

 

[oldbrewer]

ah I see !!! 2150 is far enough into the future for me

are customers often borrowing more ? a loan of 1000 at 5% for 3 years is easy, and if the 5% is variable that is easy, but I really (sorry) cannot see what you want.

can you start with a simpler example - locating a code within a list of codes is eeasy trying to help - honest...

 

[dacheng]

FYI: This is not really for borrowing. It's interest the government charge for unpaid charges. It doesn't matter.
My question is whether I can build a single formulas to calculate the interest amount and the original charge amount given the interest rates, time past due and total charge as of today.

Example: Property Tax was due on April 1, 2000. It hasn't been paid, today the total charge for that account (original property tax + interest amount) reaches $5,000.

By doing a VLOOKUP, we can find the Property Tax account code is 17,--> then tell Excel to use code 17 to locate interest rate in the Interest Rates table,
--> HOW TO TELL Excel
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

 

[dacheng]

FYI: This is not really for borrowing. It's interest the government charge for unpaid charges. It doesn't matter.
My question is whether I can build a single formulas to calculate the interest amount and the original charge amount given the interest rates, time past due and total charge as of today.

Example: Property Tax was due on April 1, 2000. It hasn't been paid, today the total charge for that account (original property tax + interest amount) reaches $5,000.

By doing a VLOOKUP, we can find the Property Tax account code is 17,--> then tell Excel to use code 17 to locate interest rate in the Interest Rates table,
--> HOW TO TELL Excel to use 15% rate (the high rate, at this example, high rate=low rate) for the time period April 1, 2000-Dec. 6, 2004 and
18% rate for the period Dec. 6, 2004 - Jan. 18, 2005, then 15% for Jan. 18, 2005-Jul.1, 2005, then 9% for Jul.1, 2005 - Today (use "=Today()")

Building a formulas like this,I can drag it down to fill all instead of building the formular for each of the charges.
Hopefully, I make myself clear. If not, you can always email me at dl769@cornell.edu. Thx.