I had a case in a spreadsheet where I needed to test the cash flow of payments made against future loans.
However, when it came to scenario testing the cash flow we had the ask and answer the question on the viability of the organisations cash flow depending on the loan repayment schedule.
Would we be able to repay our loans back quicker? What would our cash flow look like if repayments were 10 years, or 15 years, or 20 years’ terms?
To enable the spreadsheet to calculate this on the fly, I had to find a way whereby I could set a field value to represent the number of years the loan was to be repaid and for the single row in the report reflecting the repayment schedule to show those repayments.
For those familiar with a cash flow statement you will know there’s generally a section within the Financial Activities to detail any financial repayments made, ours was labelled Principal Repayments as shown in the basic table below of the first few columns (as we had to perform a 20year projection there were more columns than this):
Rows  A  B  C 

1  Cash Flow Report  Year 1  Year 2 
28  Assumptions area  …  … 
9  Operating Activities  
10  Adjustments to Income Statement  $B$10  $C$10 
1119  etc …  …  … 
20  Total Operating Activities  =sum($B$10:$B$19)  =sum($C$10:$C$19) 
21  
22  Investing Activities  
23  CapEx Purchases  $B$23  $C$23 
2431  etc …  …  … 
32  Total Investing Activities  =sum($B$23:$B$31)  =sum($C$23:$C$31) 
33  
34  Financing Activities  
35  New Loans  $B$35  $C$35 
36  Capital Grant Receipts  $B$36  $C$36 
37  Principal Repayments  $B$37  $C$37 
38  Total Financing Activities  =sum($B$35:$B$37)  =sum($C$35:$C$37) 
39  
40  Total Net Cash Flow  =sum($B$20, $B$33, $B$38)  =sum($C$20, $C$33, $C$38) 
Now the issue at hand was that the New Loans row was an input row. Management wanted to insert how much they were willing to borrow for each project, but they wanted to see the impact on the Total Net Cash Flow which was the sum of the Total Operating Activities plus the Total Investing Activities plus the Total Financing Activities.
So with an input field on the New Loans row, and the output field on another row Principal Repayments all I needed to do was one more input cell representing the Loan Terms (in Years).
The cell I used as the input for the loan terms is defined in cell $B$50
.
So here’s how the Principal Repayments row ended up:
$B$37 = sum(index($B$35:B$35,0,MAX(COLUMN(B$35)COLUMN($B$35)$B$50+1,1)):B$35)/$B$50
So let’s break this formula down, starting in the heart:
MAX(COLUMN(B$35)COLUMN($B$35)$B$50+1, 1)
this formula helps to determine the column to start our range capture.
Here’s what we would get if the formula was copied across different cells:
$E$35 = MAX(COLUMN(E$35)COLUMN($B$35)$B$50+1, 1)
Which for each reference and function call results in:
COLUMN(E$35) = 5
COLUMN($B$35) = 2
$B$35 = 10
MAX(5  2  10 + 1, 1) = MAX(6, 1) = 1
For the cell $Z$35
which would contain this portion of the formula as MAX(COLUMN(Z$35)COLUMN($B$35)$B$50+1, 1)
has the following results:
COLUMN(Z$35) = 26
COLUMN($B$35) = 2
$B$50 = 10
MAX(26  2  10 + 1, 1) = MAX(15, 1) = 15

index($B$35:B$35, 0, ...)
the nifty thing about theindex
formula is that it returns a value or the reference to a value. The parameters of theindex
function are:INDEX(reference, [row_offset], [column_offset])
and as we don’t want to change therow_offset
this is represented as0
, but we do want to change thecolumn_offset
and this is where theMAX
formula helped us. From the value achieved from theMAX
formula we then receive a reference. 
sum(index(...):B$35)
as we’ve captured the cell reference from theindex
function we can add the range notation:
and have thesum
value add all values from that cell to the current cell we are in. =sum(...)/$B$50
lastly as we have added up all the new loans in thesum
formula we then divide the total New Loans amount by the loan term.
So if we had this cash flow sheet spread out of 25 time periods out, by the time we got to the formula in $Z$37
it would look like this:
$Z$37 = sum(index($B$35:Z$35,0,MAX(COLUMN(Z$35)COLUMN($B$35)$B$50+1,1)):Z$35)/$B$50
Thereby only getting us the last $B$50
(loan term) periods and dividing that amount by the loan term.
INDEX
formula is a nifty little formula to learn!