Declining balance depreciation method
Hi, I want to calculate depreciation of assets using declining balance depreciation method on Anaplan.
I went through Anapedia's financial function pages to find function like Excel's VDB, but couldn't find any function applicable for the method.
Is there any function that is available for declining balance depreciation method, or is there any other best practice for the method on Anaplan?
Hi @KokiS ,
There is no function for this, but you can "recreate" it yourself via the different available functions. I'd have a look at the PREVIOUS() function.
All the best,
Please explain the difficulty you're facing by creating a mock up in Excel, and then share the screen shots with as much descriptions as possible.
By bringing up 2 separate topics (1 being finance formula related and 1 being Anaplan formula creation related), the group of users who can help you is smaller.
To create a depreciation schedule, you will need to create a module with three line items namely 1. opening balance, 2. depreciation ( per month) and 3. Closing balance.
This module needs to be dimensioned by Assets ( list), time ( month ).
1. Opening balance: Net book value for a given asset as the opening balance plus previous( closing balance)
2. Depreciation: This will be calculated (Acquisition cost or NBV at the beginning of the period divided by asset life ( useful economic life).
3. Closing balance: Opening balance less depreciation.
You will also need a system module to calculate start and end periods as well as to factor in asset disposals.
Let me know if above is of some help. If you need detailed guidance I am happy to help.
See if this helps
Step 1: I have created first module called Community Global Module where I store the assumptions. I have created it for one single asset category but you can add multiple asset categories as a dimension if you wish to.
Step 2: Created another module called Community Module - Declining Balance
This module will respect the Salvage value of an asset and will not depreciate beyond that - Line 3rd above
This module will also respect the Final Depreciated Value of an asset and will not cross the Salvage Value.
Formulae in the Backend
Amount: Community Global Module.Amount
Depreciation without Restrictions: IF NOT First Year THEN PREVIOUS(Asset Amount Until Salvage * Community Global Module.Accelerated Deprecation Percentage) ELSE Community Global Module.Accelerated Deprecation Percentage * Community Global Module.Amount
Depreciation until Final Value Crosses Salvage: IF Asset Amount without Depreciation Restrictions >= Asset Amount Until Salvage THEN Depreciation without Restrictions ELSE PREVIOUS(Asset Amount without Depreciation Restrictions) - Asset Amount Until Salvage
First Year: OFFSET(1, -1, 0) = 0. Practically it can not be the first year of a timescale, you can keep it manual but you need this boolean for every asset category to determine the first year of Asset
Asset Amount Until Salvage: MAX(IF NOT First Year THEN PREVIOUS(Asset Amount Until Salvage) - Depreciation without Restrictions ELSE Amount - Depreciation without Restrictions, Community Global Module.Salvage)
Asset Amount without Depreciation Restrictions: IF NOT First Year THEN PREVIOUS(Asset Amount Until Salvage) - Depreciation without Restrictions ELSE Amount - Depreciation without Restrictions
Note: You can add Shelf Live as well in your calculations to zero out all the future years which are irrelevant for the asset category2
Thanks for your quick reply.
I'll check PREVIOUS() function you mentioned.
Thanks for your reply.
I got some solutions that might work for my problem here.
So I will try if those solutions work for me. And if they doesn't work, will post further information in detail as you described.
Thanks for your information.
It helped me a lot.
currentry l'm trying to apply the method to my model, and might ask some question again.
Thanks for detailed information.
l'm trying to apply the method to my model. if the problem is solve, i will let you know.