Upgrade your Excel Fixed Assets Accounting Schedule!

Write a formula to: calculate the depreciation charge based on the date. The formula will never result negative NBV, stopping at the value of your asset.

To do this, we will write a formula that calculates depreciation of our current assets to the nearest month.

Fixed Assets Example

Let’s use an example to illustrate the problem.

  • Year End: 31/12/2021
  • Prior Year End: 31/12/2020

Every month we must calculate depreciation.

  • update each and every formula
  • ensure NBV does not result in a negative

Our formula will save all this work.

Fixed Assets The Steps

Step One: Months to Date

We need to calculate the months of depreciation to charge.

logic

current month - prior year end = days to date
days to date/average days in month = months to date
ROUND(months to date, decimal places) = nearest months to date

result

30/11/2021 - 31/12/2020 = 334 days to date
334 days to date/30.5 = 10.95 months to date
ROUND(10.95 months to date,0 decimal places)=11 months to date

formula

output

We can then easily insert this onto our schedule next to each asset.

Step Two: Addittions Months

We need to adjust the number of months to depreciate if we purchase the asset in the year.

We can do this using an IF statement.

=IF(Date of purchase <= Prior Year End, nearest months to date, purchased the asset in the year)

If we purchase the asset in the year, we will use the date we purchased the asset, instead of using the reporting month.

logic

=IF(purchased before this year, 
nearest months to date, nearest months to date)
month of purchase - prior year end = days to date
days to date/average days in month = months to date
ROUND(months to date, decimal places) = nearest months to date

result

=IF(FALSE - not purchased before year, 
nearest months to date, nearest months to date)
18/04/2021 - 31/12/2020 = 334 days to date
334 days to date/30.5 = 10.95 months to date
ROUND(10.95 months to date,0 decimal places)= 7 months to date

formula

output

Step Three: Fully Depreciated

We must test if the asset is already fully depreciated. If so, we want to stop charging depreciation.

Fully Depreciated Column

To do this we shall add a column that signifies if this condition is TRUE or FALSE.

This column will check if the resulting depreciation produces a negative NBV.

logic

= (Cost - Brought Forward Depreciation - Depreciation charge) < 0
Depreciation_charge=Cost*Depreciation Rate*(nearest months to date/12)

result

Depreciation_charge = 1,000*20%*(11/12) = -183.33 
= (1,000 - 900 -183.33) < 0
= -83.33 < 0
= TRUE

formula

output

Charge Column

We can now can calculate depreciation and make sure it never results in a negative NBV.

The Charge Column will calculate depreciation based upon how many months have passed since purchased.

We can do this using an IF statement. 

=IF(Fully Depreciated?, Remaining depreciation, Depreciation for months to date)

logic & Result

=IF(Fully Depreciated, Remaining depreciation, Depreciation for months to date)
=IF(TRUE, 1,000 - 900, Depreciation for months to date)
=100

formula

logic & Result

=IF(Not Fully Depreciated, Remaining depreciation, Depreciation for months to date)
=IF(FALSE, Remaining depreciation, 1,000*(0.2/12)*11)
=IF(FALSE, Remaining depreciation, 92)
=92

And there you have it, we have a depreciation schedule that will update itself!