Need help in calculating IRR using custom function in Excel

Question

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.

Total Investment
Time Period of investment
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?

Not sure about the rest but to use a worksheet function in VBA you have to prefix it with WorkSheetFunction. Try ROI = WorkSheetFunction.IRR(cashFlow) — Bee_Riii, Apr 09 '21 at 12:41
You have declared cashflow as an array but haven't set its size. You need to reDim the array to match the time period. — Tom Sharpe, Apr 18 '21 at 19:30
It's not clear exactly what investment strategy you are trying to model here. Can you add a small sample data set and a description of what each entry represents. — chris neilsen, Apr 19 '21 at 05:56

Tom Sharpe · Answer 1 · 2021-04-21T21:23:53.747

1

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

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.

edited Apr 21 '21 at 21:23

answered Apr 18 '21 at 19:46

Tom Sharpe

30,727
5
24
37

2

Actually, that should be `ReDim cashFlow(0 To timePeriod)` – chris neilsen Apr 19 '21 at 04:49
I think they are equivalent, if base zero is the default, total number of elements being timePeriod+1 in both cases? I agree that (0 to to timePeriod) is clearer and more explicit though and I would normally put option base zero as well. – Tom Sharpe Apr 19 '21 at 06:28
Re the lower bound, I agree (personally, I always explicitly include the lower bound). My main point was `To timePeriod` cf `To timePeriod + 1` – chris neilsen Apr 19 '21 at 06:31
1

You are right, of course if you omit the lower bound you are specifying the upper bound, not the number of elements as I mistakenly thought. Thank you. – Tom Sharpe Apr 19 '21 at 06:37

score 1 · Answer 2 · answered Apr 20 '21 at 20:17

1

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.

answered Apr 20 '21 at 20:17

Scott Craner

148,073
10
49
81

zzzcolin · Answer 3 · 2021-04-19T05:17:28.460

-1

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

edited Apr 19 '21 at 05:17

answered Apr 19 '21 at 02:42

zzzcolin

24
3

I think I DO put ReDim Preserve before any references to cashFlow – zzzcolin Apr 19 '21 at 05:18
Yes, I missed that Redim in the For loop! That said, redim'ing repeatedly is pointless (and slow) when the required size of the array is known from the start – chris neilsen Apr 19 '21 at 05:27
Yes, thanks for the pointing-out, below answer would be better. And don't forget to add WorkSheetFunction before use IRR. – zzzcolin Apr 19 '21 at 05:32
`IRR` _is_ available as a [VBA function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/irr-function). So, while `WorkSheetFunction.IRR(cashFlow)` works, so does `IRR(cashFlow)` – chris neilsen Apr 19 '21 at 05:46

Need help in calculating IRR using custom function in Excel

3 Answers3

Linked