At the end of the term of the contract, an automatic subtraction is made.
If you need to make a deduction in the middle of a period you need to do it manually by putting the deduction value into the columns: AD_Deduction, Asset_Deduction, and Liab_Other (liability part). The difference between the above fields will go into Other Expenses during the subtraction period.
To support period granularity (Years, Quarters, Months, Part of-month calculations) the interest calculations were made daily.
This is an example of interest calculation:
Liab_Interest =Liab_Open*((1+Rate/365)^(Eomonth-OpenMonth+1)-1)
In some cases, this may not be accurate. It is worth consulting with an auditor whether there is a material deviation.
In the model implemented straight-line method. To support the regularity of the period, the depreciation calculation is also done daily, so you will be able to see different expenses from month to month due to the different number of days in the months.
Excel formulas – A large part of the calculations are done with the help of Excel formulas, you should avoid changing these formulas. Because a table object is used, formulas are automatically filled in while adding data. if this does not happen you can copy a formula from another cell in the same column.
When you click the button ‘Run calculation’ starts running a process identical to running a macro (VBA). This process updates ‘Liab_Open’ column with relevant values.
Essential to know some principles for working with this process:
These changes can’t be canceled with (CTR+Z) – Therefore, it is recommended to close a period for calculation by manually changing a value in column ‘PeriodStatus’ to ‘Locked ‘.
Note the date you choose from the runtime. The calculation will run for that day and ahead.
If you want to calculate a certain contract you can select it with a slicer
It is recommended to copy Excel before calculating and manage a separate file for each balance period, old files will serve as a backup.
Manual update of rates
If an error occurs in the formula as a result of updating data, the results within pivot tables are dropped. In this case, you should cancel an update with CTR+Z. Then find a problem and update again.
Recommended controls for checking data:
| Topic | Header | Description | Comments |
| Common | Agt_ID | Agreement ID | The key field for calculation, according to which this algorithm associates data with the contract |
| Agt_Description | Agreement description | ||
| Type | You can assign types like Rent, Car leases, and so on | Does not affect the calculation | |
| Option | Informative field: Base agreement / Option | Does not affect the calculation | |
| # | Key field – Payment in agreement | ||
| TermNpv | Method to calculate a present value | Supports two values: end, begin | |
| PeriodStatus | The closing period for NPV calculation | Supports two values: Locked or blank (“”) | |
| OpenMonth | Beginning of the payment period | ||
| Eomonth | End of the payment period | It always has to be the end of the month | |
| NominalPmt | Nominal amount before the linkage | ||
| ind_base | Base index for linkage | ||
| ind_current | A current index for linkage | ||
| Rate | Interest rate (Annual) | ||
| ind_link | Linkage coefficient | Calculated field (excel formula) | |
| pmt_linked | Amount to be paid linked to linkage coefficient | Calculated field (excel formula) | |
| Liability | Liab_Open | Present value in a period | Calculated field (DLL) |
| Liab_New | New contract amount | Calculated field (excel formula) | |
| Liab_Change | Amount of changes in the contract as a result of a change in linkage, interest rate, etc. | Calculated field (excel formula) | |
| Liab_Interest | Interest expenses in the period | Calculated field (excel formula) | |
| Liab_Pmt | Payment according to contract (including interest) | Calculated field (excel formula) | |
| Liab_Other | Changes in the liability (e.g. as a result of asset impairment) | Calculated field (excel formula) | |
| Liab_Close | Liability balance at the end of a period | Calculated field (excel formula) | |
| Assets | Asset_New | New contract amount | Calculated field (excel formula) |
| Asset_Change | Amount of changes in the contract as a result of a change in linkage, interest rate, etc. | Calculated field (excel formula) | |
| Asset_Other | Changes in the asset (e.g. as a result of asset impairment) | Calculated field (excel formula) | |
| Asset_Deduction | Deduction amount. Automatic deduction at the end of the contract, manual in the middle | Calculated field (excel formula) | |
| Asset_Close | Assets – Closing balance at the end of a period | Calculated field (excel formula) | |
| Depreciation | Depreciation amount | Calculated field (excel formula) | |
| AD_Deduction | Accumulated depreciation- Automatic deduction at the end of the contract, manual in the middle | Calculated field (excel formula) | |
| AD_Close | Accumulated depreciation– Closing balance at the end of a period | Calculated field (excel formula) | |
| NetA_Open | Reduced cost – balance at the beginning of a period | Calculated field (excel formula) | |
| NetA_Close | Reduced cost – Closing balance at the end of a period | Calculated field (excel formula) | |
| Comments | field for comments |
This is a journal entry for 31 December 2022 (previous period):
The P&L impact is 332 (11,480+852-12,000=332)
To write JE for 31 March 2023 you need to write two journal entries:
