## coldfusion IRR calculation

apache poi xirr

I am trying to replicate the IRR (internal rate of return) function in excel. I found one cfc in riaforge.com but it doesn't return the same value as the excel's irr.

The newton - raphson method uses derivatives and I am not sure how to calculate derivatives in coldfusion.

year cash flow ---- -------- 0 -4000 1 1200 2 1410 3 1875 4 1050

should return 14.3% ( from wikipedia's example )

Has anybody done this before? thanks

Extending to what Jason said, you would need to implement a code that works efficiently and not rely on the brute force algorithm that Falconeyes suggested. nothing personal here the first time i programmed IRR as a server side script it was using brute force and a day later my web host called me as said they were taking my site offline as the code was consuming 100% system resources

What follows is a step by step IRR calculation using Newton Raphson method and you can follow it and implement the ideas in Cold Fusion

f(x) = -4000(1+i)^0 +1200(1+i)^-1 +1410(1+i)^-2 +1875(1+i)^-3 +1050(1+i)^-4 f'(x) = -1200(1+i)^-2 -2820(1+i)^-3 -5625(1+i)^-4 -4200(1+i)^-5 x0 = 0.1 f(x0) = 382.0777 f'(x0) = -9560.2616 x1 = 0.1 - 382.0777/-9560.2616 = 0.139965195884 Error Bound = 0.139965195884 - 0.1 = 0.039965 > 0.000001 x1 = 0.139965195884 f(x1) = 25.1269 f'(x1) = -8339.5497 x2 = 0.139965195884 - 25.1269/-8339.5497 = 0.142978177747 Error Bound = 0.142978177747 - 0.139965195884 = 0.003013 > 0.000001 x2 = 0.142978177747 f(x2) = 0.126 f'(x2) = -8256.0861 x3 = 0.142978177747 - 0.126/-8256.0861 = 0.142993440675 Error Bound = 0.142993440675 - 0.142978177747 = 1.5E-5 > 0.000001 x3 = 0.142993440675 f(x3) = 0 f'(x3) = -8255.6661 x4 = 0.142993440675 - 0/-8255.6661 = 0.142993441061 Error Bound = 0.142993441061 - 0.142993440675 = 0 < 0.000001 IRR = x4 = 0.142993441061 or 14.3%

**Internal Rate of Return – IRR Definition,** Net Present Value (NPV)
For each amount (either coming in, or going out) work out its Present Value, then: Add the Present Values you receive. Subtract the Present Values you pay. The IRR formula is as follows: Calculating the internal rate of return can be done in three ways: Using the IRR or XIRR XIRR Function The XIRR function is categorized under Excel Financial functions. The function will calculate the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic.

I don't know what ColdFusion is, but the idea for finding IRR is very simple.

The IRR is a number `r`

such that

sum i = 0 to N C_i * (1 + r)^(-t_i) = 0

where there are `N + 1`

cashflows `C_0, C_1, ..., C_N`

at times `t_0, t_1, ..., t_N`

. Define

f(r) = sum i = 0 to N C_i * (1 + r)^(-t_i).

Then

f'(r) = sum i = 0 to N -C_i * (1 + r)^(-t_i - 1).

Choosing an initial guess `r_0`

and iterate via

r_{n + 1} = r_n - f(r_n) / f'(r_n)

In your specific example, you have

t_0 = 0 C_0 = -4000 t_1 = 1 C_1 = 1200 t_2 = 2 C_2 = 1410 t_3 = 3 C_3 = 1875 t_4 = 4 C_4 = 1050

Try a guess of `r_0 = 0.1`

.

Again, I don't know what ColdFusion is, but it has to be a programming language, and so it should allow this basic math to be computed.

**Internal Rate of Return (IRR),** , you would need to "reverse engineer" what r is required so that the NPV equals zero. The internal rate of return allows investments to be analyzed for profitability by calculating the expected growth rate of an investment’s returns and is expressed as a percentage.

<cffunction name="calcIRR"> <cfargument name="arrCashFlow" type="Array" required="true" hint="array of cashflow"> <cfscript> var guess = 0.1; var inc = 0.00001; do { guess += inc; npv = 0; //net present value for (var i=1; i<=arrayLen(arguments.arrCashFlow); i++) { npv += arguments.arrCashFlow[i] / ((1 + guess) ^ i); } } while ( npv > 0 ); guess = guess * 100; </cfscript> <cfreturn guess> </cffunction> <cfscript> cFlow = arrayNew(1); cFlow[1] = -4000; cFlow[2] = 1200; cFlow[3] = 1410; cFlow[4] = 1875; cFlow[5] = 1050; c = calcIRR(cFlow); </cfscript> <cfdump var="#cFlow#"> <cfdump var="#c#">

**What is the formula for calculating internal rate of return (IRR) in ,** I don't know what ColdFusion is, but the idea for finding IRR is very simple. f(r) = sum i = 0 to N C_i * (1 + r)^(-t_i). f'(r) = sum i = 0 to N -C_i * (1 + r)^(-t_i - 1). Try a guess of r_0 = 0.1 . How to calculate IRR in Excel with formulas. Microsoft Excel provides 3 functions for finding the internal rate of return: IRR - the most commonly used function to calculate the internal rate of return for a series of cash flows that occur at regular intervals. XIRR – finds IRR for a series of cash flows that occur at irregular intervals

I tried all submited solutions and none of them worked like it should(like in excel). Here is the code for calculating XIRR that is working like it should. For the IRR you just need to change this line:

<cfset npv = npv + (arguments.values[i] / ((1 + arguments.rate) ^ time_span))>

And here is the whole script

<cfoutput> #XIRR(values=[-5000,500,110500], dates=["2015-07-06","2016-07-06","2017-07-06"])# </cfoutput> <cffunction name="XIRR"> <cfargument name="values" required="true"> <cfargument name="dates" required="true"> <cfset var do_calculation = check_data(values=arguments.values, dates=arguments.dates)> <cfif do_calculation.is_ok> <cfset var rate = 1> <cfset var npv = calculate_NPV(rate=rate, values=arguments.values, dates=arguments.dates)> <cfloop condition="#npv# gt 10e-6"> <cfset rate = rate + 1> <cfset npv = calculate_NPV(rate=rate, values=arguments.values, dates=arguments.dates)> </cfloop> <cfloop from="1" to="6" index="pow"> <cfset fac = 1 / (10 ^ pow)> <cfset npv = 0> <cfloop condition="#npv# lt 10e-6"> <cfset rate = rate - fac> <cfset npv = calculate_NPV(rate=rate, values=arguments.values, dates=arguments.dates)> </cfloop> <cfset rate = rate + fac> </cfloop> <cfreturn rate> <cfelse> <cfreturn "error: #do_calculation.error#"> </cfif> </cffunction> <cffunction name="check_data"> <cfargument name="values" required="true"> <cfargument name="dates" required="true"> <cfset var is_ok = true> <cfset var has_negative = false> <cfset var has_positive = false> <cfset var error = 0> <cfset var return = structNew()> <cfset var date_prev = ""> <cfset var date_curr = ""> <cfif arguments.values[1] gte 0> <cfset is_ok = false> <cfset error = -1> </cfif> <cfloop array="#arguments.values#" item="value"> <cfif value gt 0> <cfset has_positive = true> </cfif> <cfif value lt 0> <cfset has_negative = true> </cfif> </cfloop> <cfif !has_negative or !has_positive> <cfset is_ok = false> <cfset error = -2> </cfif> <cfif arrayLen(arguments.values) neq arrayLen(arguments.dates)> <cfset is_ok = false> <cfset error = -3> </cfif> <cfloop from="2" to="#arrayLen(arguments.dates)#" index="d"> <cfset date_prev = arguments.dates[d-1]> <cfset date_curr = arguments.dates[d]> <cfif dateDiff("d", date_prev, date_curr) lte 0> <cfset is_ok = false> <cfset error = -4> </cfif> </cfloop> <cfset return.is_ok = is_ok> <cfset return.error = error> <cfreturn return> </cffunction> <cffunction name="calculate_NPV"> <cfargument name="rate" required="false" default="1"> <cfargument name="values" required="true"> <cfargument name="dates" required="true"> <cfset var npv = arguments.values[1]> <cfset var time_span = ""> <cfloop from="2" to="#arrayLen(arguments.values)#" index="i"> <cfset time_span = dateDiff('d', arguments.dates[1], arguments.dates[i]) / 365> <cfset npv = npv + (arguments.values[i] / ((1 + arguments.rate) ^ time_span))> </cfloop> <cfreturn npv> </cffunction>

**Calculating Internal Rate of Return Using Excel or a Financial ,** Calculates Internal Rate of Return (IRR) similar to excel IRR function. Calculate IRR. @author CF Ninja (coldfusion.ninja@hotmail.com) @version 1, November 13, 2014 ---> <cffunction name="calcIRR" output="false"> The IRR is the discount rate which, when plugged into the NPV formula, gives an NPV of zero. In the Generic Widgets example, the formula would look like this: [100,000/ (1+IRR)]+ [200,000/ (1+IRR) 2 ]+ [300,000/ (1+IRR) 3] - 500,000 = 0 Managers can attempt to plug in different rates to approximate the IRR.

**coldfusion IRR calculation,** The calculation of the IRR is easier when the cash flows are of a constant amount Let us now calculate the internal rate of return for project B in Example 13.1. Enter the cash flow for each period. Once ready, press "Calculate" and our IRR calculator will output: the Internal Rate of Return, a.k.a. discount rate. the Gross Return in percentages. the Net Cash Flow (Profit - Loss)

**calcIRR,** And what if you needed a total cell containing a formula to SUM the to excel sheet some data in C1 to C14 and formula to calculate say IRR Based on our comparison table and our internal rate of return example calculation of IRR levered above, it seems that Project B is better, since it requires almost the same amount of investment as Project A, while offering 30% instead of 20% IRR levered.

**Management and Cost Accounting,** - Use YEARFRAC to calculate the distance of each midpoint date from the valuation date. Please note that YEARFRAC provides 5 calculation For calculating the IRR, the NPV value is set to zero and then the discount rate is found out. This discount rate is then the Internal Rate of Return value that we needed to calculate.

##### Comments

- I was going to suggest the lazy route, as POI has an IRR function. It may not be included in the built in version. But breaking it down and writing your own, as Jason suggests, sounds better. Not to reinvent the wheel, but to better understand the function. poi.apache.org/apidocs/org/apache/poi/ss/formula/functions/…
- i can't even see the code of this IRR() function.
- That is just the usage API ;) For the actual java source you need to go to SVN svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/…
- Good point. The POI version seems to use that approach svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/…
- The code on Apache looks professional. I have a similar piece of code in C# elsewhere yet I can't recall where I have seen it
- @Leigh I actually tried Newton-Raphson method mentioned here :svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/… The results were quite different then what was expected.
- the solution can be easily ported over to java, c#, javascript, python and many others. I will write some unit tests for ColdFusion using your formula above and see how it compares to that of excel. I will appreciate if you can provide some inputs and outputs. Thanks :)
- Nice job. Just add a
`var`

scope for`npv`

. After you bang it around for a while, you might consider submitting it to cflib.org :) - @Leigh, that's a good idea. It might help someone else save some time.
- Yep. I am sure others will appreciate it. Just take a look a the other suggestions and see if it can be improved / optimized stackoverflow.com/questions/7112919/coldfusion-irr-calculation/…
- @CFNinja when i try to use this values -2941876 33000 22748 8867 the function does not work, the IRR is -71%, but the function return 10.001 , does anyone know why?
- @Deivi, there are a number of different algorithms and guess numbers. If you can prove that this solution does not work for "n" cash flow arrays over "i" solutions, then i will revisit the posted solution. let me know, thanks.