Page 73 - Finanancial Management_Feb_Apr23

wish to flex cell references. Whilst Some of you will feel
functions such as INDEX may offer
similar versatility, they require the full uncomfortable that the time
range to be known, whereas OFFSET
does not. Because position is stipulated
rather than sought, OFFSET frequently value of money has not been
calculates faster than INDEX; the reason
modelling academics is because it is a considered. But this is a minor
OFFSET is not well liked amongst

volatile function, which means it often
calculates when not needed (but not adjustment to the technique.
always). Quite frankly, this is not an
issue in most spreadsheets modelled.
Indeed, in one 250+ MB file, our
company noted that OFFSET calculated columns to the right of this cell, ie, cell the cash flow remains in deficit. But
formulas up to 600 times faster than P17, which is the first non-negative more on that anon.
INDEX. cumulative cash flow ($14,974 for the Now I have enough information to
date of 11 October 2025). calculate the payback period in days (see
Returning to payback Similarly, OFFSET($H$17,,$H$19-1) the screenshot “Calculating the Payback
Assuming we have calculated the returns the last negative cumulative cash Period in Days”).
“First Period Non-Negative” in cell flow seven [7] columns to the right of cell Here, in cell H23 I have constructed
H19 (see the screenshot “Calculating H17 in O17 ($(7,863) for the date of 31 the formula
Proportion of Period That Is Non- March 2025). Thus,
Negative”), the proportion of that =OFFSET($H$15,,$H$19-1)+(1-
period that would be positive (strictly OFFSET($H$17,,$H$19)- $H$21)*(OFFSET($H$15,,$H$19)-
speaking, it would be non-negative) OFFSET($H$17,,$H$19-1) OFFSET($H$15,,$H$19-1))
could be calculated as:
considers the increment in the You may already be coming to terms
=OFFSET($H$17,,$H$19)/ cumulative cash flow for the eighth with this formula now. The first part
(OFFSET($H$17,,$H$19)- period. Now, before everyone points
OFFSET($H$17,,$H$19-1)) out this is simply the value in cell P13 OFFSET($H$15,,$H$19-1)
(which could have been derived using
This really is not as bad as it first OFFSET($H$13,,$H$19)), I do realise utilises a similar technique to the one
looks. Essentially, it is almost the same this. I am writing it this way to make the described above by determining the total
calculation no less than three times. concept clearer. number of days up to and including the
Consider the calculation: Hence, seventh start date (ie, cell O15, which is
820 days).
OFFSET($H$17,,$H$19) =OFFSET($H$17,,$H$19)/
(OFFSET($H$17,,$H$19)- OFFSET($H$15,,$H$19)-
which is based upon cell H17 (see OFFSET($H$17,,$H$19-1)) OFFSET($H$15,,$H$19-1)
the screenshot “First Non-Negative
Cumulative Cash Flow”). reflects the proportion of the cash flow thus calculates the number of days
This is the cell immediately to the for that period that puts the cumulative between the seventh and eighth dates
left of the first cumulative cash flow. cash flow into surplus. It also means (ie, P15 – O15 = 194 days). This is
Therefore, OFFSET($H$17,,$H$19) that 100% less this proportion would analogous to the calculation for the cash
references the cell H19 — eight [8] represent the proportion of the period for the eighth period constructed earlier.

First non-negative cumulative cash flow

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