How to use our unique Excel template
Our accrual calculation template enables users to calculate divorce accrual values by adding all assets and liabilities of the plaintiff and defendant to the appropriate sheets, entering commencement values as at the beginning of the marriage and entering CPI inflation index values for adjustment of commencement values. The accrual calculation is then automated and the balance due by one spouse to the other determined while taking the effect of negative accrual into account.
Note: Our template has been created with the main purpose of simplifying divorce accruals for the parties involved in the divorce proceedings as well as lawyers who deal with these calculations frequently. The template should be used as an accrual calculation aid which should be reviewed for accuracy by the appropriate experts involved in the divorce proceedings and we can therefore not accept any liability for inaccuracies in the calculation results produced. The template has been designed for South Africa, the accrual calculation methodologies of other countries may not be consistent with these design specifications.
The following sheets are included in this template:
Setup - enter the names of the plaintiff and defendant, enter the commencement values for each spouse and enter the CPI index values as at the commencement and dissolution of the marriage.
Assets - add all the plaintiff and defendant's assets to this sheet. Columns with yellow column headings require user input and columns with light blue column headings contain formulas which are automatically copied when adding rows to the table. Plaintiff asset values can be entered as values or percentages and defendant asset values are determined based on the difference between the total asset balances and the plaintiff values or the remaining percentages if plaintiff percentages are specified.
Liabilities - add all the plaintiff and defendant's liabilities to this sheet. Columns with yellow column headings require user input and columns with light blue column headings contain formulas which are automatically copied when adding rows to the table. Plaintiff liability values can be entered as values or percentages and defendant asset values are determined based on the difference between the total asset balances and the plaintiff values or the remaining percentages if plaintiff percentages are specified.
Accrual - this sheet contains the accrual calculation. No user input is required on this sheet. Assets and liabilities are included based on the items added to the Assets and Liabilities sheets and inflation adjusted commencement values are calculated based on the values specified on the Setup sheet. The net accrual values for each spouse are calculated before determining the difference in accrual between the spouses and the net accrual owed by one spouse to the other as half of the difference in accrual.
Note: The spouse who files for divorce will be the plaintiff and the other spouse becomes the defendant.
Template Setup
The Setup sheet contains input cells for the plaintiff and defendant names which is included as a sheet and calculation section heading. If these cells are not populated, the parties are referred to as the plaintiff and defendant.
Users also need to enter the commencement values of the spouses as at the start of the marriage. These values need to be specified in the prenuptial contract entered into between the spouses before the commencement of the marriage. The values are then adjusted to inflation for the period between the commencement and dissolution of the marriage.
The inflation adjustment is calculated based on historical consumer price index (CPI) values which are published by Statistics South Africa on a monthly basis in table B. You can Google "historical cpi table b south africa" and you should get to the latest table B data. Use the month of marriage as the commencement month and enter the appropriate value as the CPI commencement value. Use the likely month of dissolution or the latest CPI value provided as the dissolution value.
The CPI Factor is calculated by dividing the dissolution value by the commencement value. This factor is multiplied by the individual commencement values to determine the inflation adjusted commencement values which are included in our accrual calculation on the Accrual sheet and deducted from each spouse's net estate to calculate the net accrual of the spouses.
Note: If the inflation adjusted commencement value exceeds the net estate of the spouse, the net accrual value will be negative but it will be limited to a nil value because negative accruals are not permitted.
Assets
The assets of each of the spouses should be included in the accrual calculation. We've set up a separate sheet to include all of the assets - the Assets sheet contains the following user input columns (yellow column headings):
Asset Description - enter a description of the asset.
Value Date - enter the valuation date of the asset. This is just for recordkeeping purposes to make it easier to refer to supporting documents which may need to be provided to the other spouse's legal representatives or the court.
Balance - enter the balance of the asset at the value date. For shared assets, this would be the combined value or total value of the asset.
Plaintiff Value - if the plaintiff's value is not determined based on a percentage of the total value, enter that value in this column.
Plaintiff % - if the plaintiff value is determined as a percentage of the total value, enter the percentage in this column.
The Assets sheet also contains a number of calculated columns with light blue column headings:
Defendant % - where a plaintiff % is entered, the value in this column will equal the remaining percentage (100% less the plaintiff percentage).
Pvalue - this is the plaintiff value of the asset
Dvalue - this is the defendant value of the asset
Ecode - if any input errors occurred, the error code will be reflected in this column.
Ptext - this is the plaintiff text which will be included in column A of the accrual calculation on the Accrual sheet.
Dtext - this is the defendant text which will be included in column D of the accrual calculation on the Accrual sheet.
PAS - this status is used to populate lines in the Assets section of the plaintiff's accrual calculation on the Accrual sheet.
DAS - this status is used to populate lines in the Assets section of the defendant's accrual calculation on the Accrual sheet.
Note: The data on the Assets sheet have been included in an Excel table which means that the formulas in the calculated columns with light blue column headings will be extended automatically when new assets are added in the first blank row below the table. You therefore do not need to copy any formulas when adding new entries to the table.
There are basically only two error codes which may be displayed in the Ecode column if there is an issue with the balance entered for an asset. Error code E1 means that the user has entered both a plaintiff value and a plaintiff percentage which is not correct and the erroneous value just need to be deleted. Error code E2 means that the plaintiff value entered is greater than the balance of the asset which is not possible - just correct the inaccurate value to resolve the error.
The accrual calculation contains 15 rows for assets by default. If either the plaintiff or defendant has more than 15 assets, you will need to add new rows to be able to accommodate more assets. A green or red status will be displayed above the Ecode column to indicate whether adding more rows is necessary - a red "add" message with the number of rows which need to be added indicates that you need to add rows to the Assets section of the accrual calculation on the Accrual sheet.
You can add additional rows quite easily - just select the total assets row and insert the appropriate number of additional rows. Find the last row which contains a formula, copy the entire row and paste the entire row's formulas into the newly added blank rows. You can confirm whether you have added sufficient rows by checking whether the status cell above the Ecode column heading now reflects a green "ok" message.
You can also delete rows if you do not need 15 rows for 15 assets - just make sure that you delete the rows from the bottom row upwards otherwise you may delete the wrong formulas and all assets may not be reflected in your accrual calculation.
Note: Assets will only be included in the appropriate Assets section of the accrual calculation if the value of the asset for the plaintiff or defendant is greater than nil. Assets with a nil value will not be included in the accrual calculation.
Liabilities
The liabilities of each of the spouses should be included in the accrual calculation. We've set up a separate sheet to include all of the liabilities - the Liabilities sheet contains the following user input columns (yellow column headings):
Description of Debt - enter a description of the debt.
Statement Date - enter the statement date of the debt. This is just for recordkeeping purposes to make it easier to refer to supporting documents which may need to be provided to the other spouse's legal representatives or the court.
Balance - enter the balance of the liability at the statement date as a positive value. For shared debts, this would be the combined value or total value of the debt.
Plaintiff Value - if the plaintiff's value is not determined based on a percentage of the total value, enter that value in this column.
Plaintiff % - if the plaintiff value is determined as a percentage of the total value, enter the percentage in this column.
The Liabilities sheet also contains a number of calculated columns with light blue column headings:
Defendant % - where a plaintiff % is entered, the value in this column will equal the remaining percentage (100% less the plaintiff percentage).
Pvalue - this is the plaintiff value of the liability
Dvalue - this is the defendant value of the liability
Ecode - if any input errors occurred, the error code will be reflected in this column.
Ptext - this is the plaintiff text which will be included in column A of the accrual calculation on the Accrual sheet.
Dtext - this is the defendant text which will be included in column D of the accrual calculation on the Accrual sheet.
PDS - this status is used to populate lines in the Liabilities section of the plaintiff's accrual calculation on the Accrual sheet.
DDS - this status is used to populate lines in the Liabilities section of the defendant's accrual calculation on the Accrual sheet.
Note: The data on the Liabilities sheet have been included in an Excel table which means that the formulas in the calculated columns with light blue column headings will be extended automatically when new liabilities are added in the first blank row below the table. You therefore do not need to copy any formulas when adding new entries to the table.
There are basically only two error codes which may be displayed in the Ecode column if there is an issue with the balance entered for a liability. Error code E1 means that the user has entered both a plaintiff value and a plaintiff percentage which is not correct and the erroneous value just need to be deleted. Error code E2 means that the plaintiff value entered is greater than the balance of the liability which is not possible - just correct the inaccurate value to resolve the error.
The accrual calculation contains 15 rows for liabilities by default. If either the plaintiff or defendant has more than 15 liabilities, you will need to add new rows to be able to accommodate more liabilities. A green or red status will be displayed above the Ecode column to indicate whether adding more rows is necessary - a red "add" message with the number of rows which need to be added indicates that you need to add rows to the Liabilities section of the accrual calculation on the Accrual sheet.
You can add additional rows quite easily - just select the total liabilities row and insert the appropriate number of additional rows. Find the last row which contains a formula, copy the entire row and paste the entire row's formulas into the newly added blank rows. You can confirm whether you have added sufficient rows by checking whether the status cell above the Ecode column heading now reflects a green "ok" message.
You can also delete rows if you do not need 15 rows for 15 liabilities - just make sure that you delete the rows from the bottom row upwards otherwise you may delete the wrong formulas and all liabilities may not be reflected in your accrual calculation.
Note: Liabilities will only be included in the appropriate Liabilities section of the accrual calculation if the value of the liability for the plaintiff or defendant is greater than nil. Liabilities with a nil value will not be included in the accrual calculation.
Accrual Calculation
The divorce accrual calculation is included on the Accrual sheet. The calculation method is to simply deduct each spouse's liabilities from their assets to determine their net estate value. The inflation adjustment commencement value of each spouse as per their prenuptial agreement is then deducted from the net estate value to calculate each spouse's net accrual value.
This value cannot be less than zero as this would mean that the net accrual is negative meaning that there was no accrual. The negative value therefore needs to be replaced by a nil value which means that the appropriate spouse had no accrual.
The difference between the two spouses' net accrual values is determined next and divided by two to calculate the value that the spouse with the higher net accrual value owes the spouse with the lower net accrual value. The spouse with the higher accrual value therefore basically owes half of the difference in accrual to the other spouse.
Our template will basically automatically calculate which spouse owes the other one and the amount owed.
The Accrual sheet requires no user input but you may need to add additional rows to the Assets or Liabilities sections of the sheet if any of the parties have more than 15 assets or liabilities. Refer to the Assets or Liabilities sections of these instructions for guidance on how to add additional rows to these sections.
