Brian Dunning's FileMaker Custom Functions

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   Lewis Lorenz - Show more from this author
Lorenz Companies
http://www.LorenzCom.com

Share on Facebook Share on Twitter

  Sample input:
IRR (
  Sample output:
.1882462096837047.07932168788117581.4633789720926647

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

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   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   Abdullateef Sulaiman , Sabtech
Feb 5, 2019
*I mean the course code. Thank you.
 
Lewis Lorenz   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   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   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   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   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   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)
 

Log in to post comments.

 

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: