Transcripts for the above video clip:

=#N/A Error when using Analysis Toolpak functions in Excel 2003 files opened in Excel 2007 in compatibility model

There currently seems to be a bit of a problem in Excel files  that are converted from Excel 2003 to Excel 2007 but stay in what is called “Compatibility Mode” so that they can be viewed in either Excel 2003 and  2007. The problem  also only seems  to occur with  functions that are apparent in the Analysis Tool Pack eg  EOMONTH, XNPV, XIRR, NOMINAL, EFFECT, EDATE, CUMPRINC AND CUMIPMT etc.  You’ll notice that in this file,  this  cell , G31,  contains a formula that makes use of the NOMINAL function. What it  is  trying  to do is create  a benchmark  or an index for the CPI based on whatever number is here. Now what seems to happen is when you take a file that was  in Excel 2003 and you open it up in Excel 2007 and it is not consistent at all,  a message suddenly  appears that says “file error , data may have been lost”. But for all intents and purposes when you look at the spreadsheet you can’t see any obvious errors.  If you look more closely, however, so we are going to a file that has got it and you look at the same cell, what you’ll discover is that the formula that previously existed suddenly disappears and  is replaced by =#N/A. So if you look at the file, you’ll   see previously we had a formula like this and now we have this formula. The strange thing about this, though, is that even though it has got a nonsensical formula, there are still numbers showing here – which doesn’t quite make sense. So if I go to an empty sheet – if I’ve got a formula that says =#N/A, it should generate an N/A error message and any formula referring to it,  should generate an error and  therefore the whole spreadsheet should be corrupted and you should see that there is an error. However with this error even though the underlying formula is nonsensical the number is still shown and the rest of the spreadsheet continues to make use of that number. So now let’s look at what happens when we start changing some of the inputs – I will go back to the pre-error – this number here –  182.54 – I will go in this cell and let’s  change it to 6%. When I click “enter”, you’ll notice that it changes the index and therefore the rest of the spreadsheet changes.  If, however, with   this  error  in it we change the number to 6%,  you’ll see nothing happens. Because there is no formula, no change is made but the rest of the spreadsheet continues to look at  this number (182.54)  and it works with this number. This can have a serious effect on your models because even though you think you’re makng changes and it is being reflected, it is actually NOT being reflected.

How can you see if you have got this problem? The easiest  solution is to use the Find key (Control F) and type in Find what: =#N/A.  To  make it more thorough click on “options”. Instruct   Excel not just to look at the sheet but at the entire workbook. Type “next”  and it will show you every situation. You will see here   I  have something which is representing  a date and it has the formula  =#N/A – so there is another problem. Basically you will find this in any single   cell which has  Analysis Tool Pack functions in it. Depending on what  your  spreadsheet is doing it could have a serious impact on whether the answers you are looking at are correct or not.

Unfortunately there seems to be no easy way to correct this mistake, you can’t undo. Once you have the =#N/A error you have to recreate the formula  and try to figure out what was there previously  and replace it to get it to work again  or find an older version of the file which hasn’t been corrupted.

Some of the formula that may be affected by the =#n/a error include.

Depreciation Formulas

• * AMORLINC - (for the French accounting system) Depreciation for each accounting period
• * AMORDEGRC - (for the French accounting system) Uses a depreciation coefficient

Formulas for Interest, Cash Flow, Investments, Annuities

• * CUMIPMT - Cumulative Interest Payment
• * CUMPRINC - Cumulative Principal
• * EFFECT - Effective annual interest rate
• * FVSCHEDULE - Future Value with a variable rate
• * XIRR - Internal Rate of Return (not necessarily periodic)
• * XNPV - Net Present Value (not necessarily periodic)

Functions for Coupons

• * COUPDAYBS - Days from the Beginning of the Coupon period to the Settlement date
• * COUPDAYS - Days in the coupon period that contains the Settlement date
• * COUPDAYSNC - Days from the Settlement date to the Next Coupon date
• * COUPNCD - Next Coupon Date after the settlement date
• * COUPPCD - Previous Coupon Date before the settlement date
• * COUPNUM - Number of coupons between the settlement and maturity date

Finance Formulas for Securities

• * ACCRINT - Accrued Interest
• * ACCRINTM - Accrued Interest at Maturity
• * DISC - Discount rate
• * DURATION - Annual Duration
• * INTRATE - Interest rate for a fully invested security
• * MDURATION - Macauley modified duration (with an assumed par value of $100)
• * NOMINAL - Annual nominal interest rate
• * ODDFPRICE - Price per $100 face value with an Odd First period
• * ODDFYIELD - Yield with an Odd First period
• * ODDLPRICE - Price per $100 face value with an Odd Last period
• * ODDLYIELD - Yield with an Odd Last period
• * PRICE - Price per $100 face value
• * PRICEDISC - Price per $100 face value of a Discounted security
• * PRICEMAT - Price per $100 face value of a security that pays interest at Maturity
• * RECEIVED - Amount received at maturity for a fully invested security
• * YIELD - Yield on a security that pays periodic interest
• * YIELDDISC - Annual yield for a discounted security (Treasury bill)
• * YIELDMAT - Annual yield of a security that pays interest at maturity

Formulas for Dollar Price Conversions

• * DOLLARDE - Converts a dollar price from a Fraction to a Decimal number
• * DOLLARFR - Converts a dollar price from a Decimal number to a Fraction

Treasury Bill Functions

• * TBILLEQ - Bond-equivalent yield for a Treasury Bill
• * TBILLPRICE - Price per $100 face value for a Treasury Bill

* TBILLYIELD - Yield for a Treasury Bill