IRR ( cashFlowsList ; guessRate )

Calculate Internal Rate of Return of a value list of cash flows with an initial guessed rate-of-return, using the Newton-Raphson method.

Average rating: 4.3 (34 votes) Log in to vote

Lewis Lorenz - Show more from this author
Lorenz Companies
http://www.LorenzCom.com

Sample input:

IRR ( "-500000¶200000¶300000¶200000" ; .1 )
IRR ( List ( -2000 ; 600 ; 300 ; 500 ; 700 ; 400 ) ; .05 )
IRR ( List ( -5000 ; 10000 ; 0 ; 10000 ; 10000 ) ; .1 )

Sample output:

.1882462096837047
.0793216878811758
1.4633789720926647

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let ( [ 
cfct = ValueCount ( cashFlowsList ); // A decreasing number of cash flows, initially.
gct = ValueCount ( guessRate ); // 1 on first pass, 3 on template building and 4 on irr calculation iterations.
itct = If ( gct > 1; GetAsNumber ( GetValue ( guessRate; 4 ) ); 0 ) // Stored irr calculation iterations count.
];
Case 
( cfct > 0; // Create templates from cashFlowsList, working from the last to the first, until list is empty.
	Let ( [ 
	// Create values: guessRate & NPV template & NPVDerivative template & counter (default guessRate adjustable).
	guessRate = If ( gct < 2; If ( IsEmpty ( Trim ( guessRate ) ); .0001; guessRate ) & "¶¶¶0"; guessRate ); 
	// NPV:
	ftemplate = "(" & GetValue ( cashFlowsList; cfct ) & "/(1+g)^" & GetAsText ( cfct - 1 ) & ")+" & GetValue ( guessRate; 2 ) ; 
	ftemplate = If ( cfct = 1; Left ( ftemplate; Length ( ftemplate ) - 1 ); ftemplate ); // Trim last " + ".
	// NPV Derivative:
	fdtemplate =  "(-" & GetAsText ( cfct - 1 ) & "*" & GetValue ( cashFlowsList; cfct ) & "/(1+g)^(" & GetAsText ( cfct - 1 ) & "+1))+" & GetValue ( guessRate; 3 );
	fdtemplate = If ( cfct = 1; Left ( fdtemplate; Length ( fdtemplate ) - 1 ); fdtemplate ); // Trim last " + ".
	// Store templates in the guessRate parameter. Create 3 value list.
	guessRate = GetValue ( guessRate; 1 ) & "¶" & ftemplate & "¶" & fdtemplate
	];
	// Recursive call to prepend the next previous cash flow to the templates after removing last item from list.
	//If ( cfct > 1; IRR ( LeftValues ( cashFlowsList ; cfct - 1 ); guessRate ); cashFlowsList & "¶" & guessRate ); // For debug only.
	IRR ( LeftValues ( cashFlowsList ; cfct - 1 ); guessRate )
	);
itct < 21; // Test for correct rate. Maximum irr calculation iterations (adjustable).
	Let ( [ 
	gr = GetAsNumber ( GetValue ( guessRate; 1 ) );
	f = Evaluate ( Substitute ( GetValue ( guessRate; 2 ); "g"; GetAsText ( gr ) ) );	fd = Evaluate ( Substitute ( GetValue ( guessRate; 3 ); "g"; GetAsText ( gr ) ) );
	ir = gr - f / fd; // The essence of the Newton-Raphson Method
	b = ( Abs ( ir - gr ) <= 1.0e-7 ); // If ir is to the accuracy required, return ir as calculated irr (accuracy adjustable).
	gr = If ( b; gr; ir ); // Set guessRate to calculated ir if not true and try again.
	itct = itct +1;
	// Prepare for recursive call
	guessRate = GetAsText ( gr ) & "¶" & GetValue ( guessRate; 2 ) & "¶" & GetValue ( guessRate; 3 ) & "¶" & GetAsText ( itct )	];
	//If ( b; ir & "¶" & cashFlowsList & "¶" & guessRate; IRR ( cashFlowsList; guessRate ) ) // Debug only.
	If ( b; ir; IRR ( cashFlowsList; guessRate ) ) // Return ir or try again.
	);
-1 ) // Return -1 as error code.
)

/*
IRR ( cashFlowsList ; guessRate )

Created: 4-5-2013
Modified: 1-3-2014
Author: Lewis C. Lorenz

Calculate Internal Rate of Return of a value list of cash flows (cashFlowsList) using an initial guessed rate of return (guessRate). The parameter guessRate defaults to .0001 if initially blank. 

This function uses the Newton-Raphson method for speed and calculates to a modifiable decimal precision - presently set at 1.0e-7. The C source code from which this custom function was derived is included in the comments.

Examples:
	IRR ( "-500000¶200000¶300000¶200000" ; .1 ) = .1882462096837047
	IRR ( List ( -2000 ; 600 ; 300 ; 500 ; 700 ; 400 ) ; .05 ) = .0793216878811758
	IRR ( List ( -5000 ; 10000 ; 0 ; 10000 ; 10000 ) ; .1 ) = 1.4633789720926647

DISCLAIMER: Using this IRR function, several calculations were compared against the results obtained on other IRR calculators and the results matched. However, that does not guarantee a correct result in every operation of this custom function, so you assume this risk when you use it.
 
NOTICE: This is a recursive function. If you change the function name then also change it in the function code.

IRR by Newton-Raphson Method.       
	int maxIterationCount = 20;
        double absoluteAccuracy = 1E-7;

        double x0 = guess;
        double x1;

        int i = 0;
        while (i < maxIterationCount) {

            // the value of the function (NPV) and its derivate can be calculated in the same loop
            double fValue = 0;
            double fDerivative = 0;
            for (int k = 0; k < values.length; k++) {
                fValue += values[k] / Math.pow(1.0 + x0, k);
                fDerivative += -k * values[k] / Math.pow(1.0 + x0, k + 1);
            }

            // the essense of the Newton-Raphson Method
            x1 = x0 - fValue/fDerivative;

            if (Math.abs(x1 - x0) <= absoluteAccuracy) {
                return x1;
            }

            x0 = x1;
            ++i;
        }
        // maximum number of iterations is exceeded
        return Double.NaN;
    }

*/

IRR ( cashFlowsList ; guessRate )

Created: 4-5-2013
Modified: 1-3-2014
Author: Lewis C. Lorenz

Calculate Internal Rate of Return of a value list of cash flows (cashFlowsList) using an initial guessed rate of return (guessRate). The parameter guessRate defaults to .0001 if initially blank.

This function uses the Newton-Raphson method for speed and calculates to a modifiable decimal precision - presently set at 1.0e-7. The C source code from which this custom function was derived is included in the comments.

Examples:
IRR ( "-500000¶200000¶300000¶200000" ; .1 ) = .1882462096837047
IRR ( List ( -2000 ; 600 ; 300 ; 500 ; 700 ; 400 ) ; .05 ) = .0793216878811758
IRR ( List ( -5000 ; 10000 ; 0 ; 10000 ; 10000 ) ; .1 ) = 1.4633789720926647

DISCLAIMER: Using this IRR function, several calculations were compared against the results obtained on other IRR calculators and the results matched. However, that does not guarantee a correct result in every operation of this custom function, so you assume this risk when you use it.

NOTICE: This is a recursive function. If you change the function name then also change it in the function code.

Comments

		Abdullateef Sulaiman , Sabtech Feb 5, 2019
Hello! Please help me with source for the following values. ("-20000, 6000, 6000, 6000, 6000, 6000 "). Thank you.

		Abdullateef Sulaiman , Sabtech Feb 5, 2019
*I mean the course code. Thank you.

		Lewis Lorenz, Lorenz Companies Feb 8, 2019
It appears that your cashFlowsList is a text string, not a list. Change it to: List ( -20000, 6000, 6000, 6000, 6000, 6000 ).

		Lewis Lorenz, Lorenz Companies Feb 11, 2019
It appears that your cashFlowsList is a text string, not a list. Change it to: List ( -20000, 6000, 6000, 6000, 6000, 6000 ).

		Lewis Lorenz, Lorenz Companies Feb 15, 2019
It appears that your cashFlowsList is a text string, not a list. Change it to: List ( -20000, 6000, 6000, 6000, 6000, 6000 ).

		Sean Oliver, Finance Technology Services Ltd Apr 26, 2019
Hi Lewis - the function appears to work fine for 12 months payments but not for an odd number I'm trying. I pass the following to the script -27000[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]741[cr]10800 and I get .0150978303424344 returned but with the same values in Excel I get 0.0145145013939187. Do you have any suggestions?

		Sean Oliver, Finance Technology Services Ltd Apr 26, 2019
To be clear Lewis, I actually used List(-27000,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,10800)

		Sean Oliver, Finance Technology Services Ltd Apr 27, 2019
To be clear Lewis, I actually used List(-27000,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,741,10800)

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

IRR ( cashFlowsList ; guessRate )

Comments

Support this website.

Support this website.