Duncan's Diurnal Diatribe: Depreciation and Dynamic Array Functions

Introduction

There is a video to accompany this page and its purpose is to demonstrate how to use the Dynamic Array Function SEQUENCE() to automate the expansion and contraction of a range of data. I have written about Dynamic Array Functions before.

SEQUENCE()

The SEQUENCE() Function just creates lists of numbers for us: that's all. For example, if I enter =SEQUENCE(10) in cell A1 we will see A1=1, A2=2, A3=3 ... A10=10. Change that to =SEQUENCE(,10) and this happens: A1=1, B1=2, C1=3 ... J1=10

The model I have built looks like this, overall:

And my SEQUENCE() function in cell D7 has grown a bit ..

D7 = =IFERROR(SEQUENCE(IF(OR(B8<B10,B8>B11),0,B8),,1,1),"Min "&B10&" years, Max "&B11&" years")

Not only is that formula creating the list of numbers from 1 to whatever is in cell B8 but it stops us from going outside the acceptable range of useful life from a minimum of 2 years to a maximum of whatever is in cell B11. By wrapping the IFERROR() function around SEQUENCE(), I created an error message for the situation in which someone tries to create a useful life range outside the limits.

The one formula in cell D7, that spills down to cell D16 in the above screenshot, is the heart of this model and until Dynamic Array Functions came along, the only ways to achieve what you see here is by programming sometimes complex dynamic formulas for the worksheet and for the graph or to create a macro. We don't need to do either of those things now!

Everything Else

All other formulas are simple for upper basic Excel users and beyond. The graph is a scattergraph and since we are dealing with the reducing balance method of depreciation
, we need a calculation for the Depreciation Rate ...