zondag, januari 27, 2013

Compound Annual Growth rate in Business Objects

To calculate the Compound Annual Growth rate, Excel has a function called XIRR. BO doesn't have that, but that's easy to remedy. All you need to do, is work out the following formula:

So, now we need a bunch of numbers and see where we get:

Right -- this has everything to do with investments, something I know nothing about, but here's some numbers to get us started :

On Jan 1 2010, we invest 10000€
On Jan 1 2011, our investment has grown to 13000€
On Jan 1 2012, our investment has grown to 15000€
finally, On Jan 1 2013, our investment has now a value of 19500€

The table looks like this in BO:

First, we'll divide our ending value by our beginning value -- interesting challenge.

Our last date would be : =Max([Date]) In Report = 1/1/2013
Our first date would be : =Min([Date]) In Report = 1/1/2010

So, our formula for that division becomes :

=[Value] where ([Date]=Max([Date]) In Report) / [Value] where ([Date]=Min([Date]) In Report)

I'll call this one basefigure

Now, we need to find out how many years it took:

The formula for that one is rather easy :

=Year(Max([Date]) In Report) - Year(Min([Date]) In Report)

And we divide 1 by that figure:

=1/(Year(Max([Date]) In Report) - Year(Min([Date]) In Report))

this one, I'll call exponent

There. Now, the next step would be to raise our first figure to the power of that second figure and subtract 1.


Our Compound Annual Growth rate would be : 0.25 -- I guess those figures occurred in a parallel universe :)


Cheers folks


Geen opmerkingen: