OFFSET tutorial workbook
Excel binary workbook (.xlsb). A DSRA target balance and straight line depreciation, both calculated with SUM(OFFSET()).
Enter your details and we will email you the workbook.
Loading the form…
The form did not load. Send us an enquiry and we will email you the workbook.
OFFSET() returns a cell, or a range of cells, a set number of rows and columns away from a reference cell. This tutorial covers its most common uses in financial models and the mistakes people often make with it.
How OFFSET() works
The syntax is:
OFFSET(reference, rows, columns, [height], [width])
The parts in square brackets are optional. Excel’s help file describes each one in detail.
If rows or columns is left blank, Excel uses zero, so OFFSET(C38, , 1) reads as OFFSET(C38, 0, 1). Height and width can be left out altogether, and the result is then the same size as the reference.
If height and width are given, neither can be zero, or the formula returns a #REF error.
The four examples below show what each part does.
Example 1: the basic mechanics
OFFSET(D10, 1, 2) returns the value in F11, which is 7: the cell one row below and two columns to the right of D10.
Example 2: moving up and left with negative values
OFFSET(G12, -2, -2) returns the value in E10, which is 2: the cell two rows above and two columns to the left of G12.
Example 3: selecting an area with height and width
OFFSET(F12, , , -2, -3) returns the range D11:F12, two rows by three columns. The reference cell F12 is part of that range.
Example 4: combining every part
OFFSET(D10, 1, 1, 2, 3) returns the range E11:G12. Excel first finds OFFSET(D10, 1, 1), which is E11, one row below and one column to the right of D10. It then applies OFFSET(E11, , , 2, 3).
Learn this in a room
Best Practice Project Finance Modelling
Build and sensitise robust cashflow models from scratch, faster in Excel. Hands-on, practitioner-led, in person or online. See dates and book your place.
Common mistakes with OFFSET()
- Tracing an OFFSET() formula shows only the reference cell. Tracing the precedents of OFFSET(D10, 1, 1, 2, 3) returns D10, not the range E11:G12
- Excel leaves the reference cell out when it counts rows and columns, but includes it when it counts height and width. The difference is confusing and needs great care
- OFFSET() is hard to grasp at a glance, which can reduce users’ confidence in a model
Combining OFFSET() with other functions
Because OFFSET() returns a cell or a range, it combines easily with SUM(), MIN(), MAX() and AVERAGE(). SUM(OFFSET()) adds up whatever range OFFSET() returns.
In example 4, SUM(OFFSET(D10, 1, 1, 2, 3)) is the same as SUM(E11:G12), which is 54 (6 + 7 + 8 + 10 + 11 + 12). AVERAGE(OFFSET(D10, 1, 1, 2, 3)) is the same as AVERAGE(E11:G12).
Common uses for OFFSET()
A forward-looking DSRA target balance
The target balance of a debt service reserve account (DSRA) is usually the sum of the debt service due over the coming periods. OFFSET() makes that target balance dynamic, as Figure 5 shows.
Here the target is the sum of the next two quarters’ debt service, as set in H24. Each column is one quarter, so the target adds the debt service in the next two columns: SUM(OFFSET(I23, 0, 0, 1, $H24)), which is SUM(OFFSET(I23, 0, 0, 1, 2)) or SUM(I23:J23).
The user can change the lookforward period in H24 without touching the formula. Our MMRA tutorial uses the same SUM(OFFSET()) approach for a major maintenance reserve target balance.
Straight line depreciation with OFFSET()
Where maintenance in operations is capitalised and depreciated on a straight line, only additions within the depreciable life should count, as Figure 6 shows.
Here the depreciable life is 20 quarters, or five years, so 5% (1/20) is depreciated each quarter. The lookback periods (row 37) are the lesser of 20 quarters and the number of additions (row 40) up to the current period.
Because the formula OFFSET(I40, 0, 0, 1, -J37) uses height and width, an IF() statement is needed to avoid an error when the lookback period is zero. SUM(OFFSET(I40, 0, 0, 1, -J37)) is the same as SUM(OFFSET(I40, 0, 0, 1, -2)) or SUM(H40:I40), the sum of the additions in the two quarters up to the current one. The total is then multiplied by the depreciation rate.
OFFSET() questions
What does the OFFSET() function do in Excel?
It returns a cell or range a set number of rows and columns away from a reference cell. The optional height and width make it return a range instead of one cell.
Why is OFFSET() sometimes seen as risky in a financial model?
Tracing an OFFSET() formula shows only its reference cell, not the range it returns, which can hide what the formula depends on. It is also hard to read at a glance, which can reduce a reviewer’s confidence in the model.
Why do rows and columns behave differently from height and width?
Excel leaves the reference cell out when it counts rows and columns but includes it when it counts height and width. This difference is a common source of errors where a range is one cell too long or too short.
What is OFFSET() usually combined with in project finance models?
SUM(), AVERAGE(), MIN() and MAX(), which work directly on the range OFFSET() returns. A dynamic DSRA or major maintenance reserve target balance and straight line depreciation over a rolling lookback period are both common uses of SUM(OFFSET()).