Depreciation calculation is not correct

In many jurisdiction the annual depreciation rate for specific asset classes is specified in taxation law, or at least that is the case in Australia. As such for a calculation to be at all usable it must achieve that, not some Manager invented approximation.

The maths to achieve it is not difficult, but for clarity lets start with the simplest case and the OP example ie $5000 initial value and 20% annual depreciation

Straight line depreciation
This is identical to simple interest with a negative interest rate. The formula to calculate multi-year depreciation is
Straight line Depreciation forumla

Substituting into this formula the asset book value can be calculate, shown below at yearly intervals for the next 5 years

Time in Years Book Value Interval depreciation
0 5000
1 4000 1000
2 3000 1000
3 2000 1000
4 1000 1000
5 0 1000

As well as calculating deprecation for multiple years it can also be used to calculate the straight line depreciation for a fraction of a year. Shown below is for 1/12 of a year intervals.

Time in Years Book Value Interval depreciation
0.0000 $5,000.00
0.0833 $4,916.67 $83.33
0.1667 $4,833.33 $83.33
0.2500 $4,750.00 $83.33
0.3333 $4,666.67 $83.33
0.4167 $4,583.33 $83.33
0.5000 $4,500.00 $83.33
0.5833 $4,416.67 $83.33
0.6667 $4,333.33 $83.33
0.7500 $4,250.00 $83.33
0.8333 $4,166.67 $83.33
0.9167 $4,083.33 $83.33
1.0000 $4,000.00 $83.33

Of particular note, the value at 1 year is exactly 20% independent of interval it is calculated over. Now moving on to:

Declining Value
This is identical to compound interest with a negative interest rate. The formula to calculate multi-year depreciation is:
Declining value forumla
Substituting into this formula the asset book value can be calculate, shown below at yearly intervals for the next 5 years

Time in Years Book Value Interval depreciation
0 $5,000.00
1 $4,000.00 $1,000.00
2 $3,200.00 $800.00
3 $2,560.00 $640.00
4 $2,048.00 $512.00
5 $1,638.40 $409.60

As well as calculating deprecation for multiple years it can also be used to calculate the declining value depreciation for a fraction of a year. Shown below is for 1/12 of a year intervals.

Time in Years Book Value Interval depreciation
0.0000 $5,000.00
0.0833 $4,907.88 $92.12
0.1667 $4,817.46 $90.42
0.2500 $4,728.71 $88.75
0.3333 $4,641.59 $87.12
0.4167 $4,556.07 $85.51
0.5000 $4,472.14 $83.94
0.5833 $4,389.74 $82.39
0.6667 $4,308.87 $80.87
0.7500 $4,229.49 $79.38
0.8333 $4,151.56 $77.92
0.9167 $4,075.08 $76.49
1.0000 $4,000.00 $75.08

Of particular note, the value at 1 year is exactly 20% independent of interval it is calculated over. Any calculation method which does not achieve this does not meet taxation regulation requirements.

1 Like

@Patch
thank you for clarification
I’m agreed with you

I think we can all agree that the Depreciation Calculation Worksheet in Manager is an interesting development but that in its current state does not meet many users requirements.

While reading through the various posts, I have to agree with Tut that it does what it says in the Guide and as such is not bugged/incorrect in any way.

I also agree with the many here who say that it is incomplete and could be improved greatly to cater for more methods of calculation but that is not the responsability of Tut or of anyone other than Lubos

Perhaps we should close this topic whilst awaiting further improvements along the lines outlined here by many

@lubos
I would like to suggest one thing
for those major changes, if we can have pre-discussion for ideas contribution to all interest parties
so, that manager can reduce rework
just my idea
thanks

Agreed. Likewise, the method is often specified.

While any method must follow legal mandates to be useful to a particular user, that does not mean Manager’s approach is an “inventented approximation” if it does not follow those mandates. It only means Manager follows a different method and, therefore, you must currently perform your own calculations if you want to be in compliance in a jurisdiction where Manager’s method is not permissible.

Let’s set aside your discussion of straight-line depreciation, since Manager does not currently support that method. The Guide plainly says so.

The flaw in your monthly illustration is that you used the formula with both annual rate and periods (duration) in years. While this was correct for your annual illustration, you get different results if you actually convert the notional annual rate to a daily rate and use daily periods. This is akin to changing the compounding period for compound interest. The real formula to use is:

OldBookValue x (1 - r/N) ^ n = NewBookValue,

where r is the notional annual depreciation rate, N is the number of periods in a year, and n is the number of periods over which you are calculating.

Going back to your example, the value at the end of a one year calculation period would be:

5000 x (1 - .2/1) ^ 1 = 5000 x .8 = 4000

This matches Manager’s book value if you calculate a single worksheet for a full year. Notably, however, the monthly values you showed are incorrect, even though they appear to lead to the correct annual value. For monthly depreciation (corresponding to your illustration), the value should have been:

5000 x (1-.2/12) ^ 12 = 5000 x .98333 ^ 12 = 5000 x .81735 = 4086.76

This is what Manager produces (within a fractional cent rounding difference) when 12 consecutive monthly worksheets are created. And for daily depreciation:

5000 x (1-2./365) ^ 365 = 5000 x .99945 ^ 365 = 5000 x .81869 = 4093.43

Note that to achieve this last result in Manager, you would need to create 365 worksheets, each for a one-day period. These calculations show that the more periods you divide your asset’s life into, the slower your book value will depreciate. This is the reverse of interest compounding, where the shorter the compounding period the higher the effective interest rate will be. Verification of this can be seen through a simple thought experiment. Suppose you depreciated a five-year asset with a single depreciation entry at the end of five years. The formula would be:

5000 x (1 - 1/1) ^ 1 = 5000 x 0 = 0

The point is that the depreciation rate in your original formula needs to be adjusted based on the length of the depreciation period used. More importantly, since most users are not going to change depreciation rates for their assets, depreciation compounding in Manager will be controlled by the time period selected for the worksheet.

Having written all that, I acknowledge there are many sources to be found on the internet with incomplete examples. Usually, they show calculations based only on annual depreciation periods and ignore monthly accrual or proration of depreciation expense. Most avoid the issue of adjusting rates to match periods. Guidance from many tax authorities also provides such inadequate examples, perpetuating misconceptions. There is no question this topic can be difficult to understand. I believe that is why some tax authorities require depreciation over standardized intervals instead of allowing daily calculations. And others mandate hybrid systems that effectively involve linear interpolation between widely spaced (usually annual) calculation points.

It seems ironic to me that the first depreciation calculation worksheet provided for Manager—a program most of us value for its simplicity—implements one of the hardest depreciation methods to understand and does not allow calculation of the most common form of depreciation (straight-line).

And I am now through with this topic, since I cannot use the worksheet in my jurisdiction.

I am not aware of any jurisdiction which specifies a “Notional” annual depreciation rate. In every jurisdiction and product instead an “Annual depreciation” is specified. It is the actual annual depreciation claimed which must be achieved. There may be some flexibility in how a business apportions the depreciation over a year particularly if they only report annually.

For each interval calculated, that defines the depreciation as (1-r/N) = (1-r * DurationInYears) that is a straight line approximation (correct only for straight line depreciation). It matches residual value depreciation only at N=1 ie for an interval of 1 year. However residual value depreciation is an exponential decay not a straight line so the formula is wrong for every other interval.

It is however what is documented in the guides and what Manger implements. As a result Managers worksheet only calculate the correct residual value depreciation for N=1 ie and interval of 1 year.

If you still doubt The logic, try getting Manager to calculate the residual value depreciation for a 5 year period (by entering dates 5 years apart into Managers depreciation worksheet) with the OP 20% depreciation, Manager will tell you it reaches $0.

Manager is using straight line depreciation maths to calculate residual value depreciation over durations other than one year. Or to put it another way, it is mixing up the simple interest and compound interest formulas. That is an error.

The solution is to update the documentation to prominently display something like:

“Notional depreciation rate” can also then be be replaced by “Annual depreciation rate”

Lubos then may or may not choose to extend the worksheet functionality at some time in the future.

:slight_smile: I took a while to get there.

@Patch, I disagree with many of the comments, conclusions, and recommendations in your last two posts. But I will not rebut them at this time. I will only say that I have collected numerous examples in test companies that validate the assertions I’ve made in this thread. And I stand by my statements that the worksheet functions as described in the Guide covering it.

What is clear, however, is that the automatic depreciation worksheet is difficult to understand, hard to use, and unforgiving. It does not support what I consider essential aspects of proper depreciation accounting, needs I shared with @lubos months ago, when it was first introduced. Most importantly, it does not yet support the most common methods of depreciation. So its current utility is extremely limited.

@lubos has assured me of coming improvements repeatedly, as recently as today. So until those are introduced, further discussion seems pointless.

In case it is of interest to others, I do my depreciation calculations in a separate spreadsheet available from here: Fixed Asset Program (Excel) | PDF | Debits And Credits | Book Value. This is a macro enabled Excel workbook with instruction book which is made available under a GPL (changed now to Creative Commons) licence. The workbook includes are error checking process that is applied before roll over into a new financial year.

Comprehensive reports can also be generated. Depreciation entries into Manager are done once each year (but could be monthly) through a journal with one line for each class of asset.

Cheers… Richard

There is now a separate Depreciation Entries tab in Manager.

But it shouldn’t be, depreciation worksheets have been around for decades (published by tax authorities) and none of them produce the difficulties being noted by users here because they work on “Actual Annual Depreciation” and not “Notional Annual Depreciation”.

From my perspective, Manager is attempting to do a two step process in one step.
First you calculate the annual depreciation value and second you distribute that value over the user’s required sub-periods - month, quarter.

Calculating annual depreciation on a sub-period basis (daily) lies at the base of users problems.

Then perhaps Lubos should be talking to the community at large instead of privately updating single user’s, otherwise acknowledgements like above creates the question, is the user being just a “user” on the forum or a de facto Manager representative.

As an aside - some Indian commentary: (text as copied)
DEEMING NOTIONAL DEPRECIATION AS DEPRECIATION ACTUALLY ALLOWED IS AGAINST WELL SETTLED LEGAL POSITION AND CAN BE CONSIDERED CONTRARY TO GENERAL LAW OF TAX ON REAL INCOME.

Neither, but a moderator sharing all information available for the benefit of all.

Agree
The jurisdictions I’m aware of specify the annual depreciation and how the annual depreciation must be calculated. For Manager to be usable in those jurisdictions it must achieve that requirement.

The maths to calculate depreciation over other time intervals is typically not taught in accounting classes. As a result many ways of distributing the depreciation over the year have been used. Examples include:

  • in twelve equal installments paid each month
  • in 4 equal installments paid each quarter
  • As a function of the continuous asset depreciation, calculated Monthly, Quarterly, yearly or any other time interval

In many jurisdictions these are all consistent with legal requirements. The problem from a software perspective is; supporting many equivalent but different solutions is lots of work for limited user benefit.

The existing implementation can be made consistent with most jurisdictions just by a documentation change, ie state it is designed to be used to calculate annual depreciation (not sub period which make up a year).

The maths to calculate depreciation at time intervals other than one year is relatively simple for straight line depreciation and residual value depreciation.

For straight line depreciation (ie prime value)
AssetValue(TimeInYears) = InitialAssetValue * ( 1 - AnnualDepreciation% * TimeInYears)

Which for the first year is
AssetValue(1) = InitialAssetValue * ( 1 - AnnualDepreciation%)

For Residual value depreciation at the end of the first year, the formula is the same
AssetValue(1) = InitialAssetValue * ( 1 - AnnualDepreciation%)

At every other time point however it is different. For example at the end of the second year it is
AssetValue(2) = InitialAssetValue * ( 1 - AnnualDepreciation%) * ( 1 - AnnualDepreciation%)
AssetValue(2) = InitialAssetValue * ( 1 - AnnualDepreciation%)^2

By the mathematical process of induction in can be shown at end of each year
AssetValue(TimeInYears) = InitialAssetValue * ( 1 - AnnualDepreciation%)^TimeInYears

By definition in residual value depreciation, the depreciation rate is proportional to the current book value. Mathematically that means residual value depreciation is an exponential decrease, and the above formula also applies for fractions of years.

The other way of understanding the process is to recognizing, residual value depreciation is identical to compound interest with a negative interest rate. Whereas straight line depreciation is identical to simple interest with a negative interest rate.

Managers “Depreciation Calculation Worksheet” could be readily extended to support specification of “Annual depreciation rate” and time intervals other than 1 year simply by calculating residual value depreciation using the exponential decay formula:

Depreciation(IntervalInYears) = BookValue * (1 - (1 - AnnualDepreciation%)^IntervalInYears))

Instead of the current straight line depreciation formula

To calculate IntervalInYears in a manner which allows for leap years, the start and stop time can be converted to a time in years first. Ie

StartTime = StartYear + StartDay / DaysInStartYear

EndTime = EndYear + EndDay / DaysInEndYear

IntervalInYears = EndTime - StartTime

Summary
Using the residual value curve function to calculate depreciation ensures the annual depreciation is the amount the user entered independent of what intervals they divide the year into and leap years

Agreed. Full year depreciation values don’t change because of the number of days / periods in a year.
The full year depreciation value is attributed to the financial / tax year not the calendar year.
If the tax authority permits a fixed asset to be depreciated at 20%, then 20% of the applicable beginning of year book value (Prime or WDV) is charged to the P&L.

Say a fixed asset has a beginning of year book value of 5,000, therefore at 20 % the full year charge to the P&L is 1,000 for that financial / tax year and that charge doesn’t change because the year has 360 / 370 days or 12 /13 periods, as the depreciation rate relates directly to the beginning of year book value for the financial / tax year.

The problem with the current worksheet is that it mixes full year depreciation with pro rata year depreciation (acquisition/disposal) by using the same daily formula base, hence the distortion created when the worksheet is used for non yearly depreciation periods, where it accumulates to a yearly value not matching the full year value.

Or in other words, currently a user can’t create a full year worksheet as well as 12 monthly worksheets, and end up with the same financial / tax year result, where they should be able to.

We need to find a jurisdiction where that is specified. To be exact where the depreciation is specified as per financial year not per annum.
They are identical in jurisdictions where the financial year starts on 1 Jan.

The difference is Most pronounced in jurisdictions (such as Australia) where the financial year starts on 30 Jun. To illustrate what the difference means for the 20% residual value depreciation example

FY end Days in yr Day(30 jun) Year+Day(30 jun)/DaysInYear FY FY length 20% calendar yr depreciation
30/06/17 365 181 2017.49589
30/06/18 365 181 2018.49589 2017/2018 1.0000000 20.0000%
30/06/19 365 181 2019.49589 2018/2019 1.0000000 20.0000%
30/06/20 366 182 2020.49727 2019/2020 1.0013773 20.0246%
30/06/21 365 181 2021.49589 2020/2021 0.9986227 19.9754%
30/06/22 365 181 2022.49589 2021/2022 1.0000000 20.0000%
30/06/23 365 181 2023.49589 2022/2023 1.0000000 20.0000%
30/06/24 366 182 2024.49727 2023/2024 1.0013773 20.0246%
30/06/25 365 181 2025.49589 20/24/2025 0.9986227 19.9754%

So when doing the end of financial year books in a leap year we are slightly further through the calendar year, and the following year we are sightly less through the calendar year. The depreciation differences (on a exponential curve) exactly balancing out.

If there are jurisdiction which both specify a financial year depreciation (not per anum) and have their financial year ending on other than 1 jan, then the solution is for Manager business setting to allow specification of the start of the financial year. IMO doing so would be a good idea as that would allow display useful periods such as pay this financial year etc.

The alternative is to insist Manager only calculates residual value depreciation per year. And leave the user to arbitrarily distribute the annual depreciation over the year in what ever fashion they want. This latter approach really only requires a documentation change, and retaining the current program code.

An alternative way of dealing with leap year is to not depreciate assets on 29 Feb.
That way every year has 356 days depreciation is done on, independent of leap years and when the financial year starts. So every day (except for 29 Feb) is 1/365 of a year.

The alternative is every day in a (financial) leap year is 1/366 of a year.

Not sure what is the easiest to implement, nor what some jurisdiction legislation necessitates.

@Patch, you are overthinking it, depreciation is not that complex as depreciation rates are expressed in simple (20%) and not compound (20% p.a.) percentage terms as the rates only relate to a fixed asset’s opening balance.

Firstly, the financial / tax year is the period between the first day and the last day, examples being Jan 1 to Dec 31, Apr 1 to Mar 31 and Jul 1 to Jun 30.

The financial / tax year definition isn’t changed by tax authorities because of a leap year.

Secondly, the depreciation rate is applied to the opening year’s balance, unrelated to time dimensions. The basic formula is - year’s opening balance x depreciation rate.

Transfer 20% of the apples in the cool room to the shop.
Transfer 20% of the year’s opening balance to the P&L.

Even if you add a time dimension to the formula the basic result doesn’t change:

0000000 Bug 2a

The same formula is applicable to both prime and diminishing methods, just the opening balance changes

Prime
2019 > 5000 x 20% = 1000
2020 > 5000 x 20% = 1000

Diminishing
2019 > 5000 x 20% = 1000
2020 > 4000 x 20% = 800

Even with acquisitions and disposals the basic formula doesn’t change, once the per financial / tax year figure is calculated you then pro rata that for the period owned.

Therefore, any worksheet needs to have the basic formula (per financial / tax year) at its core otherwise you end up with Accounting Depreciation rather than Taxation Depreciation. Anyhow, that’s how the text books have always explained it.

1 Like

Agree
For residual value depreciation, the depreciation for one year is given by that formula. Look carefully at that formula, it does not contain a continuous time variable. It can only be used to calculate the depreciation at one year intervals.
image

To calculate residual value at any other time point, the definition of Residual Value depreciation can be used: Which is the depreciation rate is proportional to the residual value.
That relationship mathematically defines an exponential depreciation curve:
image

The maths is a little more complicated and typically not taught in accounting courses.

The advantage of using it is:

  • The change is trivial given Managers current implementation, it literally involves changing one line of code. No change is required to the user interface.

  • The change would result in no change to the calculated depreciation for 1 year intervals, so any meaningful calculations which can be done with the current implementation can still be done in an identical fashion.

  • The difference is the user would be able to divide the year into any intervals they wanted (months, weeks, quarters, thirds or full year blocks and still get an identical depreciation over a full year (within the limits of rounding errors).

Note this approach is different to methods often used for hand calculated residual value depreciation.

Manual Calculation Approximations
When calculating residual value depreciation by hand the initial formula is used to calculate the annual residual value depreciation. Most jurisdictions only report depreciation annually, so the depreciation can be arbitrarily distributed over the year and still be compliant. Typically quantities easily calculated by hand are used for example

  • 12 equal amounts allocated monthly

  • 4 equal amounts allocated quarterly

  • 2 equal amounts allocated each half year

  • Straight line approximation (typically only used in the year an asset is purchased or disposed of to estimate the part year depreciation to a financial year boundary).

From a program perspective the important thing to realize is:

  • All of these methods while compliant with most jurisdictions annual reporting requirements are arbitrary and different. Each would need to be explicitly coded to be directly supported.

  • All can be partly supported by a using the depreciation worksheet to calculate the annual depreciation then arbitrarily dividing it and distributing it throughout the year in any manner the user desires, just as they have in the past.

@Brucanna
I agree Days can be used to linearly scale to a year on the x (time) axis.
Where I believe my understanding of the underlying mathematical process differs from yours is

That is the the case for straight line depreciation.
Residual value depreciation like compound interest depends on the assets current (residual) value so follows an exponential curve.

A continuous time variable can be added to straight line depreciation or residual value depreciation. The formulas are different when calculating the y (depreciation) axis. Using linear scaling of the annual depreciation to calculate depreciation of other time intervals is only correct for straight line / prime value / simple interest depreciation.

Nor does tax regulation, it only refers to the per financial / tax year depreciation rates.

This topic is not about continuous time variable nor your introduced “residual value” concepts.

In closing, “depreciation” is not “interest” therefore calculations between the two aren’t comparable. Depreciation represents a potential write down of an asset, whereas with interest it is an independent addition to an asset.

3 Likes