# VBA financial :: IRR

## 0. Introduction to cash flow functions

All examples are based on the xlf Carrot Washer project link.

## 1. Internal rate of return (IRR)

The internal rate of return (IRR) for a series of cash flows, is the discount rate that equates to a zero net present value (NPV).

In equation form the NPV of 0 is set equal to the sum of discounted cash flows $$\begin{equation} 0=\sum_{t=0}^{n} \frac{C_t}{(1+IRR)^t} \end{equation}$$ where the cash flows at time $t$ are denoted $C_t$ over $n$ periods. Equation 1 is solved for $IRR$.

### 1.1 IRR VBA function

SYNTAX (VBA): IRR(ValueArray() As Double, [Guess]) As Double

Code 1: Sub DemoIRR returns the NPV for the carrot washer project using the VBA IRR function (VBA.IRR)
Option Explicit

' =================
' IRR VBA function
' =================
Sub DemoIRR()
' procedure declarations
Dim CashFlows(0 To 5) As Double ' << Type [Cashflows array] must be Double
Dim IrrDec As Double

' assignment statements
CashFlows(0) = -80000
CashFlows(1) = 10000
CashFlows(2) = 20000
CashFlows(3) = 15000
CashFlows(4) = 50000
CashFlows(5) = 20000

' calculation
IrrDec = IRR(CashFlows)
Debug.Print Time & ", IRR value (decimal): " & IrrDec & " (15 significant figures)"
Debug.Print Time & ", IRR value (percent): " & Format(IrrDec, "percent")
Debug.Print Time & ", IRR value (percent): " & Format(IrrDec, "0.0000%")

End Sub Fig 1: Immediate window - calculate the internal rate of return for the carrot washer project with the VBA IRR function (time stamp suppressed) Type [Cashflows array] - the array Type, code 1 line 8 must be a Double
Other data types such as Variant or Single return a Compile error: Type mismatch array or user-defined type expected at line 20

### 1.2 IRR WS function

SYNTAX (WS): IRR(values, [guess])

Pass cash flow values from a VBA array.

Code 2: Sub DemoIRRws returns the NPV for the carrot washer project using the (WS) IRR function (Application.IRR)
' =================
' IRR WS function
' =================
Sub DemoIRRws()
' procedure declarations
Dim CashFlows(0 To 5) As Double
Dim IrrDec As Double

' assignment statements
CashFlows(0) = -80000
CashFlows(1) = 10000
CashFlows(2) = 20000
CashFlows(3) = 15000
CashFlows(4) = 50000
CashFlows(5) = 20000

' calculation
IrrDec = Application.IRR(CashFlows)
Debug.Print Time & ", IRR value (percent) WS: " & Format(IrrDec, "percent")

End Sub Fig 2: Immediate window - calculate the internal rate of return for the carrot washer project with the WS IRR function

### 1.3 IRR GoalSeek method

SYNTAX (VBA): Range().GoalSeek Goal:=value, ChangingCell:=Range(). Returns TRUE or FALSE

Cash flow values are stored in a worksheet range.

Code 3: Sub DemoIRRgs returns the NPV for the carrot washer project using the VBA Range.GoalSeek method
 =================
' IRR GoalSeek method
' =================
Sub DemoIRRgs()

Dim IrrDec As Double

' reset disc rate
Range("Disc").Value = "5%"

' calculation
Range("NPV").GoalSeek Goal:=0, ChangingCell:=Range("Disc")
IrrDec = Range("Disc")
Debug.Print vbNewLine & Time & ", IRR value (percent) GoalSeek: " & Format(IrrDec, "percent")

End Sub Fig 3: Immediate window - calculate the internal rate of return for the carrot washer project with the Range.GoalSeek method