Results 1 to 4 of 4

Thread: IRR Formula Q?

  1. #1

    IRR Formula Q?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screen Shot 2015-10-24 at 5.42.11 PM.jpg 
Views:	19 
Size:	103.7 KB 
ID:	4063  

  2. #2
    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

  3. #3
    Quote Originally Posted by Patrick View Post
    I have inherited a spreadsheet (pictured attached)
    For future reference, an image like that is almost useless. It is better to attach an example Excel file.

    Quote Originally Posted by Patrick View Post
    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)
    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.

  4. #4
    Thanks It makes sense now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •