 # Asset Depreciation Calculation - Percenatage wise

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.

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.