In this tutorial, we’ll teach you how to compute both the payback period and the discounted payback period in Excel.
If you’ve arrived here from our Youtube channel, click on “Video tutorial and Excel template” section in the Contents box below to download the Excel template.
If you’d like to learn more about the payback period rule and how it compares to the net present value rule, we recommend the lesson below.
Contents
Step 1: Enter the project’s cash flows
We begin with entering the project’s cash flows into the spreadsheet. Let’s assume that we have a 5-year project that costs $11,000 to invest today and that generates annual cash flows of $3,000. And, let’s suppose that the appropriate discount rate is 10%.
As you can see in Figure 1, we have the project’s cash flows on the left hand side and their present values on the right hand side. The former will be used to compute the payback period and the latter to compute the discounted payback period. The present value of each cash flow is obtained by dividing it by (1+10%)t. For example, as seen in Figure 1, the present value of the final cash flow of $3,000 is $3,000 / (1+10%)5.
Step 2: Track the profit/loss for each period
The second step is to create a profit/loss column, which will help us find the point at which we recover the original investment. Specifically, we start the project with a loss of $11,000 after the initial investment outlay. Once we receive $3,000 at the end of year 1, our loss comes down to $8,000 (or $8,273 in present value terms). At the end of year 2, the loss further goes down to $5,000 (or $5,793 in present value terms) and so on.
As seen in Figure 2, each year the running profit/loss is computed by adding the current year’s cash flow (e.g., C8 for year 5) to the previous year’s profit/loss (D7 for year 4).
Step 3: Determine the (discounted) payback period through the breakeven point
As we can see in Figure 3, we’re running a loss of $2,000 at the end of year 3 and a profit of $1,000 at the end of year 4. This means that the project’s payback period is somewhere between 3 and 4 years. By the same argument, the discounted payback period is somewhere between 4 and 5 years.
To find the exact values, we need to divide the remaining loss by the amount received in the current year. In particular, we receive $3,000 in year 4 and there was a loss of $2,000 remaining in year 3. Then, as the selected cell in Figure 3 indicates, the payback period is:
3 + ($2,000 / $3,000) = 3.67
Similarly, the discounted payback period is:
4 + ($1,490 / $1,863) = 4.80
Video tutorial and Excel template
You can download our Excel template that automatically calculates the (discounted) payback period here.
What is next?
If you have enjoyed this tutorial, you might find the following tutorials useful too.