Calculating depreciation is easy, no matter which method you prefer, with the financial functions in Google Sheets. That means you can very easily plot a chart showing depreciation curves in Google Sheets. I mean you can use the financial functions to format the data for this chart.
SLN and DDB are the two most popular depreciation methods. But choosing one depends entirely on your accounting methods and also your business practices. If you want to get a good idea about the output of different depreciation calculations, plotting a chart will be a good idea.
You can follow this tutorial to plot a chart which shows different depreciation curves in Google Sheets. Along with the depreciation curves, I am also going to include a straight-line series in chart aka SLN.
The depreciation curves and the straight line will help you understand how the depreciation differs in each method. You can use my free template, which I have included at the end of this post, to calculate the depreciation.
If you don’t want to bear the expense of hiring an accountant to calculate the depreciation of your assets but want to claim your assets’ expenses, use Google Sheets depreciation functions. It will make your job ease. Otherwise, you may find it hard to calculate the depreciation manually.
How to Plot Depreciation Curves in Google Sheets
Google Sheets offers different functions to make your task simple. In the below chart I have used all the available depreciation functions, except the AMORLINC, to populate the series values.
The below chart (the one that we are going to prepare) doesn’t include the depreciation based on the French accounting system (AMORLINC). This is because in that the input values are slightly different from other methods. You can refer to my tutorial on AMORLINC for that chart.
To plot the depreciation curves in Google Sheets, I am using the functions SYD, DB, DDB, and VDB. In this DDB and VDB are almost the same. So you will see only one curve for these methods. For the straight line, I am going to use the SLN function.
Input Values to Plot the Depreciation Curve Chart
Depreciation is the decrease in the value of the assets. You can depreciate your car, building, laptop, printer, or any other tangible asset to allocate its cost over its useful life.
Purchase cost, salvage value, the period for which to calculate the depreciation (D3:D), and the useful life of the asset are the input values that you want to plot the chart.
The range C3:C contains the X-axis values for the chart. It has nothing to do with the formulas.
The ‘Life’ of the asset is 5 years (cell B4). Then why there are 6 periods in column D?
The 6th period is to only show the opening balance of the asset (that means the asset value after its useful life period). In most of the cases that value will be equal to the residual/salvage value.
Arrange the values as above. Don’t worry, I will leave the link to my sample Sheet at the end of this tutorial. You can copy that.
In order to create the depreciation curves in Google Sheets, you need to calculate the depreciation for each period (5 periods) and deduct it from the asset value.
SLN, SYD, DB, DDB, and VDB – All Types of Depreciation Calculations in Sheets
I have arranged the input values in A1:D as above for creating the depreciation curves in Google Sheets.
The balance columns E1:P contains the formulas for calculating the opening value of the asset, depreciation, and closing value of the asset in each period.
I will give you the formulas to calculate the depreciation for the entire period (5 years). I mean the formulas to be used in cell F3 (SLN), cell I3 (SYD), cell L3 (DB), and cell O3 (DDB/VDB). Before that see how to calculate the opening and closing values of the asset in each period after depreciation.
I am giving you the formulas (other than depreciation) used in the SLN in E3:G.
| E | F | G | |
| 1 | SLN | – | – |
| 2 | Opening Value | Depreciation | Closing Value |
| 3 | $85,000.00 | Depreciation Period 1 | E3-F3 |
| 4 | G3 | Depreciation Period 2 | E4-F4 |
| 5 | G4 | Depreciation Period 3 | E5-F5 |
| 6 | G5 | Depreciation Period 4 | E6-F6 |
| 7 | G6 | Depreciation Period 5 | E7-F7 |
| 8 | G7 | – | – |
Similar formulas used in SYD (H3:J), DB (K3:M), and DDB/VDB (N3:P). Please refer to my template/sheet (link included at the end of this page).
All Depreciation Formulas Required for the Curves and Straight Line
Enter this Formula # 1 (SLN) in cell F3 then copy to F4:F7.
=sln($E$3,$B$3,$B$4)Formula # 2 (SYD) in I3: Copy this formula to I4:I7.
=syd($H$3,$B$3,$B$4,D3)Formula # 3 (DB) in L3: Copy this formula to L4:L7.
=db($K$3,$B$3,$B$4,D3)The last Formula # 4 (DDB/VDB) to be added in cell O3. Do copy this formula to O4:O7.
=DDB($N$3,$B$3,$B$4,D3)Our depreciation table/schedule is ready, right? Actually, we can use this table for creating the depreciation graph in Google Sheets. But, you only need a few columns in this table to plot the chart.
You can use the column C or column D as X-axis labels. Other than this, for creating the depreciation curves and straight line, hide all the columns except column E, column H, column K and column N.
But here I am not hiding the columns. Instead, I am copying the values in the required columns to a new tab! Why so?
I would like to add series labels to the depreciation curves inside the chart area itself. For that, I need to format the data differently.
Related Reading: Add Legend Next to Series in Line or Column Chart in Google Sheets.
Data Formatting and Custom Series Labels for Depreciation Curves in Sheets
Nothing important here. Just copy the values in the required columns C, E, H, K, and N from the above table to a new tab as below.
Copying Data in the Required Columns to New Sheet
Adding Custom Seris Labels and Avoiding Clutter in the Chart
Since there are multiple depreciation curves and a depreciation straight line, it’s good to move the legends to the chart area.
Instead of moving legends to the inside of chart area, which is not possible, we are adding custom labels. That we can do by entering custom series labels for each series.
The columns C, E, G, and I are the columns for adding custom series labels to the depreciation curve in Google Sheets.
Why I have entered the series labels for each series in different rows?
When you see the finished chart, you can understand that. If you move these labels to the same row, it would cause clutter in the chart.
Creating Depreciation Curves Chart in Google Sheets
I am going to use the Smooth Line Chart in Google Sheets for creating the straight line as well as the depreciation curves.
Since we have formatted the data properly, we can make the chart within a few clicks. Here are the final steps.
- Select the data range A1:I.
- Go to Insert > Chart > Setup > Chart type > Smooth Line Chart.
- Go to Insert > Chart > Customise > Series > Apply to all Series. Under this enable “Data Labels” and choose the “Type” as “Custom”.
That’s all. We have created the depreciation curves in Google Sheets. You can easily identify each series since the labels are added.
The chart will tell you a whole lot about the depreciation of an asset using different depreciation methods. That’s all for now. Enjoy!
