How to Calculate a Payback Period in Excel

Find how long a gym’s LED lighting upgrade takes to pay for itself when yearly savings grow over time.

You will learn to

• Estimate a simple payback period
• Count the full years before payback
• Interpolate the exact payback point

How it works

The payback period is how long an investment takes to earn back its cost. With equal yearly savings it is just cost ÷ annual savings. With uneven savings, build a running (cumulative) total and find where it crosses zero.

COUNTIF(cumulative,"<0") counts the full years still under water. Add the fraction of the next year needed: the remaining shortfall ÷ that year’s cash flow. INDEX picks both numbers out of their columns, so the formula updates when the savings change.

=COUNTIF(C3:C8,"<0")