How to Do Depreciation on Excel

by Helen Akers, Demand Media

Microsoft Excel 2007 and 2010 have built-in depreciation formulas.

Stockbyte/Stockbyte/Getty Images

Small business owners will find that Microsoft Excel is capable of performing intermediate to advanced financial calculations, including depreciation. Businesses need to calculate depreciation costs when making decisions about whether to sell or keep an asset, determining tax obligations and preparing financial statements. Owners and managers choose a depreciation method to account for the expense of an asset while it is in use. Some of the more popular methods of depreciation include straight-line, sum-of-years digits, declining balance and double-declining balance.

Step 1

Determine the original cost of the asset. Locate the price that was paid for the asset, which most likely will be found on an original bill of sale, loan documents or an invoice sticker price. Keep in mind that the purchase price for some capital assets, such as vehicles, may include dealer fees. Watch for loan origination fees on assets such as land and buildings. Remember that the cost of the asset equals the actual purchase price, not its original market value.

Step 2

Estimate how long the asset will be of use to the company. Estimate how much the asset will be sold for at the end of its life, which often is its projected market value. For example, some companies routinely replace fleet vehicles once they've been in service for 10 years. Determine which depreciation method the company will use. Be aware that most small business owners prefer to use the straight-line method due to its simplicity.

Open up Microsoft Excel 2007 or 2010. Highlight one of the cells in a spreadsheet. Click "Formulas" in the main toolbar or ribbon, then click "Financial." Click "SLN" to calculate straight-line depreciation for an asset. Type in the original cost of the asset in the "Cost" field. Enter in the asset's estimated salvage value in the "Salvage" field. Type in the amount of years the company will use the asset in the "Life" field. Enter in all amounts as numbers. Click "OK." Review the dollar amount displayed in the highlighted cell. Record this dollar amount as the per year depreciation cost for each year of the asset's life.

Step 4

Choose the "SYD" formula if using the sum-of-years-digits method, "DB" for declining-balance and "DDB" for double-declining balance. Keep in mind that the depreciation cost will be different for each period when one of these methods is used. Enter in the desired period or year that you wish to calculate depreciation costs for in the "Period" or "Per" field. For example, if you need to calculate the amount of depreciation in the third year for an asset with a useful life of five years, enter "5" in the "Life" field and "3" in the "Period" or "Per" field.

About the Author

Helen Akers specializes in business and technology topics. She has professional experience in business-to-business sales, technical support, and management. Akers holds a Master of Business Administration with a marketing concentration from Devry University's Keller Graduate School of Management and a Master of Fine Arts in creative writing from Antioch University Los Angeles.

Photo Credits

Have Feedback?

Thank you for providing feedback to our Editorial staff on this article. Please fill in the following information so we can alert the Small Business editorial team about a factual or typographical error in this story. All Fields are required.