ExcelInsider Team
If you want to make an investment for the future, you might be interested to know how many years it would take for the investment to start giving you a profit. While it is pretty easy when the cash flow is the same for every year, it is not the same when the cash flow is uneven. In this article, we will learn how to calculate payback period with uneven cash flows in excel.
➤ Create a Cumulative Cash Flow column using the following formula:
=SUM(B$2:B2)
➤ Replace B$2 and B2 with the investment and autofill the rows to create the whole column.
➤ Note the year when the Cumulative Cash Flow starts being positive.
➤ Write this formula to calculate the payback period:
=5+(-C7/B8)
➤ Replace 5 with the year count prior to the one when it started being positive, C7 with the cumulative cash flow cell of that year, and B8 with the cash flow of the following year (year 6 in this context).
I’m sure you are looking for an explanation of this method, and we are ready to give it to you. Read the whole tutorial to know exactly how we did the calculation, and what you will need to do it properly yourself.
What is the Payback Period in Finance?
In finance, when you are making an investment, you are doing it for some profit. An investment usually pays back in multiple payments or installments. In the end, you will receive more money than you invested. For example, if you invested 10000 dollars and you are getting 1000 dollars per year for 15 years, you will get 15000 dollars in total.
However, at some point, you will get the original amount back and start making a profit. In this example, the payback period is 10 years, as you get the initial investment of 10000 dollars back after 10 years, and start making a profit.
Not all investments are the same. In a less secure yet high-profit investment, the cash flow might not be the same every year. In this tutorial, we will show how you can calculate the payback period with uneven cash flows in Excel.
Calculating Payback Period with Uneven Cash Flows in Excel
For this example, we have a dataset with a 10000-dollar investment. The investment will pay for 10 years, and the cash flows are different for each year. Here is how to calculate the payback period for this dataset:
Step 1: Calculate the Cumulative Cash Flow
➤ Start by making a new column for Cumulative Cash Flow. In this column, we will calculate the cash flow generated so far from the investment.
➤ In the first cell of the column ( C2 as the heading is not counted as the first cell), write this formula:
=SUM(B$2:B2)
➤ Drag the bottom-right corner of the C2 cell to C12 so that the whole column can be filled.
Step 2: Calculate the Payback Period
➤ In the Cumulative Cash Flow column, notice that the values started being positive at year 6.
➤ In another cell (E2 for this example), write this formula:
=5+(-C7/B8)
➤ Press Enter.
➤ As the calculation is derived from the currency cells, excel will show it as currency as well.
➤ In the Home tab, go to the Number group, and select Number from the drop-down menu.
➤ Now the payback period will be shown properly.
Frequently Asked Questions
What is the series of uneven cash flows?
Cash flows from an investment are not always even. In general, an investment will give you the exact same amount of money every year. Low-risk investments provide payments this way in general. However, for high-risk investments, the cash flows fluctuate every year, and you end up with a series of uneven cash flows.
What is the formula for payback period?
When the cash flows are the same for every year, the formula for the payback period in Excel is as below:
=A1/B1
Replace A1 with the initial investment and B1 with the average annual cash flow. However, do not write the initial investment in negative, else the formula will return a negative value as well.
How to calculate payback period with negative cash flows?
The method is the same for calculating the payback period with uneven cash flows. You need to keep calculating the cumulative cash flows until it becomes positive, and then measure the payback period.
Is cash flow the same as profit?
No. For an investment, a few cash flows are your initial investment, which is why we calculate the payback period. In a business, the cash flows are the cash inflows and outflows that occur on a daily basis. They are not the profit made by the company.
What if cash flows are negative?
For a business, having negative cash flows means that the business needs to strategize or take a loan to increase its liquid cash. For an investment, if the cash flows are negative for a long time, it means that the investment is probably not worth it.
Wrapping Up
In this article, we learned how to calculate the payback period with uneven cash flows. For your convenience, the workbook used in this article is available to download for free. Bookmark this site so you can revisit for more Excel tutorials. Leave your feedback under this tutorial for us to read. We will see you in another article.
