+ Reply to Thread
Results 1 to 6 of 6

Getting ridiculous IRR answers

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    69

    Getting ridiculous IRR answers

    Hi all,

    I'm trying to use the IRR function for monthly cashflows and I'm getting 3359.90% annualized. This seems wrong...

    See cashflows and calculations in the attached workbook.

    Any help would be great!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Getting ridiculous IRR answers

    Disclaimer: I am not in financials, so I cannot make any comments about the financial side and logic of these calculations.

    Speaking strictly as a mathematician, the result seems to be correct. According to the help files for the IRR and NPV functions (https://support.office.com/en-us/art...__toc309306711 ), the IRR function finds the "root" (zero) of the NPV function by changing IRR

    NPV=sum for i=1 to n of [values(i)/(1+irr)^i]

    Using this formula, it appears to me that the root for NPV is indeed near 35%. Mathematically, it appears to me that the IRR function is finding the "correct" answer based on the inputs. Why do you think this IRR is unreasonable? What IRR would you expect? Are the raw data all correct? Is there a different financial function that should be used instead of the NPV/IRR function? These are all questions that I cannot answer, but should give you some ideas to consider.
    Attached Files Attached Files
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-08-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    69

    Re: Getting ridiculous IRR answers

    I agree, the math makes sense. The 35% is a monthly IRR however. How do I get to an annualized IRR from that? I summed up the numbers for each year and came up with an IRR of 96%. So then, my question would be how do I convert from a monthly IRR (the 36%) to an annual IRR. I had tried to do this with the following formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is that the correct formula? If not, what is?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Getting ridiculous IRR answers

    As I indicated, I do not know the philosophy behind financial calculations. The formula you give is the one that I see given around the internet for a search like "how to annualize monthly returns", so I assume this is the correct formula. If it is not the correct formula, it will require someone who knows financial calculations to explain the correct formula to annualize monthly returns for this case.

  5. #5
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Getting ridiculous IRR answers

    Quote Originally Posted by bgreeson View Post
    I agree, the math makes sense. The 35% is a monthly IRR however. How do I get to an annualized IRR from that? I summed up the numbers for each year and came up with an IRR of 96%. So then, my question would be how do I convert from a monthly IRR (the 36%) to an annual IRR. I had tried to do this with the following formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is that the correct formula? If not, what is?
    Based on these cashflows, it appears that the resulting IRR is indeed correct. The main reason why I am convinced of this is because of the LARGE positive number of $3M in the 4th period of the cashflow (1/2/2016). Cashflow timing plays a very heavy role in determining IRR and NPV and as such, numbers earlier to the base date (1/11/2015) have a higher weight.

    35% Monthly IRR -correct
    Conversion to Annual IRR is correct as well.

    Keep in mind though, that even though it does not seem to apply in this case, the IRR functions best when the cashflows either go from negative straight to positive once, or vice versa. Having cashflows cut across the $0 mark (i.e. switching between negative and positive) multiple times can result in multiple roots (i.e. multiple IRRs). In which case you will need to use the estimate.

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Getting ridiculous IRR answers

    Quote Originally Posted by bgreeson View Post
    I agree, the math makes sense.
    Although the result makes sense for the numbers given, beware of "garbage in, garbage out".

    I cannot think of too many situations that generate a return of 3+ million just 3 months after an initial investment of 1+ million. That greatly influences the IRR for the 60-month cash flow series.

    The effect of cash flows on the IRR drops off significantly over time. The 60-month IRR is essentially set within the first 12 months.

    Quote Originally Posted by bgreeson View Post
    The 35% is a monthly IRR however. How do I get to an annualized IRR from that? I summed up the numbers for each year and came up with an IRR of 96%. So then, my question would be how do I convert from a monthly IRR (the 36%) to an annual IRR. I had tried to do this with the following formula: =(IRR_Value+1)^12-1. Is that the correct formula?
    There are two methods of annualizing the monthly IRR, both valid. Which to use depends on the purpose for calculating the IRR. Sometimes the method is dictated by law or industry standards.

    If you want an annualized IRR that is comparable to the Excel XIRR result, compounding the monthly IRR, as you did, is correct.

    Sometimes, we simply multiply the monthly IRR by 12. (We could nominalize the Excel XIRR result to get a comparable number.)

    The huge difference between the IRR of the simple annual net cash flows (96.38%) and
    the annualized IRR of the monthly cash flows (3360% compounded or 412% nominal) is the timing of the 3+ million return just 3 months after the initial 1+ million investment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF OR different answers
    By bubs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 10:19 AM
  2. [SOLVED] Count the number of matches in a row of one word answers with a row of correct answers
    By flammer4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2012, 01:17 PM
  3. Removing ridiculous scientific notations
    By Xmosis in forum Excel General
    Replies: 2
    Last Post: 03-01-2011, 03:10 PM
  4. RIDICULOUS Chart Printing Error
    By pipp22 in forum Excel General
    Replies: 2
    Last Post: 07-14-2010, 01:33 PM
  5. I think this is ridiculous
    By Steve in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-01-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1