Hello, I have a list of assets that went in service in different dates and I want to use DB function to calculate depreciation BY MONTH. I tried =DB($J$43,0,60,R2,4) where:
J43 is end of PPE balance
0 salvage value
useful life 60 months
R2 =1 or the first period when depreciation start
4 is 4 months left since asset is acquired in Aug and first depreciation start in Sept. By the third month the asset is fully depreciated.
Question:
How do I fix the formula?
How do I make "4" to be dynamic?
[TABLE="width: 828"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Asset Id[/TD]
[TD] End Ppe Balance [/TD]
[TD] Recalculated EOL [/TD]
[TD]In service Date[/TD]
[TD="align: right"]08/31/2014[/TD]
[TD="align: right"]09/30/2014[/TD]
[TD="align: right"]10/31/2014[/TD]
[/TR]
[TR]
[TD="align: right"]237457[/TD]
[TD="align: right"]-90,280.02[/TD]
[TD="align: right"]02/28/2020[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]237477[/TD]
[TD="align: right"]42,571.81[/TD]
[TD="align: right"]05/28/2020[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230865[/TD]
[TD="align: right"]253,255.44[/TD]
[TD="align: right"]07/28/2019[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]$84,418.48[/TD]
[TD="align: right"]$168,836.96[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]
J43 is end of PPE balance
0 salvage value
useful life 60 months
R2 =1 or the first period when depreciation start
4 is 4 months left since asset is acquired in Aug and first depreciation start in Sept. By the third month the asset is fully depreciated.
Question:
How do I fix the formula?
How do I make "4" to be dynamic?
[TABLE="width: 828"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Asset Id[/TD]
[TD] End Ppe Balance [/TD]
[TD] Recalculated EOL [/TD]
[TD]In service Date[/TD]
[TD="align: right"]08/31/2014[/TD]
[TD="align: right"]09/30/2014[/TD]
[TD="align: right"]10/31/2014[/TD]
[/TR]
[TR]
[TD="align: right"]237457[/TD]
[TD="align: right"]-90,280.02[/TD]
[TD="align: right"]02/28/2020[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]237477[/TD]
[TD="align: right"]42,571.81[/TD]
[TD="align: right"]05/28/2020[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230865[/TD]
[TD="align: right"]253,255.44[/TD]
[TD="align: right"]07/28/2019[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]$84,418.48[/TD]
[TD="align: right"]$168,836.96[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]