How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)

Calculating cumulative cash flow is a very handy approach for various business analysis purposes. Moreover, sometimes for our own personal reasons, we need to calculate the cumulative cash flow. And with Excel, we can create a dynamic one very quickly. In this article, I will show you step-by-step guidelines to calculate cumulative cash flow in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Cumulative Cash Flow?

Cumulative cash flow is the accumulated cash inflows and outflows of an institution over the years from the very beginning of the institution. From this calculation, you can determine the worth of the institution at the current time. 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.

Net Cash Flow vs Cumulative Cash Flow:

Net cash flow is mainly calculated each year. This mainly calculates the net profit or loss for a year. On the other hand, cumulative cash flow doesn’t calculate the company worth for a single year alone, but for the whole time span from the starting of the company to the current time.

Say, in the first year of the company, your inflow is $5000 dollars and outflow is $20000 dollars. And, in the second year, the inflow is $20000 dollars and the outflow is $10000 dollars.

Then, the net cash flow for the first year would be $ -15000 dollars and the net cash flow for the second year would be $10000 dollars. But, the cumulative cash flow in the first year would be $ -15000 dollars and in the second year, it would be $ -5000 dollars.

Uses of Cumulative Cash Flow:

  • You can determine a company’s present value through this.
  • You can also calculate the Internal Rate of Return (IRR).
  • Besides, you can find the cash conversion ratio for the customers.
  • Moreover, you can calculate your payback period by calculating cumulative cash flow.

Steps to Calculate Cumulative Cash Flow in Excel

Say, you have a dataset of cash flows for 5 years of an institution. Now, you need to calculate the cumulative cash flows for this dataset at end of each year. Follow the steps below to accomplish this.

Dataset to Calculate Cumulative Cash Flow in Excel

In this article, we have used the Office 365 version of Microsoft Excel. But, if you face any problems regarding versions, please leave a comment below.


📌 Step 1: Calculate Net Cash Flow

First, you have to calculate the net cash flows for every year of the given dataset.

  • To do this, at the very beginning, click on the C7 cell.
  • Subsequently, insert the following formula.
=C5-C6
  • Afterward, press the Enter button.

Insert Formula to Calculate Net Cash Flow in Excel

  • Following, place your cursor in the bottom right position of the cell.
  • Subsequently, a black fill handle will appear. Now, drag it rightward to copy the formula.

Drag Fill Handle Rightward

Thus, you will get the net cash flows for every year.

Net Cash Flows of Each Year

Read More: How to Create Cash Flow Statement Format in Excel


📌 Step 2: Insert Formula to Calculate Cumulative Cash Flow

Now, you need to calculate the cumulative cash flow for every year.

  • In order to do this, click on the D8 cell and insert the following formula.
=C7
  • Subsequently, hit the Enter button.

Calculate Cumulative Cash Flow for Year 0

  • Afterward, click on the D9 cell.
  • Following, write the formula below in the formula bar.
=C8+D7
  • Subsequently, press the Enter button.

Insert Formula to Calculate Cumulative Cash Flow in Excel

  • At this time, place your cursor in the bottom right position of the cell.
  • When the black fill handle appears. Drag it rightward to copy the formula.

Drag Fill Handle Rightward to Copy Same Formula

As a result, you can find the cumulative cash flow for every year. And, the result should look like this.

Cumulative Cash Flow for Every Year

Read More: Create Cash Flow Statement Format Using Direct Method in Excel


How to Calculate Discounted Cumulative Cash Flow

Now, it might be needed sometimes to calculate the discounted cumulative cash flow in regard to the present value. Suppose, the discount rate is 10%. Now, follow the steps below to calculate the discounted cumulative cash flow.

📌 Steps:

  • First and foremost, you need to calculate the discounting factor for each year from the year 0.
  • To do this, click on the C10 cell and insert the following formula.
=1/(1*(1+$C$4)^C6)
  • Subsequently, hit the Enter button.

Insert Formula to Calculate Discounting Factor

  • As a result, you will get the discounting factor for the year 0.
  • Now, place your cursor in the bottom right position of the C10 cell.
  • Subsequently, drag the fill handle rightward upon its appearance.

Drag the Fill Handle Rightward

  • Consequently, you will get the discounting factors for all the years.
  • Now, to calculate the present value of your net cash flow, click on the C11 cell.
  • Afterward, insert the following formula.
=C9*C10
  • Now, press the Enter button.

Insert Formula to Calculate Present Value of Net Cash Flows

  • At this time, place your cursor in the bottom right position.
  • As a result, a black fill handle will appear. Drag it rightward to copy the same formula.

Drag Fill Handle Rightward

  • As a result, you will get all the present values for each year of your net cash flows.
  • At this time, click on the C12 cell.
  • Following, write the formula below and press the Enter button.
=C11

Calculate Cumulative Cash Flow for Year 0

  • Next, click on the D12 cell and insert the following formula.
=C12+D11
  • Following, press the Enter button.

Insert Formula to Calculate Cash Flow in Excel

  • As a result, you will get the cumulative cash flow for year 1.
  • Now, place your cursor in the bottom right position of the cell.
  • Lastly, when the fill handle appears, drag it rightward to copy the same formula.

Drag Fill Handle Rightward to Copy Same Formula

Finally, you will get the discounted cumulative cash flow for each year. And for instance, the outcome should look like this.

Calculated Cumulative Cash Flows in Excel

Read More: How to Apply Discounted Cash Flow Formula in Excel


Conclusion

In a nutshell, in this article, I have shown you step-by-step guidelines to calculate the cumulative cash flow and the discounted cumulative cash flow in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. Besides, you are very welcome to comment here if you have any further questions or recommendations. And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo