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!
  • Students Click Here

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

Students Click Here

Microsoft: FoxPro FAQ

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.
*-- 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%
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
    lnPVSum = 0
    *-- Add Present value of all future cash flows
    FOR ln = 2 TO lnb
        lnPVSum = lnPVSum + (laValues[ln]/ (1+lnInt)^(ln-1))

    *-- Assess results
    lnPV = laValues[1] + lnPVSum
    *-- We need to establish direction of search on the first pass
    IF ISNULL(llAdd)
        llAdd = ( lnPV > 0)
        CASE lnPV = 0
            *-- This is the rate we are looking for
        CASE lnStep < 0.000001
            *-- Precision limit reached. Return current rate
        CASE LASTKEY() = 27
            *-- Allow escaping by pressing ESC
            lnInt = .NULL.
        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
            *-- Get new rate for testing
            lnInt = lnInt + (IIF(llAdd, 1, -1) * lnStep)    
RETURN IIF(ISNULL(lnInt), .NULL., lnInt*100)

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

My Archive

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