FinIRR

Syntax

FinIRR(ValueArray(),Guess)

Description

Returns the internal rate of return for a series of periodic payments and receipts.

The internal rate of return is the equivalent rate of interest for an investment consisting of a series of positive and/or negative cash flows over a period of regular intervals. It is usually used to project the rate of return on a business investment that requires a capital investment up front and a series of investments and returns on investment over time.

The value of IRR is found by iteration. It starts with the value of Guess and cycles through the calculation adjusting Guess until the result is accurate within 0.00001 percent. After 20 tries, if a result cannot be found, IRR fails, and the user must pick a better guess.

Parameter	Description
ValueArray()	Array of Double numbers that represent payments and receipts. Positive values are payments, and negative values are receipts. There must be at least one positive and one negative value to indicate the initial investment (negative value) and the amount earned by the investment (positive value).
Guess	Double containing your guess as to the value that the IRR function will return. The most common guess is .1 (10 percent).

See Also

FinFv, FinMIRR, FinNpv, FinPv.

Example

Sub Main
'This example illustrates the purchase of a 
'lemonade stand for $800 and a series of incomes 
'from the sale of lemonade over 12 months. The 
'projected incomes for this example are generated 
'in two For...Next Loops, and then the internal 
'rate of return is calculated and displayed. 
'(Not a bad investment!)
 
    Dim valu#(12)
    valu(1) = -800  'Initial investment
    msg = valu#(1) & ", "
 
'Calculate the second through fifth months’ sales.
    For x = 2 To 5
        valu(x) = 100 + (x * 2)       
        msg = msg & valu(x) & ", "
    Next x
 
'Calcluate the sixth through twelfth months’ sales.
    For x = 6 To 12
        valu(x) = 100 + (x * 10)
        msg = msg & valu(x) & ", "
    Next x
 
'Calcluate the equivalent investment return rate.
    retrn# = IRR(valu,.1)
    msg = "The values: " & vbCrLf & msg & vbCrLf
    MsgBox msg & "Return rate: " & _
			Format(retrn#,"Percent")
End Sub