## Difference in calculation on Excel and Farpoint: IRR Calculation

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

• Post Options:

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?

Vips.

• Post Options:

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.

• Post Options:

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.

• Post Options:

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

##### Need extra support?

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

• #### ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• #### ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server