XIRR ( cashFlowList ; cashFlowDateList ; guessRate )

Returns extended internal rate of return - XIRR. Mimics Excel XIRR function.

Average rating: 4.4 (24 votes) Log in to vote

Lewis Lorenz
Lorenz Companies
http://www.LorenzCom.com

Sample input:

when using:
numbers: -6777.00, -6000.00, 150.00, 300.00, 300.00, 300.00, 300.00, 300.00, 12960.00
dates: 4-30-2001, 9-17-2001, 9-28-2001, 4-2-2002, 9-30-2002, 3-31-2003, 9-30-2003, 3-31-2004, 5-5-2004,

XIRR ( list ( data::number ) ; list ( data::date ) ; ".01" )

Sample output:

.0510565877350815

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

Let ( [
~cashct = ValueCount ( cashFlowList ) ; //Cash flows list count.
~datect = ValueCount ( cashFlowDateList ) ; //Dates list count.
~rate = Let ( ~r = GetAsNumber ( GetValue ( guessRate ; 1 ) ) ; If ( IsEmpty ( ~r ) or not ( ~r ) ; .1 ; ~r ) ) ; //Set passedguess rate to 0.1 if empty or zero.
~f = GetValue ( guessRate ; 2 ) ; // Internal function flag.
~o1 = GetValue ( guessRate ; 3 ) ; //Optional value 1.
~o2 = GetValue ( guessRate ; 4 ) ; //Optional value 2.
~idx = Let ( ~i = GetAsNumber ( GetValue ( guessRate ; 5 ) ) ; If ( IsEmpty ( ~i ) or not ( ~i ) ; 1 ; ~i ) ) ; //Set passed index to 1 if empty or zero.
~itermax = 1000 ; //Set maximum number of iterations.
~epsmax = 1.0e-10 ;   //Set maximum epsilon for end of iteration.

/* FUNCTIONS. */
~rr = Case ( 
	IsEmpty ( ~f ) or ~f = "CHECKCASH"; //On first entry (from user).
		Let ( [
			~positive = If ( GetAsNumber ( GetValue ( cashFlowList; ~idx ) ) > 0 ; True ; GetAsBoolean ( ~o1 ) ) ; //Positive number flag.
			~negative = If ( GetAsNumber ( GetValue ( cashFlowList; ~idx ) ) < 0 ; True ; GetAsBoolean ( ~o2 ) ) ; //Negative number flag.
			~x = Case ( //Check cash flows for positive and negative numbers and equal number of dates.
				~cashct ≠ ~datect ; 
					If ( ~cashct < ~datect ;"#CASHFLOWMISSING" ; "#DATEMISSING" ) ;
				~idx < ~cashct ; //Call the negative and positive number check routine if not finished.
					XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "CHECKCASH" ; ~positive ; ~negative ; ~idx + 1 ) ) ; 
				~idx = ~cashct and ( ( not ~positive ) or ( not ~negative ) ) ; 
					"#NUMMISSING" ;
				XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "GETRESULT" ; "" ; "" ; 1 ) )
				)
		]; 
		~x
		);
	~f = "IRRRESULT" ; //irrResult: Calculate the resulting amount.
		Let ( [
			~r = ~rate + 1 ;
			~n = GetAsNumber ( GetValue ( cashFlowList; ~idx ) ) ; //Indexed cashflowlist value.
			/* o1 contains the incrementing resultAmount starting with cashflow[1], o2 contains the first date as sent from GETRESULT. */
			~result = GetAsNumber ( ~o1 ) + ~n / ~r ^ ( ( GetAsDate ( GetValue ( cashFlowDateList; ~idx ) ) - GetAsDate ( ~o2 ) ) / 365 ) ;
			~x = Case (
				~idx < ~cashct ; //Increment  index and finish calculating result.
					XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "IRRRESULT" ; ~result ; ~o2 ; ~idx + 1 ) ) ; 
				~result //Return the resulting amount.
				)
		];
		~x //Return it.
		);
	~f = "IRRRESULTDERIV"; //irrResultDeriv: Calculates the first derivation
		Let ( [
			~r = ~rate + 1 ;
			~n = GetAsNumber ( GetValue ( cashFlowList; ~idx ) ) ; //Indexed cashflowlist value.
			/* o1 contains the incrementing resultAmount starting with zero value, o2 contains the first date as sent from GETRESULT. */
			~frac = ( GetAsDate ( GetValue ( cashFlowDateList; ~idx ) ) - GetAsDate ( ~o2 ) ) / 365 ;
			~result = GetAsNumber ( ~o1 ) - ~frac * ~n / ~r ^ ( ~frac + 1 ) ;
			~x = Case (
				~idx < ~cashct ; //Increment  index and finish calculating result.
					XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "IRRRESULTDERIV" ; ~result ; ~o2 ; ~idx + 1 ) ) ; 
				~result //Return the resulting amount.
				)
		];
		~x //Return it.
		);
	~f = "GETRESULT"; //Implement Newton's method.
		Let ( [
			/* iteration will be set to 1 on entry and incremented in recursive call to GETRESULT to track iteraions.*/
			~iteration = GetAsNumber ( GetValue ( guessRate ; 6 ) ) ; //Passed below.
			/* Send 1st values in both cashFlowList and cashFlowDateList and set index to start with the next (2nd) values.*/
			~resultValue = 	XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "IRRRESULT" ; GetValue ( cashFlowList ; 1 ) ; GetValue ( cashFlowDateList ; 1 ) ; 2 ) ) ; 			~newRate = ~rate - ~resultValue / XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "IRRRESULTDERIV" ; 0 ; GetValue ( cashFlowDateList ; 1 ) ; 2 ) ) ; 
			~epsRate = Abs ( ~newRate - ~rate ) ;
			~rate = ~newRate ;
			~contLoop = GetAsBoolean ( GetAsBoolean ( ~epsRate > ~epsMax )  and GetAsBoolean ( Abs ( ~resultValue ) > ~epsMax ) ) ;
			~x = Case (
				GetAsBoolean ( ~iteration < ~iterMax ) and ~contLoop ; //Continue until
					XIRR ( cashFlowList ; cashFlowDateList ; List ( ~rate ; "GETRESULT" ; "" ; "" ; ~idx ; ~iteration + 1 ) ) ; 
				~contLoop ;
					"#NUM!" ;
				~rate //Return caluclated rate.
				)
		];
		~x //Return the rate.
		);
	"END OF FUNCTIONS CASE" 
)
];
~rr //Returned extended internal rate of return.
)

/*Extended Internal Rate of Return

XIRR ( cashFlowList, cashFlowDateList, guessRate )

Returns the eXtended Internal Rate of Return of a list of cash flows with corresponding cash flow dates. Mimics Excel XIRR function. 

By Lewis Lorenz
Modified 6-25-2015

DISCLAIMER: Using this XIRR function, several calculations were compared against the results obtained on other XIRR 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, so, if you change the function name remember to also change it in the function code.

Example:

when using:
numbers:	-6777.00, -6000.00, 150.00, 300.00, 300.00, 300.00, 300.00, 300.00, 12960.00
dates:	4-30-2001, 9-17-2001, 9-28-2001, 4-2-2002, 9-30-2002, 3-31-2003, 9-30-2003, 3-31-2004, 5-5-2004, 

XIRR ( list ( data::number ) ; list ( data::date ) ; ".01" )  = .0510565877350815

*******
Derived from the following Javascript code.

function XIRR(values, dates, guess) {
  // Credits: algorithm inspired by Apache OpenOffice
  
  // Calculates the resulting amount
  var irrResult = function(values, dates, rate) {
    var r = rate + 1;
    var result = values[0];
    for (var i = 1; i < values.length; i++) {
      result += values[i] / Math.pow(r, moment(dates[i]).diff(moment(dates[0]), 'days') / 365);
    }
    return result;
  }

  // Calculates the first derivation
  var irrResultDeriv = function(values, dates, rate) {
    var r = rate + 1;
    var result = 0;
    for (var i = 1; i < values.length; i++) {
      var frac = moment(dates[i]).diff(moment(dates[0]), 'days') / 365;
      result -= frac * values[i] / Math.pow(r, frac + 1);
    }
    return result;
  }

  // Check that values contains at least one positive value and one negative value
  var positive = false;
  var negative = false;
  for (var i = 0; i < values.length; i++) {
    if (values[i] > 0) positive = true;
    if (values[i] < 0) negative = true;
  }
  
  // Return error if values does not contain at least one positive value and one negative value
  if (!positive || !negative) return '#NUM!';

  // Initialize guess and resultRate
  var guess = (typeof guess === 'undefined') ? 0.1 : guess;
  var resultRate = guess;
  
  // Set maximum epsilon for end of iteration
  var epsMax = 1e-10;
  
  // Set maximum number of iterations
  var iterMax = 500000;

  // Implement Newton's method
  var newRate, epsRate, resultValue;
  var iteration = 0;
  var contLoop = true;
  do {
    resultValue = irrResult(values, dates, resultRate);
    newRate = resultRate - resultValue / irrResultDeriv(values, dates, resultRate);
    epsRate = Math.abs(newRate - resultRate);
    resultRate = newRate;
    contLoop = (epsRate > epsMax) && (Math.abs(resultValue) > epsMax);
  } while(contLoop && (++iteration < iterMax));

  if(contLoop) return '#NUM!';

  // Return internal rate of return
  return resultRate;
}*/

Extended Internal Rate of Return

XIRR ( cashFlowList ; cashFlowDateList ; guessRate )

Returns the eXtended Internal Rate of Return of a list of cash flows with corresponding cash flow dates. Mimics Excel XIRR function.

Example:

when using:
numbers: -6777.00, -6000.00, 150.00, 300.00, 300.00, 300.00, 300.00, 300.00, 12960.00
dates: 4-30-2001, 9-17-2001, 9-28-2001, 4-2-2002, 9-30-2002, 3-31-2003, 9-30-2003, 3-31-2004, 5-5-2004,

XIRR ( list ( data::number ) ; list ( data::date ) ; ".01" ) = .0510565877350815

Comments

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