Accumulated Depreciation to Fixed Assets Ratio in Excel

Accumulated depreciation to fixed assets ratio is a financial ratio that measures how much of the original cost of a fixed asset has been depreciated over time. It indicates how old or worn out the fixed assets of a company are. A high ratio means that the fixed assets are mostly depreciated and may need to be replaced soon. A low ratio means that the fixed assets are relatively new and have more useful life left.

The formula for calculating the accumulated depreciation to fixed assets ratio is:

Accumulated Depreciation to Fixed Assets Ratio=Fixed AssetsAccumulated Depreciation​

It is important to make sure that land is not included in the fixed assets number, as land does not depreciate.

How to Calculate the Ratio in Excel

To calculate the accumulated depreciation to fixed assets ratio in Excel, we need to have the following data:

• The original cost of the fixed assets
• The salvage value of the fixed assets
• The useful life of the fixed assets
• The depreciation method used

There are different methods of calculating depreciation, such as straight-line, sum-of-years’ digits, declining-balance, double-declining-balance, and units-of-production. Excel has built-in functions for most of these methods, except for units-of-production, which requires a simple formula.

In this article, we will use the straight-line method as an example. The straight-line method calculates the depreciation expense by dividing the difference between the cost and the salvage value by the useful life. The formula is:

Depreciation Expense=Useful LifeCost−Salvage Value​

The accumulated depreciation is the sum of the depreciation expenses over the periods. The formula is:

$$\text{Accumulated Depreciation}=\text{Depreciation Expense}\times \text{Number of Periods}$$

In Excel, the function SLN(cost, salvage, life) calculates the depreciation expense using the straight-line method. The function SUM(range) calculates the sum of a range of cells.

Example

Let’s say we have the following data for a company’s fixed assets:

We want to calculate the accumulated depreciation to fixed assets ratio for each year from 1 to 15. We can use the following steps:

1. Enter the data in an Excel worksheet, such as Sheet1. Leave some empty rows and columns for the calculations.
2. In cell B9, enter “Year”. In cell C9, enter “1”. In cell D9, enter the formula =C9+1 and drag it to cell Q9. This will create a series of numbers from 1 to 15, representing the years.
3. In cell A10, enter “Depreciation Expense”. In cell B10, enter “Asset A”. In cell C10, enter the formula =SLN($B$2,$C$2,$D$2) and drag it to cell Q10. This will calculate the depreciation expense for asset A for each year using the SLN function.
4. In cell B11, enter “Asset B”. In cell C11, enter the formula =SLN($B$3,$C$3,$D$3) and drag it to cell Q11. This will calculate the depreciation expense for asset B for each year using the SLN function.
5. In cell B12, enter “Asset C”. In cell C12, enter the formula =SLN($B$4,$C$4,$D$4) and drag it to cell Q12. This will calculate the depreciation expense for asset C for each year using the SLN function.
6. In cell A13, enter “Accumulated Depreciation”. In cell B13, enter “Asset A”. In cell C13, enter the formula =SUM($C$10:C10) and drag it to cell Q13. This will calculate the accumulated depreciation for asset A for each year using the SUM function.
7. In cell B14, enter “Asset B”. In cell C14, enter the formula =SUM($C$11:C11) and drag it to cell Q14. This will calculate the accumulated depreciation for asset B for each year using the SUM function.
8. In cell B15, enter “Asset C”. In cell C15, enter the formula =SUM($C$12:C12) and drag it to cell Q15. This will calculate the accumulated depreciation for asset C for each year using the SUM function.
9. In cell A16, enter “Total Accumulated Depreciation”. In cell C16, enter the formula =SUM(C13:C15) and drag it to cell Q16. This will calculate the total accumulated depreciation for all assets for each year using the SUM function.
10. In cell A17, enter “Total Fixed Assets”. In cell C17, enter the formula =SUM($B$2:$B$4) and drag it to cell Q17. This will calculate the total fixed assets for all assets for each year using the SUM function. Note that this does not change over time, as we assume that no new assets are purchased or sold.
11. In cell A18, enter “Accumulated Depreciation to Fixed Assets Ratio”. In cell C18, enter the formula =C16/C17 and drag it to cell Q18. This will calculate the accumulated depreciation to fixed assets ratio for each year using the formula.

The final result should look like this:

Analysis

From the table, we can see that the accumulated depreciation to fixed assets ratio increases over time, as the fixed assets depreciate more and more. The ratio reaches 0.90 in the 15th year, which means that 90% of the original cost of the fixed assets has been depreciated. This suggests that the company’s fixed assets are very old and may need to be replaced soon. A low ratio, on the other hand, would indicate that the company has invested in new fixed assets recently and has more potential to generate revenue from them.