Payback Period in Excel

Payback period is the time it takes for the cash which has been invested at the start of the project to be returned by cash generated from the project.  So how long does it take for me to receive my initial investment back in full.  Let’s use a simple example to explain. You are thinking of investing $1000 in an office coffee machine. The machine will generate $250 each year for its 10 year useful life. The equation for Payback Period depends whether the cash inflows are the same or uneven. If they are the same (even) then the formula is as follows;

Payback Period=Initial Investment ÷ Annual Cash InFlows

Payback Period=$1000 ÷ $250=4.0 Years

When the cash flows are uneven, we need to calculate the cumulative net cash flow for each period and then use the following formula for payback period:

Payback Period (PP)=Pi +B/C

In the above formula;

Pi is the last period with a negative cumulative cash flow;

B is the value of the cumulative cash flow at the end of the period Pi

C is the total cash flow during the period after Pi

If we use the above example with uneven cash inflows year 0=-1000, Y1=  $200 Y2=$440 Y3=$300 Yr 4=$200.  The following table shows an example using colour coding to denote the figures to use in the calculation. Both of the above situations are applied in the following example.

The decision rule is to accept the project only if its payback period is less than the target payback period.

Advantages - The payback period method is simple to calculate, so it give a 'gut feel' for timelines around the return of the initial investment.

Disadvantages - While simple Payback is easy to compute, it falls short as it does not factor in the time value of money, inflation, project lifetime or operation, and maintenance costs.  There are other methods used in Capital budgeting such as Net Present Value, Internal Rate of Return and Discounted Cash Flow which are currently more in vogue.