CODEWITHSUNDEEP
excelvba
Chronicles.84
Chronicles.84

Reputation: 27

Populating Array for IRR calculation

I'm trying to create a function to calculate the required rate of return of a retirement fund. The situation is this: The retiree has a target amount of funds for their retirement account at the commencement of their retirement. They also have a target withdraw amount that they would like to draw down every year, for a target amount of years.

For example: A retirement fund amount of $1,000,000. A annual withdraw amount of $100,000, and a target of 15 years.

The code I have written is as follows:

Function RequiredReturn(retire_amnt, annual_spending, n_year)

    Dim arr()

    ReDim arr(0)

    arr(0) = -retire_amnt

    ReDim arr(1 To n_year)
    For i = 1 To n_year
        arr(i) = annual_spending
    Next i

    RequireReturn = Application.WorksheetFunction.IRR(arr)

End Function

The goal of this function is to:

  1. Create an array with the first value (0) = the negative retirement amount

  2. Fill the array from (1 to n) with the annual_spending amount

  3. Call the IRR function on this to find the minimum required annual return to be able to achieve this cash flow.

If you were to do by entering this array into cells, and then use the IRR function, the required return would be 5.56%

Obviously this is not working.

How can I change this to populate the array correctly? If populated correctly, is the IRR function being applied correctly?

Upvotes: 2

Views: 749

Answers (2)

brettdj
brettdj

Reputation: 55692

Or more directly than your initial approach, use the RATE formula

This calls the initial amount as a negative, ie

Sub Test()
   MsgBox Format(RequiredReturn(-1000000, 100000, 15), "#.0000%")
End Sub

options

  1. RequiredReturn = Application.Evaluate("=RATE(" & n_year & "," & annual_spending & "," & retire_amnt & ")")
  2. RequiredReturn = Application.Rate(n_year, annual_spending, retire_amnt)

Upvotes: 1

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

Your assign the array twice and the second assignment overrides the first - that is the error in your array logic.

You can fix the array by defining it as:

ReDim arr(0 To n_year)

Which will give it n_year + 1 slots, where 0th slot can take the lump sum (retire_amnt) and the slots from 1-n_year take the annuity payments (annual_spending). Then you have the correct data structure for an IRR calculation.

The code below works and gives you the expected result of 5.56%

Option Explicit

Sub Test()

    MsgBox Format(RequiredReturn(1000000, 100000, 15), "#.0000%")

End Sub

Function RequiredReturn(retire_amnt, annual_spending, n_year)

    Dim arr() As Variant
    Dim i As Long

    ReDim arr(0 To n_year)

    arr(0) = -retire_amnt

    For i = 1 To n_year
        arr(i) = annual_spending
    Next i

    RequiredReturn = Application.WorksheetFunction.IRR(arr)

End Function

I've mainly used IRR in the case of a capital out-flow followed by a series of equally timed cash in-flows. But in the case of annuity rate of return my understanding is that IRR can be used as well because it doesn't matter if the starting amount is a credit or debit as long as the subsequent cash in/out-flows are of the opposite signage. You probably don't want to risk your pension on that comment though :)

Upvotes: 0

Related Questions