Excel #NUM! error
The calculation has no valid numeric result: an impossible number or a function that cannot converge.
Why it appears
- Square root of a negative number: =SQRT(-4).
- IRR or RATE cannot find a solution, for example when every cash flow is positive.
- DATEDIF with the end date before the start date.
How to fix it
- Check the signs: in IRR the initial investment is negative.
- Put DATEDIF’s dates in order: start date first.
- Give IRR or RATE a guess close to the expected value.
Gives the error
=IRR(B5:B8)
Fixed
=IRR(B4:B8)
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Return with IRR
IRR(cash flows) is the rate at which net present value is 0: the project’s return. Here the initial investment does go inside the range. If the IRR beats the rate you require, the project is worth it.
In B10, calculate the internal rate of return of the cash flows for years 0 to 4.
| A | B | C | |
|---|---|---|---|
| 1 | Required rate | 12% | |
| 2 | |||
| 3 | Year | Cash flow | |
| 4 | 0 | -80,000.00 | |
| 5 | 1 | 22,000.00 | |
| 6 | 2 | 26,000.00 | |
| 7 | 3 | 30,000.00 | |
| 8 | 4 | 28,000.00 | |
| 9 | |||
| 10 | IRR | ||
| 11 | Worth it? | ||
| 12 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What does #NUM! mean in Excel?
- The calculation has no valid numeric result: an impossible number or a function that cannot converge.
- How do I get rid of the #NUM! error?
- Check the signs: in IRR the initial investment is negative. Put DATEDIF’s dates in order: start date first. Give IRR or RATE a guess close to the expected value.
- What is #NUM! called in Spanish Excel?
- Spanish Excel shows it as #¡NUM!.