XIRR ( cashFlowList ; cashFlowDateList ; guessRate )

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

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

Lewis Lorenz - Show more from this author
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

		Sean Oliver, Finance Technology Services Ltd Apr 26, 2019
This function works really well and has always mimicked the Excel function for me.

		Albert Steg Nov 3, 2020
Hi -- I currently use the XIRR function to calculate my annualized investment returns in Excel on a spreadsheet someone set up for me (I'm Excel-illiterate). In my spreadsheet, at the end of the schedule of inflows and outflows, I enter my current account valuation on today's date as a negative value. I don't see anyplace in this custom function example for expressing the current valuation. Could someone explain what I'm missing, or how I would use this custom function for the purpose I describe? Thanks!

		Albert Steg Nov 3, 2020
Oh I think I see -- this function uses negative numbers for the inflows and positive for the withdrawals and then a positive from the current balance, which is just the reverse of what I had seen before?

		Benny DB, B&E Equities Dec 1, 2020
Stated that I'm not an expert of FM, I'm trying to move a big Excel file to FM 19 and I'd need XIRR performance result. I found this function, it seems to fit but, as suggested I copied & pasted into Custom Functions editor, then I set a calculation field with the new XIRR function. Unfortunately, immediately after I have always #NUMMISSING message. I tried to enter in the script but is far too complicate for me. I feel my problem is related to the variables and the link with the fields. Does anybody any suggestions?

		Benny DB, B&E Equities Dec 2, 2020
SOLVED!! It was my inexperience on FM. I created 2 Summary fields (cashFlowsList and dateList), option "List of" and collecting the cashflows and dates sources. Then I created a Calculation field with the XIRR function as follows: XIRR ( list ( cashFlowList ); list ( dateList ); "0.1" ) It works. Thanks Lewis for your work.

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

XIRR ( cashFlowList ; cashFlowDateList ; guessRate )

Comments

Support this website.

Support this website.