Question on XIRR Usage

[W]

I have a population of users that is expanding daily. At any given day, I
want to know what is the rate of growth over the entire period since
measurements began. How can I use XIRR to determine that rate of growth?

Say my data is:

1/27/2010 8199
1/28/2010 8298
1/31/2010 8750

How would I apply XIRR to that set to get the implied growth rate from 1/27
to 1/31?

 

[joeu2004]

I have a population of users that is expanding daily.
At any given day, I want to know what is the rate of growth
over the entire period since measurements began.   How can I
use XIRR to determine that rate of growth?

Say my data is:
1/27/2010    8199
1/28/2010    8298
1/31/2010    8750

How would I apply XIRR to that set to get the implied growth
rate from 1/27 to 1/31?

XIRR would be painful to use with this data because, as you may know,
it requires that you use opposite signs for some data. In fact, the
data would require more modifications to be consistent with XIRR
assumptions ("cash flows" = change, not current values).

Moreover, XIRR would annualize the computed growth rate. It's unclear
that that is what you want because you use ambiguous terms like "rate
of growth over the entire period" and "implied growth rate from 1/27
to 1/31".

Suppose your data are in A1:B3.

The simple growth rate is =B3/B1-1. In this case, that is the 3-day
growth rate.

The one-day simple growth rate is =B3/B1/(A3-A1)-1.

The annualized simple growth rate is =(B3/B1)*365/(A3-A1)-1.

The compounded daily growth rate is =(B3/B1)^(1/(A3-A1))-1.

The annualized compounded growth rate is =(B3/B1)^(365/(A3-A1))-1.

Which of those formulas you should use depends on your purpose.

Note: You might be able to use RATE to compute the compounded daily
growth rate; for example, =RATE(A3-A1,0,-B1,B3). However, sometimes
RATE fails with a #NUM error indicating that you need to provide a
"guess" in the optional 4th parameter. I don't know of any way to
determine a "good" guess.

PS: Just curious: did you receive my response to your email? I
might have flubbed your SpamArrest verification. In any case, the
response was: I do not do consulting work; but if you want to send me
your Excel file (preferrably compatible with XL2003) along with a
description of what you want to do, I will see if I can help.

 

[joeu2004]

Errata....

The simple growth rate is =B3/B1-1.  In this case,
that is the 3-day growth rate.

Actually, the __4__day growth rate between 1/27 and 1/31 counting
calendar days.

Or the __2__day growth rate counting only business days.

I ass-u-me-d you wanted calendar days because you mentioned XIRR.
XIRR knows nothing about business days.

The one-day simple growth rate is =B3/B1/(A3-A1)-1.
The annualized simple growth rate is =(B3/B1)*365/(A3-A1)-1.
The compounded daily growth rate is =(B3/B1)^(1/(A3-A1))-1.
The annualized compounded growth rate is =(B3/B1)^(365/(A3-A1))-1.

If you wanted business days, you could use NETWORKDAYS(A1,A3) or
NETWORKDAYS(A1,A3,H1:H100) instead of A3-A1, where H1:H100 contains
additional holidays (business closure dates).

But you would also want to replace 365 with some number of business
days less holidays, an average per year.

 

[W]

XIRR would be painful to use with this data because, as you may know,
it requires that you use opposite signs for some data. In fact, the
data would require more modifications to be consistent with XIRR
assumptions ("cash flows" = change, not current values).

Yes, so I had converted the column of values to:

-8199
8199
99
452

but applying XIRR I still get #NUM!

I added back 8199 to the negative initial outlay just to simulate the idea
of getting back the initial investment immediately.

Moreover, XIRR would annualize the computed growth rate. It's unclear
that that is what you want because you use ambiguous terms like "rate
of growth over the entire period" and "implied growth rate from 1/27
to 1/31".

I want annualized rate of growth, exactly.

I did get your email thanks.

 

[joeu2004]

Yes, so I had converted the column of values to:
-8199
8199
99
452
but applying XIRR I still get #NUM!

Patient:
"Nurse! My head hurts when I bang it against the wall!"

Nurse:
"Don't do that."

I'll tell you what's wrong with your XIRR usage if you promise not to
use XIRR. It is overkill. It is wrong-minded for your purpose.

(It even gives the "wrong" answer in this case! I'll explain below.)

I want annualized rate of growth, exactly.

But do you want the simple or compounded growth rate?

Best way to decide is: graph your historical data and see which
trendline fits best: linear or exponential.

My guess: you should use the simple growth rate. But with just the 3
data points that you provide, it's impossible to know.

In any case, use one of the following formulas -- reiterated with
corrections.

Simple annualized growth rate:
=(B3/B1-1)*365/(A3-A1)

Compounded annualized growth rate:
=(B3/B1)^(365/(A3-A1))-1

With your data and the latter formula, the compounded annualized
growth rate is 37705.4100705437%+6*2^-44 (!).

It is easier to work with the compounded daily growth rate, (B3/B1)^(1/
(A3-A1))-1. That is 1.63932955682007%-5*2^-58.

(That is, the compounded daily growth rate is (1+CAGR)^(1/365)-1.)

If we use that to compute the daily user count over 5 days (1/27
through 1/31), we get about: 8199, 8333, 8470, 8609, and 8750.

(Hint: Do __not__ explicitly round the intermediate user counts.)

Note that the intermediate user counts do not match your data exactly.

Not surprising. The reason is that the computed growth rate is an
__average__ growth rate.

Only the 5th day (1/31) should match.

-----

As for XIRR as you wish to use it, your "cash flows" should be:

1/27/2010 -8199
1/28/2010 -99
1/31/2010 8750

Think of it this way.... Initially you "invest" 8199 users. The next
day you "invest" an additional 99 users. And on the 5th day you
"withdraw" all the users that you invested plus additional users that
were "invested" in the interim.

Contorted? You betcha!

With that set up, Excel XIRR(B1:B3,A1:A3) returns
12727.9145050049%-12*2^-46.

And the daily IRR is computed using (1+XIRR(...))^(1/365)-1.

With that the daily IRR, the daily user count over 5 days is about:
8199, 8312, 8426, 8541, 8659.

Note that the XIRR result is less than the correct CAGR computed above
using (B3/B1)^(365/(A3-A1))-1.

Ergo, the 5th-day user count is incorrect.

And that's all the more reason not to use XIRR in the first place.

The XIRR is "wrong" because the model is wrong for your purpose.

The concept behind IRR is the time-value of money: early inflows are
worth more than later ones; and later outflows are less costly than
early ones.

But presumably that is not the case for your purpose.

Okay, you could make a case for applying that concept to user counts:
the more users you have early on, perhaps the more revenue you might
generate from advertisements and sponsors.

But the point is: that is not a factor in what you are trying to
predict, namely the number of users at any given time.

That is dictated by stochastic factors. The users don't know that it
is to your advantage to join earlier rather than later. At least,
they do not take that into account.

Is that clear? It is a complicated concept to grasp.

If you don't get it, just trust me: use the compounded CAGR formula
that I provided first, not XIRR.

 

[joeu2004]

PS....

The XIRR is "wrong" because the model is wrong for your purpose.

I should have said "because __that__ model" is wrong for your purpose.

There __is__ an XIRR model that should have about the same result as
the CAGR, namely simply:

1/27/2010 -8199
1/31/2010 8750

And indeed XIRR result is about the same, then.

But again, XIRR is overkill.

 

[W]

But do you want the simple or compounded growth rate?

For less than one year of data, compound and simple rate should be the same,
and I have less than one year of data.

There is really no way to get XIRR to work for my case?

 

[W]

PS....

I should have said "because __that__ model" is wrong for your purpose.

There __is__ an XIRR model that should have about the same result as
the CAGR, namely simply:

1/27/2010 -8199
1/31/2010 8750

And indeed XIRR result is about the same, then.

I can try that, but in this case how do you get XIRR to operate on *only*
the endpoints of an array of numbers or dates? Given that XIRR takes a
range of cells, is there a cell range syntax that restricts XIRR to use
specific values in the array?

 

[joeu2004]

For less than one year of data, compound and simple rate
should be the same, and I have less than one year of data.

That is simply incorrect mathematically. Look at the formulas. If
you don't understand, simply "do the math". That is, implement both
in Excel with your data and observe the results.

There is really no way to get XIRR to work for my case?

Your posting and my PS cross on the wire. See my PS.

But why bother? What's your love affair with XIRR?

To make matters worse, Excel XIRR does not always work without your
help. That is why your usage returned the #NUM error, even though the
root cause was your incorrect model in this case.

The point is: sometimes Excel XIRR needs us to provide a "guess".
And there is no good way for us to do that.

Why not use the CAGR formula? It is straight-forward. And it is
correct!

 

[joeu2004]

I can try that

I was not encouraging you to. I was trying to demonstrate how silly
it is.

but in this case how do you get XIRR to operate on *only*
the endpoints of an array of numbers or dates?   Given that
XIRR takes a range of cells, is there a cell range syntax
that restricts XIRR to use specific values in the array?

No. Yet another reason to give up on using XIRR.

The Excel IRR function accepts "union ranges". For example, you could
write IRR((B1,B3)). But of course, that does not help you because IRR
assumes that the "cash flows" are evenly spaced, not true in your
case.

We would like to write XIRR((B1,B3),(A1,A3)). But XL2003 XIRR does
not accept that. I don't know about XL2007 and XL2010 XIRR.

If you put a gun to my head and forced me to use XIRR, I would set
aside a 2x2 range where I might write the following formulas:

X1: =B1
X2: =B3
Y1: =A1
Y2: =A3

Then we could use XIRR(X1:X2,Y1:Y2) -- if it does not return a #NUM
error for the lack of a "guess" parameter.

And of course, we could make the formulas in X1:Y2 fancier so that
they automagically adapt to our data.

Not worth my time. This is the last I will speak of the matter.

Read my lips: DO NOT USE XIRR.

Sorry for "shouting". You seem to be hard of hearing. ;-)

 

[W]

I was not encouraging you to. I was trying to demonstrate how silly
it is.

The reason to prefer XIRR is that it is a simple universally understood
function. If it doesn't work for my case so be it.

Your other formulas were complex and make it harder to document the
spreadsheet. I'll struggle with them, and thanks for giving those
options.

 

[W]

That is simply incorrect mathematically. Look at the formulas. If
you don't understand, simply "do the math". That is, implement both
in Excel with your data and observe the results

You are right. XIRR appears to be sensitive to when you receive cash flows,
even for a one year calculation.

 

[joeu2004]

I promised to shut up, but I can't resist one last parting shot.

The reason to prefer XIRR is that it is a simple universally
understood function.   If it doesn't work for my case so be it.

I would say that XIRR is a universally misunderstood and misused
function -- as evidenced by your efforts.

The CAGR formulas that I provided are also "universally understood" --
at least among people who know what they're doing.

(Not to belittle you in any way. We have something to learn ometime.)

Go to http://en.wikipedia.org/wiki/Compound_annual_growth_rate.
Notice that there is no mention of IRR, much less XIRR.

Of course, XIRR is an Excel term. And before XL2007, XIRR was part of
the ATP, which many Excel users do not install.

So I would quibble with your claim of universal knowledge of it.
Believe it or not, the whole computing world does not use Excel.

Your other formulas were complex and make it harder to
document the spreadsheet.

I will grant you that they are more complex, obviously. But
correctness must take precedence over form.

 

[joeu2004]

You are right.  XIRR appears to be sensitive to when you
receive cash flows, even for a one year calculation.

Actually, I was not talking about XIRR. I was talking about these two
formulas:

Simple annualized growth rate:
=(B3/B1-1)*365/(A3-A1)

Compounded annualized growth rate:
=(B3/B1)^(365/(A3-A1))-1

There __is__ a difference regardless of how short the timeframe is.

But I should point out that I neglected to explain how to use the
__daily__ simple growth rate. If the daily growth rate is x%, then
you add $B$1*x% each day instead of B[i-1]*x%, which is compounding.

Really, my point was: look at the trendline of the data and use
whichever fits best.

 

[W]

But I should point out that I neglected to explain how to use the
__daily__ simple growth rate. If the daily growth rate is x%, then
you add $B$1*x% each day instead of B[i-1]*x%, which is compounding.

Really, my point was: look at the trendline of the data and use
whichever fits best.

Probably you are some kind of IRR prodigy. Thanks for your guidance.