• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic WDV depreciation schedule

Venkatakrishnan

New Member
Hi

Attached the working file of WDV calculation. Request the below help from the team.

1. Based on the start date and end date of the asset life the WDV for each year needs to be generated monthly, to the relevant period (say if a asset start date is 10/01/2020 then the depreciation should be for 21 days for Jan'20 the rest depreciation for balance relevant periods
2. If we change the useful life, the calculation of WDV per year and monthly depreciation value should have been changed to the relevant period.
3. By maintaining 5% residual value the WDV depreciation rate considered, hence final WDV should be maintained for all asset 5% which is coming correctly.
For referring the file : Sheet : Data : working given for yearly depreciation, depreciation calculation is not dynamic, if we change the start date and add the useful life the depreciation calculation should end at end date and accurate depreciation to be calculated for each month. Also marked in blue colour the residual WDV value based on the formula which is right.
Sheet : Dep rates : Calculated the each year WDV depreciation rate

Expecting the positive reply from the team, thanks in advance.

Regards
K Venkatakrishnan
 

Attachments

  • WDV working file.xlsx
    16.7 KB · Views: 18
Venkatakrishnan
I try to be positive ...
Do others need to know - what is WDV?
Is somewhere expected results?
Your
#1 Where do You do those changes?
#2 Where is connection with the useful life and the relevant period?
#3 You write something about residual value ... and finally that is coming correctly .. how to know those?
You have marked four cells with blue - correct formulas ... how about other formulas? ... those look same.
You've used links to other file.
What should be dynamic?
 
Hi

Thanks a lot for your response.

WDV - Written down value
1. Will do changes in sheet - "Data" in columns "Start date" and "Useful life", based on this change the dep (depreciation needs to be changed). i have provided the skeleton, the actual work books needs to be constructed based on the formulas (for which i need the help)
The depreciation for each year will be calculated based on the rate (sheet : WDV dep rate working) and the same needs to be showed for each month of total useful life period (start date to end date).
2. There is no connection for useful life, we have given useful life as per relevant asset category based on companies act, provided only the sample.
3. Residual value is 5% of opening gross block value which is the base for all assets.
The formulas required on the below columns in sheet : data
A. The formula has to start from column "I" and the depreciation calculation to be for the total useful life
B. The dynamic fields are "start date" and "useful life, if we change this based on the period the yearly depreciation to be calculated and further to this the yearly depreciation needs to be calculated monthly ie from start date to end date.

Changed the other workbook link, Attached the revised working file with columns highlighted in yellow color.


Regards
K Venkatakrishnan
 

Attachments

  • WDV working file_V1.xlsx
    15.4 KB · Views: 1
Venkatakrishnan
You should reread Forum Rules
There are clear instructions - what to do? - if use cross-posting
... seems You have skipped those for some reason
... It cannot be that You've not read those - because new users should read those before posting.
... and same kind of rules are in every Forum.
 
Extremely sorry for the inconvenience caused, really i haven't read the rules. As a clear instruction will obey the rules and will not do cross post. As I need the answer quickly hence done the same, will ensure to follow the rules in future.
Hope you would able to help me.

Regards
K Venkatakrishnan
 
Hi

Have developed formula and tried to bring this file to some extent. Attached the file and requested for the points mentioned on the file itself.

Regards
K Venkatakrishnan
 

Attachments

  • WDV working file_V1.xlsx
    26.9 KB · Views: 4
I haven't got as far as answering your question (it is all new to me) but the refactoring into array formulas should make any subsequent extraction of data far easier. At present the depreciation is linked to a period counter but that will need to be expressed in terms of a date line to allow aggregation (probably by using MMULT)
75456
The array formulas with a yellow fill are new.
 

Attachments

  • WDV working file_V2.xlsx
    30.8 KB · Views: 3
This workbook contains modifications to place depreciation amounts against a time line but the exact split between reporting years is not considered.
75466
 

Attachments

  • WDV working file_V2.xlsx
    23.4 KB · Views: 4
Dear Peter

Thanks a lot for your effort you showed to help me. Really the file is exactly working on the aspect of bringing the WDV value at the end of useful life is equal to my residual value.

Want to list out the below clarification points from my side

Let me to list couple of my exact requirement and issues i faced on the file shared to me.

1. The starting date and useful life to be dynamic, if i try to change the starting date formulas not working (I am having 2016 excel version) also the file got closed automatically.
2. If you refer my file the depreciation is calculated based on the useful file exactly for the respective useful life, whereas the file given to me having the deprecation value started from 2008 for first asset, actually there would be some depreciation needs to calculated from year 2007 if we split the yearly depreciation into monthly. (the splitting of yearly depreciation into monthly is another requirement already i mentioned).
3. The formulas LET is not working in my laptop, also all formulas are new to me, and this the same are array formulas which is not working in my excel version.

The requirement from my side.
1. I have given the revised file on which the depreciation for the total useful life is getting calculated year wise correctly based on the if formula which is working fine in my excel 2016 version
2. Further as i have asked for the file required to be tweaked as per the requirement i listed in the same file (attached the file again)

Really once again thanks for the effort you put, my humble request is, will it possible to fix my requirement as I asked for, the reason behind based on the dynamic columns "start date" and "useful life" change i need to calculate the depreciation for a lakh of line items. Moreover the array formula will take huge time and i can't use the "LET" function in my excel due to version restriction.

Further it would be very much helpful that based on the yearly depreciation rate you have calculated, i need each month depreciation (for monthly days) by considering the starting date and useful life. It would be very much helpful to fix all in one instead of calculating yearly depreciation and splitting the same into months. (I got this thought based on the yearly depreciation you calculated)

Regards
K Venkatakrishnan
 

Attachments

  • WDV working file_V3.xlsx
    26.5 KB · Views: 1
Last edited:
Sorry, but I only develop solutions for Excel 365.
There are calculations that you could extract from my workbook to use in your solution. For example the depreciation
= opening*rateDep*(1-rateDep)^(period-1)
is written as a direct calculation rather than applying the factor (1-rateDep) over multiple years or calendar months. Also the calculation of %depreciation rate as
= 1 - (0.05)^(1/usefulLife)
rather than as a lookup simplifies the solution.
I think that the proportion of each depreciation amount that goes to each of two reporting years will be fixed so it may not be necessary to mover from 20 years to 240 months in the calculation grid.
I hope someone else is able to help. Good luck.
 
Thanks to you again for your immediate reply. The reason for calculating the monthly depreciation is required to pass relevant entries for the respective months. I got this formula and the same is used in my calculation too.
The reason to calculate this to find out the exact depreciation charged over the months and want to know the asset written down value as of Mar21.
Moreover using the dynamic field to understand if we change the useful life what will be the hit to my profit & loss account

Again attached the recently developed file. if somebody help on this it would be great. Pl refer sheet "Data" and refer the if formulas given for monthly depreciation (able to bring the dep calculation for two years) further not working. Even this formula needs to be checked

regards
K Venkatakrishnan
 

Attachments

  • WDV working file_V3.xlsx
    47.2 KB · Views: 12
Thanks to you guys. Got the solution on my own but if we use the formula for whole line item the file size become huge which is very inconvenient to use. Thanks.
 
Back
Top