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

=VDB accumulated depreciation, help!

R

Robin Miller

New Member
Hi
I've been going round and round in circles trying to get =VDB to give me accumulated depreciation for straight line and declining balance. I can't even get it to work for double-declining balance!!
Help, please, someone tell me what the [factor] element actually does in practice and how to get it to work!
Workbook attached, hopefully it's obvious
Thanks
Robin
 

Attachments

  • vdb db sln.xlsx
    17.2 KB · Views: 3
Hi Narayan
Thanks.

Ok, the 1st link has made me realise that I need to use 0 for start_period. I was using 1, so I now get SLN and DDB to work by using [factor] of 0 for SLN and 2 for DDB.

However, I still can't get DB to work. I used goal-seek to find the necessary [factor] and it needs to be 2.28 to get DB to work (using my original attached spreadsheet). Can you exlplain where 2.28 comes from?

The 2nd link is no use, it doesn't explain [factor] at all.

Thanks
Robin
 
Hi ,

The Declining Balance method is described here :

http://www.investopedia.com/terms/d/decliningbalancemethod.asp

According to the link , the depreciation is constant in this method.

Thus , in the first year , the amount is 190 , which is 190/10000 , which is 1.9 %.

This depreciation remains constant throughout the life of the equipment.

Thus , in the second year , the depreciation would be an amount equal to 1.9 % of the remaining asset value , which would be (10,000 - 190) ; this would work out to 1.9 % of 9810 = 186.39

And so it would go , for all the remaining periods.

Narayan
 
Your VDB function's Start_period argument is what's causing the issue...

Formula should be...
=VDB($B$1,$B$2,$B$3,0,$B19,C$18)

Means depreciation starting at purchase (period 0) to end period of 12.

Your current formula...
=VDB($B$1,$B$2,$B$3,1,$B19,C$18)
is calculating from start period of 1 to end period of 12.
Excluding first depreciation cycle (0 to 1).
 
Chihiro said:
Your VDB function's Start_period argument is what's causing the issue...

Formula should be...
=VDB($B$1,$B$2,$B$3,0,$B19,C$18)

Means depreciation starting at purchase (period 0) to end period of 12.

Your current formula...
=VDB($B$1,$B$2,$B$3,1,$B19,C$18)
is calculating from start period of 1 to end period of 12.
Excluding first depreciation cycle (0 to 1).
Click to expand...

Hi Chihiro

Thanks, yes, we've spotted that, see above.
Now trying to work out what the factor should be to get declining-balance method!

R
 
Narayan

OK, yes, I understands that now.
So, where does the 190 come from? What's the calculation that gets the answer 190. I know it's 1.9% of 10000, but where does 1.9% or 0.019 come from?

It's not 1/120, that is 0.00833

Sorry, still confused!
R
 
Last edited:
Ok, the [factor] of 2.28 is the 0.019 x 120 (periods) = 2.28.
So, I'm good except for knowing where the 0.019 comes from
 
Now trying to work out what the factor should be to get declining-balance method!
Click to expand...
Ah my bad should have read more carefully.

As for your question on where 0.019 comes from.
You need to solve for...
1000=10000(1-x)^120

Edit: added top portion.
 
To give bit more detail.

1000=10000(1-x)^120

Becomes...
0.1=(1-x)^120

Inverse of y = x^b is x = y^(1/b)

So...

x = 1 - 0.1^(1/120)

x ≈ 0.0190052
 
Wow, Chihiro, you're the man!!

So the theory then is
[factor] for DB = (1-((salvage/cost)^(1/life)))*life

Does that still work if the salvage is not 10% of the cost, as it is in this example?
 
You must log in or register to reply here.
Back
Top