Asset Depreciation Calculation - Percenatage wise

Transact-SQL
1

Hi,

I have below tavle structure

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

many thanks once again.

regards,
Basit.

2

Since you have not specified what dbo.fnTally(arg1, arg2) does, I am not able to discern how you are attempting to solve it.

There are three possibilities that I can think of to solve this problem.

  1. Recursive CTE. This will work, but can be slow.

  2. Use log to find the cumulative product. It would be something like

    EXP(SUM(LOG(1-Rate))OVER (ORDER BY Year))*InitialBookValue.

    This is efficient, but you should take care to handle edge cases (i.e., depreciation rate of 100% or higher)

  3. Use a while loop. This will be slow, but in some cases it can be faster than the CTE.

If I had to do this, I would use #2 together with a Numbers table.