Formula to reduce balance monthly

[rition]

Hello

I have a figure in E30 which I would like to reduce by a set amount
each month, is it possible to write a formula to do this?

Thank you

 

[The Cardinal]

It's certainly possible to do this. What's the scenario though.
presume you want to keep the figure in E30 rather than have a list o
historical figures

 

[Bob Phillips]

Not with a formula, it would need VBA.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[rition]

It's certainly possible to do this. What's the scenario though. I
presume you want to keep the figure in E30 rather than have a list of
historical figures?

If possible I would like to keep the figure in E30 but will accept it
any way it will work.

 

[joeu2004]

I have a figure in E30 which I would like to reduce by a set amount
each month, is it possible to write a formula to do this?

You question is not entirely clear to me, but I wonder if something
like the following will suit your needs. If E29 contains the initial
amount and E28 contains the date of the initial amount, then following
computes the current remaining amount, reduced by a fixed amount
($1000) for each intervening month:

=max( E29, E29 - $1000 * ( 12*(year(today()) - year(E28)) +
month(today()) - month(E28) ) )

Notes:
1. This computes zero months if today() is in the same month as E28.
If you want to compute one month in that case, simply add 1 as follows:
$1000*(1 + 12...).

2. The max() function ensures that if the initial amount (E29) is not
an exact multiple of the monthly reduction ($1000), the formula
computes the remainder (less than $1000) for the last month.

3. You could use DATEDIF() instead as follows:
$1000*datedif(today(),E28,"m").