Analysis Toolpak - Excel 2008

[albireo]

Hello, does anybody know how to replicate the XIRR formula from the Analysis
Toolpak in Excel 2008 (given that the Toolpak itself seems to be misssing)?
Many thanks,

 

[Mike Middleton]

albireo -

I think the functions that were in the Analysis ToolPak are now built-in
functions in Mac Excel 2008 (and in Windows Excel 2007).

So, to check, type =XIRR(...

It works for me.

It's the Data Analysis menu items of the Analysis ToolPak that are missing
in Mac Excel 2008.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[albireo]

Hi Michael,

Many thanks. I can see the function in Excel but it returns the #NUM! error,
in an existing spreadsheet which calculates no problem in the Win version of
Excel 2007.

Regards,

Martin

 

[albireo]

Well, in fact the problem seems to be in Excel 2008's inability to calculate
formulae that intentionally include circular references (such as to calculate
the interest on a loan where the loan amount is determined by reference to
the total cash deficit, including the interest). These formulae return the
#VALUE! error in Excel 2008 whereas they calculate as normal in Excel 2007.
Oh dear,....
Regards, Martin

 

[JE McGimpsey]

Well, in fact the problem seems to be in Excel 2008's inability to calculate
formulae that intentionally include circular references (such as to calculate
the interest on a loan where the loan amount is determined by reference to
the total cash deficit, including the interest). These formulae return the
#VALUE! error in Excel 2008 whereas they calculate as normal in Excel 2007.
Oh dear,....

Oh dear, indeed...

You've reported both the #NUM! and the #VALUE! error. Are you seeing
both?

If you have intentional circular references, are you setting Iterations
as Help explains (that needs to be done in XL07, too)?

 

[albireo via MacKB.com]

I am seeing the #NUM! message all the time in the XIRR cells and the #VALUE!
message occasionally when I change parameters that affect the cell with the
circular reference. Interestingly, when I save the file in Excel 2008 and
then reopen in Excel 2007, the same errors occur - however when I retype the
formulae in Excel 2007 (exactly as they were before), it catches up and
recalculates ok. Then when I save the file again in Excel 2007, and re-open
it in Excel 2008, the same problem occurs again. Looks like my in investment
in Parallels was a good one, at least until the first fixes become available
for Office 2008.

 

[albireo via MacKB.com]

I am seeing the #NUM! message all the time in the XIRR cells and the #VALUE!
message occasionally when I change parameters that affect the cell with the
circular reference. Interestingly, when I save the file in Excel 2008 and
then reopen in Excel 2007, the same errors occur - however when I retype the
formulae in Excel 2007 (exactly as they were before), it catches up and
recalculates ok. Then when I save the file again in Excel 2007, and re-open
it in Excel 2008, the same problem occurs again. Looks like my in investment
in Parallels was a good one, at least until the first fixes become available
for Office 2008.

 

[Joe_LeBlanc]

Hello albireo,

Could you provide a sample of the file in which you saw this behavior, or an example of the formula? emailing it to me, (e-mail address removed), or posting a link to it would be very helpful. Thanks!