Generalized Method of Determining the Payback Period for both Conventional and Non-conventional Cash Flows: Ready-to-Use Excel Formulas and UDF

Abstract

The paper presents a generalized algorithm of determining the payback period for either a conventional or a non-conventional cash flow of an investment project. A non-conventional cash flow may have more than one payback periods, if an investor makes additional investments during the operating phase of the project. I give numeric examples and explain in detail the calculation of the payback period with Excel formulas, as well as with Excel user-defined function written in VBA. In conclusion, I give some thoughts on why the payback period can be a useful performance measure in capital budgeting in spite of the criticisms against it in academic literature on the ground that it is not compatible with the NPV criterion.

Keywords: payback period, investment project, capital budgeting, nonconventional cash flow, financial modeling, Excel, VBA, modified cumulative cash flow

JEL Classification: M10, M40, M41, G31