depreciation/amortization schedule

Aperszon — Wed, 15 Nov 2023 14:55:08 GMT

Hello,

I am trying to expand a cashflow. And is running out of ideas.

I have amortizing schedule which works ok. I now want to expand it to also base it off straight line depreciation.

What I am trying to do is if [ProductID] = 0-1-0-1 then principal calculation should based on a straight line depreciation rather than on a amortizing basis for each period.
So in essence (Total Rentals - Residual ) / Periods. Interest would then just be difference between Rental and Principal. Residual is fixed at 250.

Where I am getting stuck is how to combine the two methods and switch the calculation when only this filter (0-1-0-1) is chosen and when all others use the declining interest.

Amortization table.

Amortization Schedule = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( 'Lease Details', 'Calendar Date'[YearMonth], [Lease ID ], [Present Value ], [Rent], "Beginning balance", VAR PV = CALCULATE ( SUM('Lease Details'[Present Value ]), FILTER ( ALL ( 'Lease Details' ), [Lease ID ] = SELECTEDVALUE ( 'Lease Details'[Lease ID ] ) ) ) VAR I = 0.0033 VAR Series = SELECTEDVALUE ('Series New'[Series]) VAR Payment = 'Lease Details'[Rent] VAR Result = IF ( PV * POWER ( 1 + I, Series - 1 ) - Payment * DIVIDE ( POWER ( 1 + I, Series - 1 ) - 1, I ) >= 0, PV * POWER ( 1 + I, Series - 1 ) - Payment * DIVIDE ( POWER ( 1 + I, Series - 1 ) - 1, I ), 0 ) RETURN Result ), "Interest", [Beginning balance] * 0.0033 ), "Ending balance", IF ( [Beginning balance] - ( [Rent] - [Interest] ) >= 0, [Beginning Balance] - ( [Rent] - [Interest] ), 0 ) ), "Principal", VAR Principal = IF([Rent] - [Interest] >= 0, [Rent] - [Interest], 0 ) RETURN Principal )

Base Data

Date Interest Discount Lease ID Present Value RentEst Present ValueEst RentMonth Count

1/1/2019	1.00	0-1-1-0	259.14	260	259.14	260	1
1/1/2019	1.00	0-2-0-0	259.14	260	0.00	0	1
2/1/2019	0.99	0-1-1-0	258.29	260	258.29	260	2
2/1/2019	0.99	0-2-0-0	258.29	260	0.00	0	2
3/1/2019	0.99	0-1-1-0	257.86	260	257.86	260	3
3/1/2019	0.99	0-2-0-0	257.52	260	0.00	0	3
4/1/2019	0.99	0-1-1-0	256.58	260	256.58	260	4
4/1/2019	0.99	0-2-0-0	256.58	260	0.00	0	4
4/1/2019	1.00	0-1-0-1	388.71	390	0.00	0	4
5/1/2019	0.98	0-1-1-0	255.75	260	255.75	260	5
5/1/2019	0.98	0-2-0-0	255.35	260	0.00	0	5
5/1/2019	0.99	0-1-0-1	387.43	390	0.00	0	5
6/1/2019	0.98	0-1-1-0	254.56	260	254.56	260	6
6/1/2019	0.98	0-2-0-0	254.89	260	0.00	0	6
6/1/2019	0.99	0-1-0-1	386.15	390	0.00	0	6
7/1/2019	0.98	0-1-1-0	254.05	260	254.05	260	7
7/1/2019	0.98	0-2-0-0	254.05	260	0.00	0	7
7/1/2019	0.99	0-1-0-1	384.88	390	0.00	0	7
8/1/2019	0.97	0-1-1-0	253.21	260	253.21	260	8
8/1/2019	0.97	0-2-0-0	253.21	260	0.00	0	8
8/1/2019	0.98	0-1-0-1	383.61	390	0.00	0	8
9/1/2019	0.97	0-1-1-0	252.38	260	252.38	260	9
9/1/2019	0.97	0-2-0-0	252.38	260	0.00	0	9
9/1/2019	0.98	0-1-0-1	382.34	390	0.00	0	9
10/1/2019	0.97	0-1-1-0	251.55	260	251.55	260	10
10/1/2019	0.97	0-2-0-0	251.55	260	0.00	0	10
10/1/2019	0.98	0-1-0-1	381.08	390	0.00	0	10
11/1/2019	0.96	0-1-1-0	250.72	260	250.72	260	11
11/1/2019	0.96	0-2-0-0	250.72	260	0.00	0	11
11/1/2019	0.97	0-1-0-1	379.82	390	0.00	0	11
12/1/2019	0.96	0-1-1-0	249.89	260	249.89	260	12
12/1/2019	0.96	0-2-0-0	249.89	260	0.00	0	12
12/1/2019	0.97	0-1-0-1	378.57	390	0.00	0	12
1/1/2020	0.96	0-1-1-0	249.06	260	249.06	260	13
1/1/2020	0.96	0-2-0-0	249.06	260	0.00	0	13
1/1/2020	0.97	0-1-0-1	377.32	390	0.00	0	13
2/1/2020	0.95	0-1-1-0	248.24	260	248.24	260	14
2/1/2020	0.95	0-2-0-0	248.24	260	0.00	0	14
2/1/2020	0.96	0-1-0-1	376.07	390	0.00	0	14
3/1/2020	0.95	0-1-1-0	247.42	260	247.42	260	15
3/1/2020	0.95	0-2-0-0	247.42	260	0.00	0	15
3/1/2020	0.96	0-1-0-1	374.83	390	0.00	0	15
4/1/2020	0.95	0-1-1-0	246.61	260	246.61	260	16
4/1/2020	0.95	0-2-0-0	246.61	260	0.00	0	16
4/1/2020	0.96	0-1-0-1	373.60	390	0.00	0	16
5/1/2020	0.95	0-1-1-0	245.79	260	245.79	260	17
5/1/2020	0.95	0-2-0-0	245.79	260	0.00	0	17

https://drive.google.com/file/d/1YXb78yhZsuzEl6xuWdNHFBKaQMcNbEga/view?usp=drive_link

Re: depreciation/amortization schedule

lbendlin — Sat, 18 Nov 2023 19:12:16 GMT

You created a calculated table. That cannot be influenced by users interacting with slicers ( like selecting a Lease ID). You would either have to add the straight line depreciation calculation to your static table, and then use a measure to flip the result based on the Lease ID, or you would need to compute everything dynamically in a measure.

Note - Your DAX code could use some refactoring, both for performance and readability.

Re: depreciation/amortization schedule

Aperszon — Mon, 20 Nov 2023 16:57:05 GMT

That is fair enough. some improvements can defintely be done. and would be open to any suggestions.

this would definitely need to be acheived through a measure rather than table. Flipping the Principal and interest between the two could be done by something like this, same thing for the principal with slight variations. FYI just created two new columns for the OPL as you suggested above.

Selected_Interest = VAR DEP = SUM('Amortization Schedule'[Rent]) - SUM('Amortization Schedule'[Capital OPL]) VAR Interest = SUM('Amortization Schedule'[Interest]) VAR Results = SWITCH( SELECTEDVALUE('Amortization Schedule'[Lease ID ]), "0-1-0-1", DEP, "0-1-1-0", Interest ) RETURN Results

I guess the problem then becomes how to derive the beginning balances based on a measure rather than a table.

I could create new column and do the same Switch logic, but becomes very ineffiecent...

topic Re: depreciation/amortization schedule in DAX Commands and Tips

depreciation/amortization schedule

Re: depreciation/amortization schedule

Re: depreciation/amortization schedule