# An Excel approach to calculate depreciation

The OFFSET function can assist accountants with creating a spreadsheet-based schedule of depreciation.In last month’s column, I explained the **OFFSET** function, which employs the following syntax:

**OFFSET(Reference,Rows,Columns,[Height],[Width])**

The arguments in square brackets (**Height** and **Width**) can be omitted from the formula, but they will prove to be useful in this article.

As I explained last time, **OFFSET **will select a reference **Rows** number of rows down (**-Rows** would be **Rows** number of rows up) and **Columns** number of rows to the right (**-Columns** would be **Columns** number of rows to the left) of the **Reference**. For example, consider the following grid:

**OFFSET(A1,2,3)** would take us two rows down and three columns across to cell **D3**. Therefore, **OFFSET(A1,2,3)** = 16, as indicated below*.*

**OFFSET(D4,-1,-2)** would take us one row up and two rows to the left to cell **B3**. Therefore, **OFFSET(D4,-1,-2)** = 14, as indicated below.

For this second discussion, let me extend the formula to **OFFSET(D4,-1,-2,-2,3)**. It would again take us to cell **B3**, but then we would select a range based on the **Height** and **Width** parameters. The **Height** would be two rows going up the sheet, with row 4 as the base (ie, rows 2 and 3), and the **Width** would be three columns going from left to right, with column **B** as the base (ie, columns **B**, **C**, and **D**).

Hence **OFFSET(D4,-1,-2,-2,3)** would select the range **B2:D3**, as shown below*.*

Note that **OFFSET(D4,-1,-2,-2,3)** = **#VALUE!** since Excel cannot display a matrix in one cell, but it does recognise it. However, if after typing in **OFFSET(D4,-1,-2,-2,3)**, we press **Ctrl+Shift+Enter**, we turn the formula into an array formula: **{OFFSET(D4,-1,-2,-2,3)}**. (Do not type the braces in; they will appear automatically as part of the Excel syntax.) This gives a value of 8, which is the value in the top left-hand corner of the matrix, *but Excel is storing more than just that*. This can be seen as follows:

**SUM(OFFSET(D4,-1,-2,-2,3))**= 72 (ie,**SUM(B2:D3)**)**AVERAGE(OFFSET(D4,-1,-2,-2,3))**= 12 (ie,**AVERAGE(B2:D3)**).

I am going to use these ideas to build on the scenario table example I demonstrated last time. This time, I am going to consider a calculation many accountants have to perform regularly: depreciation of a capital expenditure.

**Example**

Let’s imagine I have been charged with creating a depreciation schedule in an Excel spreadsheet with the following assumptions:

You can follow this example in **this Excel file**. All cells in yellow are assumptions that may be changed.

I wish to build a depreciation grid to calculate my total amortised cost as follows:

My first challenge is to link the assumptions in cells **J21:O21** to the grey cells **H30:H35**, converting row assumptions into column data. Converting from rows to columns (or vice versa) is known as **transposing**. There are several ways you can do it, but only one way I truly recommend.

**Method 1: Cut and paste a formula**

For the purposes of simplification, I am going to assume that we are looking to transpose data from going across a row to going down a column (the concepts are similar if columns are transposed into rows).

This method is very simple. First, create a formula that links to the data to be transposed, say, in the row beneath:

Second, once the formulas have all been created, cut and paste each formula individually into its appropriate position on the spreadsheet:

This is a very simple method but certainly would be an ill-advised approach if you need to transpose 1,000 data points, for example.

However, simplicity is often a highly underrated attribute in modelling. If you have only a few data items and you require the original inputs to remain (in our example, row 12, above), this method can often be deemed “simplest bestest”.

**Method 2: Copy and paste special, transpose
**

Another very simple approach is to copy and paste special as follows:

In this instance, simply highlight the data and copy the range in the usual way (eg, **Ctrl+C**). Next, simply select the first cell (ie, the top left-hand corner) of the intended range and **Paste Special**, **Transpose** (**Alt+E+S+E+Enter**). As can be plainly seen from the illustration (above), the formatting as well as the content will be transposed.

This is an ideal approach for copying and transposing data from one source to another where links are not required. Aside from the inherited formatting, the main disadvantage here is that depending upon the nature of the source data and how it is copied, updates in the original data will not flow through to the destination range.

If the data need to be linked to the source, then this approach is probably inappropriate. However, if inherited formatting is all that is putting you off, make the appropriate adjustments to the **Paste Special** dialog box before pressing **OK**/**Enter,** eg, set the dialog box as follows to copy only the formulas before transposing:

**Method 3: Using the TRANSPOSE function**

On first glance, this looks like the “best” method. Once you discover there is a **TRANSPOSE** function, you think life is simple and your problems (well, your modelling problems anyway!) are over. Unfortunately, this function is not without its limitations.

Consider the following example:

Here, the intention is to transpose the values in cells **G12:K12** into the range **F16:F20**. To do this, simply highlight cells **F16:F20**, and then type in the following formula:

**=TRANSPOSE(G12:K12)**

but rather than press **Enter**, press **Ctrl+Shift+Enter** to enter the formula as an array formula.

This method is very simple, as long as you ensure that the destination range contains precisely the same number of cells in the column as there are cells in the source row. If you change the source data, the outputs will update accordingly, too.

So, what’s the problem?

Array formulas can consume memory exponentially, which in turn can soon prevent Excel from calculating correctly, especially if you are working on a Windows 32-bit operating system, which is the platform most businesses employ.

Further, if you wanted to insert additional rows between rows 16 and 20 (eg, to extend the range), you will find that this is not possible:

Interestingly, if you insert columns *between* **G** and **K** in the illustration above, this is possible, but the array formulas will not act like other Excel calculations: Cell references in the **TRANSPOSE** formulas will not update (so the references will always link to what is in cells **G12:K12** in our example). However, if you insert columns before column **G**, the formula will update. This can be confusing.

Therefore, **TRANSPOSE** is useful where you wish to protect the destination range’s structure, but it can be seen as inflexible and inefficient, particularly with larger Excel files, slowing calculation times unnecessarily.

**Method 4: OFFSET approach**

Used with the **ROWS** function (which simply counts the number of rows in a specified range), transpositions may be performed quickly and effectively using **OFFSET**:

In this example, the following formula has been typed into cell **F16**:

**=OFFSET($F$12,,ROWS($E$16:$E16))**

As the formula is copied down, the number increases in the **Columns **argument of **OFFSET** as the number of rows increases. This is a neat trick for transposing without using array formulas and can be seen as a good “general” solution, being quite flexible. The disadvantages here are twofold:

- Often modellers make mistakes in the absolute and semi-absolute references required to make this formula calculate correctly. This is easily overcome with practice;
*and* - The formula can look unnecessarily complex to the inexperienced or uninitiated. Many end users (and modellers for that matter) are unfamiliar with the
**OFFSET**function in particular. It may be worthwhile to educate them accordingly.

**Returning to the example**

It is this last method I have employed in my depreciation example:

The formula in cell **H30** (highlighted) is

**=OFFSET(BC_Capex,,$E30)**

**BC_Capex** is cell **I21**. The prefix “**BC**” in this range name stands for “base cell” and is used to acknowledge the fact that **OFFSET** is nonauditable, as explained last time. This cell is styled speckled blue to emphasise that this cell must deliberately remain blank and not be deleted. I use this “empty cell” style regularly in modelling.

The reference **$E30** points to the label “Year 1”, which is actually the number 1 formatted to look like text. This dispenses with the need to use the **ROWS **function explained earlier.

Now that I have successfully transposed the assumptions, I can calculate the depreciation grid:

Assuming depreciation is to be calculated on a straight-line basis (ie,* *the capital expenditure is apportioned equally across all periods it will provide economic benefit), the depreciation may be calculated across the grid using the formula in cell **J30**:

**=IF(J$28>=$E30,MIN($H30-SUM($I30:I30),$H30*Depn_Rate),)**

The **IF **statement checks that depreciation does not commence before the capital expenditure has occurred. The **MIN** function takes a proportion of the capital expenditure but ensures that the cumulative total (“accumulated depreciation”) does not exceed the amount spent.

It is a simple method, recognised by many financial analysts and accountants. The main problem with this method concerns how many calculations are required in the grid. For example, if you built a 20-year monthly model, you would need 240 rows by 240 columns in the grid — a total of 57,600 calculations!

This is where **OFFSET **can come to the rescue…

In this example, row 44 contains the formula **=MIN(J42,Economic_Life)**, which restricts the period counter to not exceed the **Economic_Life** (here, four years). With row 48 calculating each period’s representative depreciation, the total depreciation charge in row 50 becomes trivial. For example, the formula in cell **J50 **is:

**=SUM(OFFSET(J$48,,,,-J44))**

This formula simply refers to the corresponding periodic charge in row 48 and then sums a range of cells with a width defined by the value in cell J44. The negative sign is a directional indicator: It is counting a number of columns going from right to left, rather than left to right. This means that:

- In Year 1, depreciation is simply given by cell
**J48**. - In Year 2, the depreciation is the sum of the first two harges in row 48 (namely, cells
**J48:K48**). - In Year 3, the depreciation is the sum of the first three charges in row 48 (namely, cells
**J48:L48**). - In Year 4, the depreciation is the sum of the first four charges in row 48 (namely, cells
**J48:M48**). - In Year 5, the depreciation is the sum of the last four charges in row 48 (namely, cells
**K48:N48**). Note that the first period is now excluded (the width is -4) as the first period’s capital expenditure is now fully depreciated.

A simple comparison of the two approaches to depreciation will determine that the two methods give the same result. The **OFFSET** approach may not be as transparent upon first glance, but it does reduce the number of formulas required.

When I model depreciation in reality, I usually model the first asset class *both* ways so that end users can see they give the same result. After that, I adopt the **OFFSET** approach exclusively, as end users now trust this less familiar approach.

I recommend if you are not familiar with **OFFSET**, you should do the same. Play with the function for a while until you trust it and realise how useful it can be in your spreadsheets going forward.

*— Liam Bastick**, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *An Introduction to Financial Modelling*. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an *FM* magazine senior editor, at Jeff.Drew@aicpa-cima.com.*