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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

What Is Cumulative Cash Flow?

Steps to Calculate Cumulative Cash Flow in Excel

📌 Step 1: Calculate Net Cash Flow

📌 Step 2: Insert Formula to Calculate Cumulative Cash Flow

How to Calculate Discounted Cumulative Cash Flow

Conclusion

Download Practice Workbook

You can download our practice workbook from here for free!

Calculate Cumulative Cash Flow.xlsx

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.

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.

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.

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

📌 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.

Afterward, click on the D9 cell.
Following, write the formula below in the formula bar.

=C8+D7

Subsequently, press the Enter button.

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.

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

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.

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.

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.

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.

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

Next, click on the D12 cell and insert the following formula.

=C12+D11

Following, press the Enter button.

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.

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

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!