How to use the VBA IRR Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the VBA IRR Function (Syntax + Example)

The VBA IRR function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the internal rate of return (i.e. a series of payments and returns) for an investment. The calculation done by the IRR is a measure of an investment’s rate of return.

Syntax

IRR(ValueArray, [Guess])

Arguments

  • ValueArray: An array of cash flow which represents the payments and income. Payments would be in negative values and incomes would be in positive values [It must contain at least one negative and at least one positive value].
  • [Guess]: An initial estimate (guess) what will be the IRR [This is an optional argument and if omitted VBA takes 10% (=0.1) by default].

Example

To practically understand how to use VBA IRR function, you need to go through the below example where we have written a vba code by using it:

Sub example_IRR()
Dim cF(0 To 9) As Double
cF(0) = -1000
cF(1) = 213.6
cF(2) = 259.22
cF(3) = 314.6
cF(4) = 381.79
cF(5) = 463.34
cF(6) = 562.31
cF(7) = 682.42
cF(8) = 828.19
cF(9) = 1005.09
Range("A1").Value = IRR(cF)
End Sub

In the above code, we have used IRR to get the interest rate of return from an array (cF) in which we have an initial investment of -1000 and then all the cash returns in the next 9 years. So when we ran this code, it has returned 34% in the result.

Notes

  • The first value in the arrays needs to a negative value as it’s the initial amount paid for the investment.
  • If the array we supplied doesn’t have the one value in negative and one value in positive or not able to find results after 20 iterations, VBA will return a run-time 5 error.

Related Functions

DDB | FV | IPMT | MIRR | NPER | NPV | PMT | PV | RATE | SLN | SYD