Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR Function - #NUM error
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR Function - #NUM error
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 "KevinGH" wrote in message ... | | 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 | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR Function - #NUM error
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? "Niek Otten" wrote: 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 "KevinGH" wrote in message ... | | 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 | |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR Function - #NUM error
On Tue, 8 Jul 2008 17:01:16 +0200, "Niek Otten" wrote:
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR Function - #NUM error
On Tue, 8 Jul 2008 07:41:01 -0700, KevinGH
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help for FV and XIRR function. | Excel Worksheet Functions | |||
xirr function | Excel Worksheet Functions | |||
xirr value error | Excel Worksheet Functions | |||
#VALUE error with XIRR | Excel Discussion (Misc queries) | |||
XIRR Function Error | Excel Worksheet Functions |