Option Explicit
Function fcm_xirr(ZeroValues As Range, ZeroDates As Range) As Double
Dim values() As Double, dates() As Date
ReDim values(0 To ZeroValues.Count - 1)
ReDim dates(0 To ZeroDates.Count - 1)
Dim j As Integer
Dim i As Integer
j = 0
For i = 1 To ZeroValues.Count
If ZeroValues(i) <> 0 Then
values(j) = ZeroValues(i)
dates(j) = ZeroDates(i)
j = j + 1
End If
Next i
Dim valuesX() As Double, datesX() As Date
ReDim Preserve values(0 To j - 1)
ReDim Preserve dates(0 To j - 1)
fcm_xirr = Application.Xirr(valuesX, datesX)
End Function