# Calculating Interest with Various Rates and Time Periods

#### dacheng

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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### oldbrewer

##### Well-known Member
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

##### New Member
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

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

#### oldbrewer

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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

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

Replies
1
Views
240
Replies
13
Views
176
Replies
4
Views
94
Replies
0
Views
144
Replies
6
Views
706

1,181,058
Messages
5,927,882
Members
436,575
Latest member
Tiger750

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back