https://doi.org/10.53656/math2021-5-4-exs

2021/5, стр. 490 - 502

EXCEL’S CALCULATION OF BASIC ASSETS AMORTISATION VALUES

Резюме: For Excel’s calculation of basic (fixed) assets amortisation values, the investigated economic and mathematical foundation with required values and their relations were used. The investigated and introduced theory is adapted to Excel calculations of fixed assets amortisation based on today’s needs. All values for Excel’s calculations are sorted into input and output values, and input to main and nested calculations. Two methods for calculating fixed assets amortisation were introduced using Excel. The first method is based on a linear decreasing function, \(G(t)=G_{0}(1-p t)\), which presents the simple interest calculation of the reduced equities for equal periodic amortisation values. The second method is based on the exponential degrading function, \(G(t)=G_{0} q t\), which presents a complex interest calculation of the reduced equities for periodic amortisation amounts in a descending sequence. The continuity of the introduced functions results from the continuity of: the life of the fixed asset, periodic amortisation, accumulated amortisation and non-amortized amount (residual value) of the fixed asset. It is particularly important to introduce dates with the exact time, for the beginning and the end of each amortisation period of the fixed asset. The theory for Excel’s calculation of the fixed asset output values for an arbitrary (planned or unplanned) term has also been explored and introduced. Such calculations relate mainly to terms of alienation, permanent damage, permanent loss of the process function of a fixed asset and periodic accounting reporting.

Ключови думи: asset; values; amortisation

Introductions

The aim of this paper is to introduce the researched economic, mathematical and Excel basis for calculating the amortization of fixed assets of a work process in accordance with International Accounting Standards (IAS). In the general theoretical part of the paper there are definitions of the basic concepts.

A fixed asset is tangible or intangible assets of work process that are not intended for the market. Examples of tangible assets are: buildings, ships with a carrying capacity of over 1000 GRT \({ }^{1)}\), motor vehicles, various equipment and machinery, etc. Intangible assets include: investments in research and development, patents, licenses, copyrights, work promotions, goodwill, etc. The book value of a fixed asset is the purchase value increased by costs until the date of its introduction into the work process.

Depreciation of a fixed asset is the gradual loss of its use value in the predicted time interval due to its wear, stagnation or obsolescence. Thus, the amortization of a fixed asset depends on: the intensity of its consumption, the passage of time and technological progress. International Accounting Standards prescribe annual amortization rates by fixed asset category.

For some fixed assets, the annual amortization rates for 2018 have the prescribed amounts:

(1) Buildings and vessels with a carrying capacity exceeding 1 000 GRT, \(5 \%=0.05\)

(2) Basic and passenger cars, \(20 \%=0.2\)

(3) Intangible assets, vehicles, mechanization equipment, etc., \(25 \%=0.25\)

(4) Computers and computer equipment, software and computer networks, \(50 \%=0.5\)

They are prescribed; annual, semi-annual, quarterly, monthly, weekly and daily time intervals, i.e. periods expressed in days, hours, minutes and seconds, depending on the type of fixed asset and its shelf life. The known regular relations between the stated time intervals are: 1 year \(=365\) days, 1 half-year \(=182.5\) days \(=182\) days and 12 hours, 1 quarter \(=91.25\) days \(=91\) days and 6 hours, 1 month \(=30.41\) ( 6) days \(=30\) days and 10 hours. Some term (T) of the time interval has a date and time expressed in hours minutes and seconds, or in Excel form, \(\mathrm{T}=\mathrm{dd} . \mathrm{mm} . \mathrm{yyyy}\) \(\mathrm{hh}: \mathrm{mm}: \mathrm{ss}\). The length of the fixed asset’s useful life is determined by the number of amortization periods from the date of introduction into the work process. The number of amortization periods of a fixed asset depends on the periodic amortization rate. Among other things, IASs prescribe 1 year as the shortest useful life and the lowest carrying amount of a fixed asset expressed in US dollars. Based on IAS, the amortization rate of a fixed asset is inversely proportional to its useful life. To extend the useful life of a fixed asset, it is allowed to reduce its amortization rate. To shorten the useful life of a fixed asset, it is allowed to increase its amortization rate to twice its amount.

1. Old methods of calculating the amount of depreciation of fixed assets

There are mainly two methods of calculating the amortization of fixed assets, known as: (1) Linear method and (2) Functional method.

According to the Linear Method, the calculation of amortization is mostly annual, with equal annual amounts of amortization over the life of the fixed asset. In this case, the basis for calculating amortization is the carrying amount of the fixed asset for all amortization periods. For some fixed assets, periodic residual values of the fixed asset are used as the basis for calculating amortization.

According to the Functional Method for some fixed assets, the basis for calculating amortization is: number of products, volume of production, degree of capacity utilization, number of kilometres travelled, volume of transport performed, number of working hours, etc. For the calculations, mainly a calculator and a simple interest account with annual amortization periods were used. The date of introduction of the fixed asset into the work process is the first day of the month of the current year in relation to the regular term from the previous month. The term of accounting reporting on the amounts of amortization of fixed assets is December 31 of the current year. These methods, based on today’s needs, do not have an appropriate economic and mathematical basis for calculating the amount of quantities related to the amortization of fixed assets. The authors of the paper investigated and introduced two new methods with a new economic and mathematical basis, using Excel to calculate the amount of amortization of fixed assets.

2. Economic and mathematical basis for Еxcel’s calculation of the amount of depreciation of fixed assets

The authors of the paper investigated and introduced a new economic and mathematical basis, and Excel-based model, for calculating the amortization of fixed assets.

The introduced values of the economic base are: book value of fixed assets, annual amortization rate, number of amortization periods shorter than 1 year, length of one amortization period, periodic amortization rate, periodic unamortized amount of fixed assets, periodic amortization amount, accumulated amortization amount, start dates and the end of each amortization period of the fixed asset.

The mathematical basis for equal amounts of amortization of fixed assets is presented by a linear decreasing (descending) function and a simple interest calculation in the form \(G(t)=G_{0}(1-p t)\) for \(t \in\left[0, t_{1}\right]\) (Figure 2.1.). The function \(G(t)\) represents the non-amortized value of the fixed asset, \(G_{0}\)– the book value, \(p\)– the periodic amortization rate and \(t\)-t – the number of the amortization period of the fixed asset.

The mathematical basis for the decreasing series of amortization of fixed assets is presented by an exponential, decreasing function and a complex interest calculus in the form \(G(t)=G_{0} q^{t}\) for \(t \gt 0\) and \(0 \lt q \lt 1\) (Figure 2.1.) The function \(G(t)\) represents the non-amortized value of the fixed asset, \(G_{0}\)– the book value, \(q\)– the periodic amortization factor and \(t\)– the number of the amortization period of the fixed asset.

Adequate calculations in MS Excel are derived from the economic and mathematical basis for the amortization of fixed assets; equal periodic amounts of amortization and amounts that are in descending order. During the calculation, it is not allowed to change the amortization rate of the fixed asset. Depreciation of all fixed assets is an intangible cost of each registered activity and affects the balance sheet and income statement.

This is followed by the introduction of the first new method for Excel calculation of the amount of quantities for equal amounts of amortization of fixed assets.

3. Excel calculation of the amount of outputs for equal amounts of depreciation of fixed assets

In the calculations, as a data processor, MS Excel will be used. The main parameters and formulas in the spreadsheets are: default values of input data, introduced formulas of nested values and introduced formulas for output values of amortization of fixed assets.

Default input data:

(1) Carrying amount of the fixed asset, \(G_{0}\)

(2) Annual amortization rate, \(p_{1}\)

(3) Lengths of equal amortization periods in days, \(D\)

(4) Amortization start date of the fixed asset, \(T_{0}\) in format dd.mm.yyyy hh: mm: ss

(5) Coefficient of correction of the lifetime of total amortization of fixed assets, \(0 \lt k \leq 1\)

The correction coefficient \(k\) for the duration of the total amortization of the fixed asset is determined by the percentage rate \(P(0 \% \leq P \lt 100 \%)\), where \(k=1-P\).

The required derivative values are:

(1) Number of amortization periods in the year, \(m=\tfrac{365}{D}\)

(2) Periodic, relative amortization rate, \(p=\tfrac{p_{1}}{m}\)

(3) Useful life of the fixed asset of its total amortization, \(t_{1}=\tfrac{1}{p}\) as a consequence of \(G(t)=\mathrm{G}_{0}(1-p t)=0\) and \(t=t_{1}\) (Figure 2.1.)

(4) Planned amortization life of fixed assets, \(t_{2}=t_{1} k\) for \(t_{2} \leq t_{1}\)

(5) Term of completion of the planned duration of amortization, \(T_{t_{2}}=T_{0}+D . t_{2}\) (6) Term of end of total amortization duration, \(T_{t_{1}}=T_{0}+D \cdot t_{1}\)

Required output values are:

(1) Terms of amortization calculation, \(T_{i}=T_{0}+D . i\) for \(k=1\)

\(i \in\left\{0^{+}, 1,2, \ldots, t_{1}\right\}\); for \(0 \lt k \lt 1 i \in\left\{0^{+}, 1,2, \ldots, t_{2}\right\}\)

(2) Unamortized amount (residual value) of the fixed \(G_{i}=G_{0}(1-p i)\)

(3) Amount of amortization at the end of the current \(a_{i}=G_{i-1}-G_{i}\)

(4) Amount of accumulated amortization until the end of the current period, \(A_{i}=i \cdot a_{i}\)

Fixed asset amortization periods have ordinal numbers, \(i \in\left\{0^{+}, 1,2, . ., t_{2}, \ldots t_{1}\right\}\), where \(t_{2}\) and \(t_{1}\) are real numbers in decimal notation, and \(0^{+}\)is the right neighbourhood of 0. Thus, a linear, decreasing function, \(G_{t}=G_{0}(1-p t)\) is continuous, for \(t \in\left[0^{+}, t_{1}\right]\) and \(G(t) \in\left[G_{0}, 0\right]\). For automatic expression of ordinal numbers, \(i \in\left\{0^{+}, 1, \ldots, t_{1}, \ldots, t_{2}\right\}\), the period of amortization of the fixed asset, in the creation of the example it is used Excel function \(I F\) with introduced conditional transitions.

During the amortization in the term \(T_{\mathrm{z}}\) the fixed asset can be alienated or for some reason permanently damaged, with the need to calculate the output values for the term \(T_{\mathrm{z}}\). Thus, the term \(T_{\mathrm{z}}\) can be any arbitrary, unplanned or planned financial reporting term for calculating the amortization output of a fixed asset.

The authors of the paper investigated and introduced the economic and, mathematical basis, and MS Excel model, for calculating the amount of output amortization of fixed assets for the term \(T_{z}\).

For the term \(T_{z}\) represented in format dd.mm.yyyy hh:mm:ss from , it follows:

(1) Number of fixed asset amortization period, \(z=\tfrac{T_{z}-T_{0}}{D}\) (2) Integer number of amortization periods, \(Z=\operatorname{int}(z)\),

(3) Unamortized value of fixed asset, for \(t=Z, G_{Z}=G_{0}(1-p Z)\)

(4) Unamortized value of fixed asset, for \(t=z, G_{z}=G_{0}(1-p z)\)

(5) Accumulated amortization amount, for \(t=z, A_{z}=G_{0} p z\)

(6) Amount of amortization for the current period, \(a_{z}=G_{z}-G_{z}\)

Example 1

The book value of one fixed asset is \(11,500 \mathrm{KM}^{2)}\), and the beginning of its working process is 04/17/2018 09:28:15. The annual amortization rate is \(15.2 \%\) linearly on the carrying amount of the fixed asset. The length of the amortization period is 1 year. The correction coefficient is 0.825 in relation to the useful life of the total amortization of the fixed asset. Using MS Excel, calculate the periodic amounts of output values of amortization of fixed assets and especially for the planned period of financial reporting, 31.12.2020 23:59:59.

Default input sizes:

\(G_{0}=11,500 \mathrm{KM}\) (book value of fixed assets),

\(p_{1}=15,2 \%=0,152\) (annual amortization rate of fixed assets),

\(T_{0}=17.04 .2018\) 09:28:15 (date of the beginning of the working process of the fixed asset),

\(D=1\) year = 365 days (lengths of equal amortization periods),

\(k=0.825\) (total amortization life adjustment coefficient)

\(T_{\mathrm{z}}=31.12 .2020\) 23:59:59 (planned term of accounting reporting).

The introduced economic and mathematical basis was used for Excel calculations of output values of amortization of fixed assets (Figure 1. and Figure 2.). Figure 1. presents spreadsheets 1, 2 and 3 from the used Excel workbook. Figure 2. shows the spreadsheet 4 and histogram overview of the output values of amortization of fixed assets. The cells, containing the input values are highlighted in yellow, and cells, containing the output data amounts are in green. Users of the spreadsheet can change the only the input data (yellow cells).

This is followed by the introduction of another new method of Excel calculation of the amount of output quantities with the amounts of amortization of fixed assets in descending order.

4. Excel calculation of the output data with the amounts of depreciation of fixed assets in decrease

The mathematical basis for Excel’s calculation of the amount of amortization in descending order is a descending exponential function, \(G(t)=G_{0} q^{t}\) for \(\mathrm{t} \gt 0\) and \(0 \lt q \lt 1\) which presents a complex interest account for reduced principal.

Applied to the continuous and periodic amortization of fixed assets, the names and designations of quantities are derived from \(G(t)=G_{0} q^{t}\) :

(1) Amount of book value of fixed asset, \(G_{0}\)

(2) Amount of unamortized cost of fixed asset, \(G(t)\)

(3) Periodic amortization factor of the fixed asset, \(a\)

(4) Elapsed amortization time for periods \(i \in\left\{0^{+}, 1, \ldots\right\}, t\)

The previous quantities are the basis for the introduction of other required quantities and their relations. To calculate the required output values, in Excel, main and intermediate calculations will be used.

Major data:

(1) Carrying amount of the fixed asset, \(\mathrm{G}_{0}\)

(2) Start time of the asset depreciation, \(T_{0}\) in format dd.mm.yyyy hh:mm:ss,

(3) Annual depreciation rate of fixed assets, \(p_{1}\)

(4) Duration of the depreciation period expressed in days, \(D\)

(5) Correction coefficient of the asset life, \(k\).

The correction factor \(k\) for the life of the fixed asset depends on the percentage rate \(P(0 \% \leq P \lt 100 \%)\). The correction factor \(k\) may have the following values:

(1) \(k=1\) (for the optimal life of the fixed asset),

(2) \(k=1-P\) (to shorten the optimal life of the fixed asset), and

(3) \(k=1+\mathrm{P}\) (to extend the optimal life of the fixed asset).

To extend the optimal life of the fixed asset, the percentage rate \(P\) may have the value \(P \gt 100 \%\), if the fixed asset is in the planned process function.

Intermediate calculations:

(1) Number of fixed asset amortization periods in a year, \(m=\tfrac{365}{D}\)

(2) Annual amortization factor of fixed assets, \(q_{1}=1-p_{1}\)

(3) Periodic amortization factor of fixed asset, \(q=q_{1}^{\tfrac{1}{m}}\)

(4) Periodic amortization rate of fixed assets, \(p=1-q\)

(5) Optimal service life of fixed assets, for \(k=1, t_{1}=\tfrac{1}{p}\)

(6) Planned life of the fixed asset, for \(0 \lt k \lt 1 \Leftrightarrow t_{2} \lt t_{1}\), for \(k=1 \Leftrightarrow t_{2}=t_{1}\) and for \(k \gt 1 \Leftrightarrow t_{2} \gt t_{1}, t_{2}=t_{1} \cdot k t_{2}=t_{1} \cdot k\)

(7) Term of optimal end of life of fixed assets, \(T_{t_{1}}=T_{0}+t_{1}\)

(8) Term of the planned end of the life of the fixed asset, \(T_{t_{2}}=T_{0}+t_{2}\)

The main data is used for intermediate Excel calculations and, based on the introduced formulas, the output data calculations is performed.

Required output data with introduced formulas:

(1) Terms of fixed asset amortization period, \(T_{i}=T_{0}+i . D\) for \(i \in\left\{0^{+}, 1, \ldots, t_{2}\right\}\)

(2) Amounts of unamortized part of fixed assets, \(G_{i}=G_{0} \cdot q^{i}\)

(3) Amounts of accumulated amortization of fixed assets, \(A_{i}=G_{0}\left(1-q^{i}\right)\)

(4) Amounts of amortization of fixed assets in the current period,

\(a_{1}=G_{i-1}-G_{i}\) For any term \(T_{\mathrm{z}}\) from the interval \(T_{0} \leq T_{z} \leq T_{t_{2}}\) the following values were investigated and introduced:

(1) Any term as input quantity, \(T_{\mathrm{z}}\) presented in dd.mm.yyyy hh:mm:ss

(2) Interval length \(T_{\mathrm{z}}-T_{0}\) in days as decimal number, \(z=\tfrac{T_{2}-T_{0}}{D}\)

(3) Integer units of number(4) Unamortized value of fixed asset, for \(z, Z=\operatorname{int}(z)\) \(i=Z, G_{Z}=G_{0} \cdot q^{Z}\)

(5) Unamortized value of fixed asset, for \(i=z, G_{z}=G_{0} \cdot q^{z}\)

(6) Amount of accumulated amortization of fixed assets, for \(i=z\),

\(A_{z}=G_{0}\left(1-q^{z}\right)\) (7) Amount of amortization of fixed assets for the current period,

\(a_{z}=G_{z}-G_{z} a_{z}=G_{z}-G_{z}\) The values of input data from Example 1 will be used for Excel calculations, with the aim of comparing the output values for the two introduced calculation methods.

Example 2

The book value of one fixed asset is \(11,500 \mathrm{KM}\), and the beginning of its working process is 04/17/2018 09:28:15. The annual amortization rate is \(15.2 \%\) exponentially in relation to the book value of the fixed asset. The length of the amortization period is 1 year. The correction factor is 0.825 in relation to the life of the optimal amortization of the fixed asset. Using Excel, calculate the periodic amounts of output values of amortization of fixed assets and especially for the planned period of financial reporting, 12.31.2020 23:59:59.

Default input values:

\(G_{0}=11,500 \mathrm{KM}\) (book value of fixed assets),

\(p_{1}=15.2 \%=0.152\) (annual amortization rate of fixed assets),

\(T_{0}=04.17 .2018\) 09:28:15 (date of the beginning of the working process of the fixed asset),

\(D=1\) year = 365 days (lengths of equal amortization periods),

\(k=0.825\) (optimal amortization life correction coefficient), and

\(T_{\mathrm{z}}=12.31 .2020\) 23:59:59 (planned term of financial reporting).

The introduced economic and mathematical basis was used for Excel calculations of the output values of amortization of fixed assets. The Excel spreadsheets and a histogram overview of the amount of output values of amortization of fixed assets are shown of Figure 3 and Figure 4.

The cells, containing the input values are highlighted in yellow, and cells, containing the output data amounts are in green. Users of the spreadsheet can change the only the input data (yellow cells).

Conclusion

The choice of method for calculating the output values of amortization of fixed assets is made by the legal entity as their owner. The authors of the paper recommend the application of the first method of Excel calculations for the amortization of buildings, furniture, white goods, intangible fixed assets and the like. By applying this method, the useful life of the fixed asset, in addition to the amortization rate \(p\) is affected by the correction coefficient \(k\) of the amortization life \(t_{2}\) and the unamortized amount \(G\left(t_{2}\right) \geq 0\) of the fixed asset. \(k=1\) relates to the useful life of total amortization \(t_{2}=t_{1}\) and the unamortized amount of fixed assets \(G\left(t_{2}\right)=G\left(t_{1}\right)=0\). The values of \(k\) between 0 and \(1(0 \lt k \lt 1)\) mean that the planned amortization period is \(t_{2}\) and the unamortized amount of fixed assets \(G_{t_{2}} \gt 0\) ). In this case, the amounts of periodic amortization are equal for equal periods.

By applying the second method, the periodic amounts of residual values and the periodic amounts of amortization of the fixed asset decrease exponentially. The amortization life \(t_{2}\) of a fixed asset depends on its correction coefficient \(k \gt 0\) in relation to the optimal amortization life \(t_{1}\), for \(k=1\) and \(t_{2}=t_{1}\). By applying this method, the unamortized amount of a fixed asset always has a value greater than zero \(G_{t_{2}} \gt 0\). If the fixed asset does not have a planned use function, then a special commission writes off its unamortized amount. If the fixed asset has a planned use function, then the commission determines the new amount of its book value. This method is useful for Excel calculations of amortization output values of all fixed asset categories.

In particular, it is important that the researched and introduced economic and mathematical basis for Excel’s calculation of the output values of amortization of fixed assets provides results for any planned or unplanned term \(T_{\mathrm{z}}\) from the life of the fixed asset.

The introduced Excel calculation could be used also:

(1) After the alienation of the fixed asset;

(2) After permanent damage;

(3) After an unplanned loss of use function;

(4) For periodic financial reporting; and

(5) For unplanned reporting of fixed asset amortization amounts.

Thus, the introduced two new methods of Excel calculations of the amount of output values of amortization of fixed assets, can mainly meet today’s needs of users.

NOTES

1. Gross register tonnage.

2. Bosnia and Herzegovina convertible marks.

REFERENCES

Alastair Day, 2010. Mastering Financial Mathematics in Microsoft Excel: A Practical Guide for Business Calculations. UK: Pearson Intl. \(2{ }^{\text {nd }}\) edition [July 17, 2010].

Kothari, J. & Barone, E., 2012. Finansijsko računovodstvo – Međunarodni pristup, Data Status. Srbija.

Kapić, J., 2010. Računovodstvo. Sarajevo: Ekonomski fakultet.

Jahić, M., 2003. Finansijsko računovodstvo, Sarajevo: Zavod za računovodstvo i reviziju Federacije Bosne i Hercegovine.

Rešić, S. & Mešanović, S., 2017. Uvod u finansijsku matematiku. Tuzla.

Benninga, S., 2010. Principles of Finance with Excel. UK: Oxford University Press, \(2^{\text {nd }}\) edition [September 24, 2010].