I am looking for a formula that will calculate a accrued late fee with two
variables €œlate fee€� and €œintrest€�.The folloing in the logic behind what Im
trying to do. My goal is to do this with one formula ? I have been exploring
the financial formulas with absolutely no luck.....


Open Amount [$1,182.50] + (2% late fee) + (1.5% interest) = "Month 1 Amount"

Month 1 Amount + (2% of $1,182.50)) + (1.5% of Month 1 Amount) = "Month 2
Amount"

Month 2 Amount + (2% of $1,182.50) + (1.5% of Month 2 Amount) = "Month 3
Amount"

.....and so on for the x of months overdue...

any help would be much appreciated€¦€¦

Hi

With Open Amount in A1, late fee% in B1, Interest% in C1 and number of
months in D1
=A1*(1+(B1+C1))^D1
will give the total value outstanding

--
Regards

Roger Govier


"JAC" wrote in message
...
I am looking for a formula that will calculate a accrued late fee with
two
variables "late fee" and "intrest".The folloing in the logic behind
what I'm
trying to do. My goal is to do this with one formula ? I have been
exploring
the financial formulas with absolutely no luck.....


Open Amount [$1,182.50] + (2% late fee) + (1.5% interest) = "Month 1
Amount"

Month 1 Amount + (2% of $1,182.50)) + (1.5% of Month 1 Amount) =
"Month 2
Amount"

Month 2 Amount + (2% of $1,182.50) + (1.5% of Month 2 Amount) = "Month
3
Amount"

....and so on for the x of months overdue...

any help would be much appreciated..

On Mar 8, 6:12 am, JAC wrote:
I am looking for a formula that will calculate a accrued late fee with two
variables "late fee" and "intrest".The folloing in the logic behind what I'm
trying to do. My goal is to do this with one formula ?
[....]
Open Amount [$1,182.50] + (2% late fee) + (1.5% interest) = "Month 1 Amount"
Month 1 Amount + (2% of $1,182.50)) + (1.5% of Month 1 Amount) = "Month 2
Amount"
Month 2 Amount + (2% of $1,182.50) + (1.5% of Month 2 Amount) = "Month 3
Amount"
....and so on for the x of months overdue...

I believe the following does what you want:

=fv(1.5%, A1, -A2*2%, -A2)

where A1 is "x" periods and A2 is the "open amount" (initial
outstanding balance).

Month n amount would equal
=((1+late/rate)*(1+rate)^n - late/rate)*initial
where late=2%=0.02, rate=1.5%=0.015, and initial=$1,182.50.

If late=0, this reduces to the usual formula for compound interest of
(1+rate)^n*initial

Jerry

"JAC" wrote:

I am looking for a formula that will calculate a accrued late fee with two
variables €œlate fee€� and €œintrest€�.The folloing in the logic behind what Im
trying to do. My goal is to do this with one formula ? I have been exploring
the financial formulas with absolutely no luck.....


Open Amount [$1,182.50] + (2% late fee) + (1.5% interest) = "Month 1 Amount"

Month 1 Amount + (2% of $1,182.50)) + (1.5% of Month 1 Amount) = "Month 2
Amount"

Month 2 Amount + (2% of $1,182.50) + (1.5% of Month 2 Amount) = "Month 3
Amount"

....and so on for the x of months overdue...

any help would be much appreciated€¦€¦

Jerry,

Thanks you for the push.. Well more then that... you solved the entire
issue.. I very much appreciate it.. I was bugging me all night...

Cheers
Joel

"Jerry W. Lewis" wrote:

Month n amount would equal
=((1+late/rate)*(1+rate)^n - late/rate)*initial
where late=2%=0.02, rate=1.5%=0.015, and initial=$1,182.50.

If late=0, this reduces to the usual formula for compound interest of
(1+rate)^n*initial

Jerry

"JAC" wrote:

I am looking for a formula that will calculate a accrued late fee with two
variables €œlate fee€� and €œintrest€�.The folloing in the logic behind what Im
trying to do. My goal is to do this with one formula ? I have been exploring
the financial formulas with absolutely no luck.....


Open Amount [$1,182.50] + (2% late fee) + (1.5% interest) = "Month 1 Amount"

Month 1 Amount + (2% of $1,182.50)) + (1.5% of Month 1 Amount) = "Month 2
Amount"

Month 2 Amount + (2% of $1,182.50) + (1.5% of Month 2 Amount) = "Month 3
Amount"

....and so on for the x of months overdue...

any help would be much appreciated€¦€¦

You're welcome. Glad it helped.

Jerry

"JAC" wrote:

Jerry,

Thanks you for the push.. Well more then that... you solved the entire
issue.. I very much appreciate it.. I was bugging me all night...

Cheers
Joel