How to Calculate Payback on Excel
Businesses track the amount of time it takes to pay back expenditures for a project by calculating what is called the "payback period". This calculation takes into account cash inflows and outflows accumulated for the duration of the given project. While the payback period can be calculated manually, Microsoft Excel offers an easy method for updating this information automatically with a payback formula. Inflows and outflows can be updated and the calculation can be amended automatically with the payback formula.
Open a new Microsoft Excel worksheet and type "Initial Investment" in cell A1. Put the cost of the project in cell B1. For example, assume a project costs $500.
Type "Annual Cash Inflows" in cell A2. Put your estimated cash inflows each year in cell B2. In the example, assume you receive $60 a year from the project.
Type "Payback" in cell A3.
Type "=B1/B2" in cell B3. In the example, B3 will say 8.333333333. The project will take about eight years and three months to cover its costs.