|
Syntax
|
IRR
(ValueArray(),Guess)
|
|
Description
|
Returns the internal rate of return for a series of periodic payments and receipts.
|
|
Comments
|
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
IRR
function requires the following parameters:
|
|
|
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).
|
|
|
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.
|
|
Example
|
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!)
Const crlf = Chr$(13) + Chr$(10)
Sub Main()
Dim valu#(12)
valu(1) = -800 'Initial investment
msg1 = valu#(1) & ", "
'Calculate the second through fifth months' sales.
For x = 2 To 5
valu(x) = 100 + (x * 2)
msg1 = msg1 & valu(x) & ", "
Next x
'Calculate the sixth through twelfth months' sales.
For x = 6 To 12
valu(x) = 100 + (x * 10)
msg1 = msg1 & valu(x) & ", "
Next x
'Calculate the equivalent investment return rate.
retrn# = IRR(valu,.1)
msg1 = "The values: " & crlf & msg1 & crlf & crlf
MsgBox msg1 & "Return rate: " & Format(retrn#,"Percent")
End Sub
|
|
See Also
|
Fv (function); MIRR (function); Npv (function); Pv (function).
|
CIMPLICITY