INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Usefull Functions & Procedures

How to calculate Internal rates of returns (IRR) by jesylv
Posted: 12 Dec 02

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.
*----------------------------------------------------------


*-- 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)



Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close