Calculating Late Fees

[kaistarr]

I have 5 years of data by month. I'm trying to calculate the total amount due each month, assuming the bill was never paid, to find out how much is owed. For example:

Date: Amount:
12/1/2006 $21.10
11/1/2006 $21.10
10/1/2006 $21.10

etc...

A 10% penalty is added for the 30 days overdue each month. In addition, a .5% penalty is added to the balance over 60 days due.

Any idea how I can caluclate what is owed? Thank you!

 

[venkat1926]

data sheet with calculation formulas given below

Excel Workbook
	A	B	C	D
1	Date:	Amount:	penalty	total
2	12/1/2006	$21.10	3.165	$24.27
3	11/1/2006	$21.10	2.11	$23.21
4	10/1/2006	$21.10
Sheet1

slight ambiguity in your statement. is this 10% and 5% are for each month or overall. I have taken it as over all. any how if necessary modify the formula

first create formula in C3 NS d3 (USUALLY DATES ARE GIVEN IN ASCENDING ORDER BUT YOU HAVE GIVEN IN DESCENDING ORDER PERHAPS DUE TO SOME NEED)