Question on VDB function and Half-year convention

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

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

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

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

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.

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.