Page 74 - Finanancial Management_Feb_Apr23

Calculating the payback period in days

Calculation using cumulative discounted cash flow

This duration is then multiplied by Cumulative Discounted Cash Flow”).
1-$H$21 to denote the duration of the The rest of the approach then ensues.
period the cumulative cash flow remains For those who feel a little nervous
negative. Therefore, prorating periods linearly in this
scenario, I completely understand —
=OFFSET($H$15,,$H$19-1)+(1- especially if the duration between dates
$H$21)*(OFFSET($H$15,,$H$19)- is excessive. However, this is what is
OFFSET($H$15,,$H$19-1)) done in practice, and sometimes it is
best to follow in this direction. n
adds this proportion to the total number
of days up to and including the last date.
This is the payback period in days. I can
divide this figure by the number of days Liam Bastick, FCMA, CGMA, FCA,
in a year to present this duration in years is director of SumProduct, a global
if I wish (check out the example Excel consultancy specialising in Excel
file for this final step). training. He is also an Excel MVP
(as appointed by Microsoft) and
Word to the wise author of Introduction to Financial
Some of you will be noting the above Modelling and Continuing Financial
but feel uncomfortable that the time Modelling. Send ideas for future
value of money has not been considered. Excel-related articles to him at
But this is a minor adjustment to the liam.bastick@sumproduct.com.
above technique. All you do is calculate To comment on this article or to
the present values of the cash flows suggest an idea for another article,
first and then total these to construct contact Oliver Rowe at Oliver.Rowe@
the cumulative discounted cash flow aicpa-cima.com.
(see the screenshot “Calculation Using