XIRR function in Excel 2007

[nobuhle sibusiso]

I entered the xirr function to calculate a projected CAGR but it didn't work.
I was instructed to install and activate the Analysis Tool Pak Add-in. I
installed it and see it on the data tab, but the formula still does not work
properly. I have the formula entered as followed: =XIRR(C2:E2,C1:E1) where
c2-e2 are the values and c1-e1 are the dates viewed in mm/dd/yy format. All
I get is the #NUM! error in that field. How do I fix this problem? Also, I
was reading that here on the forum that there must be a negative number. If
so, won't this affect my outcome? I'm a real novice at this so I don't quite
understand.

 

[joeu2004]

I have the formula entered as followed:  =XIRR(C2:E2,C1:E1)
where c2-e2 are the values and c1-e1 are the dates viewed in
mm/dd/yy format.  All I get is the #NUM! error in that field.

Did you read the Help page to see if you are violating any of the
rules that cause #NUM?

 How do I fix this problem?  Also, I was reading that here on the
forum that there must be a negative number.  If so, won't this
affect my outcome?

Can I infer from your question that you do not have at least one cash
flow with a negative value and at least one cash flow with a positive
value?

If that is true, that is probably the cause of your #NUM error.

It would be helpful to know what you "cash flows" represent. It would
also be helpful to know what the dates -- especially, whether or not
they are regular in frequency. It is very possible that you do not
need to use XIRR at all.

Since you have only 5 value/date combinations, I suggest that you post
them along with whay they "cash flows" represent.

 

[joeu2004]

PS....

I was reading that here on the forum that there must be a negative number.
 If so, won't this affect my outcome?

Well yes, but not in a negative way <wink>. By convention, inflows
and outflows have opposite signs. Which is negative and which is
positive depends on your point of view. Most people choose positive
for inflows and negative for outflows; but what is an inflow or
outflow depends on your point of view. It is an arbitrary choice, as
long as you are consistent.

If you had a problem that XIRR is intended to solve -- namely real
cash flows that vary in both amount and frequency over time -- this
would be obvious to you. In a real (X)IRR problem, you invest some
initial capital and perhaps some periodic expenditures, and you get
perhaps some periodic revenue (which might offset expenditures in some
periods) and usually some final return of capital and perhaps profit.

But I suspect you are looking at periodic gains and losses in value of
an appreciable asset. In that case, the CAGR is computed simply based
on the initial and current values (unless there are non-reinvested
dividends paid in the interim). We can use positive values in the
mathematical form of the solution. Or we can use financial functions
(typically FV) to perform the mathematical computation, in which case
we must still use opposite signs for the initial and current values.

Again, this can be made much more clear to you if you provide the data
that you are working with and a description of the situation.