Calculating Late Charges and Intrest

I'm trying to come up with a formula to calculate intest for me here at work.
Here'e my example i'm working on now.
I have a customer that owes $118.00.
Each month late there is a $15.00 late fee
and then 2% intrest added on top of that.
This customer is 11 months late.

What i am having trouble with is getting a formula to addthe 2% intrest each month after the $15.00 charge is added each month.
My fomula adds the inital charge, then the late fees, then the intrest based off that.

Right now my formula reads
[initial balance plus late fees]*1.02^late months
but over 11 months that comes to $351.84

it should come up $333

My friend came up with a program answer, which doesn't help in the case of Excel

Sorry, but all I have is a program oriented answer.

;variables
n=11
i=.02
p=118
f=15

;loop equation
for n
p=(p+f)*(1+i)

;outputs
month 1 118
month 2 135.66
month 3 153.67
month 4 172.05
month 5 190.79
month 6 209.90
month 7 229.40
month 8 249.29
month 9 269.58
month 10 290.27
month 11 311.37

Note, his is a little off; month 1 = month 0, month 2 would actully equal 1 month late.

I used Excels Future Value function

Please Login or Register  to view this content.

The interest is 0.02, the # of payments is the amount of months it's late which is equal to MONTH(TODAY()-Due Date. The payment amounts I set equal to the late fee of $15. Current value is the original bill ($118) and the code is set to 1 which means due at beginning of new month. Since this gives a negative number, I put a neg sign in front of it. With initial bill in A2 and billing date in B2, the formula becomes

Please Login or Register  to view this content.

This calculates to $332.90. Try it using a code of 0 for that last argument and see which comes closer to what you are expecting. Does this work for you? I attached my example.

ChemistB

Thank you so much, this has got to be it. I've been looking for answers all day. you finally got it.