ExcelBanter - Calculating Intrest and Late Fees

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Calculating Intrest and Late Fees (https://www.excelbanter.com/excel-discussion-misc-queries/191580-calculating-intrest-late-fees.html)

Calculating Intrest and Late Fees

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

Quote:

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.

Calculating Intrest and Late Fees

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

"deezeejoey" wrote in message ...
| 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
|

Quote:

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