IRR vba vs. worksheet function

[MetLife]

Hi,

I updated a macro so that it uses the IRR within vba rather than the IRR worksheet function.

To test this compared the worksheet function vs. IRR function & notice that the vba function is erroring out & the worksheet function returns a real value.

Why is this?


Worksheets("calcs").Range("BY8:BY1200").Value = Application.Transpose(arr1)

Dim t1 As Double, t2 As Double, t3 As Double
If IsError(Application.IRR(arr1, 0.01)) Then
ERCreditedRate = 0.01
Else
t1 = (1 + Application.IRR(arr1, 0.01)) ^ 12 - 1
ERCreditedRate = (1 + Application.IRR(arr1, 0.01)) ^ 12 - 1
End If


excel 2016

 

[shg]

Did you mean to do

arr1 = Application.Transpose(Worksheets("calcs").Range("BY8:BY1200").Value)

 

[MetLife]

No the array "arr1' is populated in the code.

The original program used the worksheet function pointing to the arr1 output. Now the vba just does the irr calculation on the array directly. Both should be the same in theory.

 

[shg]

No the array "arr1' is populated in the code.

Not in the code you posted.

Is it a one-dimensional array?

 

[MetLife]

Not in the code you posted.

Is it a one-dimensional array?

It is a 1-D array.

Actually I figured out this issue. The "guess" was different in VBA.

When I change this to match excel, the two match.

Thanks!

 

[shg]

Good job.