Toggle sidebar Toggle sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Excel 2008 Calculation Errors

T

TXFinanceGuy

macrumors newbie
Original poster
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.
 
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.
 
ggjstudios said:
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.
Click to expand...

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
pawprint.gif
 
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?
 
TXFinanceGuy said:
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?
Click to expand...
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.
 
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.
 

Attachments

TXFinanceGuy said:
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.
Click to expand...

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.
 
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.
 
jazzkissmac said:
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.
Click to expand...
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.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back