IRR Formula Q?

Hello,

I have inherited a spreadsheet (pictured attached) that I am trying to check and modify.

I have an IRR calc in Cell C53=(IRR(U4:U365,0.001)+1)^12-1 This formula returns an IRR of 9.4%.
I am not sure I understand what they did here since the IRR formula builder returns a 0.74% IRR with this formula: = IRR (U4:U365)

Can anyone explain?

Thanks

Patrick

The IRR of 0.74% is not correct. It was an error on my part. However I am still trying to understand and check the accuracy of the formula.

Thanks

Pat

Patrick said:

I have inherited a spreadsheet (pictured attached)

Click to expand...

For future reference, an image like that is almost useless. It is better to attach an example Excel file.

Patrick said:

I have an IRR calc in Cell C53=(IRR(U4:U365,0.001)+1)^12-1 This formula returns an IRR of 9.4%. I am not sure I understand what they did here since the IRR formula builder returns a 0.74% IRR with this formula: = IRR (U4:U365)

Click to expand...

Excel IRR returns a periodic IRR. In your case, it is monthly.

(In contrast, Excel XIRR always returns an annual IRR. But I'm suggesting that you use it.)

So the monthly IRR is about 0.74% [sic].

(Later, you say that 0.74% is not what IRR(U4:U365) returns. Presumably, it returns between 0.7476% and 0.7554%. I could determine the actual monthly IRR if you had attached an Excel file.)

Apparently, you want an annual IRR.

The annualized IRR is either 12*IRR(U4:U365) or (1+IRR(U4:U365))^12-1, depending on how you use the annual IRR. The author of the formula chose the latter.

Apparently, the "guess" parameter (0.001, which is 0.1%) is not needed for that particular set of cash flows. However, you might need some "guess" for other sets of cash flows. But AFAIK, there is no good way to choose a "guess" if IRR(U4:U365) returns a #NUM error.

Thanks It makes sense now.