Lasso Soft Inc. > Home

[xirr]

Linkxirr
AuthorJon Harris
CategoryMath
Version8.5.x
LicensePublic Domain
Posted13 Mar 2012
Updated13 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

Please log in to comment

LassoSoft Inc. > Home

 

 

©LassoSoft Inc 2014 | Web Development by Treefrog Inc | PrivacyLegal terms and Shipping | Contact LassoSoft