XIRR Function - #NUM error

[KevinGH]

I am attempting to get IRRs using the XIRR function. This is being used in
a model in which the dates are dynamic and can be changed. The XIRR function
is being used over a number of time periods, i.e. a 1-yr, 2-yr ... up to 8-yr
time period.

Some of the dates that I have chosen are giving me a #NUM error.
For example if I start the date sequence with July, 2008 in the 1-yr time
sequence, and include as other dates in that sequence Aug, 2008, etc I get
the #NUM error, but if I start that sequence with July 2008 in order to not
get the error I have to include a date that is many years out for example

August 1, 2008
August 1, 2010
December 1, 2013
January 1, 2014
February 1, 2014
March 1, 2014

The above scenario gets me an acceptable result (which is unrealistic for
the model) but the following which is more realistic gets me a #NUM error

August 1, 2008
August 1, 2008
December 1, 2008
January 1, 2009
February 1, 2009

 

[Niek Otten]

All dates must be later than the first one. You have the same date for 1st and 2nd.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| I am attempting to get IRRs using the XIRR function. This is being used in
| a model in which the dates are dynamic and can be changed. The XIRR function
| is being used over a number of time periods, i.e. a 1-yr, 2-yr ... up to 8-yr
| time period.
|
| Some of the dates that I have chosen are giving me a #NUM error.
| For example if I start the date sequence with July, 2008 in the 1-yr time
| sequence, and include as other dates in that sequence Aug, 2008, etc I get
| the #NUM error, but if I start that sequence with July 2008 in order to not
| get the error I have to include a date that is many years out for example
|
| August 1, 2008
| August 1, 2010
| December 1, 2013
| January 1, 2014
| February 1, 2014
| March 1, 2014
|
| The above scenario gets me an acceptable result (which is unrealistic for
| the model) but the following which is more realistic gets me a #NUM error
|
| August 1, 2008
| August 1, 2008
| December 1, 2008
| January 1, 2009
| February 1, 2009
|

 

[KevinGH]

OK, that was helpful, I no longer have the #NUM error after changing to a
date that is different from the first one, but the IRR is pretty much useless
if the date is close (which is the realistic scenario), I have 151718%,
whereas when I put in a much later date i have a better irr, at around 40%.

Any thoughts?

 

[Ron Rosenfeld]

All dates must be later than the first one. You have the same date for 1st and 2nd.

I know it says that in the HELP section , but this sequence, with the same date
for 1st and 2nd, does not give an error. The #NUM! error may be due to the
absence of an appropriate Guess.

5-Jan -10000
5-Jan 5000
10-Jan 5000
31-Dec 5000
--ron

 

[Ron Rosenfeld]

I am attempting to get IRRs using the XIRR function. This is being used in
a model in which the dates are dynamic and can be changed. The XIRR function
is being used over a number of time periods, i.e. a 1-yr, 2-yr ... up to 8-yr
time period.

Some of the dates that I have chosen are giving me a #NUM error.
For example if I start the date sequence with July, 2008 in the 1-yr time
sequence, and include as other dates in that sequence Aug, 2008, etc I get
the #NUM error, but if I start that sequence with July 2008 in order to not
get the error I have to include a date that is many years out for example

August 1, 2008
August 1, 2010
December 1, 2013
January 1, 2014
February 1, 2014
March 1, 2014

The above scenario gets me an acceptable result (which is unrealistic for
the model) but the following which is more realistic gets me a #NUM error

August 1, 2008
August 1, 2008
December 1, 2008
January 1, 2009
February 1, 2009

Check HELP for the various causes of a #NUM! error. The actual dates are
likely not relevant, so long as you meet the criteria of not having a date that
is any earlier than the first date:

The most common reason is the absence of an appropriate "Guess", but other
reasons are possible, too.
--ron