Need help in calculating IRR using custom function in Excel

user_8419988 0
Viewed 176

I am using an excel sheet to track all my investments. In order to calculate my IRR, I need to have the values entered in a specific way for Excel to calculate. So I decided to create a custom function. I will feed this custom function the following values.

  1. Total Investment
  2. Time Period of investment
  3. Final Value of the investment.

I used the following code for creating a custom function. But I get the #VALUE error

Function ROI(fundInvested, timePeriod, finalValue)
    
    eachValue = fundInvested / timePeriod
    Dim cashFlow() As Double
    Dim n As Integer
        For n = 0 To (timePeriod - 1)
            cashFlow(n) = -1 * eachValue
        Next n
    cashFlow(timePeriod) = finalValue
    ROI = IRR(cashFlow)
 
End Function

Where is my formula wrong?

excel-formula vba excel
3 Answers

So you need to reDim it like this:

Function ROI(fundInvested, timePeriod, finalValue)
    
    eachValue = fundInvested / timePeriod
    Dim cashFlow() As Double
    Dim n As Integer
    ReDim cashFlow(0 to timePeriod)
    'ReDim cashFlow(timePeriod) is also correct - see @Chris Nielsen comment.

        For n = 0 To (timePeriod - 1)
            cashFlow(n) = -1 * eachValue
        Next n

    cashFlow(timePeriod) = finalValue
    ROI = IRR(cashFlow)
 
End Function

enter image description here

Notes

-In absence of Option Base statement, array subscripts start at zero.

-Putting both lower and upper limits in ReDim statement is recommended, but if the lower limit is omitted and only the upper limit is specified, the lower limit is taken from the Option Base currently in operation, in this case zero so

ReDim cashFlow(0 to timePeriod)

and

ReDim cashFlow(timePeriod)

are equivalent.

user_38949170

Since you tagged it formula:

=IRR(CHOOSE(INT((ROW($ZZ$1:INDEX($ZZ:$ZZ,B2+1))-1)/B2)+1,-1*A2/B2,C2))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

user_48515900

Try below:

Function ROI(fundInvested, timePeriod, finalValue)
    
    eachValue = fundInvested / timePeriod

    Dim cashFlow() As Double
    Dim n As Integer

        For n = 0 To (timePeriod - 1)
            ReDim Preserve cashFlow(n)
            cashFlow(n) = -1 * eachValue
        Next n

    ReDim Preserve cashFlow(timePeriod)
    cashFlow(timePeriod) = finalValue
    ROI = WorkSheetFunction.IRR(cashFlow)

End Function
user_126238020
Related