coldfusion IRR calculation

Question

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

Answer 1

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>

Answer 2

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%

Answer 3

<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#">

Answer 4

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.