Encountered a #NUM! error in the IRR section

The #NUM! error is something common that you can encounter in Excel. This error often occurs when a formula or a function contains numeric values that aren’t valid.

Usually, when you have entered a numeric value using a data type or a number format that is not supported in the argument section of the formula, you will be prompted (noticeable mark within the cell with error) that there is a #NUM! error.

For example, you can’t enter a value like $500,000 in currency format, because, as you already know in Excel, dollar signs are used as absolute reference indicators while commas are considered as argument separators in formulas. Basically, to avoid the #NUM! error, enter values as unformatted numbers, like 500000, instead.

When Financial Modeling in Excel, the #NUM! error usually happens when a formula that uses a function that iterates, such as IRR or RATE, can’t find a result.

The logic behind it is because the IRR can only be calculated if there’s an investment and consecutive profit, otherwise, it might not be possible to calculate the IRR. Therefore, please recheck if your cash flow projections show at least 1 year negative in the beginning and positive cash flows in the later years.

Sometimes, the reason as to why the IRR resulted in #NUM! error is an indication that your business plan is not working or you are not making enough money (in case your projections show more losses rather than going forward). In that case, please try to change your assumptions in order to figure out how your project can become profitable.

Was this helpful?