Calculating Interest with Various Rates and Time Periods

dacheng

New Member
Joined
Aug 3, 2016
Messages
6
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
Joined
Apr 11, 2010
Messages
11,005
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
Joined
Aug 3, 2016
Messages
6
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
Joined
Aug 3, 2016
Messages
6
The total charge amount (including original charge and interest) is given.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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
Joined
Aug 3, 2016
Messages
6
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
Joined
Apr 11, 2010
Messages
11,005
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
Joined
Aug 3, 2016
Messages
6
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
Joined
Aug 3, 2016
Messages
6
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top