Excel 2007 : How to calculate an annual reduction rate from a monthly variable

Hi,

This is probably dead easy and obvious, but Excel is not my forte. My boss wants to play with some what if? scenarios in Excel and has asked me to do the prep work

I have a value (A) that reduces every month by a fixed percentage value (Variable Y). E.g. 3% reduction month on month.
A is a product of a load of calculations on another worksheet so I can't be more specific.
How do I calculate the annual percentage reduction rate of Y, so that I can tell the boss what A is at the end of year 1, 2, 3 etc?

I tried to do this in a similar way as an APR calculation and got as far as G14=1-(B12/12)*12, but that clearly doesn't work.

Thanks

Asparak

I have done this longhand for now on the base data sheet and then reference the annual percentage, but I get 28.47% from a 3% month on month.

If anyoine can think of a cleaner way of doing this, rather than longhand, I would be really grateful.

Hi,

A colleague from another office solved it for me. Using a VBA module to do the donkey work for me

Please Login or Register  to view this content.