Difference in calculation on Excel and Farpoint: IRR Calculation

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

  • 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.

  • Replied 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.

  • Replied 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:

    <FONT color=#0000ff size=2>
    <BLOCKQUOTE dir=ltr>

    Dim</FONT><FONT size=2> Guess, RetRate, Values(4) </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Double

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> Fmt, Msg </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String

    </FONT><FONT size=2>

    Guess = 1.5 </FONT><FONT color=#008000 size=2>' Guess starts at 10 percent.

    </FONT><FONT size=2>

    Fmt = </FONT><FONT color=#800000 size=2>"#0.00"</FONT><FONT size=2> </FONT><FONT color=#008000 size=2>' Define percentage format.

    </FONT><FONT size=2>

    Values(0) = -1922398 </FONT><FONT color=#008000 size=2>' Business start-up costs.

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>' Positive cash flows reflecting income for four successive years.

    </FONT><FONT size=2>

    Values(1) = 3504367


    Values(2) = 26660750


    Values(3) = 27678467


    Values(4) = 29067533


    RetRate = IRR(Values, Guess) * 100 </FONT><FONT color=#008000 size=2>' Calculate internal rate.

    </FONT><FONT size=2>

    Msg = </FONT><FONT color=#800000 size=2>"The internal rate of return for these five cash flows is "

    </FONT><FONT size=2>

    Msg = Msg & Format(RetRate, </FONT><FONT color=#0000ff size=2>CStr</FONT><FONT size=2>(Fmt)) & </FONT><FONT color=#800000 size=2>" percent."

    </FONT><FONT size=2>

    MsgBox(Msg) </FONT><FONT color=#008000 size=2>' Display internal return rate.</FONT>

    </BLOCKQUOTE>

    <FONT color=#000000>This gives the value of IRR as it's expected with MS Excel.</FONT>


    Thanks.


    Vips.<FONT color=#008000 size=2>

    </FONT>
  • Replied 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
  • Replied 30 May 2022, 1:52 am EST

    IRR stands for Internal Rate of Return, which helps analysts choose a specific project on the basis of the cash flows expected. In case of IRR, it is the rate at which the Net Present Value, that is the difference between the present value of cash inflows and present value of cash outflows is equal to zero.

    It is a great measure to make our analytical essay effective with https://kingessays.com/reflective-essay.php.

    Excel provides us with an in built formula to calculate IRR.

    The user needs to input the necessary parameters and the IRR is calculated.

    I hope that helps!
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels