Pieter Vandenberg [email protected] wrote: : I am trying to calculate a few IRRs that are returning #NUM. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. I've been trying different guesses and I've been > tweaking the number of iterations and max change in Tools | Options | > Calculation, and yet I still get #NUM. error Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel Web App, Excel for iPhone, Excel

If IRR gives the #NUM! A number that you guess is close to the result of IRR. All succeeding payments are discounted based on a 365-day year. I just tried on a brand new excel workbook and same issue.

More... error Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel Web App, Excel for iPhone, Excel IRR uses the order of values to interpret the order of cash flows. What does it do to the calculation of the IRR?

error value. Arg2 Optional Variant Guess - a number that you guess is close to the result of IRR. As for the first and third cash flows, IRR computes the rate at which the NPV is zero. IRR uses the order of values to interpret the order of cash flows.

The actual formula is: If there is no negative number as the initial investment, no combination of positive cash flows will be able to equal zero. When you change the inputs (like 272564 --> 100,000) you change the answer and if you leave the same guess and its too far from the answer, Excel runs out the To avoid the #NUM! Author Bio Allen Wyatt With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author.

Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent. Any other feedback? I am using Excel 2007 on Windows 7. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

The following sequence of cash flows without a guess rate will produce an answer of 25%. Post a question in the Excel community forum Help us improve Excel Do you have suggestions about how we can improve the next version of Excel? That's what the #num is telling you. Which version do I have?

For the same reason you will not be able to find the :> value of X in the following: 6X^2 - 10X + 5 = 0[.] :> Look up Descartes rule What is in what cells? Remarks Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. To avoid the #NUM!

Forum Today's Posts FAQ Calendar Community Groups Forum Actions Mark Forums Read Quick Links What's New? Can Communism become a stable economic strategy? Excel uses an iterative technique for calculating XIRR. If the first value is a cost or payment, it must be a negative value.

For formulas to show results, select them, press F2, and then press Enter. How can we improve it? If values and dates contain a different number of values, XIRR returns the #NUM! The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment.

Last edited by NickRizz; Mar 8th, 2013 at 08:18 PM. If an array or reference argument contains text, logical values, or empty cells, those values are ignored. asked 3 years ago viewed 1296 times active 3 years ago Related 1Applying Excel formula to a large column2Formula error while counting unique entries in Excel0Why am I getting a “name DatesÂ Â Â Â Required.

Enter your e-mail address and click "Subscribe." (Your e-mail address is not shared with anyone, ever.) Want to see what the newsletter looks like? To fix this, change the number of times Excel iterates formulas: Click File > Options. IRR has no trouble computing the rate (2%) of the following cash flow, despite 8 sign changes: -100000 {10000,-1000} eight times 53435 Register To Reply 04-30-2006,06:10 PM #5 [email protected] Guest RE: Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Mar 8th, 2013,10:34 PM #7 JoeMo MrExcel MVP Join Date May 2009 Posts 12,744 Re: IRR giving #NUM error

He is president of Sharon Parq Associates, a computer and publishing services company. Remember Me? In the Maximum Change box, type the amount of change youâ€™ll accept between calculation results. Description Returns the internal rate of return for a series of cash flows represented by the numbers in values.

The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. The guess is your best guess as to the answer. How can we improve it? HELP!!!

Why? To fix this, change the formula so that its result is between -1*10307 and 1*10307. share|improve this answer answered Sep 3 '13 at 2:09 Pieter Geerkens 1308 add a comment| up vote 0 down vote Internal Rate of Return calculates the discount rate for an original Leave your own comment: *Name: Email: Notify me about new comments ONLY FOR THIS TIP Notify me about new comments ANYWHERE ON THIS SITE Hide my email address *Text: *What