Can a company have positive cash flow even if its situation is bad or vice versa?
Definition of Net LossA net loss occurs when a company's revenues and gains are less than its operating expenses, other expenses and losses. The net loss or net income is reported on the company's income statement. Show
Keep in mind that under the accrual basis of accounting:
Definition of Positive Cash FlowGenerally, a positive cash flow refers to a positive net inflow of cash from operating activities, which is shown on a company's statement of cash flows (SCF) or cash flow statement. [The SCF should be distributed with a company's income statement and balance sheet.] The cash from operating activities almost always begins with the company's accrual accounting net income or net loss for the accounting period. The net income or net loss amount is then adjusted to the cash amount. It is in this section of the SCF where you will find the reasons why a company can have an accounting net loss but have a positive net cash inflow. Example of Net Loss But a Positive Cash FlowA common adjustment to a company's accrual accounting net income or net loss is depreciation expense. Depreciation expense was included on the income statement and reduced the company's net income (or increased its net loss), but the depreciation amount did not involve a current period cash payment. For example, if a company purchased equipment in the previous year for $2,100,000 and is depreciating the equipment over seven years, the depreciation expense for the year of the income statement might be $300,000. The $300,000 accounting entry debited Depreciation Expense and credited Accumulated Depreciation. As you can see, not a penny left the checking account in the year of the income statement. (All $2,100,000 of cash left the checking account in the previous year.) If the company's income statement reported a loss of $50,000 after the $300,000 "non-cash" depreciation expense, the company's cash may have actually increased by $250,000. There are many other SCF adjustments to the company's net income that are less significant than depreciation, such as:
This tutorial explains the syntax of the Excel IRR function and shows how to use an IRR formula to calculate the internal rate of return for a series of annual or monthly cash flows. IRR in Excel is one of the financial functions for calculating the internal rate of return, which is frequently used in capital budgeting to judge projected returns on investments. IRR function in ExcelThe Excel IRR function returns the internal rate of return for a series of periodic cash flows represented by positive and negative numbers. In all calculations, it's implicitly assumed that:
The function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007. The syntax of the Excel IRR function is as follows: IRR(values, [guess]) Where:
For example, to calculate IRR for cash flows in B2:B5, you'd use this formula:
For the result to display correctly, please make sure the Percentage format is set for the formula cell (usually Excel does this automatically). As shown in the above screenshot, our Excel IRR formula returns 8.9%. Is this rate good or bad? Well, it depends on several factors. Generally, a calculated internal rate of return is compared to a company's weighted average cost of capital or hurdle rate. If the IRR is higher than the hurdle rate, the project is considered a good investment; if lower, the project should be rejected. In our example, if it costs you 7% to borrow money, then an IRR of about 9% is fairly good. But if the cost of funds is, say 12%, then the IRR of 9% is not good enough. In reality, there are many other factors that influence an investment decision such as the net present value, absolute return value, etc. For more information, please see IRR basics. 5 things you should know about Excel IRR functionTo ensure that your IRR calculation in Excel is done correctly, please remember these simple facts:
Understanding IRR formula in ExcelSince the internal rate of
return (IRR) is a discount rate that makes the net present value (NPV) of a given series of cash flows equal to zero, the IRR calculation relies on the traditional NPV formula: Where:
Because of a specific nature of this formula, there is no way to calculate IRR other than through trial and error. Microsoft Excel also relies on this technique but does multiple iterations extremely quickly. Starting with the guess (if supplied) or the default 10%, the Excel IRR function cycles through the calculation until it finds the result accurate within 0.00001%. If after 20 iterations an accurate result is not found, the #NUM! error is returned. To see how it works in practice, let's perform this IRR calculation on a sample data set. For starters, we will try to guess what the internal rate of return might be (say 7%), and then work out the net present value. Assuming B3 is the cash flow and A3 is the period number, the following formula gives us the present value (PV) of the future cash flow:
Then we copy the above formula to other cells and add up all the present values, including the initial investment:
And find out that at 7% we get the NPV of $37.90: Obviously, our guess is wrong. Now, let's do the same calculation based on the rate computed by the IRR function (around
8.9%). Yep, it does lead to a zero NPV:  Tip. To display the exact NPV value, choose to show more decimal places or apply the Scientific format. In this example,
the NPV is exactly zero, which is a very rare case! Using IRR function in Excel – formula examplesNow that you know the theoretical basis of IRR calculation in Excel, let's make a couple of formulas to see how it works in practice. Example 1. Calculate IRR for monthly cash flowsAssuming you have been running business for six months and now you want to figure out the rate of return for your cash flow. Finding IRR in Excel is very straightforward:
Now, you are ready to calculate IRR for the project:
Note. In event of monthly cash flows, the IRR function produces a monthly rate of return. To get an annual rate of return for monthly cash flow, you can use the XIRR function. Example 2: Use guess in Excel IRR formulaOptionally, you can put an expected internal rate of return, say 10 percent, in the guess argument:
As shown in the
screenshot below, our guess does not have any impact on the result. But in some cases, changing the guess value may cause an IRR formula to return a different rate. For more information, please see Multiple IRRs. Example 3. Find IRR to compare investmentsIn capital budgeting, IRR values are often used to compare investments and rank projects in terms of their potential profitability. This example demonstrates the technique in its simplest form. Supposing you have three investment options and you are deciding which one to choose. The reasonably projected returns on the investments can help you make an informed decision. For this, enter the cash flow for each project in a separate column, and then calculate the internal rate of return for each project individually: Formula for project 1:
Formula for project 2:
Formula for project 3:
Given that the company's required rate of return is, say 9%, project 1 should be rejected because its IRR is only 7%. The two other investments are acceptable because both can generate an IRR higher than the company's hurdle rate. Which one would you choose? At first sight, project 3 looks more preferable because it has the highest internal rate of return. However, its yearly cash flows are much lower than for project 2. In situation when a small investment has a very high rate of return, businesses often choose an investment with a lower percentage return but higher absolute (dollar) return value, which is project 2. The conclusion is: the investment with the highest internal rate of return is usually preferred, but to make the best use of your funds you should evaluate other indicators as well. Example 4. Calculate compound annual growth rate (CAGR)Though the IRR function in Excel is designed for calculating the internal return rate, it can also be used for computing the compound growth rate. You will just have to reorganize your original data in this way:
When done, write a regular IRR formula and it will return the CAGR:
To make sure the result is correct, you can verify it with the commonly used formula for calculating CAGR: (end_value/start_value)^(1/no. of periods) -1 As shown in the screenshot below, both formulas produce the same
result: For more information, please see How to calculate CAGR in Excel. IRR and NPV in ExcelThe internal rate of return and the net present value are two closely related concepts, and it's impossible to fully understand IRR without understanding NPV. The result of IRR is nothing else but the discount rate corresponding to a zero net present value. The essential difference is that NPV is an absolute measure that reflects the dollar amount of value that could be gained or lost by undertaking a project, while IRR is the percentage rate of return expected from an investment. Because of their different nature, IRR and NPV may "conflict" with each other - one project may have a higher NPV and the other a higher IRR. Whenever such a conflict arises, finance experts advise to favor the project with a higher net present value. To better understand the relationship between IRR and NPV, please consider the following example. Let's say, you have a project that requires an initial investment of $1,000 (cell B2) and a discount rate of 10% (cell E1). The project's lifespan is five years and the expected cash inflows for each year are listed in cells B3:B7. To find out how much the future cash flows are worth now, we need to compute the net present value of the project. For this, use the NPV function and subtract the initial investment from it (because the initial investment is a negative number, the addition operation is used):
A positive net present value indicates that our project is going to be profitable: What discount rate will make the NPV equal to zero? The following IRR formula gives the answer:
To check this, take the above NPV formula and replace the discount rate (E1) with IRR (E4):
Or you can embed the IRR function directly into the rate argument of NPV:
The screenshot above shows that the NPV value rounded to 2 decimal places indeed equals zero. If you are curious to know the exact number, set the Scientific format to the NPV cell or choose to show more decimal places: As you can see, the result is well within the declared accuracy of 0.00001 percent, and we can say that the NPV is effectively 0. Tip. If you do not entirely trust the result of IRR calculation in Excel, you can always check it by using the NPV function like shown above. Excel IRR function not workingIf you have run into some problem with IRR in Excel, the following tips might give you a clue for fixing it. IRR formula returns a #NUM! errorA #NUM! error may be returned because of these reasons:
Blank cells in the values arrayIn case no cash flow arises in one or more periods, you may end up with empty cells in the values range. And it is the source of problems because rows with empty cells are left out of Excel IRR calculation. To fix this, just enter zero values in all blank cells. Excel will now see the correct time intervals and compute the internal return rate correctly. Multiple IRRsIn situation when a cashflow series changes from negative to positive or vice versa more than once, multiple IRRs can be found. If the result of your formula is far from what you expected,
check the guess value – in case the IRR equation can be solved with several rate values, the rate closest to the guess is returned. Possible solutions:
Irregular cash flow intervalsThe IRR function in Excel is
designed to work with regular cash flow periods such as weekly, monthly, quarterly or annually. If your inflows and outflows occur at unequal intervals, IRR would still consider the intervals equal and return a wrong result. In this case, use the XIRR function instead of IRR. Different borrowing and reinvestment ratesThe IRR function implies that the project earnings (positive cash flows) are continuously reinvested at the internal rate of return. But in the real word, the rate at which you borrow money and the rate
at which you reinvest the profits are often different. Luckily for us, Microsoft Excel has a special function to take care of this scenario – the MIRR function. That's how to do IRR in Excel. To have a closer look at the examples discussed in this tutorial, you are welcome to download our sample workbook to Using IRR function in Excel. I thank you for reading and hope to see you on our blog next week! You may also be interested inCan a company have positive cash flow but in great trouble?Yes, an organization can show positive cash flows but be in grave trouble.
Is it possible for a company to show positive cash flows?Key Takeaways: It is possible for a company to have positive cash flow while reporting negative net income. If net income is positive, the company is liquid and profitable. If a company has positive cash flow, it means the company's liquid assets are increasing.
Can a company have a negative free cash flow and still be considered successful?Though negative cash flow is not inherently bad, this financial asymmetry is not sustainable or viable for your business in most cases. Ultimately, your business needs enough money to cover operating expenses. Uncontrolled or overlooked negative cash flow can render your business unprofitable.
Why positive profits can still result in a negative cash flow?You can make a net profit and have negative cash flow. For example, your bills might be due before a customer pays an invoice. When that happens, you don't have cash on hand to cover expenses. You can't reinvest cash into your business when you have negative cash flow.
|
