Calculating Intrest and Late Fees

D

deezeejoey

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
Click to expand...

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

Niek Otten

That is not easy to do with just one formulas, although I'm sure one of the array formula gurus will come up with an answer!
I always create a table for this:

A1: 118
B1: =A1+15
C1: =B1*1.02
A2: =C1
Copy B1 and C1 to the row below
Copy row 2 down the rows, ending in row 11

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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
|
Click to expand...
|
| Note, his is a little off; month 1 = month 0, month 2 would actully equal 1
| month late.
|
 
Top