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

I