This fixed asset register template can be used to compile a complete fixed assets register which incorporates an unlimited number of classes and categories; facilitates recording additions, disposals, revaluations & impairments; automatically calculates depreciation charges; calculates tax values and calculates deferred tax balances. Only accommodates depreciation calculations on a straight-line basis.
All Excel template prices are once-off, there are no annual or monthly fees for our Excel-based products
Full AccessBuy the full versions of our Excel templates to get the Excel file with no passwords and no protected cells
100+ Countries Our global customer base consists of customers in more than 100 countries on 6 continentsI must say that these templates are a work of genius.
- Anton F - United Kingdom
I have been using your templates for a few years and it changed my whole life! You are such a perfectionist! Just brilliant!!
- Nigel C - South Africa
This is exactly what I am looking for! Thanks very much for your great products! Very much appreciated.
- Jeroen V - Netherlands
Thanks for a great product!
- Julie P - Canada
Your products have simplified the basics of accounting for small businesses who just start out and entrepreneurs, I absolutely love it. As my business grows I will definitely purchase more products from you.
- Shanelle C - South Africa
Thank you so much for your great products & all your help!
- Sue M - United States
Thank you and your formulas are amazing!
- Simphiwe M - South Africa
That is excellent, thank you very much for an extremely well designed excel sheet.
- Dwayne F - United States
This comprehensive template enables users to compile a complete fixed assets register which incorporates an unlimited number of fixed asset classes and categories; facilitates recording additions, disposals, revaluations and impairments; automatically calculates monthly and year-to-date depreciation charges; calculates tax values based on user defined tax codes; calculates deferred tax balances and includes a comprehensive automated journal report. The template has been designed specifically for the requirements of IFRS but can also be used to compile a simple fixed asset register which is only based on historical costs.
Note: This template only accommodates depreciation calculations on a straight line basis. If you therefore need to calculate depreciation on a reducing balance or other depreciation basis, the template will unfortunately not be suitable for your requirements.
The following sheets are included in this template:
Set-up - the input cells on this sheet enable you to customize the template for your business. You can enter your business name, select a year end period, specify an income tax percentage, maintain the default fixed asset classes, maintain the default fixed asset categories and maintain the default tax codes. A complete list of user input error codes are also included on this sheet for information purposes.
Assets - this sheet enables users to create a unique asset code for all fixed assets and the columns with the light blue column headings contain all the comprehensive, automated calculations that form part of the fixed asset register. User input is limited to entering an asset identification number, asset description, asset type, asset category and tax code. The period for which the fixed asset register is compiled is determined by the review date that is specified at the top of the sheet.
Transact - all asset transactions need to be recorded on this sheet. Asset transactions include acquisitions, revaluations and disposals. User input is limited to entering the transaction date, transaction type, asset number, supplier, document number, transaction amount, asset lifetime, residual value and proceeds on disposal. The columns with light blue column headings contain the formulas that form the basis of all the calculations that are required in order to produce a comprehensive, accurate fixed asset register.
Category - this sheet contains a fixed assets summary which is based on the fixed asset categories that are created on the Set-up sheet. No user input is required on this sheet and 30 fixed asset categories are accommodated. Additional fixed asset categories can be added by simply copying the formulas in the last row into the required number of additional rows.
Class - this sheet contains a fixed assets summary which is based on the asset classes that are created on the Set-up sheet. No user input is required on this sheet and a maximum of 30 asset classes are accommodated.
Journals - this sheet contains an automated journal report for all fixed asset transactions. The journal report can be compiled on a monthly or year-to-date basis and is based on the account numbers that are specified in the asset category set-up on the Set-up sheet. The journal report accommodates 30 asset categories but additional categories can be added by simply copying the formulas in the last row into the required number of additional rows.
Template Set-up
The template can be customized for your business by editing the default business details, asset categories, asset classes and tax codes on the Set-up sheet. The business name that is entered in cell C5 is used as a heading on all the other sheets, the year-end month that is selected in cell C7 is used in order to calculate year-to-date balances and the income tax percentage that is specified in cell C9 is used to calculate deferred tax balances.
The template includes 6 default fixed asset classes which can be customized in the cell range from cell A13 to cell B18. The default asset class codes in column A and the default asset class descriptions in column B can be amended and you can add additional asset classes by simply inserting a new row between the first and last class codes, entering a new code for the asset class in column A and entering a description for the new class in column B.
Note: All new asset classes must be inserted above the "ZZ" asset class code and a maximum of 30 asset classes can be accommodated in the template.
The asset classes that are created on the Set-up sheet should be representative of the asset classes that are included on the financial statements. Some businesses however require more asset classes than what is disclosed on the financial statements in order to accommodate separate allocation in the business accounts. We have therefore created separate asset categories for this purpose.
The template includes 11 asset categories which can be customized in the cell range from cell A23 to cell K33. Each asset category contains a unique code in column A, is linked to an asset class in column B, contains a description in column C and contains an account number in each of the columns from column D to K.
The asset category codes can be in any format but we suggest using a combination of letters and numbers as illustrated with the default category codes. Each category that is created will be included separately on the Category sheet and needs to be linked to one of the asset class codes in cells A13 to A18 by selecting the appropriate asset class code from the list box in column B. The asset categories are therefore in fact sub classes of the main asset classes that are included on the financial statements.
The account numbers that need to be entered in columns D to K are included in the automated journal report on the Journal sheet. All fixed asset journals are therefore based on the asset categories that are created which means that a separate asset category is required for each fixed asset account group or depreciation cost centre. The accounts that need to be entered in each column are better described as follows:
Note: All new asset categories must be inserted above the "ZZ" asset category code. The template accommodates 30 asset categories on the Category sheet but you can add additional asset categories on this sheet by simply copying the formulas in the last row into the appropriate number of additional rows. An unlimited number of asset categories are therefore accommodated.
The template includes 10 default tax codes in the cell range from cell A38 to J47 but you can add as many additional tax codes as required. The tax codes are used to calculate the tax allowances that can be claimed for each fixed asset and the tax values at the beginning and the end of each financial year. These tax balances are then used in order to calculate the deferred tax balances and charges for each financial year.
A single alphabetical letter is used as the tax code in column A and a description for each tax code can be entered in column C. All the tax codes that are created on the Set-up sheet are available for selection in column F on the Assets sheet.
Note: You can create additional tax codes by inserting a new row anywhere between the first and last rows, entering a new tax code in column A and specifying the appropriate annual tax percentages.
Column B in the tax codes section contains a list box which enables users to specify whether appropriation needs to be applied in the tax allowance calculations for the particular tax code. If appropriation is applied (the "Yes" option is selected), it means that the tax allowance calculation will take the effect of partial years into account. If appropriation is not applicable (the "No" option is selected), the tax allowance will be based on a full year regardless of when an asset is acquired.
Example: An asset with a cost of 10,000 is acquired in December by a business with a February year-end. The tax code that is assigned to the asset is B which has "No" appropriation specified in column B and a tax percentage of 20% in year 1. A full year's tax allowance will therefore be written off in December (and each subsequent year) which means that the tax allowance for the first year would be 2,000.
If the tax code of the asset had been A (which has a "Yes" option in column B) and the tax percentage in the first year is also 20%, it means that the tax allowance in the first year would only be 333.33 (10,000 x 20% ÷ 12 x 2). This is because appropriation would apply and the tax allowance calculation will therefore only include 2 months.
The tax allowance percentages in column D to H are used to calculate the appropriate tax allowance amounts for the assets that are linked to the particular tax codes. You will therefore only be able to specify tax percentages for 5 years and if a longer period is applicable, the year 5 percentage will be used for all subsequent annual periods until an asset is fully written off for tax purposes.
Example: If a tax code is created for buildings and a tax allowance percentage of 2% is applicable, the year 1 to 5 tax percentages need to be entered as 2% and the year 5 percentage (which is 2%) will be applied to all the annual periods after year 5. This means that a tax allowance of 2% per year will be written off until the asset is fully written off after 50 years.
Note: The 5Y Total and AF columns contain formulas which should be copied for all new tax codes that are added to the Set-up sheet. It is imperative that these formulas are copied for all new tax codes otherwise some of the tax calculations may not be accurate.
The 5Y Total column contains the total percentage value of the tax allowance percentages that are entered in the Year 1 to 5 columns and will automatically be highlighted in red if the value exceeds 100%. The maximum percentage that can be written off on an asset is 100% - if you therefore specify tax allowances of more than 100%, only the tax allowances that make up the first 100% will be taken into account in determining the appropriate tax values of the asset. The AF column contains an appropriation value which is based on the selection in column B.
Input Error Codes
The input error codes at the bottom of the Set-up sheet are included for information purposes only and provide users with a reason for the error codes that may be encountered when entering transactions on the Assets and Transact sheets. These error codes are covered in more detail in the Error Codes section of these instructions.
Asset Set-up
Each individual fixed asset must be added to the Assets sheet by assigning an asset number to the fixed asset. The Assets sheet is in actual fact a comprehensive fixed asset register which contains all the individual assets that form part of the fixed assets register. All fixed asset transactions need to be recorded on the Transact sheet and the Category and Class sheets contains summaries of all the appropriate asset groups.
A unique asset number must be created for each fixed asset on the Assets sheet. You can use any asset numbering convention but we strongly recommend using a combination of letters and numbers as illustrated in our example data. We have used an asset numbering convention which starts with two letters followed by a hyphen and five numbers. The letters can be used to distinguish between different asset categories or classes.
Note: All fixed assets must be added to the Assets sheet by assigning a unique asset code to the particular asset. The acquisition of the asset then needs to be recorded on the Transact sheet after which the appropriate values will be reflected on the Assets sheet. Users therefore need to create asset codes for all their assets and record acquisition transactions for all assets on the Transact sheet.
All the columns on the Assets sheet with yellow column headings require user input while the columns with light blue column headings contain formulas which are automatically copied for each new asset that is added to the sheet. The following user input is required on the Assets sheet:
Asset Number - enter a unique asset number for each fixed asset in this column. Duplicate asset numbers may result in inaccurate calculations and it is therefore imperative that each fixed asset has a unique asset number. Similar assets or asset components can be distinguished by adding a "/1" and "/2" at the end of the asset number.
Asset ID Number - enter an asset identification number in this column. This number should preferably be applied to the asset in order to facilitate physical verification of assets and assist in identifying each fixed asset that is included in the fixed asset register.
Description - enter a comprehensive description of the asset in this column. The asset description should enable users to easily identify the asset and should assist users in distinguishing between similar assets.
Asset Type - select an asset type from the list box. This column assists in identifying leased assets and the list boxes in this column therefore include an owned and leased option.
Category - select an asset category from the list box in this column. All the asset categories that are created on the Set-up sheet are available for selection. Each asset needs to be linked to an asset category which is linked to an asset class in the Asset Category section on the Set-up sheet.
Tax Code - select a tax code from the list box in this column. All the tax codes that are created on the Set-up sheet are available for selection.
Note: All the columns on the Assets sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas that are included in calculated columns (the columns with a light blue column heading) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore enter a new transaction by simply entering an asset number in column A - the table will then automatically be extended to include the new asset number.
All the columns with light blue column headings contain calculations which are based on the review date that is entered in cell E2. The date that is entered in this cell therefore determines which transactions are included in the asset balance and depreciation calculations. It is therefore imperative that you enter the correct date in this cell when reviewing the fixed asset register.
The cell range from cell H2 to cell K2 displays the appropriate year-to-date (YTD) and month-to-date (MTD) from and to dates which are in effect based on the review date that is specified in cell E2. Note that all the template calculations are based on month end dates - even if you enter a review date that does not fall on a month end, the template calculations will still include all the transactions and depreciation calculations for the entire month.
The YTD periods are determined based on the year-end period that is selected in cell C7 on the Set-up sheet. All the YTD asset balances and depreciation calculations will therefore be calculated automatically based on the review date and the year-end period that has been specified.
Note: The review date makes it easy to roll the template forward or back for any subsequent or previous month and all the template calculations are updated automatically. You can therefore simply enter a new date in cell E2 and all the calculations on all the sheets in the template are automatically updated.
The Assets sheet contains 52 calculated columns which all have light blue column headings. The calculations in these columns are all based on the transactions that are entered on the Transact sheet and some of the calculations are for information purposes only while others form an integral part of the fixed asset register. We'll now briefly cover the purpose of each calculated column:
The following columns are included for information purposes only:
Error Code - if there is a problem with the input in any of the user input columns, an error code will be displayed in this column. Refer to the Set-up sheet for a description of each error code and to the Error Code section of these instructions for more information about the reason for the error code that is displayed.
Class - this column contains the asset class that the asset is linked to. The asset class is determined by the asset category that is selected in column E and each asset category is linked to a single asset class in the Asset Category section on the Set-up sheet.
Acquisition Date - the asset acquisition date is displayed in this column. After creating an asset number, an acquisition transaction needs to be recorded on the Transact sheet in order to record the acquisition of the asset. If an acquisition transaction has not been recorded for the particular asset, this column will contain an "add!" message.
Last Trn Date - this column contains the date of the last transaction that has been recorded for the appropriate asset. If no transaction has been recorded, the column will contain an "add!" message which indicates that an asset acquisition transaction still needs to be recorded.
Current Trn Date - this column contains the date of the last transaction for the asset that is dated before the YTD To Date which is displayed in cell I2. It therefore represents the last transaction that has been recorded before the current period end.
Last PY Trn Date - this column contains the date of the last transaction for the asset that is dated before the YTD From Date which is displayed in cell H2. It therefore represents the last transaction that has been recorded before the end of the previous financial period.
Last PM Trn Date - this column contains the date of the last transaction for the asset that is dated before the MTD From Date which is displayed in cell J2. It therefore represents the last transaction that has been recorded before the end of the previous month.
Disposal Date - if an asset has been disposed of, the date of disposal as per the Transact sheet will be displayed in this column. Note that a disposal transaction has to be recorded on the Transact sheet for all asset disposals.
Proceeds on Disposal - if an asset has been disposed of, the proceeds on disposal as per the Transact sheet will be displayed in this column.
Historical Cost - the historical cost of all assets that have been acquired before the end of the current financial period is displayed in this column. The historical cost is defined as the transaction amount of the acquisition transaction that is recorded on the Transact sheet for the particular asset.
Active Lifetime - the lifetime that has been recorded on the Transact sheet for the most recent transaction that is dated before the current period end is displayed in this column.
Active Res Value - the residual value that has been recorded on the Transact sheet for the most recent transaction that is dated before the current period end is displayed in this column.
Tax Cost - the tax cost of the asset is displayed in this column. The tax cost is the same as the historical cost of the asset. This column will only contain a value if the asset has been acquired before the current period end.
Current Tax % - the tax allowance percentage for the current period is displayed in this column. This percentage is dependent on the tax code that has been selected in column F and the annual tax percentages that have been defined for the particular tax code on the Set-up sheet. If a tax code has not been selected in column F, a "code?" message will be displayed in this column.
Elapsed Months PY - the value in this column indicates the number of months that have elapsed since the acquisition of the asset up to the end of the previous financial period. A value will only be displayed for assets that have not been disposed of before the end of the previous financial period.
Elapsed Months CY - the value in this column indicates the number of months that have elapsed since the acquisition of the asset up to the end of the current financial period. A value will only be displayed for assets that have not been disposed of before the end of the current financial period.
Elapsed Months PM - the value in this column indicates the number of months that have elapsed since the acquisition of the asset up to the end of the previous month. A value will only be displayed for assets that have not been disposed of before the end of the previous month.
Assets at Cost:
AC Opening Balance - the opening cost or gross carrying value of the asset at the beginning of the current financial period is included in this column.
Additions - if an asset has been acquired during the current financial period, the cost of the asset is included in this column. The cost of the asset is entered as the transaction amount when recording an acquisition type transaction on the Transact sheet.
Revaluations - if an asset has been revalued during the current financial period, the adjustment in the gross carrying value (or the cost of the asset if the asset has not been revalued previously) is included in this column. Note that this adjustment is only calculated based on the gross carrying value of the asset - if you want to recalculate the revaluation surplus or impairment, the Revaluation column in the Accumulated Depreciation section also needs to be taken into account.
Disposals - if an asset has been disposed of during the current financial period, the gross carrying value (or the cost of the asset if the asset has not been revalued previously) is included in this column as a negative value. This effectively means that the gross carrying value of the asset is removed from the fixed asset register.
Impairment - if an asset has been revalued during the current financial period at a value that is lower than the current net carrying value, the impairment amount is included in this column. Note that in some instances, a negative value may also be displayed in the Revaluations column which will be offset by a negative value in the Revaluations column in the Accumulated Depreciation section. This is simply because the impairment is calculated based on the net carrying value which therefore necessitates adjustments to the Cost and Accumulated Depreciation values in order to adjust the gross carrying value (the Cost section) to the correct values after revaluation.
AC Closing Balance - the closing cost or gross carrying value of the asset at the end of the current financial period is included in this column.
Accumulated Depreciation:
AD Opening Balance - the opening accumulated depreciation balance of the asset at the beginning of the current financial period is included in this column.
Depreciation - Cost - the year-to-date depreciation on the historical cost of the asset is included in this column. The amounts in this column are calculated on the Transact sheet. For revalued assets, only the portion of the depreciation that relates to the historical cost of the asset will be included in this column.
Depreciation - Revaluation - the year-to-date depreciation on the revaluation of the asset is included in this column. The amounts in this column are calculated by deducting the depreciation on the historical cost of the asset from the total year-to-date depreciation. Both of these amounts are calculated on the Transact sheet. If an asset has not been revalued, the value in this column will be nil.
Depreciation - Total - the total year-to-date depreciation on the asset is included in this column. All the amounts in this column are calculated on the Transact sheet.
Acc Depr - Revaluations - if an asset has been revalued during the current financial period, the accumulated depreciation as at the date of the revaluation is included in this column as a negative value. This is because we write off the accumulated depreciation to a revaluation reserve and this value together with the revaluation adjustment in the Cost section form the revaluation surplus for an asset.
Acc Depr - Disposals - if an asset has been disposed of during the current financial period, the accumulated depreciation as at the date of the disposal is included in this column as a negative value. This effectively means that the accumulated depreciation of the asset is removed from the fixed asset register.
AD Closing Balance - the closing accumulated depreciation balance of the asset at the end of the current financial period is included in this column.
Total Net Carrying Value:
Closing Carrying Value - the difference between the closing cost (or gross carrying value) of an asset and the closing accumulated depreciation balance is included in this column. The amounts in this column reflect the "book value" of an asset at the end of the financial period.
Revaluation Reserve:
RR Opening Balance - the amounts in this column reflect the revaluation reserve opening balances at the beginning of the current financial period.
Revaluation Surplus - if an asset has been revalued during the current financial period at a value that is higher than its net carrying value at the date of revaluation, the revaluation surplus is included in this column. Note that if an asset is revalued at a value that is less than its net carrying value, an asset impairment amount will be included in the Impairment Write-Offs column and the revaluation surplus will be nil.
RR Depreciation - Revaluation - the year-to-date depreciation on any revaluations that may have been made to an asset in the past is included in this column. This means that we effectively write-off the depreciation on revaluations against the revaluation reserve balance instead of allocating these depreciation amounts to the income statement.
RR Closing Balance - the amounts in this column reflect the revaluation reserve closing balances at the end of the current financial period.
Income Statement Items:
Impairment Write-Offs - if an asset has been revalued during the current financial period at a value that is lower than its net carrying value at the date of revaluation, the asset impairment amount is included in this column. Note that if an asset is revalued at a value that is higher than its net carrying value, a revaluation surplus will be included in the Revaluation Reserve section and the asset impairment amount will be nil.
Profit / (Loss) on Disposal - if an asset has been disposed of during the current financial period, the profit or loss on disposal as at the date of the disposal is included in this column. The profit or loss on disposal is calculated as the difference between the proceeds on disposal which is recorded on the Transact sheet and the net carrying value (book value) of the asset. If the asset that is sold has been revalued, any revaluation reserve that may exist on the date of disposal is also included in the profit or loss calculation.
Current Month Depreciation:
Cost - this column includes the depreciation on the historical cost of an asset for the current month. The appropriate month is determined by the review date that is specified in cell E2. The current month depreciation on historical cost is calculated on the Transact sheet. Note that this column will only contain a value if the historical cost of an asset has not been fully depreciated.
Revaluation - this column includes the depreciation on any revaluations that may have been performed on an asset and is calculated as the difference between the total depreciation for the current month and the depreciation based on the historical cost of an asset.
Total - this column includes the total depreciation of an asset for the current month. The appropriate month is determined by the review date that is specified in cell E2. The current month total depreciation is calculated on the Transact sheet. Note that this column will only contain a value if the gross carrying value (or historical cost for assets that have not been revalued) of an asset has not been fully depreciated.
Carrying Value at Historical Cost:
HC Opening Balance - this column includes the book value of an asset based only on historical cost calculations at the beginning of the current financial period.
HC YTD Movement - this column includes the YTD movement in the book value of an asset based only on historical cost calculations. All revaluations are ignored for the purpose of this calculation.
HC Closing Balance - this column includes the book value of an asset based only on historical cost calculations at the end of the current financial period.
HC MTD Movement - this column includes the MTD movement in the book value of an asset based only on historical cost calculations. All revaluations are ignored for the purpose of this calculation.
Tax Values:
TV Opening Balance - this column includes the tax value of the appropriate asset at the beginning of the current financial period. All tax value balances are calculated by deducting the appropriate tax allowances (based on the tax code that is selected in column F) from the tax cost (historical cost) of an asset.
TV YTD Movement - this column contains the YTD movement in the tax value of an asset. For most assets that have been acquired before the beginning of the financial period, these amounts will equal the tax allowances for the period. If an asset has been acquired during the current financial period, the YTD movement will equal the cost of the asset less the appropriate tax allowances for the period. If an asset has been disposed of during the current financial period, the YTD movement will equal the opening tax value.
TV Closing Balance - this column includes the tax value of the appropriate asset at the end of the current financial period. All tax value balances are calculated by deducting the appropriate tax allowances (based on the tax code that is selected in column F) from the tax cost (historical cost) of an asset.
TV MTD Movement - this column contains the MTD movement in the tax value of an asset. For most assets that have been acquired before the beginning of the current month, these amounts will equal the tax allowances for the period. If an asset has been acquired during the current month, the MTD movement will equal the cost of the asset less the appropriate tax allowance for the month. If an asset has been disposed of during the current month, the MTD movement will equal the tax value at the beginning of the month.
Deferred Taxation:
DT Opening Balance - this column includes the deferred tax balance at the beginning of the current financial period. Deferred tax balances are calculated as the difference between the carrying values at historical cost and the tax values multiplied by the appropriate tax rate which is specified on the Set-up sheet. Positive values represent deferred tax assets and negative values represent deferred tax liabilities.
DT YTD Movement - this column includes the YTD movement in the deferred tax balances.
DT Closing Balance - this column includes the deferred tax balance at the end of the current financial period. Deferred tax balances are calculated as the difference between the carrying values at historical cost and the tax values multiplied by the appropriate tax rate which is specified on the Set-up sheet. Positive values represent deferred tax assets and negative values represent deferred tax liabilities.
DT MTD Movement - this column includes the MTD movement in the deferred tax balances.
All the column headings on the Assets sheet contain a filter selection arrow. These selection arrows indicate that the Filter feature has been activated on the sheet. The Filter feature can be used to filter the data on the sheet in order to display only certain asset records on the sheet.
We have also included totals above all the column headings that contain amounts. These totals have been calculated by using the SUBTOTAL function which means that if the data on the sheet is filtered, the totals that are calculated will only include the filtered records. The Filter feature can therefore be used to obtain totals based on the filter criteria that are specified by the user and also enables users to view the detailed fixed asset records that make up the summary totals on the Category and Class sheets.
Example: If you want to obtain the fixed asset register totals for all leased assets, you can simply click the selection arrow next to the Asset Type column heading and select the Leased option. The sheet will be filtered and only the leased assets will be visible on the sheet. The totals will also only include the leased assets.
Example: If you want to view the detailed asset records that make up one of the category or class totals on the Category or Class sheets, simply click the filter selection arrow next to the Category or Class column headings (column E or column H) and select the appropriate category or class. The sheet will be filtered and only the asset records for the selected category or class will be visible on the sheet. The totals will also only include the appropriate amounts for the selected category or class.
Note: After reviewing the filtered data, you need to clear the filter in order to display all the asset records on the sheet. A filter can be cleared by simply clicking the selection arrow next to the appropriate column heading again and clicking the "Select All" option.
Recording Asset Transactions
All asset transactions must be recorded on the Transact sheet. There are basically 3 types of transactions that can be recorded namely acquisitions, disposals and revaluations. An asset transaction can be recorded by entering the required data into the user input columns - the column headings of all user input columns contain a yellow cell background while the column headings with a light blue cell background indicate that these columns contain formulas which are automatically copied for all new transactions that are added to the sheet.
Note: Recording an asset number on the Assets sheet will have no effect in terms of recording the cost of an asset in the fixed asset register. An acquisition transaction needs to be recorded on the Transact sheet for each fixed asset that is acquired before the appropriate asset values will be included in the fixed asset register on the Assets sheet and the summaries on the Category and Class sheets.
Note: All the columns on the Transact sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas that are included in calculated columns (the columns with a light blue column heading) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore enter a new transaction by simply entering a transaction date in column A - the table will then automatically be extended to include the new transaction.
The following user input is required in the user input columns on the Transact sheet (the columns with yellow column headings):
Transaction Date - enter the date of the transaction.
Transaction Type - select the transaction type from the list box. The ACQ option should be selected for all asset acquisitions, the DIS option should be selected for all asset disposals and the REV option should be selected for all revaluations of fixed assets.
Asset Number - select the appropriate asset number of the asset to which the transaction relates from the list box in this column. All the asset numbers that have been added to the Assets sheet will be available for selection. You therefore need to create an asset number for the appropriate fixed asset on the Assets sheet before you will be able to record any transactions for the particular fixed asset.
Supplier - enter the name of the supplier from which the asset has been acquired. If a disposal transaction is recorded, you can enter the name of the customer in this column. For revaluations, you can simply enter "None".
Document Number - enter the document number of a supporting document which will enable you to trace the transaction back to its supporting documentation. For acquisitions, this should be the supplier invoice number and for disposals, the customer invoice number. For revaluations, you can simply enter text like "Revalue".
Amount - enter the transaction amount in this column. For acquisitions, the supplier invoice amount should be entered exclusive of any sales tax that may be applicable. For disposals, a nil amount should be entered in this column. For revaluations, the value of the asset should be entered in this column - this should be the new value of the fixed asset and not the difference between the valuation and the previous historical cost or gross carrying amount!
Lifetime in Years - enter the estimated lifetime of the asset in years for all acquisition and revaluation transactions in this column. For disposals, enter a nil value in this column. Note that if an asset should not be depreciated, a nil lifetime also needs to be specified in this column.
Residual Value - this value should be the estimated valuation of an asset at the end of the lifetime that is specified in the previous column. If the asset will have no value at the end of its estimated lifetime, enter nil. A nil value should also be entered for all disposal transactions.
Proceeds on Sale - if a disposal transaction is being recorded, enter the proceeds on the disposal of the asset in this column. The proceeds should equal the total amount that is received for the asset. If an asset is being scrapped, enter nil in this column. A nil value should also be entered for all acquisition and revaluation transactions.
The Transact sheet also includes 38 calculated columns - the column headings of all the calculated columns contain a light blue cell background. All of these columns contain formulas which are automatically copied for all new transactions that are added to the sheet. We'll now briefly cover the purpose of each calculated column:
Error Code - if there is a problem with the input in any of the user input columns, an error code will be displayed in this column. Refer to the Set-up sheet for a description of each error code and to the Error Code section of these instructions for more information about the reason for the error code that is displayed.
Category - the asset category is looked up on the Assets sheet based on the asset number that is specified in column C. This column is included for information purposes only.
Transaction Date ID - the formula in this column assigns a time to each transaction date based on the row number of the transaction. This means that each transaction will have a unique date even if two transactions that relate to the same asset are recorded on the same date.
Previous Trn Date - this column indicates the date of the previous transaction for the specified asset number. If there is no previous transaction for the asset, a date of 1900/01/00 will be displayed.
Next Trn Date - this column contains the next transaction date for the selected asset. If there is no transaction after the current transaction, the day after the YTD To date on the Assets sheet will be included in this column.
Depr End Date - this column contains the date on which the depreciation period for the transaction will end. This date is determined by adding the lifetime of the asset to the transaction date.
Depr YTD From Date - the date from which the current financial period depreciation is calculated is included in this column. Note that if there is a subsequent transaction for an asset, a date of 1900/01/00 or the YTD From date may be included in this column.
Depr YTD To Date - the date up to which the current financial period depreciation is calculated is included in this column. Note that if there is a subsequent transaction for an asset, a date of 1900/01/00 or the YTD From date may be included in this column.
Depr YTD Months - this column includes the number of months on which the YTD depreciation for the particular transaction is based.
Transaction Amount - this column contains the transaction amount that is specified in column F.
Asset Historical Cost - this column contains the historical cost of the appropriate asset. The historical cost is defined as the transaction amount of the acquisition transaction that is recorded for an asset.
Current Lifetime - the current lifetime of the asset as specified in column G is included in this column.
Current Residual - the current residual value of the asset as specified in column H is included in this column.
Previous Deemed Cost - the gross carrying value of the previous transaction for the selected asset is included in this column.
Previous Residual Value - the residual value that was specified in the previous transaction for the selected asset is included in this column.
Previous Lifetime - the previous lifetime that was specified in the previous transaction for the selected asset is included in this column.
Current Accum Depr - the total accumulated depreciation that has been written off between the previous transaction date and the current transaction date for the selected asset is included in this column.
Current Carrying Value - the amounts in this column are calculated as the difference between the previous deemed cost and the current accumulated depreciation.
Accum Depr: Cost - the accumulated depreciation based only on the historical cost of the asset which has been written off between the previous transaction date and the current transaction date for the selected asset is included in this column.
Accum Depr: Revaluation - the difference between the total accumulated depreciation and the accumulated depreciation based on only the historical cost of the asset is included in this column and represents the accumulated depreciation that has been written of on the revaluations that have been applied to the selected asset between the previous transaction date and the current transaction date.
Accum Depr: Rev - the accumulated depreciation on revaluations as at the beginning of the financial period is included in this column.
Previous Revaluation - this column contains the revaluation reserve balance before taking any effect that the current transaction may have on the revaluation reserve balance into account.
Revaluation Surplus - if the selected transaction is a revaluation and the transaction amount that is specified in column F is greater than the current carrying value in column AA, the revaluation surplus amount is included in this column. Note that this calculation may also be influenced by any previous impairments that may have been recorded against the selected asset number.
Current Revaluation - this column includes the revaluation reserve balance after taking the effect of the current transaction into account.
Previous Impairment - this column contains the total of any impairment amounts that have been recorded for the selected asset (before taking the effect that the current transaction may have) into account.
Impairment Value - if the selected transaction is a revaluation and the transaction amount that is specified in column F is less than the current carrying value in column AA, the asset impairment amount is included in this column as a positive value. If an asset is revalued at a value that is greater than the current carrying value and an asset impairment was previously recorded for the selected asset, the impairment will be reversed to the extent that the revaluation reserve is greater than the previous impairment value and the amount of the reversal will be reflected as a negative value in this column.
Profit / (Loss) on Disposal - if the selected transaction is a disposal, the profit or loss on the disposal of the asset is included in this column. The profit or loss is calculated by deducting the current carrying value of the asset from the proceeds on disposal that is recorded in column I. Note that if the revaluation reserve for the asset is greater than nil, the revaluation reserve will also be included in the calculation of the profit or loss on disposal. This means that the reserve balance is effectively released when an asset is disposed of.
YTD Total Depreciation - the YTD total depreciation for the asset is calculated in this column. This calculation is based on the gross carrying value of the asset, the lifetime of the asset and the YTD depreciation months which is calculated in column R.
YTD Total Depr at Cost - the calculation in this column forms part of the YTD depreciation on the historical cost of an asset which is calculated in column AM.
YTD Cost Depreciation - the YTD depreciation on the historical cost of the asset is calculated in this column. This calculation is based on the historical cost of the asset, the current lifetime of the asset and the YTD depreciation months which is calculated in column R.
Accum Depr at PY Cost Bal - the calculations in this column forms part of the calculation of the accumulated depreciation on the historical cost of an asset as at the beginning of the financial period.
Accum Depr at Cost PY - the calculations in this column forms part of the calculation of the accumulated depreciation on the historical cost of an asset as at the beginning of the financial period.
Depr: Cost by Transaction - the calculations in this column indicate how much of the historical cost of an asset is depreciated as a result of each transaction. This calculation forms an integral part of the YTD and accumulated depreciation on historical cost calculations.
MTD Total Depreciation - this column includes the calculation of the total MTD depreciation. Note that the month to which the calculation is applied is determined by the review date that is entered in cell E2 on the Assets sheet.
Accum Depr Total at PM - the amounts in this column reflect the total accumulated depreciation as at the beginning of the current month. This calculation forms part of the total depreciation for the current month calculation.
MTD Total Depr at Cost - the calculation in this column forms part of the calculation of the depreciation on historical cost for the current month.
MTD Cost Depreciation - this column contains the calculation of the depreciation on the historical cost of an asset for the current month. Note that the current month is determined by the review date that is specified in cell E2 on the Assets sheet.
Accum Depr Cost at PM - this calculation reflects the accumulated depreciation on the historical cost of an asset as at the beginning of the current month. The calculation forms an integral part of the calculation of the depreciation on historical cost for the current month.
Note: Only one acquisition transaction can be recorded for each asset because an asset can only be acquired once. If an asset is scrapped (which is recorded as a disposal) and subsequently needs to be added back to the fixed asset register, a new asset number should be used for this purpose. The recording of duplicate asset acquisitions will result in an input error in the Error Code column and may also result in inaccurate template calculations.
Note: All the depreciation, accumulated depreciation, tax allowance and deferred tax calculations that form part of the fixed asset register are automated and you therefore do not need to record any transactions in order to record any of these amounts. Also note that all the automated calculations in this template are based on the review date that is specified on the Assets sheet. You can therefore roll the template forward for subsequent periods or back to previous periods by simply changing the review date on the Assets sheet.
All the column headings on the Transact sheet contain a filter selection arrow. These selection arrows indicate that the Filter feature has been activated on the sheet. The Filter feature can be used to filter the data on the sheet in order to display only certain asset records on the sheet.
Error Codes
The following error codes may result from inaccurate input on the Assets and Transact sheets and will be displayed in the Error Code columns on the appropriate sheet. The heading of the affected input column will also be highlighted in orange in order to indicate that an error is present in the appropriate column:
Note: Input errors may result in inaccurate template calculations and it is therefore imperative that all errors are resolved before reviewing the fixed asset register balances and processing any general ledger journal entries.
Calculation Methodology
In this section of the instructions, we'll cover the calculation methodology that is applied in order to compile a fixed asset register with this template. We cover all the different types of transactions that are included in the template and we'll explain the reasoning behind how each type of transaction is incorporated into the template calculations.
Fixed asset additions are included in the fixed asset register in the month of the acquisition and also depreciated from the month of acquisition. All fixed asset additions need to be recorded as acquisition type transactions on the Transact sheet in order to include the assets in the fixed asset register. Acquisitions are usually recorded in the primary accounting system by allocating the addition to the appropriate fixed asset account and crediting the bank or accounts payable control account.
For the purpose of our automated journal report, fixed asset additions are allocated to the fixed asset cost account that is specified for the appropriate fixed asset category on the Set-up sheet and credited against the Bank account.
Fixed assets that are disposed of are removed from the fixed asset register in the month of the disposal. All disposals need to be recorded as disposal type transactions on the Transact sheet. The gross carrying value of the asset is deducted from the opening balance and the accumulated depreciation on the asset is deducted from the accumulated depreciation as at the end of the month before the disposal.
Note: Assets that are disposed of are not depreciated in the month of the disposal. This means that no depreciation is calculated for the month in which the asset is disposed of. This approach is followed in order to offset the calculation of depreciation from the first month of acquisition.
The proceeds on the disposal of the asset are recorded on the Transact sheet when the disposal transaction is recorded. The difference between this amount and the net carrying value of the asset (historical cost or gross carrying value less accumulated depreciation) constitutes the profit or loss on the disposal of the asset.
Note: If an asset is scrapped, the asset can be removed from the fixed asset register by recording a disposal type transaction on the Transact sheet and entering a nil value in the Proceeds column (column I).
If an asset has been revalued previously and a revaluation reserve still exists for the asset, the revaluation reserve is released against the profit or loss account. This means that the net effect will be that the profit or loss on disposal is calculated based on the historical cost of the asset and that no further adjustments are required in order to reflect the profit or loss on disposal net of any revaluations that may have been made to the asset.
In terms of the automated journal report, the gross carrying value of the asset is credited against the cost account that is specified for the particular fixed asset category on the Set-up sheet and debited against the profit or loss account that is also specified on the Set-up sheet. The accumulated depreciation is debited against the accumulated depreciation account that is specified on the Set-up sheet and credited against the profit or loss account.
The proceeds on disposal of the asset is debited against the bank account and credited against the profit or loss account. If there is a revaluation reserve on the date of disposal, the balance of the revaluation reserve is debited against the revaluation reserve account and credited against the profit or loss account.
The template facilitates recording an unlimited number of revaluations for an unlimited number of fixed assets. All fixed asset revaluations need to be recorded on the Transact sheet by selecting the revaluation transaction type. Revaluations are accounted for in the month in which the transaction date falls and are also depreciated based on the revalued amounts from the transaction month.
When you revalue an asset, the previous cost (or gross carrying amount for assets that have been revalued previously) and the accumulated depreciation on the asset up to the month before the revaluation are written off to either a revaluation reserve account or an asset impairment account depending on whether a revaluation surplus or asset impairment results from the revaluation. This means that immediately after the revaluation, the asset will be reflected at the revalued amount with no accumulated depreciation.
If the revaluation amount is greater than the net carrying value (historical cost or gross carrying value less accumulated depreciation), the revaluation results in a revaluation surplus which is allocated to a revaluation reserve account. If the sum of the revaluation amount and any revaluation reserve which may still exist for the asset is less than the net carrying value, the result of the revaluation is an asset impairment.
Note: All asset impairments are written off immediately to the income statement by allocating the impairment amount against the profit or loss for the period.
Note: If the result of the revaluation transaction is a revaluation surplus but an asset impairment write-off has previously been allocated to the income statement for the particular asset, the previous asset impairment is reversed by allocating a credit to the income statement and the remainder of the revaluation surplus (if any) is allocated to the revaluation reserve account.
The revaluation reserve account and the asset impairment account for each fixed asset category needs to be specified on the Set-up sheet. The revaluation reserve account should be a balance sheet account in the equity account group and the asset impairment account should be an income statement account.
In terms of the automated journal report, the difference between the previous gross carrying value (or historical cost) and the revaluation amount is adjusted against the asset cost account and the revaluation reserve account. The accumulated depreciation balance as at the date of revaluation is debited against the accumulated depreciation account and credited against the revaluation reserve account.
The net result of these two journal entries will be that the asset cost account is adjusted to the revaluation amount, the accumulated depreciation is adjusted to a nil value and the revaluation surplus is credited to the revaluation reserve account (if the revaluation amount is greater than the previous net carrying value of the asset).
Note: Revaluation surpluses are calculated based on the net carrying value of an asset which in turn is calculated by deducting the accumulated depreciation from the gross carrying value (or historical cost) of the asset in the "Assets at Cost" section on the fixed asset register. The amounts that are calculated in the Revaluation columns may therefore appear a bit confusing because the gross carrying value in the cost section may need to be reduced (shown as a negative amount) in order to adjust the gross carrying value to the revaluation amount but the net result of the revaluation (sum of the Revaluation columns in both the cost and accumulated depreciation sections) may still result in a revaluation surplus. This is because the accumulated depreciation (which is always a credit balance) is also allocated to the revaluation reserve account and also needs to be taken into account when reviewing the amounts in the Revaluation columns.
If the revaluation results in an asset impairment (which means that the revaluation amount is less than the net carrying value of the asset), the accumulated depreciation as at the date of the revaluation is again debited against the appropriate accumulated depreciation account and credited to the revaluation reserve account but an equal amount is credited against the asset cost account and debited against the revaluation reserve account.
The net result of these two journal entries is that the asset cost account is adjusted to the net carrying value of the asset because the accumulated depreciation is in effect written off against the asset cost account. A separate journal entry then credits the asset cost account and debits the impairment write-off account that is specified for the appropriate asset category on the Set-up sheet.
Note: If a revaluation on an asset therefore results in an asset impairment, you can expect to see equal negative values in the Revaluation columns in both the cost and accumulated depreciation sections on the fixed asset register (which equals the accumulated depreciation on the asset as at the date of revaluation) and the amount of the impairment write-off in the Impairment column in the cost section of the fixed asset register.
Note: If a previous impairment on an asset is reversed as a result of a subsequent revaluation surplus, the impairment reversal amount will be included in the Impairment column in the cost section of the fixed asset register as a positive amount.
All the depreciation calculations that form part of the fixed asset register which is compiled with this template are automated and based on the transactions that are recorded on the Transact sheet. It is however important to note that the period for which the depreciation calculations are performed is determined by the review date that is specified in cell E2 on the Assets sheet. The date in this cell is the only user input that is required in order to update all the depreciation calculations.
All the depreciation calculations are split between depreciation based on the historical cost of an asset and depreciation based on the revaluation of an asset. This is an important point to note because only the depreciation based on the historical cost of an asset is allocated as an expense to the income statement. The depreciation based on the revaluation of an asset is debited against the revaluation reserve that is created when a revaluation surplus results from the revaluation of an asset. The revaluation reserve is therefore written off over the lifetime of the asset as part of the depreciation journals.
Note: The depreciation based on the revaluation of an asset will not necessarily contain a value immediately after the revaluation transaction has been recorded. If the new gross carrying value of the asset is less than the historical cost of an asset, depreciation on the revaluation will only come into effect once the historical cost of the asset has been fully depreciated. If an asset has however been revalued at a value that is greater than the historical cost which means that the new gross carrying value exceeds the historical cost of the asset, depreciation on the revaluation will be calculated from the date of the revaluation transaction.
All depreciation calculations are based on the historical cost or gross carrying value (for assets that have been revalued) of an asset less any residual value that is specified when recording the appropriate transaction and are calculated on a straight line basis over the lifetime of the asset which is also specified when recording a transaction.
Note: This template only accommodates depreciation on a straight line basis. If you therefore need to calculate depreciation based on a reducing balance or other depreciation basis, the template will not be suitable for your requirements!
Note: When an asset is acquired, depreciation is calculated and written off from the month in which the addition falls. When an asset is disposed of, depreciation is calculated up to the month before the date of the disposal. When an asset is revalued, the effect of the revaluation is taken into account from the month in which the revaluation transaction falls.
In terms of the automated journal report, the first two journal entries relate to the depreciation on the historical cost of assets and the depreciation based on the revaluation of assets. Just like the other journal entries, the depreciation journals are based on the asset categories that are created on the Set-up sheet. Users are therefore able to specify a separate depreciation account for each asset category.
Note: If you want to process depreciation journals on a monthly basis, make sure that you select the MTD option from the list box in cell E2 on the Journals sheet. If you select the YTD option from this list box, all depreciation calculations will be based on a year-to-date basis.
All the tax allowance and tax value calculations in this template are automated. All tax values are calculated based on the historical cost of an asset and the appropriate tax allowances are calculated based on the difference between the historical cost of assets and the residual values that are specified when recording transactions on the Transact sheet. The tax allowance rates are determined based on the tax code that is linked to an asset on the Assets sheet and the annual tax rates that are specified for the appropriate tax code on the Set-up sheet.
Note: No journals are processed as a result of tax values or tax allowance calculations. These calculations are included in the template in order to include the tax values of all assets in the fixed asset register and to facilitate the calculation of deferred tax assets and liabilities.
Deferred tax asset and liability balances are calculated based on the difference between the tax values of assets and the net carrying value of assets based only on the historical cost of the assets. Depreciation based on the revaluation of assets should not be allocated to the income statement and therefore also have no effect on deferred tax calculations. All asset revaluation transactions are therefore ignored for deferred tax purposes.
Note: If an asset is not depreciated, the asset will also not form part of the deferred tax calculations because the difference between the tax and accounting treatment of the asset is permanent and not temporary as is required in terms of the principles of deferred taxation.
The income tax rate that is specified in cell C9 on the Set-up sheet is used for all deferred taxation calculations. If this rate is amended, all deferred tax calculations are updated automatically. A rate adjustment journal has however not been included in the automated journals and we therefore recommend that users calculate and adjust deferred tax asset and liability balances independently from the template calculations.
Note: The effect of the rate change can however be determined by saving a new version of the template before changing the income tax rate, changing the rate and comparing the deferred tax balances between the new and old versions of the template.
In terms of the automated journal report, the movements in all the deferred tax asset and liability balances for the appropriate period are allocated to the deferred tax income statement and balance sheet accounts that are specified as part of the asset category set-up on the Set-up sheet.
Asset Category Summary
The Category sheet contains a fixed asset register summary for all the asset categories that are created on the Set-up sheet. Individual assets are linked to a fixed asset category by selecting a category in column E on the Assets sheet. No user input is required on the Category sheet.
Note: Only the first 30 asset categories are included on the Category sheet by default but you can add additional asset categories (if required) to the sheet by simply copying the formulas in the last row that contains data into the appropriate number of additional rows.
Note: If you want to view the detailed fixed asset records that make up the totals on the Category sheet, you can apply a filter to the Category column on the Assets sheet by clicking the selection arrow next to the column heading and selecting the appropriate asset category. The totals above the column headings will equal the amounts that are reflected on the Category sheet.
Note: The Category sheet is compiled based on the review date that is specified in cell E2 on the Assets sheet. If you therefore want to amend the reporting period for which the asset category summary is compiled, simply enter a new review date on the Assets sheet.
Asset Class Summary
The Class sheet contains a fixed asset register summary for all the asset classes that are created on the Set-up sheet. Individual assets are linked to a fixed asset class by selecting an asset category in column E on the Assets sheet. Each asset category is linked to a separate asset class by selecting the appropriate asset class in the asset category set-up on the Set-up sheet. No user input is required on the Class sheet.
Note: The template accommodates a maximum of 30 asset classes on the Class sheet. This should be more than sufficient for any business because the asset classes are used for financial statement reporting purposes.
Note: If fixed assets are reported in the financial statements based on the gross and net carrying amounts, some of the columns on the Class sheet may need to be consolidated. For example, the opening & closing cost & accumulated depreciation balances and the revaluation columns will need to be consolidated and included in a single line for the purpose of compiling a property, plant & equipment note for financial statement purposes. We have not structured the layout of the sheet on a carrying amount basis because the template also needs to accommodate the fixed assets of businesses that report on a historical cost basis.
Note: If you want to view the detailed fixed asset records that make up the totals on the Class sheet, you can apply a filter to the Class column on the Assets sheet by clicking the selection arrow next to the column heading and selecting the appropriate asset class. The totals above the column headings will equal the amounts that are reflected on the Class sheet.
Note: The Class sheet is compiled based on the review date that is specified in cell E2 on the Assets sheet. If you therefore want to amend the reporting period for which the asset class summary is compiled, simply enter a new review date on the Assets sheet.
Fixed Asset Journals
The Journals sheet contains an automated report of the recommended journal entries that need to be processed in order to account for all fixed asset transactions in a general ledger. The general ledger journal entries on this sheet are compiled on an asset category basis because each asset category is linked to a general ledger account for each of the transaction types that are included in the template.
Note: Only the first 30 asset categories are included on the Journals sheet by default but you can add additional asset categories (if required) to the sheet by simply copying the formulas in the last row that contains data into the appropriate number of additional rows.
Note: The Journals sheet is compiled based on the review date that is specified in cell E2 on the Assets sheet. If you therefore want to amend the reporting period for which the automated journal report is compiled, simply enter a new review date on the Assets sheet.
The general ledger journal entries on the Journal sheet can be compiled on a month-to-date (MTD) or a year-to-date (YTD) basis by simply selecting the appropriate period from the list box in cell E2. All the journal entry amount calculations are automatically updated.
Each general ledger journal entry consists of four columns which contains two sets of accounts and amounts. The account numbers that are included on the Journal sheet are determined based on the general ledger accounts that are linked to each asset category on the Set-up sheet. In terms of the journal entry amounts, a positive value refers to a debit entry and a negative value refers to a credit entry.
There are 11 general ledger journal entries on the Journals sheet - the purpose of each of these journal entries can be summarized as follows:
For more information on the calculation methodology that is applied in compiling the journal entries on the Journals sheet, refer to the Calculation Methodology section of these instructions.
Note: If you want to analyse the values that have been included on the Journals sheet, we recommend that you refer to the Category sheet or that you apply the Filter feature to the asset records on the Assets sheet in order to analyse the journal amounts for a specific asset category.