how to calculate payback in excel

Understanding the Payback Period

The payback period is a capital budgeting technique used to determine the length of time required to recoup the initial investment in a project or asset. In simpler terms, it's how long it takes for an investment to "pay for itself" through the cash flows it generates. This metric is particularly popular for its simplicity and its focus on liquidity and risk.

Why is Payback Period Important?

• Risk Assessment: Projects with shorter payback periods are generally considered less risky, as the capital is tied up for a shorter duration, reducing exposure to market fluctuations or unforeseen events.
• Liquidity: Businesses, especially those with limited capital, often prioritize projects that return cash quickly to fund other operations or investments.
• Simplicity: It's straightforward to calculate and easy to understand, making it accessible even to non-financial managers.

The Basic Payback Period Formula (Even Cash Flows)

When a project is expected to generate a consistent, even amount of cash flow each period, the payback period calculation is very simple:

Payback Period = Initial Investment / Annual Net Cash Inflow

For example, if you invest $100,000 in a new machine that generates $25,000 in net cash inflow annually, the payback period would be:

$100,000 / $25,000 = 4 years

Calculating Payback Period in Excel: The Simple Case (Even Cash Flows)

Excel makes this calculation incredibly easy. Here’s how:

1. Open a new Excel spreadsheet.
2. In cell A1, type "Initial Investment". In cell B1, enter the investment amount (e.g., 100000).
3. In cell A2, type "Annual Net Cash Inflow". In cell B2, enter the annual cash flow (e.g., 25000).
4. In cell A3, type "Payback Period (Years)". In cell B3, enter the formula: =B1/B2

Excel will instantly display the payback period. In our example, it would show 4.

Calculating Payback Period in Excel: Uneven Cash Flows

Most real-world projects don't generate perfectly even cash flows year after year. Cash flows might fluctuate due to market conditions, depreciation schedules, or project phases. For uneven cash flows, the calculation requires a slightly more detailed approach, often involving a cumulative cash flow table.

Step-by-Step Example with Uneven Cash Flows in Excel

Let's say you have an initial investment of $150,000 and the following projected annual net cash inflows:

• Year 1: $40,000
• Year 2: $50,000
• Year 3: $60,000
• Year 4: $30,000
• Year 5: $20,000

Here's how to calculate the payback period in Excel:

1. Set up your data:

   In Excel, create columns for 'Year', 'Cash Flow', and 'Cumulative Cash Flow'.

   Year	Cash Flow ($)	Cumulative Cash Flow ($)
   Initial Investment	-150,000	-150,000
   1	40,000
   2	50,000
   3	60,000
   4	30,000
   5	20,000

   Let's assume the Initial Investment is in cell B2 (as a negative value), and annual cash flows are in B3:B7.

2. Calculate Cumulative Cash Flow:
   • In cell C2 (for the initial investment), enter =B2.
   • In cell C3 (for Year 1), enter =C2+B3. This adds the current year's cash flow to the previous cumulative total.
   • Drag the fill handle of cell C3 down to cell C7 to apply the formula to all subsequent years.

   Your table will now look something like this:

   Year	Cash Flow ($)	Cumulative Cash Flow ($)
   Initial Investment	-150,000	-150,000
   1	40,000	-110,000
   2	50,000	-60,000
   3	60,000	0
   4	30,000	30,000
   5	20,000	50,000

3. Identify the Payback Year:

   Look at the 'Cumulative Cash Flow' column. The payback period is achieved in the year where the cumulative cash flow turns positive (or zero, as in our example). In this case, it's Year 3, where the cumulative cash flow becomes $0.

   So, the payback period is 3 years.

   What if it wasn't exactly zero? Let's adjust the example slightly:

   Initial Investment: $150,000

   • Year 1: $40,000
   • Year 2: $50,000
   • Year 3: $55,000
   • Year 4: $30,000

   Year	Cash Flow ($)	Cumulative Cash Flow ($)
   Initial Investment	-150,000	-150,000
   1	40,000	-110,000
   2	50,000	-60,000
   3	55,000	-5,000
   4	30,000	25,000

   Here, the cumulative cash flow is still negative (-$5,000) at the end of Year 3, but turns positive ($25,000) at the end of Year 4. This means payback occurs sometime within Year 4.

4. Calculate the Fractional Part:

   To find the exact payback period, we use the following formula for the fractional part:

   Fractional Part = (Amount Needed to Recover at Start of Payback Year / Cash Flow During Payback Year)

   From our adjusted example:

   • Amount needed at the start of Year 4 (end of Year 3) is $5,000 (the absolute value of the negative cumulative cash flow).
   • Cash flow during Year 4 is $30,000.

   Fractional Part = $5,000 / $30,000 = 0.1667 years

   So, the total payback period is 3 years + 0.1667 years = 3.17 years (approximately).

   In Excel, if Year 3 cumulative is in C5 and Year 4 cash flow is in B6, you could calculate the fractional part as =ABS(C5)/B6.

Limitations of the Payback Period Method

While simple and useful, the payback period has significant drawbacks:

• Ignores Time Value of Money: It treats all cash flows equally, regardless of when they occur. A dollar received today is more valuable than a dollar received in five years, but the payback period doesn't account for this.
• Ignores Cash Flows After Payback: It disregards any cash flows generated after the initial investment has been recovered. A project might have a longer payback period but generate substantial profits later, which would be overlooked.
• Doesn't Measure Profitability: It's a measure of liquidity and risk, not ultimate profitability. A project with a short payback period might not be the most profitable overall.

When to Use Payback Period

Despite its limitations, the payback period is still a valuable tool in certain contexts:

• For companies facing severe liquidity constraints, a quick return on investment is critical.
• For projects with high uncertainty or rapidly changing technology, a shorter payback period reduces exposure to risk.
• As a preliminary screening tool to filter out projects that take too long to recoup their investment, before moving on to more sophisticated analyses like Net Present Value (NPV) or Internal Rate of Return (IRR).

Conclusion

Calculating the payback period in Excel, whether for even or uneven cash flows, is a fundamental skill for financial analysis and project evaluation. While it offers a quick glance at an investment's liquidity and risk, it's crucial to remember its limitations and ideally use it in conjunction with other capital budgeting techniques for a more comprehensive financial assessment.