Difference in calculation on Excel and Farpoint: IRR Calculation

Difference in calculation on Excel and Farpoint: IRR Calculation

Posted by: skibum-62 on 8 September 2017, 3:56 am EST

• 

  skibum-62

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:56 am EST

  Hi,

  I'm using Farpoint 3.0 and MS Excel 2003.

  The way "IRR" is being calculated for Excel is entirely different from Farpoint grid!

  With a value set of:

  -1,922,398   3,504,367    26,660,750    27,678,467    29,067,533

  and Guess factor as: 150%,

  I'm getting a value of 416.08% on Excel.

  And a value of -345.66% on Farpoint.

  Please throw some light on this. Is it an inherent problem with Farpoint? Is there any workaround or equivalent formula present in Farpoint for IRR?

  Thanks for your time.

  Vips.

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:56 am EST

  Vips,

  I will try to throw some light on this. First of all I believe both FarPoint Spread and Excel's answers are both correct and incorrect. Let me try to explain. The way that Spread AND Excel calculate IRR is to take the root of the NPV function. Basically if you graph the calculation, the IRR would return where the graph crosses the x coordinate. Sometimes, this can return 2 answers. This is why we have a guess factor to help determine the correct value.

  The NPV of your serries of numbers is a positive number. The graph of this equation returns a negative slope for an IRR of 416.08%. This is not the correct IRR. -345.66 has a positive slope which is why we assumed it is the correct answer, but this does not work logically. Thus, bith answers are incorrect. If you use a Financial Calculator, it returns N/A for the numbers you passed. This is what should be passed as the result. Because of this, it is reported as a bug in the FarPoint product, but since our calculation returned a true result, this is marked as a low priority bug at this time.

  To see that both answers are correct, you can use the NPV function with the rate set to the IRR. Both IRR rates return 0.

  If you have a way of calculating this with better precision, you can create a custom function and override the Evaluate method to perform your own calculation. If you do this and find a way to calculate the numbers you are using correctly to N/A, please share on the forums. I am sure there are others, including ourselves that would like a way to not return false positive values for this function.

• 

  vipulksinha

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:56 am EST

  Scott,

  Unfortunately, none of the users who intend to use my application, will understand all of this. Even I myself wasn't too sure.

  I used Farpoint in my app to emulate functionalities of MS Excel. But since I'm not getting the desired results, I'm now making use of a function IRR provided by .NET.

  Following is the code snippet that I use now to calculate IRR:

  Dim Guess, RetRate, Values(4) As Double

  Dim Fmt, Msg As String

  Guess = 1.5 ' Guess starts at 10 percent.

  Fmt = "#0.00" ' Define percentage format.

  Values(0) = -1922398 ' Business start-up costs.

  ' Positive cash flows reflecting income for four successive years.

  Values(1) = 3504367

  Values(2) = 26660750

  Values(3) = 27678467

  Values(4) = 29067533

  RetRate = IRR(Values, Guess) * 100 ' Calculate internal rate.

  Msg = "The internal rate of return for these five cash flows is "

  Msg = Msg & Format(RetRate, CStr(Fmt)) & " percent."

  MsgBox(Msg) ' Display internal return rate.

  This gives the value of IRR as it's expected with MS Excel.

  Thanks.

  Vips.

• 

  rachelgomez161999

  • Post Options:
  • Link
    Copy

  Posted 3 March 2022, 8:30 pm EST

  IRR calculated in Excel by-
  
  Image result for Difference in calculation on Excel and Farpoint: IRR Calculation
  
  Excel’s IRR function calculates the internal rate of return for a series of cash flows, assuming equal-size payment periods. Using the example data shown above, the IRR formula would be =IRR(D2:D14,. 1)*12, which yields an internal rate of return of 12.22%.

  The main difference between the IRR and NPV is that NPV is an actual amount while the IRR is the interest yield as a percentage expected from an investment.

  -Investors typically select projects with an IRR that is greater than the cost of capital.
  
  -However, selecting projects based on maximizing the IRR as opposed to the NPV could increase the risk of realizing a return on investment greater than the weighted average cost of capital (WACC) but less than the present return on existing assets.
  
  -IRR represents the actual annual return on investment only when the project generates zero interim cash flows—or if those investments can be invested at the current IRR. Therefore, the goal should not be to maximize NPV.

  Greeting,
  
  Rachel Gomez