Question on VDB function and Half-year convention

[XLML]

I can't get the VDB function to work in my spreadsheet for Year 2. Here is the info:

Cost-$4,035
Salvage Value-0
Life-10 years
Year 1 depreciation rate: 10.00
Year 2 depreciation rate: 18.00
Year 1 depreciation s/b: 403.5
Year 2 depreciation s/b: 726.30

The VDB formula works for Year 1:
=VDB(4035,0,10,0,1,.10)
=$403.5

It is returning $403.5 for Year 2 as well, BUT IT S/B $726.30!
=VDB(4035,0,10,1,2,.18)

Any ideas?
Thanks in advance,
XLML

 

[rrdonutz]

Assuming your first year formula is in cell A1, it should read:

=VDB(4035,0,10,0,1,1)

and then your second year formula in cell A2 would read:

=VDB(4035-$A$1,0,9,0,1,1.8)

This will return the $726.30 value you are expecting in the second year. Also note that your factor values, if less than 1, are rounded up to 1.

--Tom

 

[XLML]

A belated thanks for your help, Tom. Two follow-up questions:

1) Is there an easy way to increment the years(parameter 3)?
ie from 10 to 9 to 8 etc

2) Is there any place (besides XL help) that I can learn more about the VDB function? It looks like I will be using it quite a bit for a few different scenarios.

Thanks again,
XLML

 

[Aaron Blood]

What else would you like to know about VDB?
http://www.xl-logic.com/xl_files/finance/depreciation.zip

 

[XLML]

Thanks for the link, Aaron. The spreadsheet is great! The only thing I need help figuring out is: How and Where do I change the formulas to reflect a Fiscal Year that differs from the Calendar Year?

Our Fiscal Year Runs June 1st to May 31st (6/1-5/31). So an asset using 200DB added 6/26/2001 calculates depreciation for 2001. Our system calculates the first depreciation charge of $476 in 2002, not 2001. How can I change your formula to reflect our Fiscal Year and basically shift the $476 one column to the right?

I pasted an example below.

Thanks in advance,
XLML

vdb sheet.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Asset#	InSvc	Basis	Salv	Con	Method		Life			2000	2001	2002	2003	2004	2005	2006
3
4											0.000	0.500	1.500	2.500	3.500	4.500	5.000
5	15243	6/26/2001	2,378	-	HY	200	DB	5			-	476	761	457	274	274	137
Sheet1

 

[Aaron Blood]

That's my basic depreciation example file using the VDB function that I let anyone D/L for free.

I do have some more advanced depreciation logic that allows you to do much more. Runs off a custom UDF. If you like, you can review a sample of it.

Additional features:
- Better Accuracy in MM calcs. (If you test that example file, you might notice it's off a bit on that calc.)

- Allows FY, HY, MQ, MM, and Monthly conventions (the example doesn't allow monthly).

- Allows for n period delays (the feature you were asking about). Also allows you to specify writedown period to flush remaining balances.

- Allows you to specify 1 period depreciation, which just lets values flow thru uneffected. This is a very easy way to convert a capex item to an expense in a financial model.

- Handles CapEx additions in a single line!


Just be forewarned, I've scared a few people with this one. Some see it as a black box. They're just not accustomed to seeing capex addition logic without a massive matrix.

If you'd like to review it you can contact me thru regular email.

 

[Aaron Blood]

OR...

If you want, I guess it wouldn't be too difficult to just feed the formula an inservice date that is always n years delayed. Maybe leave the inservice date appear as 2001 but the formula works off a hidden column that is inservice date + 1 year?

Not too difficult for you to modify.