Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate the payback period in Excel and Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=MIN(IF(CUMULATIVE_SUM(B2:B7)>=A2, ROW(B2:B7)-ROW(B2)+1))Google Sheets
=MIN(IF(ARRAYFORMULA(CUMULATIVE_SUM(B2:B7))>=A2, ROW(B2:B7)-ROW(B2)+1))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your initial investment in cell A2.
- 2List your annual cash flows in cells B2 through B7.
- 3In cell C2, enter the formula to calculate the cumulative sum of cash flows.
- 4In cell D2, use the MIN and IF functions to find the payback period.
Tips
- Ensure your cash flow data starts from the first year after the investment.
- Adjust the range in the formula to match the number of years you have data for.
- Be aware that this method does not account for the time value of money.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the payback period?
The payback period is the time it takes for an investment to generate enough cash inflows to recover the initial cost of the investment.
Why might the payback period be misleading?
It ignores cash flows that occur after the payback period and does not consider the time value of money.
Can I use this method for long-term investments?
While possible, the payback period is generally more useful for shorter-term investments due to its simplicity and lack of consideration for long-term cash flows and the time value of money.