On Arrays and Accumulated Depreciation

Posted on June 11, 2007

What could be easier, or more common, than calculating accumulated depreciation? Generally, in your spreadsheets you probably have each years’ depreciation expense listed in its own cell and all you do is add them up. Nothing wrong with that, but there is another way.

I like to read the forums over at AnandTech and help out whenever I can. Not long ago, a user asked about calculating accumulated depreciation using the DDB (double-declining balance) function. He needed to calculate the accumulated depreciation to date for an asset so that the book value could be calculated. My solution was quite simple, but it makes use of arrays and I don’t think most Excel users are aware of this functionality.

Set up the spreadsheet as shown below:

We’ve got a simple setup, and nowhere is each years’ depreciation expense listed. Instead, we’ll calculate the accumulated depreciation in B6 using the DDB function. First, note that the DDB function is defined as:

DDB(cost, salvage, life, period, factor)

Note that the current period is listed in B4, so I could calculate the depreciation for Year 3 (just that year) with:

=DDB(B1, B2, B3, B4)

(leave out the optional “factor” argument for standard double-declining balance). The answer is that the depreciation in Year 3 is $360. But that isn’t what we really want to know. Instead, we want to know how much the total depreciation has been over years 1, 2, and 3 (or some other ending period).

This is where the array comes in. In many functions, Excel will allow an array of values where it only asks for a single value. You can define the array as a comma-separated list contained in braces. In this case, to specify the “period” argument as an array, we would type:

=DDB(B1, B2, B3, {1,2,3})

If you press the Enter key at this point, you will get only the first period’s depreciation ($1,000). So it seems that this trick doesn’t work. However, try this: Select three adjacent cells, say A8:C8 and then click in the formula bar. Type in the formula as shown above, but don’t press Enter. Instead, press Ctrl+Shift+Enter and you will get three answers ($1000, $600, $360). That gives you the depreciation for each of the first three years in separate cells.

We’re not quite done yet. The goal is to get the accumulated depreciation for those three years using only one cell. We can do this by surrounding the array formula in a Sum function (select a single cell this time, say B6):

=Sum(DDB(B1, B2, B3, {1,2,3}))

and press Ctrl+Shift+Enter to insert the function. You will see that the answer is $1,960 as shown in the table above. The three answers from DDB get fed into the Sum function, resulting in our single answer.

Note that when you enter an array formula like this, Excel will display it in the formula bar like this:

{=Sum(DDB(B1, B2, B3, {1,2,3}))}

Do not type the outer braces. They are there only as a visual reminder that this is an array formula.

Pretty cool, huh? Sure, but we aren’t done yet. The whole purpose of using spreadsheet programs (as opposed to paper) is flexibility. Notice that the {1,2,3} is hard-coded, so it will only work for that time period. The primary purpose of B4 (Current Year) is to provide the number of the last period for which we want to calculate the accumulated depreciation. If I change B4 to 4, the answer in B6 will still be $1,960 (i.e., incorrect - it should be $2,000). I need some way to dynamically change the array of years for which I want to calculate the accumulated depreciation.

This is where the problem gets interesting. I need to dynamically generate an array of values from 1 to N, where N is the number specified in B4. To do this, I’m going to use two functions: Row and Indirect.

The Row function simply tells you what row a cell address refers to. For example, Row(A10) will return 10. If I create an array formula: =Row(A10:A13) I will get {10,11,12,13} as the answer. You can type that into the formula bar and then, without pressing Enter, highlight the formula and press F9. That will show you the result. So now I know how to generate an array from 1 to N. Just do something like, =Row(A1:A3). The only problem is that, again, the address is hard-coded. This is where Indirect comes into play.

Indirect is a great little under-used function. Basically, what it does it to take a cell address as a string and return a reference to that cell address. So, if I type =Indirect("B4") into a cell, the return value will be the value from cell B4. It is an indirect way to get to that value. If I changed the string, the return value would come from some other cell. I’m going to use the function a little bit differently, but that’s the idea.

Here’s my strategy: I want to generate an address of a range from A1:A(whatever). So, I’ll build up a string that I can feed to the Indirect function, and then feed the output from Indirect into the Row function.

To build the string, I’m going to take the value from B4 (the current year). A formula that will do this is: ="A1:A"&Text(B4,"#"). In the original spreadsheet (above) B4 = 3, so that will result in the string “A1:A3”. If I change B4 to 5, then the string will change to “A1:A5”. Now, if I type:

=Row(Indirect("A1:A"&Text(B4,"#"))

the output will be {1,2,3} or {1,2,3,4} or whatever, depending on the value in B4. Again, test this in the formula bar using F9. You can’t type that into a single cell and get an answer because it is an array formula.

So, here is the final formula for B6:

=Sum(DDB(B1, B2, B3, Row(Indirect("A1:A"&Text(B4,"#"))))

Take a look at the example spreadsheet and let me know what you think. I think this demonstrates a number of very useful Excel techniques.

Posted by on June 11, 2007 at 04:14 AM

Categories: Array Formulas Functions Financial Functions DDB Indirect