We currently have an asset forecast module set up that calculates the NBV of assets over time which currently only uses straight line depreciation so it's reducing by the same value per month...simple.
However we have just changed our depreciaition policy to use reducing/declining balance depreciation and I am not sure how to use the profile formula.
So for example we currently have an asset list with it's NBV and we want to calculate 14% reducing balance over time. So for the first year its 14% of the purchase NBV but when it comes to year 2 how does it know to take 14% of the NBV at the end of year 1?
And I need this to keep calulating year on year.
Attached example of straight line depreciation working. Wanted to add a row and say if reducing balance then do this....
Remaining Value will capture the NBV of the asset and will reduce over time.
Depreciation Charge will simply be the charge.
Reducing Balance % will be the percentage to apply to Remaining Value to calculate the Depreciation Charge.
The formula for Remaining Value could look something like this: PREVIOUS(Remaining Value) - PREVIOUS(Depreciation Charge) i.e. take the prior month NBV and subtract the prior month depreciation charge.
The formula for Depreciation Charge: Remaining Value * Reducing Balance %
The formula for Reducing Balance %: 1 - POWER(1 - 0.14, 1/12) i.e. 14% raised to the power of 1/12 to spread 14% over 12 months.
Let me know if the above helps point you in the right direction!
Thanks for your response. Unfortunately it didn’t work but we are going to try adding a line "Is this a new financial period?" if yes then dep calculation (NBV *14% /12), if not a new financial year then same as previous month. So each year it changes the dep value Thanks