{"id":217,"date":"2014-01-02T03:26:44","date_gmt":"2014-01-02T03:26:44","guid":{"rendered":"http:\/\/www.lorenzcom.com\/?p=217"},"modified":"2016-08-22T10:56:08","modified_gmt":"2016-08-22T15:56:08","slug":"irr","status":"publish","type":"post","link":"https:\/\/lorenzcom.com\/?p=217","title":{"rendered":"IRR"},"content":{"rendered":"<h3>IRR ( cashFlowsList , guessRate )<\/h3>\n<p>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.<\/p>\n<p>IRR calculates the Internal Rate of Return with the least recursion possible. It is fast &#8211; using the Newton-Raphson method for speed and precision. It calculates to a modifiable decimal precision &#8211; 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.<\/p>\n<p><h4>Parameters<\/h4>\n<ul>\n<li>cashFlowsList &#8211; Value list of cash flows.<\/li>\n<li>guessRate &#8211; Initial guessed rate of return.<\/li>\n<\/ul>\n<h4>Examples<\/h4>\n<ul>\n<li>\tIRR ( &#8220;-500000\u00b6200000\u00b6300000\u00b6200000&#8221;, .1 ) =  .1882462096837047<\/li>\n<li>\tIRR ( List ( -2000, 600, 300, 500, 700, 400 ), .05 ) = .0793216878811758<\/li>\n<li>\tIRR ( List ( -5000, 10000, 0, 10000, 10000 ), .1 ) = 1.4633789720926647<\/li>\n<\/ul>\n<p><strong>DISCLAIMER:<\/strong> 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.<\/p>\n<p><strong>NOTICE:<\/strong> This is a recursive function. If you change the function name then also change it in the function code.<\/p>\n<p><strong>IRR code:<\/strong> 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. <a href=\"\/docs\/FileMaker\/IRR.txt\">Download code as a text file from here.<\/a><\/p>\n<form>\n<textarea id=\"te_217\" style=\"width:100%; height:180px; font-family:Arial,Helvetica; font-size:.90em; line-height:normal;\"><!--raw-->Let ( [<br \/>\ncfct = ValueCount ( cashFlowsList ); \/\/ A decreasing number of cash flows, initially.<br \/>\ngct = ValueCount ( guessRate ); \/\/ 1 on first pass, 3 on template building and 4 on irr calculation iterations.<br \/>\nitct = If ( gct > 1; GetAsNumber ( GetValue ( guessRate; 4 ) ); 0 ) \/\/ Stored irr calculation iterations count.<br \/>\n];<br \/>\nCase<br \/>\n( cfct > 0; \/\/ Create templates from cashFlowsList, working from the last to the first, until list is empty.<br \/>\n\tLet ( [<br \/>\n\t\/\/ Create values: guessRate &#038; NPV template &#038; NPVDerivative template &#038; counter (default guessRate adjustable).<br \/>\n\tguessRate = If ( gct < 2; If ( IsEmpty ( Trim ( guessRate ) ); .0001; guessRate ) &#038; \"\u00b6\u00b6\u00b60\"; guessRate ); \n\t\/\/ NPV:\n\tftemplate = \"(\" &#038; GetValue ( cashFlowsList; cfct ) &#038; \"\/(1+g)^\" &#038; GetAsText ( cfct - 1 ) &#038; \")+\" &#038; GetValue ( guessRate; 2 ) ; \n\tftemplate = If ( cfct = 1; Left ( ftemplate; Length ( ftemplate ) - 1 ); ftemplate ); \/\/ Trim last \" + \".\n\t\/\/ NPV Derivative:\n\tfdtemplate =  \"(-\" &#038; GetAsText ( cfct - 1 ) &#038; \"*\" &#038; GetValue ( cashFlowsList; cfct ) &#038; \"\/(1+g)^(\" &#038; GetAsText ( cfct - 1 ) &#038; \"+1))+\" &#038; GetValue ( guessRate; 3 );\n\tfdtemplate = If ( cfct = 1; Left ( fdtemplate; Length ( fdtemplate ) - 1 ); fdtemplate ); \/\/ Trim last \" + \".\n\t\/\/ Store templates in the guessRate parameter. Create 3 value list.\n\tguessRate = GetValue ( guessRate; 1 ) &#038; \"\u00b6\" &#038; ftemplate &#038; \"\u00b6\" &#038; fdtemplate\n\t];\n\t\/\/ Recursive call to prepend the next previous cash flow to the templates after removing last item from list.\n\t\/\/If ( cfct > 1; IRR ( LeftValues ( cashFlowsList ; cfct &#8211; 1 ); guessRate ); cashFlowsList &#038; &#8220;\u00b6&#8221; &#038; guessRate ); \/\/ For debug only.<br \/>\n\tIRR ( LeftValues ( cashFlowsList ; cfct &#8211; 1 ); guessRate )<br \/>\n\t);<br \/>\nitct < 21; \/\/ Test for correct rate. Maximum irr calculation iterations (adjustable).\n\tLet ( [ \n\tgr = GetAsNumber ( GetValue ( guessRate; 1 ) );\n\tf = Evaluate ( Substitute ( GetValue ( guessRate; 2 ); \"g\"; GetAsText ( gr ) ) );\tfd = Evaluate ( Substitute ( GetValue ( guessRate; 3 ); \"g\"; GetAsText ( gr ) ) );\n\tir = gr - f \/ fd; \/\/ The essence of the Newton-Raphson Method\n\tb = ( Abs ( ir - gr ) <= 1.0e-7 ); \/\/ If ir is to the accuracy required, return ir as calculated irr (accuracy adjustable).\n\tgr = If ( b; gr; ir ); \/\/ Set guessRate to calculated ir if not true and try again.\n\titct = itct +1;\n\t\/\/ Prepare for recursive call\n\tguessRate = GetAsText ( gr ) &#038; \"\u00b6\" &#038; GetValue ( guessRate; 2 ) &#038; \"\u00b6\" &#038; GetValue ( guessRate; 3 ) &#038; \"\u00b6\" &#038; GetAsText ( itct )\t];\n\t\/\/If ( b; ir &#038; \"\u00b6\" &#038; cashFlowsList &#038; \"\u00b6\" &#038; guessRate; IRR ( cashFlowsList; guessRate ) ) \/\/ Debug only.\n\tIf ( b; ir; IRR ( cashFlowsList; guessRate ) ) \/\/ Return ir or try again.\n\t);\n-1 ) \/\/ Return -1 as error code.\n)\n\n\/*\nIRR ( cashFlowsList, guessRate )\n\nCreated: 4-5-2013\nModified: 1-3-2014\nAuthor: Lewis C. Lorenz\n\nCalculate 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. \n\nThis 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.\n\nExamples:\n\tIRR ( \"-500000\u00b6200000\u00b6300000\u00b6200000\", .1 ) = .1882462096837047\n\tIRR ( List ( -2000, 600, 300, 500, 700, 400 ), .05 ) = .0793216878811758\n\tIRR ( List ( -5000, 10000, 0, 10000, 10000 ), .1 ) = 1.4633789720926647\n\nDISCLAIMER: 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.\n \nNOTICE: This is a recursive function. If you change the function name then also change it in the function code.\n\nIRR by Newton-Raphson Method.       \n\tint maxIterationCount = 20;\n        double absoluteAccuracy = 1E-7;\n\n        double x0 = guess;\n        double x1;\n\n        int i = 0;\n        while (i < maxIterationCount) {\n\n            \/\/ the value of the function (NPV) and its derivate can be calculated in the same loop\n            double fValue = 0;\n            double fDerivative = 0;\n            for (int k = 0; k < values.length; k++) {\n                fValue += values[k] \/ Math.pow(1.0 + x0, k);\n                fDerivative += -k * values[k] \/ Math.pow(1.0 + x0, k + 1);\n            }\n\n            \/\/ the essense of the Newton-Raphson Method\n            x1 = x0 - fValue\/fDerivative;\n\n            if (Math.abs(x1 - x0) <= absoluteAccuracy) {\n                return x1;\n            }\n\n            x0 = x1;\n            ++i;\n        }\n        \/\/ maximum number of iterations is exceeded\n        return Double.NaN;\n    }\n\n*\/<!--\/raw--><\/textarea><br \/>\n<input onclick=\"this.form.te_217.focus();this.form.te_217.select();\" type=\"button\" value=\"Select All\" \/> <input type=\"reset\" value=\"Reset\" \/><\/form><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 <span class=\"excerpt-dots\">&hellip;<\/span> <a class=\"more-link\" href=\"https:\/\/lorenzcom.com\/?p=217\"><span class=\"more-msg\">Continue reading &rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[10,15,14],"tags":[],"_links":{"self":[{"href":"https:\/\/lorenzcom.com\/index.php?rest_route=\/wp\/v2\/posts\/217"}],"collection":[{"href":"https:\/\/lorenzcom.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lorenzcom.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lorenzcom.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lorenzcom.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=217"}],"version-history":[{"count":21,"href":"https:\/\/lorenzcom.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions"}],"predecessor-version":[{"id":761,"href":"https:\/\/lorenzcom.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions\/761"}],"wp:attachment":[{"href":"https:\/\/lorenzcom.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lorenzcom.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lorenzcom.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}