xirr #N/A

[Terry McCoy]

I have an issue with the XIRR and XNPV functions in Excel 2007. The formulas
seem to be operating properly, but when I go back into the spreadsheet after
closing it, the cells where the formulas were now say #N/A. I have not made
any changes to the spreadsheet. I do not have the analysis toolpak checked
on the add-ins, but I understand that these functions are integral to Excel
2007, so I should not need the toolpak. I would appreciate any help anyone
can give me with this issue. Thanks,

Terry McCoy

 

[Fred Smith]

XIRR is part of Excel 2007, so you don't have to install anything extra.
Besides if Excel didn't recognize the function, it would return #NAME rather
than #N/A.

The only reason I know of for returning #N/A is if you have #N/A as one of
the values or dates. Are you sure all the data is correct?

Maybe it's a recalculation problem. Do you have recalculation set to manual,
and it's being recalculated only on save?

Do you have any of your dates dynamic (ie, based on, for example Today())
which would force a recalculation every time you open the file?

If none of these suggestions help, then post an example of your formula and
data so we can investigate it further.

Regards,
Fred.

 

[Terry McCoy]

Fred - Thanks for taking the time to respond to my question. The formulas
don't have any of the issues you mention. My calculations are set to 100
iterations, I have had problems in older versions of excel that were
corrected by lowering the iterations. Do you think that could be the issue?

 

[Fred Smith]

If XIRR was returning #NUM, I'd check on the iterations. But as your error
is #N/A, it has nothing to do with iterations.

I can only reiterate what I said in the first post. The only reason I know
of for XIRR to return #N/A is bad input data (ie, one of the cells in either
the date or value range is #N/A). If you're sure both of your data ranges
have no #N/A's in them, I have no futher suggestions.

Fred.

 

[Ron Rosenfeld]

On Thu, 17 Apr 2008 10:18:03 -0700, Terry McCoy <Terry

I have an issue with the XIRR and XNPV functions in Excel 2007. The formulas
seem to be operating properly, but when I go back into the spreadsheet after
closing it, the cells where the formulas were now say #N/A. I have not made
any changes to the spreadsheet. I do not have the analysis toolpak checked
on the add-ins, but I understand that these functions are integral to Excel
2007, so I should not need the toolpak. I would appreciate any help anyone
can give me with this issue. Thanks,

Terry McCoy

#N/A indicates that a value is not available to the function or formula.

Could it be that some of the source data is not in an available workbook, at
the time you reopen the workbook containing the function?
--ron

 

[Terry McCoy]

Ron - I appreciate your help, and I think you are on to something. The
formulas link to another spreadsheet in the same workbook. One of the links
is not working when I re-open the workbook, it also has "=N/A" in it rather
than the link. I just re-established the link and closed and re-opened the
workbook, and it went right back to "=N/A". I am also getting a message when
I open the workbook of "File Error: Data may have been lost" which may be at
the root of this problem.

I would welcome any comments or suggestions. Thanks.

 

[Terry McCoy]

Folks - For those of you who care, I worked with Microsoft support yesterday,
and apparently the problem is a compatibility conflict between old Excel and
2007 Excel. I was using 2007 Excel, but the saving the files in the 2003
version of Excel. Since the XIRR and XNPV formulas need the analysis tool
pack in old Excel, but not in new Excel, this apparently created a conflict
and the formulas, and other parts of the spreadsheets, became corrupted. The
solution, (I hope), was to repair the files, and save them as Excel 2007
files. I still have to manually re-enter the formulas, but now, hopefully,
they will be stable and will not become corrupted.