IRR

IRR ( cashFlowsList , guessRate )

IRR calculates 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.

IRR calculates the Internal Rate of Return with the least recursion possible. It is fast – using the Newton-Raphson method for speed and precision. It calculates to a modifiable decimal precision – presently set at 1.0e-7. A list of 360 cash flows was processed through this function with no noticeable speed problems. The C source code from which this custom function was derived is included in the comments.

Parameters

cashFlowsList – Value list of cash flows.
guessRate – Initial guessed rate of return.

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 were identical. 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 code: Copy the selected code (click on Select All to highlight all the code) to the clipboard and paste it into a newly created Custom Function. Download code as a text file from here.

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;
    }

*/

Lorenz Companies

A Family of Enterprises

IRR ( cashFlowsList , guessRate )

Parameters

Examples

Comments

IRR — No Comments

Leave a Reply Cancel reply