Excel function XIRR in VBA

[Guest]

In VBA I am using the Excel function XIRR to calculate the internal rate of
return on some non-periodic cash depocits.

It works well with positive returns.

But it fails when the return is negative. The code throws an error when I
use a negative "guess" (my estimate of the return).

Does anybody have any experience with the use of this function from VBA?

I made a posting on the same subject November 7 - but has so far not
received any reply.

 

[Guest]

Can you post the error you got?

 

[Guest]

Run-time error '13': Type mismatch

is caused by this statement:

calculate_IRR = objExcel.Run("xirr", post_amount, post_date, Guess)

where Guess in this case is -0.16

If Guess is 0 or greater, the statment executes well.