Asset Depreciation Calculation - Straight Line Method

Transact-SQL
1

Hi,

I have Asset table.

CREATE TABLE [dbo].[Asset](
	[item] [nvarchar](50) NULL,
	[bought_date] [date] NULL,
	[price] [numeric](18, 2) NULL,
	[AssetLife] [numeric](18, 0) NULL,
	[AssetCurrentDate] [date] NULL
) ON [PRIMARY]

GO

and the data is give below.

INSERT INTO [Accounts].[dbo].[Asset]
           ([item]
           ,[bought_date]
           ,[price]
           ,[AssetLife]
)
     VALUES
           ('x'
           ,'2020-01-01'
           ,11000
           ,8),
           
           ('y'
           ,'2020-01-01'
           ,8000
           ,5)

Looking for query for below result.

Below is the Formual

  1. Depreciation Expense= Book Value/Asset Life.
  2. Accumulated Dep.=Depreciation expense
  3. Book Valve Year End=Asset Value-Accumulated Dep.
Asset Years Book Value
Year Start		 Depreciation
Expense		 Accumulated
Depreciation		 Book Value
Year End
X 2020 11,000 1375 1,375 9,625
X 2021 9,625 1375 2,750 8,250
X 2022 8,250 1375 4,125 6,875
X 2023 6,875 1375 5,500 5,500
X 2024 5,500 1375 6,875 4,125
X 2025 4,125 1375 8,250 2,750
X 2026 2,750 1375 9,625 1,375
X 2027 1,375 1375 11,000 -

Regards,
Basit.

2

You can use the infamous tally table to handle this. The tally table table function is described here ( have modified it to receive a Min and Max parameters to set the range (Create a Tally Function (fnTally) – SQLServerCentral)

;with cte as (
Select item, AssetLife, Year(bought_date) as Years,
Cast(price as Numeric(10,2)) as Price, cast(price/AssetLife as Numeric(10,2)) as DepreciationExpense,
Cast(price - price/AssetLife as numeric(10,2)) as BookValueYearEnd
from #Asset)

select c.item, c.Years + N - 1 as Years
,cast(c.price - (c.price/c.AssetLife * (N - 1)) as Numeric(10,2)) as BookValueYearStart
,Cast(c.price as Numeric(10,2)) as Price
,Cast(c.price/c.AssetLife as Numeric(10,2)) as DepreciationExpense
,cast(c.price - (c.price/c.AssetLife * N) as Numeric(10,2)) as BookValueYearEnd
from cte c
cross apply ref.Tally (1, AssetLife) N

3

Many Many Thanks, how to add Accumulated Depreciation?

regards,
Basit.

4

Change to this

,Cast(c.price/c.AssetLife * N as Numeric(10,2)) as DepreciationExpense

5

Many Many Thanks.

6

Many Thanks, what if, if the asset calculate by percentage wise.

CREATE TABLE [dbo].[Asset](
	[item] [nvarchar](50) NULL,
	[bought_date] [date] NULL,
	[price] [numeric](18, 2) NULL,
	[AssetLife] [numeric](18, 0) NULL,
	[end_date] [date] NULL,
	[AssetCurrentDate] [date] NULL,
	[AssetDedPercent] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

INSERT INTO [Accounts].[dbo].[Asset]
           ([item]
           ,[bought_date]
           ,[price]
           ,[AssetLife]
           ,[end_date]
           ,[AssetCurrentDate]
           ,[AssetDedPercent])
     VALUES
           ('x',
           '2019-01-01'
           ,'4000000.00'
           ,'10'
           ,null
           ,null
           ,20)
GO

The result should be

Dep.Exp YTD. Dep
Year BookValue(A) Rate(B) C=A*B D=A-C
2019 4,000,000 20% 800,000 3,200,000
2020 3,200,000 20% 640,000 2,560,000
2021 2,560,000 20% 512,000 2,048,000
2022 2,048,000 20% 409,600 1,638,400
2023 1,638,400 20% 327,680 1,310,720
2024 1,310,720 20% 262,144 1,048,576
2025 1,048,576 20% 209,715 838,861
2026 838,861 20% 167,772 671,089
2027 671,089 20% 134,218 536,871
2028 536,871 20% 107,374 429,497

I was trying below.

;with cte as (
Select item, AssetLife, Year(bought_date) as Years,
Cast(price as Numeric(10,2)) as Price, cast(price/AssetLife as Numeric(10,2)) as DepreciationExpense,
Cast(price - price*AssetDedPercent as numeric(10,2)) as BookValueYearEnd,Cast(AssetDedPercent as Numeric(10,2)) as AssetDedPercent
from Asset)

select c.item, c.Years + N - 1 as Years
,cast(c.price - (c.price*c.AssetDedPercent/100 * (N - 1)) as Numeric(10,2)) as [BookValue(A)]
,Cast(c.AssetDedPercent as Numeric(10,2)) as AssetDedPercent
,cast((c.price*c.AssetDedPercent/100 * (N)) as Numeric(10,2)) as [Dep.Exp C=A*B]
,cast(c.price - (c.price*c.AssetDedPercent/100 * N) as Numeric(10,2)) as [Dep.Exp C=A*B]

from cte c
cross apply [dbo].[fnTally] (1, AssetLife) N

can you please check, many thanks once again.

regards,
Basit.

7

Try this method:

;WITH cte AS
(
    SELECT
        item, AssetLife, 
        YEAR(bought_date) AS Years,
        CAST(price AS Numeric(10, 2)) AS Price, 
        CAST(price / AssetLife AS Numeric(10, 2)) AS DepreciationExpense,
        CAST(price - price * AssetDedPercent AS numeric(10, 2)) AS BookValueYearEnd,
        CAST(AssetDedPercent AS Numeric(10, 2)) AS AssetDedPercent
    FROM
        Asset
)
SELECT
    c.item, c.Years + N - 1 AS Years,
    ROUND(c.price * POWER((1 - c.AssetDedPercent / 100), N - 1), 2) AS [BookValue(A)],
    CAST(c.AssetDedPercent AS Numeric(10, 2)) AS AssetDedPercent,
    ROUND(c.price * POWER((1 - c.AssetDedPercent / 100), N - 1) * c.AssetDedPercent / 100, 2) AS [Dep.Exp C=A*B],
    ROUND(c.price * POWER((1 - c.AssetDedPercent / 100), N ), 2) AS [Dep.Exp C=A*B]
FROM
    cte c
CROSS APPLY
    [dbo].[fnTally] (1, AssetLife) N