## coldfusion IRR calculation

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

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