How to calculate the fixed assets depreciation calculation table in the EXCE table?
1. Draw the table
First draw a picture in the A1:Q16 area In this table, for the convenience of description, the cells that do not require formulas are colored yellow. There are formulas in the colorless cells. Do not change the formulas in them during use.
2. Enter the formula
In cells A3:G11, fill in the content according to the actual situation of the unit’s fixed assets. There is no formula in this part, only in cell E12. There is a summary formula =SUM(E3:E11). This needs no further explanation, it is relatively simple.
What is filled in column D is the time when the fixed asset is first depreciated, which is usually the month after the recording time (the second month).
The formulas in other cells are explained as follows:
H3 cell, formula =DATEVALUE(YEAR(D3)+F3&"-"&MONTH(D3))
< p> What is displayed in this cell is the final depreciation deadline of the fixed asset, that is, when the fixed asset no longer needs to accrue depreciation.Meaning of the formula: First use the YEAR function to extract the "year" from cell D3, add this "year" to the expected number of years of use of the fixed asset, that is, the "number of years" in cell F3, and then Use the MONTH function to extract the "month" of cell D3, and link the "year" and "month" with a "-" symbol to get a date represented by a string. This date is not a real date and cannot be involved in calculations. Use the DATEVALUE function to turn it into a calculable date value.
After the formula input is completed, use the mouse to select cell H3, drag it down to copy, and copy the formula to the cells below column E.
When introducing the subsequent formulas from column I to column Q, you also need to "drag and copy" without further explanation.
Cell I3, formula =E3*G3
This cell displays the net salvage value, which is the value of the fixed asset after it is scrapped. Meaning of the formula: Simply multiply the original value of the fixed assets in the E3 grid by the residual value rate in the G3 grid.
Cell J3, formula =ROUND((1-G3)/F3/12,4)
This cell calculates the monthly depreciation rate. Meaning of the formula: Subtract the residual value rate of G3 grid from 1 to get the "net value rate". The so-called depreciation is based on this "net value rate". Divide the "net value rate" by the service life in box F3 to get the depreciation rate for each year, and then divide it by 12 to get the depreciation rate for each month. Finally, use the ROUND function to round this value to 4 decimal places. The reason why four digits are retained instead of two decimal places is to avoid rounding errors in calculations. The usage of function ROUND is: ROUND (value, number of decimal places to retain), and the two parameters are separated by commas.
Cell K3, formula =ROUND(E3*J3,2)
This cell calculates the monthly depreciation of fixed assets that should be withdrawn. Meaning of the formula: First multiply the original value of fixed assets in cell E3 by the monthly depreciation rate in cell J3, and then use the ROUND function to round to two decimal places.
Cell L3, formula =IF(M3>H3,0,ROUND(E3*J3,2))
This cell calculates the actual monthly depreciation amount recorded each month. Different from K3, when the fixed asset reaches the depreciation life, if the fixed asset is not scrapped and is still used, there is no need to withdraw depreciation. At this time, the depreciation amount of the fixed asset should not be displayed when making accounts. Meaning of the formula: Use the IF function to compare the current date of the M3 grid with the cut-off depreciation date of the H3 grid. If the cut-off date for depreciation extraction has been reached (i.e. M3>H3), a value of 0 will be returned. Otherwise, the monthly depreciation amount will be calculated (compared with the K3 grid). The formula in is the same).
Cell M3, formula =$C$14
This cell can actually be omitted, but it is retained for the convenience of some friends’ understanding. It displays the current depreciation calculation time. date.
Formula meaning: Directly reference the date in cell C14. The absolute reference symbol "$" is used in the formula to keep the reference unchanged when dragging down and copying.
Cell N3, formula =DATEDIF(D3,M3,"m")
This cell calculates the number of months the fixed asset has been used since it was recorded. Formula meaning: Use the DATEDIF function to calculate the number of "months" between the entry date in cell D3 and the current date in cell M3. The last parameter "m" returns the result in "months".
Cell O3, formula =IF(N3>F3*12,F3*12,N3)
This cell calculates the total number of months of actual depreciation. Meaning of the formula: Use the IF function to compare the date in the N3 cell with the date in the F3 cell (F3 multiplied by 12 changes "month" into "year"). If the number of years of use is greater than the number of expected years of use, it will be displayed as the expected number of years (indicating that it will not be used). Then extract depreciation), otherwise it will be displayed as N3 grid date.
Cell P3, formula =O3*K3
This cell displays the accumulated depreciation that has been extracted, that is, the total depreciation of the fixed asset from the time it was recorded to the present. Meaning of the formula: Directly multiply the total number of months of depreciation by the amount of depreciation that should be withdrawn each month.
Cell Q3, formula =E3-P3
This cell displays the net value of the fixed asset as of now. Meaning of the formula: Subtract accumulated depreciation from the original value of fixed assets.
Cells L14:L16 are used to calculate the depreciation accrued in the current month by category, using the SUMIF conditional summary function. If this unit does not require category calculation, this part is not required.
Cell L14, formula =SUMIF($C$3:$C$11,K14,$L$3:$L$11)
Formula meaning: Search in the area from C3 to C11 After finding the value in cell K14, return the value in L3:L11 in the same row, and add all found values ??in column L.
The formulas in cells L15 and L16 are similar to it. Just drag and copy the formula in cell L14 with the mouse.
Note: The content in K14:K16 must match the content in C3:C10, otherwise the correct result cannot be displayed.
Finally, after entering the current date in cell C14, the corresponding depreciation data will be displayed in the table above.
Tip: When entering in cell C14, you can use the "quick" input method. For example, if you want to enter "February 1, 2011", you only need to enter "2011-2" in the cell (do not enter Double quotation marks) will do. You can see the complete date in the "Edit Bar" above after selecting the cell.