In this article, we are going to focus on how to calculate cash flow in Excel. There exist different types of cash flow like annual, incremental, discounted, net, cumulative, and so on. We’ll create a knowledge hub in this article so that you can easily find and navigate to your required type of cash flow easily.
Also, we need a statement format of various kinds of cash flow on a regular basis. For example, we may need the statement format of cash flow in general or cash flow on a daily, weekly, and monthly basis. We’ll link articles with standard statement formats of these cash flow types.
Payback Period indicates the length of time in years or months that we need to recover the initial capital back from an investment. We can calculate different types of Payback Periods like general and discounted periods using Excel.
We’ll also include different important topics like cash flow calculators, diagrams, and charts in this article in the form of hyperlinks.
This article is a rich enough guide for calculating the cash flow of popular formats. To discover more on this interesting topic, just dive into this article and explore yourself!
Table of Contents Expand
What Is Cash Flow?
Cash flow is used to understand the gained or consumed amount of Cash in a certain period of time at a business, company, or institute. Basically, you can know the total spent amount and generated amount at the business over a certain period of time with Cash flow analysis. Overall Cash Flow is the total amount of money moving in and out of the business.
How to Calculate Cash Flow in Excel: 7 Suitable Examples
Here, I’m going to explain 7 examples of how to calculate Cash Flow in Excel. For your better understanding, I will use a sample dataset. Which has 5 Rows. These are Discount Rate, Year, Investing CF, Financing CF, and Operating CF. The sample dataset is given below.
Here, if you notice then you can see that some terms are in Red color. Because I have used a custom format for the negative values. Basically, Investing Cash Flow and Financing Cash Flow denote the Cash Outflows for a business. That is why those are negative values.
1. Use of NPV Function to Calculate Cash Flow in Excel
You can use the NPV function to calculate the Net Present Value of Cash flow in Excel. Actually, to apply the NPV function firstly you have to find out the Net Cash Flow. Let’s start with finding Net Cash Flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C10 where you want to see the Net CF.
- Secondly, you should use the corresponding formula in the C10 cell.
=C7+C8+C9
In this formula, I have added all the cash flows to find the net cash flow.
- Thirdly, press ENTER to get the result.
- After that, you have to drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells D10:H10.
Finally, you can see the Net Cash Flow for all those given periods.
- Now, you have to select a different cell C12 where you want to keep the Net Present Value.
- Then, you should use the corresponding formula in the C12 cell.
=NPV(C4,D10:H10)+C10
- Subsequently, you must press ENTER to get the result.
Lastly, you will get the Net present value of Cash flow.
Formula Breakdown
Here, the NPV function will return the Net present value based on a discount rate, cash inflows, and cash outflows of an investment.
- Here, C4 is the Discount Rate.
- The data range D10:H10 denotes the cash flows.
- NPV(C4,D10:H10)—> becomes $48,738.26.
- Then, I have to include the total outflows of the beginning year. As C10 is the outflow amount of the initial year thus I have added the value of the C10 cell.
- So, $48,738.26+(-$20,000)—> turns $28,738.26.
2. Employing PV Function to Calculate Discounted Cash Flow
You can use the PV function to calculate the Present Value of Cash flow in Excel. Actually, to apply the PV function firstly you have to find out the Net Cash Flow. Let’s start with finding Net Cash Flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C10 where you want to see the Net CF.
- Secondly, you should use the corresponding formula in the C10 cell.
=C7+C8+C9
In this formula, I have added all the cash flows to find the net cash flow.
- Thirdly, press ENTER to get the result.
- After that, you have to drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells D10:H10.
Finally, you will get the Net Cash Flow.
- Now, you have to select a different cell C11 where you want to keep the Present Value.
- Then, you should use the corresponding formula in the C11 cell.
=PV($C$4,C6,0,C10)
- Subsequently, you must press ENTER to get the result.
Lastly, you will get the Present value of Cash flow.
Formula Breakdown
Here, the PV function will return the Present Value of an investment.
- Firstly, $C$4 denotes the discount rate. Here, the Dollar sign ($) denotes that the value is fixed.
- Secondly, C6 denotes NPER as the period of time.
- Thirdly, 0 denotes that you don’t know the PMT.
- Fourthly, C10 denotes the Net Cash flow as Future Value.
- After that, you have to drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells D11:H11.
Finally, you will get all the Present value of the Cash flow.
3. Using Generic Formula to Calculate Cumulative Cash Flow
Here, Cumulative cash flow is the accumulated cash inflows and outflows of an institution over the years from the very beginning of the institution. Here, if you take the cash inflows as positive, the outflows would be taken as negative. So, from this calculation, one can easily determine if the institution is in profit or loss.
Now, you can find the Cumulative cash floweven without using any Excel functions. Actually, to use the generic formula firstly you have to find out the Net Cash Flow. Here, you can follow example-1 or example-2 to get the Net Cash Flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C10 where you want to keep the PV factor.
- Secondly, you should use the corresponding formula in the C10 cell.
=1/(1+$C$4)^C6
- Thirdly, press ENTER to get the result.
Formula Breakdown
- Here, I have added 1 with the discount rate.
- 1+$C$4—> becomes 108%.
- Then, I have kept the Year as the power function.
- 108%^C6—> turns 1.
- Finally, I have divided 1 by the previous output.
- 1/1—> returns 1.
- After that, you have to drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells D10:H10.
Finally, you will get all the Present value factors.
- Now, you have to select a different cell C11 where you want to keep the Present Value.
- Then, you should use the corresponding formula in the C11 cell.
=C10*C9
In this formula, I have multiplied the PV factor with the Net Cash flow.
- Subsequently, you must press ENTER to get the result.
Lastly, you will get the present value of Cash flow.
- After that, you have to drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells D11:H11.
At this time, you will get all the Present value cash flow.
- After that, you should use the corresponding formula in the C12 cell.
=C11
In this formula, I have used the value of the C11 cell.
- Subsequently, you must press ENTER to get the result.
Lastly, you will get the 1st Cumulative Cash flow.
- After that, use the following formula in the D12 cell.
=C12+D11
- Subsequently, press ENTER to get the result.
This is the 2nd Cumulative Cash flow.
- Now, drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells E12:H12.
Finally, you will get all the Cumulative Cash flow for the given periods.
4. Calculating Free Cash Flow
Free Cash Flow is a term for Cash flow. Basically, the Free Cash Flow denotes the monetary amount that you have in hand to invest or use after a certain time.
Now, I will calculate the Free Cash flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C9 where you want to keep the Free Cash Flow.
- Secondly, you should use the corresponding formula in the C9 cell.
=C4+C5-C6-C7
In this formula, I have added the Net income and Depreciation. And, from that added value I have subtracted Working capital and Capital expenditure.
- Thirdly, press ENTER to get the result.
Finally, you will get the Free Cash Flow.
5. Calculating Operating Cash Flow
Operating Cash Flow is a term of Cash flow. Basically, the Free Cash flow doesn’t give an absolute idea about the remaining cash. Thus, you need to calculate operating cash flow which will give you a clear idea about the remaining cash. Now, I will calculate the Operating Cash flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C9 where you want to keep the Operating Cash Flow.
- Secondly, you should use the corresponding formula in the C9 cell.
=C4+C5-C6-C7
In this formula, I have added the Operating income and Depreciation. And, from that added value I have subtracted Working capital and Taxes.
- Thirdly, press ENTER to get the result.
Finally, you will get the Operating Cash Flow.
6. Calculating Cash Flow Forecast in Excel
Cash Flow Forecast is another term for Cash flow. Actually, for better planning for the future, you need to forecast the Cash Flow. Now, I will calculate the Cash Flow Forecast. The steps are given below.
Steps:
- Firstly, you have to select a different cell C8 where you want to keep the Cash Flow Forecast.
- Secondly, you should use the corresponding formula in the C8 cell.
=C4+C5-C6
In this formula, I have added the Beginning Cash and Project Inflows. And, from that added value I have subtracted Project Outflows.
- Thirdly, press ENTER to get the result.
Finally, you will get the Cash Flow Forecast.
7. Finding Incremental Cash Flow
When a company accepts a new project, it generates additional operating cash flow, which is referred to as Incremental Cash flow. Now, I will calculate the incremental cash flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C8 where you want to keep the Incremental Cash flow.
- Secondly, you should use the corresponding formula in the C8 cell.
=-C4+C5-C6
In this formula, I have subtracted Initial Investment and Rent & Other expenses from the total Cash Inflow.
- Thirdly, press ENTER to get the result.
Finally, you will get the Incremental Cash flow.
Calculating Internal Return Rate of Cash Flow in Excel
You can use the IRR function to calculate the Internal rate of return for the Cash flow in Excel. Firstly, you have to find out the Net Cash Flow. Let’s start with finding Net Cash Flow. The steps are given below.
Steps:
- Firstly, you have to select a different cell C9 where you want to see the Net CF.
- Secondly, you should use the corresponding formula in the C9 cell.
=C7+C8
In this formula, I have added all the cash flows to find the net cash flow.
- Thirdly, press ENTER to get the result.
- After that, you have to drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells D9:H9.
- Now, select another different cell C11 to keep the Internal Rate of Return.
- Then, you should use the following formula in the C11 cell.
=IRR(C9:H9)
Here, C9:H9 is the data range for the IRR function.
- Subsequently, you must press ENTER to get the result.
Lastly, you will get the Internal Rate of Return for the Cash flow.
💬 Things to Remember
- Here, you must use the negative sign for all the Cash outflows. Otherwise, you have to modify all those given formulas. But, in the case of examples 4 to 7 you don’t need to use minus sign as input.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Calculating Cash Flow.xlsx
Conclusion
We’ve covered all possible topics regarding how to calculate cash flow in Excel in this article. We have included the very basics of calculating cash flow as well as the advanced topics like different kinds of standard cash flow formats here. Also, we’ve included hyperlinks to create mode, calculator, diagram, etc for cash flow. With a little bit of practice, we hope you will be able to apply these all by yourself. If you face any issues regarding this, please let us know in the comment section.
Excel Cash Flow Formula: Knowledge Hub
- How to Calculate Annual Cash Flow in Excel
- How to Calculate Incremental Cash Flow in Excel
- How to Calculate Discounted Cash Flow in Excel
- How to Forecast Cash Flow in Excel
- How to Calculate Free Cash Flow in Excel
- How to Calculate Cumulative Cash Flow in Excel
- How to Draw a Cash Flow Diagram in Excel
- How to Track Cash Flow in Excel
- How to Create a Personal Cash Flow Statement in Excel
- How to Calculate Operating Cash Flow Using Formula in Excel
- How to Calculate Payback Period in Excel
- How to Calculate Payback Period with Uneven Cash Flows
- Calculating Payback Period in Excel with Uneven Cash Flows
- How to Calculate Discounted Payback Period in Excel
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Operating Cash Flow in Excel
- How to Calculate Net Cash Flow in Excel
- How to Create a Cash Flow Waterfall Chart in Excel
<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel
As an Excel expert with a deep understanding of financial concepts and extensive experience in utilizing Excel for complex calculations, I can confidently delve into the details of the article on calculating cash flow in Excel.
Cash Flow Basics: The article rightly starts by defining cash flow as the amount of cash gained or consumed within a specific time period in a business, company, or institute. It emphasizes that cash flow analysis provides insights into total spending and generated income over time, representing the total money moving in and out of the business.
Types of Cash Flow: The article introduces various types of cash flow, including annual, incremental, discounted, net, and cumulative. These distinctions highlight the different perspectives and calculations involved in assessing cash flow dynamics.
Statement Formats: Recognizing the importance of presenting cash flow data, the article mentions the need for statement formats on a regular basis, whether it's daily, weekly, monthly, or general. This reflects an understanding of the diverse reporting requirements for different stakeholders.
Payback Period: The concept of payback period is explained as the time needed to recover the initial capital from an investment. The article goes further to illustrate how to calculate both general and discounted payback periods using Excel, demonstrating proficiency in financial analysis.
Cash Flow Calculation Examples: The bulk of the article is dedicated to providing seven practical examples of calculating cash flow in Excel. These examples cover a range of scenarios, including using the NPV function, PV function, generic formulas for cumulative cash flow, calculating free cash flow, operating cash flow, cash flow forecast, and incremental cash flow. Each example is accompanied by detailed steps, formula breakdowns, and practical instructions, showcasing a comprehensive understanding of Excel functions.
IRR Calculation: The article concludes by introducing the Internal Rate of Return (IRR) and demonstrates how to calculate it using the IRR function in Excel. This adds a layer of sophistication to the cash flow analysis, highlighting the ability to handle advanced financial metrics.
Practice Section and Conclusion: The article includes a practice section, encouraging readers to apply the explained methods on their own. This hands-on approach promotes active learning. The conclusion emphasizes coverage of all possible topics related to calculating cash flow in Excel, reinforcing the article's role as a comprehensive guide.
In summary, the article exhibits a profound knowledge of Excel functions and financial concepts, making it a valuable resource for individuals seeking to master cash flow calculations in Excel.
