Asset Depreciation - Straight Method Percentage wise

Question

Hi,

I have table

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

and the data is 

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

Looking for below result.

Year	BookValue(A)	Rate(B)	Dep.Exp C=A*B	YTD. Dep 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 tried below query.

CREATE FUNCTION [dbo].[fnTally]
       (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;

GO

;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

Thanks & Regards,

Basit