Calculate Internal Rate of Return Method

The Calculate Internal Rate of Return method calculates, and then returns the internal rate of return for a stream of periodic cash flows. This method is iterative. It improves a guess over multiple iterations until the result is within 0.00001 percent. If it does not converge to a result in 20 iterations, then it displays a failure message.

Format

IRR(valuearray( ), guess)

For more information, see Arguments You Can Use with Financial Methods.

Example

The following example calculates an internal rate of return for a series of income and cost business transactions. It expresses this rate of return as an interest rate percentage. If the first value entered is a positive amount, then the IRR statement creates an Illegal Function Call error:

Sub Button_Click
   Dim cashflows() as Double
   Dim guess, count as Integer
   Dim i as Integer
   Dim intnl as Single
   Dim msgtext as String
   guess = .15
   count = 2
   ReDim cashflows(count + 1)
   For i = 0 to count-1
      cashflows(i) = 3000
   Next i
   intnl = IRR(cashflows(),guess)
   msgtext = "The IRR for your cash flow amounts is: " 
      msgtext = msgtext & Format(intnl, "Percent")
End Sub