Page 71 - Finanancial Management_Feb_Apr23

Using Excel to

calculate an investment

payback period

Excel MVP Liam Bastick describes a method to work out the time to recoup
an initial investment that allows for irregular cash inflows.

By Liam Bastick, FCMA, CGMA

n these uncertain times it is more profiles, and possible further outlays? an outflow followed by inflows. And,
important than ever to keep track Let’s consider the example in the yes, I have fallen for the latter trap — but
of your cash flow. Concentrating screenshot “Irregular Periodic Cash I shall return to that subject in a short
solely on profits may prove to be Inflows”. while.
I a fool’s game when cash rules the I have imagined some sort of To work out payback, I need two
proverbial roost. But to make money, infrastructure project with cash inflows things:
you have to spend money — so how and outflows on specific dates (eg, 1. A running (cumulative) total of the
soon do you get it back? they may have been stipulated by a overall cash flow.
That’s the topic for this FM article, contract). Other than making the start 2. An understanding of timing of the cash
where we look at the length of time it date 1 January, I don’t think anyone will flows.
takes to recoup initial investment(s) accuse me of creating a simple periodic Therefore, I add two computations
and get back into the black. How do you example. (all calculations are detailed in the
calculate the payback period in Excel This is what motivated me to write downloadable Excel file). (See also the
such that it will be versatile to account on this topic. All the solutions I ever see screenshot “Timing and Cumulative
for irregular periodicities, payment have regular, periodic cash flows with Cash Flow”.)

Irregular periodic cash inflows

Timing and cumulative cash flow

FM-MAGAZINE.COM April 2023 I FM MAGAZINE I 27