EXCEL calculation errors (XIRR, XNPV)

[Inc._NRC]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I want to revive the old post dealing with calculation errors in 2008. From previous posts on this matter i see that i am not the only finance type of person to run into these calculation errors. That said, i have created an EXCEL workbook that calculates the XIRR and XNPV associated with a CF stream. The worksheet also employs an alternatively methodology to calculate the XIRR and XNPV (SUMPRODUCT and GOAL SEEK, respectively). This worksheet, therefore, displays the magnitude of the errors.

Additionally, EXCEL 2008 also produces #NUM errors when a change is made to cash flow elements when it should automatically update the XIRR an/or XNPV result.

At the end of the day, EXCEL 2008 is an impotent tool for investment analysis until it is fixed.

If there is some way to attach a file to this post someone let me know and i will post the file so anyone interested can see the errors for themselves.

 

[Pat McMillan]

We are hoping to release a fix soon for the XIRR bug posted previously in
this forum, but I'm not sure if it will fix the issue(s) you're seeing. We
would absolutely love to take a look at your file with these problems and
work with you to understand the problem. If you could send the file to
myself ([email protected]) and Joe LeBlanc ([email protected]),
we'll investigate.

Thanks,

Pat

 

[Michael Dowling]

Hi,

I am also having a problem with XIRR etc...The problem I face is that when I receive a file from a PC user these formulas show up as errors. From memory when I was a PC user I had to enable "extensions" or "some type of Pack" or some such to get those formulas to work. Do I need to do that here?

Any help gratefully received. I have found this a real hassle in my 6 month transition to Mac.

 

[Andrew Paquin]

I do a lot of financial modeling and have been a PC user for years. I use excel all the time and rely on functions such as XIRR. I just completed a project using XIRR. I created this model on my PC.

However, when I opened this file in Microsoft Office for Mac 2008 (I just bought a Mac last week) the XIRR function returned either #NUM! or incorrect answers. This is a HUGE problem for me.

Any ideas? Happy to send you the file.

 

[jbiasi]

Like everyone else, I too have this problem... same spreadsheet works fine on a PC... but gives me a #num error. While the spreadsheet behind it is fairly complex, the actual use of XIRR is very minimal.

Funny thing, in the function builder, I can see the correct answer, but if I accept it I get a #NUM error.

Do you have a reproducible case? When I tried to simplify my spreadsheet down and took out all links and other tabs, the error disappeared...

Two things of note:
1). There is a circular error (necessary) on a different tab
2). Therefore iterations are limited to 100 times...

Even with those things unchanged, the errors come and go depending on the existence of the other tabs which have no bearing on the XIRR calc.

This basically removes my ability to use Excel on the MAC... is there any notion of when this will be fixed? Coupled with Fusion2.X problems (that bring MAC to a crawl), my environment is unusable....

Is there somewhere I can send this example spreadsheet?

Thanks

 

[Chris]

Pat and Joe: The XIRR bug is killing me as well! I still have Mac Excel X SR1 and it works perfectly there. The same files opened in Excel 2008 give #NUM! errors for the XIRR calculations. I can send you my spreadsheet if it will help the troubleshooting, but it seems widespread. I am running Mac OS 10.4.11 on PPC G5 processor.

 

[Pat McMillan]

Hi Chris,

I believe we have all the information we need on this problem and we are
working on a fix. We're hoping to get an update out with a fix soon, so
please watch for updates via Microsoft AutoUpdate.

Thanks,

Pat

 

[DLESHAW]

I just got a Mac to convert. I'm also getting hit with the XIRR bug. It doesn't calulate sometimes. Sometimes it calculates correctly in the Formula Builder but doesn't transfer the answer back to the spreadsheet cell. When I get #NUM, I can sometimes get the correct answer by double clicking in the cell. It will never recalculate if some number in the waterfall of calculations is changed. It sometimes does not react to =today() as a starting date and I need to hardcode. I HAVE EVEN HAR WIRED THE SAME SET OF DATES AND VALUES ON TWO DIFFERENT PARTS OF THE SAME SPREADSHEET AND GOT DIFFERENT ANSWERS OR NO ANSWER. It appears that there are a long list of problems resulting from the emasculation of Excel '08 which renders it rather useless for all but pictures and graphics. I would like to know 1) is there a package one can buy to get the statistical and financial functionality back and/or 2) is MSFT really going to fix these issues. If the intent is to have Excel '08 never have the same functionality of the Windows version I'd like to know now so I can return it. It will simply never meet my needs.

 

[MCD]

I am a recent convert to Mac. The 2008 programmes on the whole work fine. Excel is a particular problem. As reported by others when I receive a PC Excel file the XIRR return #Num error. This is a huge problem to me a large numbers of other Mac users. The move away from from the previous excel version and my PC to the Mac has been a hassle and while I love the machine I am hating the excel software.

MICROSOFT: WHEN WILL IT BE FIXED?

 

[Pat McMillan]

We are actively working on fixes for the XIRR issues that have been reported
and hope to release them soon. Unfortunately I can't give a specific date
because something could always happen to throw us off schedule and we could
miss any date we promise. But this is at the top of our priority list.

Regarding the previous poster's question, our intent is not to have the
functionality of Excel 2008 and Windows Excel 2007 be exactly the same. As
you may have noticed, earlier versions of Mac Excel have also not had
feature-by-feature parity with Windows versions.

We are, however, tracking customer requests for specific features available
only in Windows Excel and will use that to help us make decisions about
which features to add to the next version of Mac Excel.

Thanks,

Pat

 

[MCD]

Thanks Pat. Appreciate the honest answer. I am not aware of any information available to users that tells them Excel works differently on the two dominant platforms. If anything I was wooed by information that lead me to believe that harmonisation had been achieved by transition to the Intel and the fact that both products are called Office albeit with the differential "2008 for Mac". In any event the calculations that are offered should work the same way, unless there is a feeling that the "creatives" do not need accurate results. I am sure that is not the case and therefore the products should be the same - in my opinion.

 

[Pat McMillan]

Thanks for the feedback. On the calculation front, we recognize that
functions and formulas are really fundamental to Excel users and have tried
to keep our support for functions as close to identical with Windows Excel
as possible. On the other hand, a feature that is supported in Windows Excel
that hasn't been implemented in Mac Excel is pivot charts.

Thanks,

Pat

 

[DLESHAW]

Thanks for the feedback. On the calculation front, we recognize that

functions and formulas are really fundamental to Excel users and have tried
to keep our support for functions as close to identical with Windows Excel
as possible. On the other hand, a feature that is supported in Windows Excel
that hasn't been implemented in Mac Excel is pivot charts.

Thanks,

It isn't clear to me why MS thinks MAC users would be any less desirous of the functionality in Excel for Windows than Windows users. That includes XIRR, other date driven financial functions as well as all of he "Add Ins" or Stat PAck. The reason for going to MAC from Windows is the ease of use of the operating system, that lack of viruses and YES the better graphics and ancilarry visual uses.. BUT that doesn't mean I want to give up computing power. IT is after all, a "Computer".. it's supposed to compute. Taking out the VB based wizards and functions is not only insulting but dishonest. It smacks of laziness. The comment by one of the MS Business Unit folks that VB removal was well published is absurd. I don't read computer mags, never will, don't care and don't want to. I expect software to add functionality with each version. I expect it to run and run properly all the time, every time without excuses. There shouldn't be patches or workarounds. The fact that there is an XIRR function left in the Formula Builder BUT IT DOESN"T WORK is insane and a testament to simple disdain for the customer. It's just bad conduct. This is the great MS.. please explain while if functionality is in '04, why couldn't it be put in '08? WHY WOULD ANYONE RODUCE AN INFERIOR PRODUCT FOR THE "NEXT" VERSION???

It's tough to want to punish Apple for the sins of MS. They have done their part, now MS, if it has any capability at all needs to step up and replace/fix what is missing/broken. If this is a two year project for MS, I can only hope someone else at Apple or another vendor has a deisre to upgrade their product first. Apple's market share continues to grow. If MS Excel doesn't want to deliver the goods they can go the way of Oldsmobile.

 

[DLESHAW]

It isn't clear to me why MS thinks MAC users would be any less desirous of the functionality in Excel for Windows than Windows users. That includes XIRR, other date driven financial functions as well as all of he "Add Ins" or Stat PAck. The reason for going to MAC from Windows is the ease of use of the operating system, that lack of viruses and YES the better graphics and ancilarry visual uses.. BUT that doesn't mean I want to give up computing power. IT is after all, a "Computer".. it's supposed to compute. Taking out the VB based wizards and functions is not only insulting but dishonest. It smacks of laziness. The comment by one of the MS Business Unit folks that VB removal was well published is absurd. I don't read computer mags, never will, don't care and don't want to. I expect software to add functionality with each version. I expect it to run and run properly all the time, every time without excuses. There shouldn't be patches or workarounds. The fact that there is an XIRR function left in the Formula Builder BUT IT DOESN"T WORK is insane and a testament to simple disdain for the customer. It's just bad conduct. This is the great MS.. please explain while if functionality is in '04, why couldn't it be put in '08? WHY WOULD ANYONE RODUCE AN INFERIOR PRODUCT FOR THE "NEXT" VERSION???

It's tough to want to punish Apple for the sins of MS. They have done their part, now MS, if it has any capability at all needs to step up and replace/fix what is missing/broken. If this is a two year project for MS, I can only hope someone else at Apple or another vendor has a deisre to upgrade their product first. Apple's market share continues to grow. If MS Excel doesn't want to deliver the goods they can go the way of Oldsmobile.

 

[MCD]

Here here!