I already have Analysis ToolPak Add-in box checked but this problem still
occurs occasionally. Please help.

On Jul 27, 10:02 am, WinC wrote:
I already have Analysis ToolPak Add-in box checked but this
problem still occurs occasionally. Please help.

Learn to put the entire question into the body of your posting, even
if it duplicates the subject line.

Do you truly mean #N/A, or do you mean #NAME?

#NAME indicates a problem a problem with ATP installation. #N/A
indicates a reference to a blank cell or NA() function.

Do you truly mean Excel 2 (introduced in 1987 and replaced in 1990!),
or do you mean Excel 2002?

XIRR and NOMINAL are both in Excel 2002 ATP. It would not surprise me
if they are missing from Excel 2.

If you cannot correct the source of the error, if you post here how
you are using these functions, we might be able to suggest
alternatives.

Many people use XIRR() unnecessarily, when IRR() or even RATE() would
suffice.

NOMINAL(eRate, nPer) can be replaced with either of the following,
whichever seems clearer to you:

=nPer * ((1+eRate)^(1/nPer) - 1)
=nPer * rate(nPer, 0, -1, 1+eRate)

But the alternatives may or may not return the same #N/A error,
depending on the source of the problem.

Thanks and Sorry - The question is "XIRR and NOMINAL formulas become #N/A
when Open/Save As in Excel 2007"

Now I think about it, perhaps the problem is NOMINAL function, not XIRR.
Since NOMINAL returns #N/A results, XIRR cannot work properly.

I use XIRR to calculate IRR over a stream of irregular dates (periods change
from time to time).

I am wondering if I should reinstall Excel to make sure NOMINAL works.

"joeu2004" wrote:

On Jul 27, 10:02 am, WinC wrote:
I already have Analysis ToolPak Add-in box checked but this
problem still occurs occasionally. Please help.

Learn to put the entire question into the body of your posting, even
if it duplicates the subject line.

Do you truly mean #N/A, or do you mean #NAME?

#NAME indicates a problem a problem with ATP installation. #N/A
indicates a reference to a blank cell or NA() function.

Do you truly mean Excel 2 (introduced in 1987 and replaced in 1990!),
or do you mean Excel 2002?

XIRR and NOMINAL are both in Excel 2002 ATP. It would not surprise me
if they are missing from Excel 2.

If you cannot correct the source of the error, if you post here how
you are using these functions, we might be able to suggest
alternatives.

Many people use XIRR() unnecessarily, when IRR() or even RATE() would
suffice.

NOMINAL(eRate, nPer) can be replaced with either of the following,
whichever seems clearer to you:

=nPer * ((1+eRate)^(1/nPer) - 1)
=nPer * rate(nPer, 0, -1, 1+eRate)

But the alternatives may or may not return the same #N/A error,
depending on the source of the problem.