VBA XIRR messes up with date formats

Application.XIRR does not 'understand' the Date data type. So, in your example, it converts your dates intro String then passes them to XIRR. Although, the CDate("2024-01-12") correctly converts to a date pointing to 12th of January, it is then converted to a text which gets interpreted as 1st of December.

The solution is to simply declare your dates as Double which is something Excel 'understands':

Dim myDates(1) As Double

Sub TestXIRR()
1   Dim myInvests(1) As Double
2   Dim myDates
3   Dim result As Double
4   myInvests(0) = -100
5   myDates = Array("2024-01-12", "2025-01-01")
6   MsgBox myDates(0)
7   myInvests(1) = 300
8   result = Application.Xirr(myInvests, myDates)  'this works
9   MsgBox Format(result, "0.00")     ' result = 2.09
End Sub

Sub TestXIRR_a()
1   Dim myInvests(1) As Double
2   Dim myDates(1) As String
3   Dim result As Double
4   myInvests(0) = -100
5   myDates(0) = "2024-01-12"
6   MsgBox myDates(0)
7   myInvests(1) = 300
8   myDates(1) = "2025-01-01"
9   result = Application.Xirr(myInvests, myDates)
10  MsgBox Format(result, "0.00")      ' result = 2.09
End Sub