FileMaker Pro Custom Functions

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. If you change the function name then 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; }*/
 Then copy & paste into FileMaker Advanced's Edit Custom Function window.