VDB function and half-year convention

joebobjoe

New Member
I'm doing a project for an engineering economy class and part of it requires calculating the MACRS depreciation in year n. We are expected to use double-declining balance switching to SL depreciation with the half-year convention and I just don't understand why VDB doesn't work. My results don't match the MACRS table on Wikipedia: http://en.wikipedia.org/wiki/MACRS#MACRS_applicable_percentage_for_property_class.

For example, =VDB(100,0,5,1,2,2,FALSE) = \$24.00, not \$32.00 as in the table. Can someone help? I've scoured Google and I can't find what I'm doing wrong.

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I've never been able to replicate MACRS using Excel's VDB(). I haven't tried for years - I guess it's possible somehow. You have to play around with the years to get the half-year convention going, then fiddle with the switch to straight line too. The lack of a half-year convention in VDB means you can't do it with VDB() alone - you'd need a schedule, or at least some series of tweaked VDB() formulas that would imitate the effects of a schedule to account for the half years at the beginning and the end. I find it easier to use a simple Double Declining Balance schedule the old fashioned way:

Excel Workbook
ABCD
1Cost BasisDDBPeriods (Years)Depreciation
2\$100.000.40.5\$20.00
380.000.4132.00
448.000.4119.20
528.800.4111.52
617.280.4111.52
75.760.40.55.76
8TOTALS5\$100.00
Sheet1

Note that you *can* get the correct value for year one with VDB() by taking a full first year's depreciation with VDB() formula, and dividing it in half to make a half-year of depreciation.
=0.5*VDB(100,0,5,0,1,2,FALSE)
Result is \$20 as expected.

What get's a little confusing after that is that the next year is really the last half of year 1 and the first half of year 2, and so on ... until you switch to straight line, which is where I'm afraid I gave up, at least for tonight

Last edited:
MACRS, including half year convention and all, is actually pretty straightforward to do in Excel, using VDB.

Here's an example that should help:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Replies
1
Views
1K
Replies
4
Views
2K
Replies
8
Views
2K
Replies
0
Views
661

1,207,390
Messages
6,078,205
Members
446,321
Latest member
thecachingyeti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back