Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to calculate Internal rates of returns (IRR)

Usefull Functions & Procedures

How to calculate Internal rates of returns (IRR)

by  jesylv  Posted    (Edited  )
IRR is a common tool to evaluate investment decisions. VFP does not provide a native function to evaluate IRR as is the case in Excel. This function will do the trick.

To use it, first build your periodic cash flow schedule in an array then pass the array to the function and voila.
*----------------------------------------------------------

Code: 
*-- IRR: Internal rate of return
*-- Interest rate where PV of future cash flows are equal to initial
*-- cash investment. This Interest rate is calculated using a trial 
*-- and error iteration. 
*--
*-- PARAMETERS
*-- laValues : An array of periodic cash flows organised chronologicaly
*--	lnStep   : Initial increment value (Optional)
*------------------------------------------------------------------------
*--
*-- RETURNS : IRR per period (depends on period used to construct laValue)
*-- 		Returns .NULL if user interrupts by pressing ESC
*-------------------------------------------------------------------------
*--
*-- Ex:  Calculate the IRR for an investment of 70 M$ generating an income
*-- stream of 5M$/year for the next 20 years
*-- 
*-- DIMENSION laCF(21)	&& Build the cash flow array
*-- laCF = 5			&& yearly income
*-- laCF[1] = -70		&& Initial investment
*-- lnIRR = IRR(@laCF)  && Calculate IRR by passing Array by reference
*-- ?lnIRR				&& Prints 3.667%
*------------------------------------------------------------------------
FUNCTION IRR
LPARAMETERS laValues, lnstep
LOCAL lnInt, lnStep, lnb, llAdd, lnPVSum, ln, lnPV 


*-- Start testing interest rates at 10%
*---------------------------------------
lnInt = 0.10
*---------------------------------------

*-- Increment (decrement) of interest rate to apply
*-- on each successive iteration
*--------------------------------------------------
lnstep = IIF(VARTYPE(lnStep) # "N", 0.01, lnStep)
*---------------------------------------------------

lnb 	= ALEN(laValues)		&& Lenght of cash flow schedule
llAdd	= .NULL.				&& Initial direction of rate testing

*-- Rate testing loop
DO WHILE .t.
	lnPVSum = 0
	*-- Add Present value of all future cash flows
	FOR ln = 2 TO lnb
		lnPVSum = lnPVSum + (laValues[ln]/ (1+lnInt)^(ln-1))
	NEXT

	*-- Assess results
	*----------------------------------------------
	lnPV = laValues[1] + lnPVSum
	*-----------------------------------------------
	
	*-- We need to establish direction of search on the first pass
	*--------------------------------------------------------------
	IF ISNULL(llAdd)
		llAdd = ( lnPV > 0)
	ENDIF		
	*-------------------------------------------------
	
	DO CASE
		CASE lnPV = 0
			*-- This is the rate we are looking for
			EXIT
			
		CASE lnStep < 0.000001
			*-- Precision limit reached. Return current rate
			EXIT
			
		CASE LASTKEY() = 27
			*-- Allow escaping by pressing ESC
			lnInt = .NULL.
			EXIT
		
		
		CASE llADD .and. (lnPV < 0)
			*-- We when to far need to narrow search
			llAdd = !llAdd 	&& Reverse direction of search
			lnStep = lnStep / 10
			
		CASE !llADD .and. (lnPV > 0)
			*-- We when to far need to narrow search
			llAdd = !llAdd 	&& Reverse direction of search
			lnStep = lnStep / 10
		
		OTHERWISE
			*-- Get new rate for testing
			lnInt = lnInt + (IIF(llAdd, 1, -1) * lnStep)	
	ENDCASE
ENDDO			
RETURN IIF(ISNULL(lnInt), .NULL., lnInt*100)

[\code]
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top