[xirr]
| Link | xirr |
| Author | Jon Harris |
| Category | Math |
| Version | 8.5.x |
| License | Public Domain |
| Posted | 13 Mar 2012 |
| Updated | 13 Mar 2012 |
| More by this author... | |
Description
This tag mimics the XIRR function found in Excel and other spreadsheets. It uses the XNPV tag, included, which can be used standalone.
It is a re-write of two of the functions in:
http://www.phpclasses.org/package/892-PHP-Financial-functions-with-the-Excel-function-names-.html
Sample Usage
// array of dates var: 'date1' = (date: '2008-01-01') ; var: 'date2' = (date: '2008-03-01') ; var: 'date3' = (date: '2008-10-30') ; var: 'date4' = (date: '2009-02-15') ; var: 'date5' = (date: '2009-04-01') ; // array of values var: 'values' = array(-10000,2750,4250,3250,2750) ; var: 'dates' = array( $date1, $date2, $date3, $date4, $date5) ; Ex_xirr(-values=$values, -dates=$dates) ;
Source Code
Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.
(type) != "array" || #dates->(type) != "array" ;
return: "Error1" ;
/if ;
if: #values->(size) != #dates->(size) ;
return: "Error2" ;
/if ;
// create an initial bracket, with a root somewhere between bot and top
local: 'x1' = 0.0;
local: 'x2' = #guess;
local: 'f1' = EX_xnpv(-rate=#x1, -values=#values, -dates=#dates);
local: 'f2' = EX_xnpv(-rate=#x2, -values=#values, -dates=#dates);
loop: -loopto= FINANCIAL_MAX_ITERATIONS;
if: (math_mult: #f1, #f2) < 0.0 ;
loop_abort ;
/if ;
if: (math_abs: #f1) < (math_abs: #f2) ;
#x1 += (1.6 * (#x1 - #x2)) ;
#f1 = EX_xnpv(-rate=#x1, -values=#values, -dates=#dates);
else ;
#x2 += (1.6 * (#x2 - #x1)) ;
#f2 = EX_xnpv(-rate=#x2, -values=#values, -dates=#dates);
/if ;
/loop ;
// this is the error
if: (math_mult: #f1 , #f2) > 0.0 ;
return: "Error3" ;
/if ;
local: 'f' = EX_xnpv(-rate=#x1, -values=#values, -dates=#dates);
if: (#f < 0.0) ;
local: 'rtb' = #x1;
local: 'dx' = (math_sub: #x2,#x1);
else ;
local: 'rtb' = #x2;
local: 'dx' = (math_sub: #x1,#x2) ;
/if ;
loop: -loopto= FINANCIAL_MAX_ITERATIONS;
#dx = #dx * 0.5;
local: 'x_mid' = #rtb + #dx ;
local: 'f_mid' = EX_xnpv(-rate=#x_mid, -values=#values,-dates=#dates);
if (#f_mid <= 0.0) ;
#rtb = #x_mid;
/if ;
if ((math_abs(#f_mid) < FINANCIAL_ACCURACY) || (math_abs(#dx) < FINANCIAL_ACCURACY)) ;
return: #x_mid;
/if ;
/loop ;
return "error4";
/define_tag ;
// xnpv function called by xirr
define_tag: 'xnpv',
-required='rate',
-required='values',
-required='dates',
-Namespace='EX_';
// check values and dates are arrays
if: #values->(type) != "array" || #dates->(type) != "array" ;
return: "Error1" ;
/if ;
if: #values->(size) != #dates->(size) ;
return: "Error2" ;
/if ;
// start with zero
local: 'xnpv_total' = 0.000 ;
local: 'power' = 1 + #rate ;
loop: -loopto=#values->(size) ;
local: 'i' = (loop_count) ;
local: 'numerator' = #values->(get: #i) + ".0000" ;
local: 'datediff' = (date_difference: #dates->(get: #i) , #dates->(get: 1), -day) ;
local: 'dateyear' = (math_div: #datediff, 365.000000) ;
local: 'denominator' = (Math_Pow: #power, #dateyear) ;
local: 'cashflow' = (math_div: #numerator, #denominator) ;
// add xnpv
#xnpv_total = (math_add: #xnpv_total, #cashflow) ;
/loop ;
return: #xnpv_total ;
/define_tag;
/encode_set ;
?>
Comments
No comments
