The Modified Accelerated Cost Recovery System (MACRS) is the system used by the IRS in the United States for reporting depreciation of property. This system is highly regulated, and you should consult the appropriate IRS Publication (and a certified accountant) to determine how to depreciate your property. See Publication 946 (2008). With that said, let me go on now to explain how to calculate the depreciation rate found in the MACRS Tables using Excel functions. These formulas are used in the Depreciation Calculator, described in Part 3 of this series.

Depreciation Formula

Under the MACRS, the depreciation for a specific year j (Dj) can be calculated using the following formula, where C is the depreciation basis (cost) and dj is the depreciation rate.

Using the MACRS Tables:
Dj = djC

The depreciation formula is pretty basic, but finding the correct depreciation rate (dj) is the difficult part because it depends on a number of factors governed by the IRS regulations. The rates can be found using the tables listed in the Appendix of the IRS Publication.

Calculating the Depreciation Rate

The IRS Publication explains the approach that you can use to calculate the depreciation instead of using the tables. See the section Figuring the Deduction Without Using the Tables.

The approach I describe in this article is different than the one described in the IRS publication. Instead of reducing the depreciation basis each year (as described in the IRS Publication), I have chosen to provide the formulas for calculating the depreciation rate for a given year as a substitute for using the MACRS tables. One benefit of this approach is that it is simple to check your calculations against the official MACRS tables. The other benefit is that you can use a set of relatively simple formulas that I've given below.

What I don't like about the approach in the IRS publication is that (1) it complicates the description of the Straight-Line Depreciation Method and (2) the depreciation rates cannot be verified against those in the MACRS tables. Regarding the second issue - it's not that the rates are off by just a rounding error - the rates are completely different as you would see if you ran through the examples.

The formulas will give slightly different values than the tables because the tabulated depreciation rates are rounded to 0.01% (for short recovery periods) or 0.001% (for longer recovery periods). Instead of always rounding to the nearest 0.01%, the tables sometimes alternate rounding up or down. The sum always ends up being 100% to ensure that the final book value is 0 at the end of the recovery period.

Depreciation Basis (Cost)

The basis for depreciation of MACRS property is the property's cost or other basis multiplied by the percentage of business/investment use." (Quoted from pub 946). The MACRS is set up to fully depreciate the asset down to 0 - it doesn't take into account a salvage value.

Recovery Period

"The recovery period of property is the number of years over which you recover its cost or other basis. It is determined based on the depreciation system (GDS or ADS) used." (Quoted from pub 946). Instead of estimating the depreciation period, the recovery period for different property classes is specified very specifically by the IRS.

Conventions

Date Placed in Service: Under the MACRS, the amount of depreciation you claim during the first year (the year you place the property in service) and the last year (the year you dispose of the property) depends upon the time of year the property is placed in service and the convention used. The conventions are either Half-Year, Mid-Quarter, or Mid-Month.

Depreciation Methods

The two basic methods for depreciation in the MACRS are the Straight-Line Depreciation method and the Declining Balance Depreciation method. The specific details for how to implement these methods depends upon the convention, asset class, recovery period, etc. I recreated most of the MACRS tables using the formulas listed below (as of Oct 13, 2009). Under each of the formulas, I've listed the MACRS Tables that the formula applies to and can be compared against.

Depreciation Methods: Quoted from pub 946 ... "MACRS provides three depreciation methods under GDS and one depreciation method under ADS."

  • The 200% declining balance method over a GDS recovery period. You must switch to the straight line method in the first year for which it will give an equal or greater deduction.
  • The 150% declining balance method over a GDS recovery period. You must switch to the straight line method in the first year for which it will give an equal or greater deduction.
  • The straight line method over a GDS recovery period.
  • The straight line method over an ADS recovery period.

Straight Line Depreciation Method

Under the MACRS, the straight-line depreciation method uses either a half-year, mid-quarter, or mid-month convention, which causes the depreciation rate in the first and last years to be different than 1/n. Because you should read and understand the official MACRS IRS Publication anyway, I have chosen not to explain in detail how the different conventions work.

Last Depreciation Year: Because of the different conventions, the last year of depreciation is not equal to the recovery period, n, as it is with the more simplified depreciation methods. For that reason, I've provided the formula for calculating the Last Depreciation Year.

Half-Year Convention

The following formula calculates the depreciation rate for year j using the straight-line method with the half-year convention (Table A-8):

dj=1/n*(MIN(n,j-0.5)-MAX(0,j-1.5))

Last Depreciation Year: =ROUNDUP(n+0.5)

Mid-Quarter Convention

The following formula calculates the depreciation rate for year j using the straight-line method with the mid-quarter convention (Tables A-9, A-10, A-11 and A-12), where Q (1, 2, 3 or 4) is the quarter that the property is placed in service:

dj=1/n*(MIN(n,j-Q/4+0.125)-MAX(0,j-1-Q/4+0.125))

Last Depreciation Year: =ROUNDUP(n+Q/4,0)

Mid-Month Convention

The following formula calculates the depreciation rate for year j using the straight-line method with the mid-month convention (Tables A-6, A-7 and A-13), where m (1, 2, 3, ... or 12) is the month that the property is placed in service:

dj=1/n*(MIN(n,j-m/12+1/24)-MAX(0,j-1-m/12+1/24))

Last Depreciation Year: =ROUNDUP(n+m/12,0)

Declining Balance Depreciation Method

Half-Year Convention

The following formula calculates the depreciation rate for year j using the declining-balance method with the half-year convention.

dj=VDB(1,0,n,MAX(0,j-1.5),MIN(n,j-0.5),factor)

The Last Year #: =ROUNDUP(n+0.5)

In Table A-1, the factor is 200% for the 3-, 5-, 7-, and 10-year recovery periods and 150% for the 15- and 20-year recovery periods. In Table A-15, the factor is 150% for all recovery periods.

Mid-Quarter Convention

The following formula calculates the depreciation rate for year j using the declining-balance method with the mid-quarter convention where Q (1, 2, 3 or 4) is the quarter in which the property is placed in service:

dj=VDB(1,0,n, MAX(0,j-1-Q/4+0.125), MIN(n,B24-Q/4+0.125), factor)

The Last Year #: =ROUNDUP(n+Q/4,0)

In Tables A-2, A-3, A-4, and A-5 the factor is 200% for the 3-, 5-, 7-, and 10-year recovery periods and 150% for the 15- and 20-year recovery periods. In Tables A-15, A-16, A-17, and A-18 the factor is 150% for all recovery periods.

Possible Solutions to the Rounding Problem

Method #1: "Depreciation Basis Reduction". This might have some other official name, but this is what I have opted to call the approach used in the IRS Publication. Because the depreciation basis is being reduced after each year, the depreciation rate used for the last year is simply 100%.

Method #2: "Zero it Out". I didn't know what else to call this approach, but basically you just make the last year's depreciation equal to the book value at the end of the previous year. The is similar in principle to the way amortization schedules adjust the last loan payment to account for prior rounding. This approach is simple to implement in a depreciation schedule because the depreciation schedule will typically include a column for the book value.

>> PART 3 - MACRS Depreciation Calculator

Depreciation References


Reference this Depreciation Article

To reference this page on your website or blog, use something similar to the following citation:

- Wittwer, J.W., "Depreciation in Excel: Part 2 - MACRS Depreciation Calculation," From Vertex42.com. Oct 16, 2009.


Disclaimer: This article is meant for educational purposes only. You may want to consult with a qualified professional regarding financial decisions.

Like This Page?

Become a Fan

Vertex42's Facebook Page Vertex42's Google+ Page Vertex42's Pinterest Page
Excel training for your entire company