Excel 2008 Calculation Errors

Discussion in 'Mac Apps and Mac App Store' started by TXFinanceGuy, Sep 8, 2008.

  1. TXFinanceGuy macrumors newbie

    Joined:
    Sep 8, 2008
    #1
    Has anyone noticed substantial calculation errors in EXCEL 2008 - specifically, the XIRR and XNPV function?

    I ran a cash-flow series and then ran both the XNPV and XIRR function. Then i used an alternative calculation methodology, using the SUMPRODUCT and Goal Seek functions, to calculation the NPV and IRR of the same series of monthly cash flows (note that these alternative calculation methodologies are used to produce the same incremental calculations that are supposed to be rolled up into the XNPV and XIRR functions).

    Results: the XNPV is off by 1.26% and XIRR is off 26.77%

    I guess that isnt to bad when considering the investment of millions of $. XNPV would only cost me $126,000 on the million. So much for Gold Standard of financial analysis.

    What ever happened to Lotus 1-2-3? It might be archaic but at least it generated proper answers.
     
  2. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #2
    Chances are far greater that something in your formulas is incorrect, rather than Excel incorrectly calculating. I've never found an error in Excel calculations.
     
  3. MacDawg macrumors P6

    MacDawg

    Joined:
    Mar 20, 2004
    Location:
    "Between the Hedges"
    #3
    I would say I have to agree here... sometimes there are "errors" because of the undisplayed decimals, and accuracy as displayed is not checked, but this sounds like some forumula issues to me.

    Woof, Woof – Dawg [​IMG]
     
  4. reclusivemonkey macrumors 6502

    reclusivemonkey

    Joined:
    Jun 2, 2008
    Location:
    Sowerby Bridge, West Yorkshire, UK
  5. TXFinanceGuy thread starter macrumors newbie

    Joined:
    Sep 8, 2008
    #5
    I will assume that the anemic responses about calculation error are from those that haven't independently run a comparison via separate calculation.

    Unless you know how to calculate the XIRR or XNPV via the root formulas for net present value and internal rate of return, you would never know if EXCEL's XIRR and XNPV results are incorrect?
     
  6. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #6
    You know the old saying about "assume". I've been using spreadsheets since Lotus 123 and have extensive experience in financial modeling, business forecasting and investment analysis. Excel calculates XIRR and XNPV correctly.

    It's amusing to me that people will have an experience that they don't understand or don't expect, either with Mac OS X or with a software application, and they quickly jump to the conclusion that Mac OS X or the application is in error, as if it's beyond the realm of reason that it could be user error! :)

    While this really isn't the forum for Excel training, if you want to post your formulas and data, I'll be happy to check them for you.
     
  7. TXFinanceGuy thread starter macrumors newbie

    Joined:
    Sep 8, 2008
    #7
    Cash flow model to test

    There should be a PDF of an EXCEL file attached to this post (not able to attach EXCEL files for some reason). It is a sample cash flow series to show the variance between two separate calculation methods.

    I am making the assumption that everyone knows how to calculate XIRR and XNPV via the built-in EXCEL functions.

    Alternative methodology for determining a monthly net present value (or, rather, a net present value with cash flows tied to a specific date).

    1. Set the discount rate (10% in the example)
    2. Number each cash flow element 1,2,3... (the discount month)
    3. Calculate the monthly discount factor for each discount month, as follows:
    =EXP((-$discount rate/12)*(discount month 1-$discount month 1))
    Drag this formula across to create the monthly discount factor for each discount month. Discount month 1 will have a factor of 1 and will decrease each month thereafter
    4. Multiply the arrays (monthly cash flow elements * monthly discount factor) using the SUMPRODUCT function.
    5. The result is the NPV of the monthly cash flow elements.

    Note the discrepancy between this calculation and the XNPV.


    Alternative methodology for XIRR calculation

    1. Recreate the above described model for determining monthly NPV.
    2. Use the Goal Seek tool to adjust the discount rate to the necessary level to reduce the SUMPRODUCT result to "0", as the definition of IRR is that discount rate needed to result a NPV of "0".

    Again, note the discrepancy between the results.

    If someone feels that i have made a formula error, then please note said error and supply the correction.

    Looking forward to the response.
     

    Attached Files:

  8. nadyne macrumors 6502a

    Joined:
    Jan 25, 2004
    Location:
    Mountain View, CA USA
    #8
    I'm sorry to say that I'm not an Excel expert, but I can point you to people who are. Could you post this question to the Excel forum? The Excel MVPs and team monitor that forum and answer in-depth questions like this. If there is a calculation issue, this is the best way to get it directly in front of the team.

    Regards,
    Nadyne.
     
  9. jazzkissmac macrumors newbie

    Joined:
    Jan 20, 2009
    #9
    Error Calculating doubling of 2

    I recently watched a video where a professor explained that exponential growth wasn't sustainable. He used two different examples:

    One: He plotted growth using percentages over a fixed period of time.
    This doesn't give a problem.

    Two: He described a chess board having 64 squares. Putting 1 grain on the first and 2 on the second 4 on the third and 8 on the fourth and so on. Dubbeling each square.

    He asked how many grains would be on the board in total.

    I decided to do the calculation in excel 2008 to find out. I formatted making sure the numbers displayed as numbers and not in scientific notation (1.0E18).

    I used two methods:

    One: Power() function
    Filled one column with the number of squares. and used the power() to calculate the next column.
    Two: Put number 1 and calculate each row consecutively by multiplying each row by 2.

    Did both to the 64 row (64 squares on chess board). However at 2 to the power 50(51) both methods excel starts to make mistakes.

    Example they both give the answer:
    562.949.953.421.312 x 2 = 1.125.899.906.842.620

    it should be 1.125.899.906.842.624

    Any ideas / solutions / answers anyone.
     
  10. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #10
    Excel only gives accuracy out to 15 significant digits. You have 16, so the last will be off.
     
  11. sushi Moderator emeritus

    sushi

    Joined:
    Jul 19, 2002
    Location:
    キャンプスワ&#
    #11
    With Excel 2004, here is what I did.

    Created a new worksheet.

    In cell A1, typed in a 1.

    Selected (highlighted) cells A1 through A64.

    EDIT --> FILL --> SERIES... Selected columns, linear with a step value of one.

    In cell B1, I entered the following formula:

    =POWER(2,A1)

    Selected cell B1, then dragged the formula down all 64 rows so the A1 changed to A2, A3 ... A64 automatically.

    I came up with the same error for number 49 to 50. Might be a rounding error.
     
  12. Izzy macrumors member

    Joined:
    Dec 3, 2002
    Location:
    Saskatoon, Saskatchewan
    #12

    You have an error in your methodology. Please see the attachment.
     

    Attached Files:

Share This Page