• 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.

Depreciation under Written Down Value method

Status
Not open for further replies.
CA Rohit

CA Rohit

New Member
Hello All,

I have a serious problem, hope someone could solve the same for me.

I have to calculate the written down value (WDV) as on specific date. I have tried to explain how the method works for those who are unaware of the same
  • This method involves applying the depreciation rate on the Net Book Value (NBV) of asset. In this method, depreciation of the asset is done at a constant rate.
  • In this method depreciation charges reduces each successive period.
  • Assume the price of a depreciable asset i.e. computer is Rs. 40,000 and rate of depreciation is 10%
Depreciation Per year = (1/N) Previous year's value, Where N= No. of years
So in our example, the depreciation amount during the first year is
[Rs. 40,000*1/10] =Rs. 4,000
NBV of computer after 1st year= Rs 40,000- 4,000 = Rs. 36,000
Depreciation for 2nd year is
[Rs. 36,000*1/10] =Rs. 3,600 and so on...

I know it forms geometric progression but the formula is not giving the proper answer and the technicalities increases when i have to calculate depreciation day wise.

For e.g. I have purchased a printer on 22/11/2012 for 16,800/-, the depreciation on which is 40%, Depreciation for the financial year ending 31/03/2013 would be 2393.42 for 130 days (31/03/2013-22/11/2012+1), and for the year ending 31/03/2014 it would be 5762.63 (16800-2393.42)*40%.

Now I want a formula wherein I can find the WDV as on particular date say 31/03/2014. I tried using the formula - WDV as on Date = Cost price x (1- Dep Rate%) ^ (Date of WDV Calculation - Date of Purchase + 1)/365. But the answer it gives for the above example is 8403.22, wherein if we calculate year wise it is (16800-2393.42-5762.63)=8643.95.

Kindly help me.

Thanks in Advance
 
Thanks Deepak for the prompt reply.

But i want a formula to find WDV at a particular date. The links you mentioned gives depreciation year wise whereas i want Depreciation on pro rata basis plus i have thousands of assets.
So it would be difficult for me to calculate yearly for thousands of assets
 
Hello Deepak,

I have gone through all the links you mentioned, they all have formulas to calculate yearly or monthly whereas I want in days. None of them is giving me the solutions i want.

I think if we proceed on the line of geometric progression, we may find something. But i am not able to get the exact answer.

Kindly help
 
Hi ,

I think the first step is to upload a workbook which has enough variety of data to cover all date ranges over several years , so that testing can be thorough.

Narayan
 
Hello,

I am uploading an excel with few examples. I have provided the depreciation on the assets in the year of purchase on pro-rata basis (Day wise). We follow financial year from 01/04/20XX - 31/03/20XX.

In the examples provided i have taken assets which i have purchased 5-6 years back, wherein i have few assets which are 40-60 years old. So calculating year wise depreciation is tedious.

What i want is what would be the written down value of asset as at 31/03/2014.

Hope this helps. Let me know in case you require further details
 

Attachments

  • Depreciation Example.xlsx
    12.1 KB · Views: 68
Hi ,

Can you see the attached file ?

What I have done is made some sort of a template , where the only items of data entry are shown in green colour.

If you can verify that the template gives correct outputs for all possible variations in input data , then what I think is we can write a UDF if possible , which you can then use in your input worksheet to derive the output from the inputs.

Narayan
 

Attachments

  • Depreciation Example.xlsx
    19.5 KB · Views: 106
Hello Narayan,

I went through the Excel you have attached and the excel works wonderfully and gives exact answers. Now my only concern is that the asset list which i have has more than 50 assets. For me to create individual sheet would be too tedious. Is there any way we can have everything in minimum possible sheets.

Also this is just the starting of what i want, for the financial 2014-15 onwards we have to shift to straight line method of depreciation wherein there is no depreciation rate but only useful no. of years with residual value and all.

For E.g. I have an asset 6 years old on which i was providing depreciation on the basis of rate specified. Now for the financial year 2014-15 and onwards the useful life defined is say 5 years, in that case I will have to write off the closing value as at 31/03/2014 in the financial year 2014-15 and if the useful life is say 10 years, in that case i would have to write off the value of asset as on 31/03/2014 in 4 years (Remaining life 10-6 years)

Also it has be calculated day wise with residual value.

Could you help me in that?

TIA
 
Hi ,

As I mentioned , the sheet which has the calculations is a template ; for 50 assets you do not have to create 50 such sheets.

If you confirm that the calculations are correct , I will put the entire set of calculations in a UDF , which you can then use in your input data sheet.

Narayan
 
Hi Narayan,

I am sorry i forgot to mention that the sheet provides the correct answers.

Also I am attaching herewith an excel wherein I have mentioned what exactly I am trying to accomplish.

Hope this helps. Let me know in case you required further details.

TIA
 

Attachments

  • Depre - Example.xlsx
    11.3 KB · Views: 56
Hi ,

Can we first complete your original requirement of WDV calculation ?

The attached file has the required code to output the WDV to the relevant cells in column G ; can you confirm that the outputs are correct ?

Narayan
 

Attachments

  • Depreciation Example.xlsm
    30.8 KB · Views: 74
Hello Narayan,

I am unable to view data in the excel provided by you.

Do i need to install some add-ins to view this?

Rohit
 
Hey Narayan,

Yes in that case we can eliminate the columns E and F. I found one more minor glitch, if by mistake i put a purchase date after 31/03/2014 in column B, column G still gives answer, whereas it should show an error as date is outside the range.
Similarly, if instead of 31/03/2014, i put 31/03/2013, column G still gives answer for the purchase made after 31/03/2013

Rohit
 
Hi ,

Fine , that is taken care of ; now can you explain what you wanted to do next , the part about the residual life .... ?

Please take a couple of examples from your present file , say rows 8 and 11 , and explain what all calculations should be done after the values for the W.D.V. as on 31-03-2014 has been arrived at.

Narayan
 
Hi ,

Can you indicate which cells are data entry , and which cells need to have outputs either through formulae or VBA code ?

Narayan
 
Hello,

After we have derived WDV as on date, Residual Value and Useful Life are data entry cells, rest are output

Rohit
 
Hi ,

I am thinking that just as we did for the WDV calculations , can we have a template for the remaining calculations ?

If so , on the sheet named Input Data , can you label the remaining columns which will have calculations ? These need not be a break-up year by year , since that will be a part of the template. Show only the final output columns which you will need as a summary report.

Narayan
 

Attachments

  • Depreciation Example (1).xlsm
    27.9 KB · Views: 74
Hi,

Apart from the column you have mentioned, i dont think there is a need to add more column except for yearly depreciation till the remaining life of asset.

If that s not what you want, could you please elaborate what has to be done by me

Rohit
 
Status
Not open for further replies.
Back
Top