You are here
Calculation of Interest on British Columbia Court Judgments - and spreadsheet with Excel macro that generates interest calculation table (Compound interest optional)
Thu, 2020-10-08 20:31 — Michael Dew
Research articles :
Donations
If you use the spreadsheet and find it useful please consider making a small donation to help cover the costs of maintaining this website.
_____________________________________________________________________________
Introduction - what the spreadsheet does
Even for skilled Excel users, coding up a spreadsheet to suit a particular set of inputs for an interest calculation takes time, and is more tedious than rewarding after having done it a few times. Wouldn’t it be nice if you could just list your inputs and then press a button and have a macro build the spreadsheet for you, coding in the formulas for your review and inspection!? Well you can, using the spreadsheet available for download here.
The spreadsheet contains macros that take user inputted data regarding:
-
principal advances / increases in debt,
-
repayments; and
-
interest rates,
and prepare an Excel spreadsheet that calculates principal an interest amounts owing over time.
The macros produce a transparent spreadsheet calculation that contains formulas that could be coded in from scratch by skilled Excel users, and so is not a “black box” that must be blindly trusted. Rather, the spreadsheet built by the macros allows one to audit the formulas to assess the accuracy of the interest calculation.
Note: Modern versions of Excel are very protective in shielding users from files with macros, and you may need to save the spreadsheet in a "trusted location" in order for the macros to run. See "Security Issues" section below.
Who this spreadsheet is for?
This spreadsheet was originally designed to calculate interest owing on debt amounts in the legal context e.g. total amount owing, including interest, on a court judgment at a particular point in time. However, it can be used in any case where there is a debt amount outstanding for some time, interest is charged, and perhaps payments made, and then one wants to determine the amount owing at a point in time taking into account debts increases, repayments, and interest. It is not limited to loan transactions, but can also be used to calculate the amount owing (including interest) on a set of invoices with different dates.
This spreadsheet is intended for people who understand the math principles related to calculating interest, and know how to use Excel, but wish to avoid tedious entry of interest calculation formulas on a case by case basis by having a macro code those formulas in at the click of a button.
Assumptions made by the macros
There are various different ways to perform interest calculations, and so the following assumptions regarding the structure of the calculations coded in by the macro should be carefully reviewed, and the spreadsheet only used if these assumptions are acceptable:
-
The macros codes in a stepwise calculation that calculates the interest on the daily outstanding balance of principal over time.
-
If the amount of principal changes (e.g. due to an additional advance, or due to a repayment) that revised principal amount is used to calculate the daily interest on subsequent days.
-
Each change in principal, or change in interest rate, triggers a new time period, and the interest in each time period is calculated as "principal at start" x "daily interest rate" x "number of days".
-
Interest rates must be specified as annual rates and the macros convert them to effective daily rates by dividing by 365, but by 366 in leap years.
-
Leap years are taken into account by starting new time periods on January 1 of leap years and years following leap years i.e. those are transition points at which daily interest rates change even though the annual interest rate is unchanged.
-
The spreadsheet codes in a new line each time there is an interest rate change, and so if there is an advance or repayment on the same day then the spreadsheet will show a line with zero days.
-
If compounding dates are specified, then on those dates the interest then outstanding is added to principal and a new time period is commenced that day, with the interest then outstanding set to zero.
-
To calculate interest accumulating in the period between two dates, including the start date and the end date, the spreadsheet codes in a formula that calculates days as: End Date - Start Date + 1 e.g. the period from March 2 – March 4 would be 3 days, the day of March 2, the day of March 3, and the day of March 4 .
Overview of the structure of the spreadsheet
This section explains the overall structure of the spreadsheet and a high-level description of operation of the spreadsheet. Additional directions are listed on the following tabs of the spreadsheet which call for input and / or perform calculations:
-
Step 1 – Input
-
Step 2 – Interest
-
Step 3 - Calculation
The Input sheet
The Input sheet provides space for entry of increases of the debt (e.g. debt advances, date of judgment, etc.) and repayments. Repayments are entered as negative numbers.
Especially for large data sets (e.g. many transactions increasing or decreasing the debt) it is best to build the list of advances and repayments on a helper sheet and then copy and paste (paste special as plan values) into the data input section on the Input sheet. This prevents harm to the Input sheet when rows or columns are inserted, deleted, etc. in the process of tidying up the data into the correct format as called for on the Input sheet.
A dropdown list on the Input sheet allows the user to decide whether to apply payments made by the debtor to interest first, or principal first.
There is a section on the Input sheet for the user to specify the end date for the calculation. Options are to:
-
Use “today’s date” as the end date for the calculation i.e. if you want to know the amount owing, including interest, right now.
-
Continue the calculation until the end of the entered data, or when the debt is paid off. (See comment below regarding payment plans).
-
Specify a custom end date e.g. how much owing, including interest, at some specified date (in the past or future).
There is a field on the Input sheet for entry of amount for “Interest at start”. This may be used in the case of a court judgment that included an award of prejudgment interest and post judgment interest is payable on a simple interest basis (not compounded) such that post judgment interest should not be charged on the pre-judgment interest amount. This may also be used in a regular commercial setting where the parties which to agree on a lump sum for past interest that should not have interest charged on it going forward.
Interest sheet
The left side of the Interest sheet (to the left of the grey divider line) provides a space to input interest rates, and to specify compounding dates.
The right side of the Interest sheet (to the right of the grey divider line) provides a space to define interest regimes. Interest rate data may be based on constant interest over time, may be based on prime rates which vary frequently, may be based on rates specified by the Rules of Court, etc.
It is strongly suggested that users do not type interest rates directly in to the interest rate data section on the left side of the Interest sheet, but rather specify interest rate data sets on the right side of the grey divider line and then use the macro to write the data to the left where it will later be used by the main calculation generation macro. This is because leap years have to be taken into account, and also daily interest rates need to be derived from the specified annual rates, and the macro available on the right side of the grey divider line is provided to perform those tasks.
Calculation sheet
The calculation sheet is where the calculations coded in by the main macro are placed. Click the green button to run the macro that writes in calculations based on the input data specified.
After the macro is finished running, be sure to audit the formulas for accuracy, and assess the reasonableness of the overall result.
Because users may wish to be left with a neat spreadsheet that contains just a clean sheet without the instructions at the top of the Calculation sheet, and perhaps without the input sheets, the orange button on the Calculation sheet strips out the instructions and provides the user the option to delete the input sheets. If the option to delete input sheets is chosen then the output of the VLOOKUP formulas in the Interest columns of the calculation are converted to plain text.
Payment plans
Where it is agreed that a debtor will pay the amount owing by way of a payment plan, the duration of the plan for specified interest rates and payment amounts can be assessed as follows:
-
Enter, on the Input sheet, a long list of payments on specified dates, extending much further into the future than you expect will be necessary to pay off the debt (use regular Excel copy and paste functionality, or fill tools to expedite this task).
-
On the Input sheet, select the option for “Go to end of data, or to debt paid off (whichever comes first)”.
-
On the Interest sheet, specify the interest rate data as usual.
-
Run the macro to code in the calculations by pressing the green button on the Calculations sheet.
The spreadsheet will then apply as many payments as necessary to pay off the debt, and will adjust the final payment down to be just enough to exactly pay off remaining principal and interest due on the final payment date.
If the result of the above method is an annoyingly small final payment and you prefer to increase the second to last payment to bring the payment plan to an end one pay period earlier then:
-
Noting the date of the second to last payment indicated by the prior run of the spreadsheet, go to the Inputs sheet and bump up the amount of the second to last payment to an amount that is confidently more than needed to fully pay off the debt.
-
Click the green button on the Calculations sheet to code in revised calculations based on the new input data. The macro will then calculate a revised payment plan that ends on what was previously the second last payment date, and will determine the amount needed to fully pay off the debt on that date.
Considerations when calculating amount due on invoices
The spreadsheet can be used to calculate the amount owing on a collection of invoices, including interest, but depending on the commercial rules for when interest paid required some tweaks to the data inputted may be required.
If interest is charged from the date of the invoice, then there are no complications and invoices are just inputted according to their amounts and dates.
However, where interest is not charged on invoices until some point after the invoice date (e.g. 30 days after the date of the invoice), then when entering data it is necessary to:
-
Specify the interest start date on the Input sheet rather than the invoice date.
-
For invoices that will not yet be at their interest start date as of the end date for the calculation (specified on the Input sheet), exclude them from the Input sheet such that they are entirely omitted from the interest calculation, and then once the interest calculation is complete list them below the calculation table prepared by the macro and add them to the amount of principal owing. This makes sense because invoices which are net yet, on the date of overall assessment, at their interest start date will not attract any interest and so need not be included in the interest calculation at all, but can just be added to the remaining principal on invoices that did attract interest.
Again, as noted above, the data to be entered into the Input tab should be prepared on a helper sheet and then copied and pasted (paste special as plain text) into the input area on the Input sheet, and where screening out of some data is needed it can be done on such helper sheet when organizing the raw data.
If a credit note is issued in relation to a particular invoice then the amount of that invoice should be decreased by the amount of the credit note i.e. modify the original invoice amount as if it was lower from the outset even though, for example, some of the materials on the invoice were only returned some time later. Make this adjustment on the line for the invoice i.e. do not record the credit note as a separate line item because then it will be interpreted as a repayment and if the option to apply repayments to interest first is selected then the calculation will be wrong. (These instructions assume the seller is willing to charge no interest on the amount of the returned goods).
British Columbia Law in support of the assumptions underlying the macros
Whereas the above discussion reviewed technical use of the spreadsheet, this section discusses the law of interest as it applies to British Columbia court judgments, and so will be primarily of interest to legal professionals in British Columbia.
The assumptions made by the macros as listed above are considered to be logical assumptions that will typically be made in the debt recovery context e.g. it is typical (in all jurisdictions) to specify interest rates on an annual rates, and in leap years it is necessary to use a slightly lower daily interest rate to account for the fact that there is an extra day in the year that interest will be calculated for, and so achieve the same annual interest rate in leap years and non-leap years.
The following passage from the British Columbia Supreme Court case of Denoni v. Swallow, 1997 CanLII 4311 (BCSC) confirms that in British Columbia the nominal annual rate must be divided by 366 in leap years, and generally explains how the relevant period must be broken up into blocks of time and that interest is calculated on the number of days in each block (this is the approach used by the spreadsheet):
[11] For many years now, the court has established a rate of interest payable on money held in court. By Rule 58(6), this rate of interest is fixed at 2% below the prime lending rate of the banker to the Province of British Columbia. The rate is fixed for six-month periods which begin on January 1st and July 1st each year. In adding prejudgment interest to a pecuniary judgment obtained by default, the District Registrars of the court apply this same interest rate.
[12] It has been usual, in calculating prejudgment interest on special damage awards at trial, to apply the same interest rates set for money held in court. There is, however, no obligation to do so. Section 1(1) of the Act directs the court to add prejudgment interest "at a rate the court considers appropriate in the circumstances". One might, for example, refer to the interest rates available on safe short-term investments during the relevant time period: see Gillis v. Bates (1979), 1979 CanLII 400 (BC SC), 12 B.C.L.R. 375 (S.C.B.C.).
[13] The next step is to calculate the number of days in each earning period within each interest rate period for which prejudgment interest is payable. Leap year days should be taken into account.
[14] After that, one must calculate, for each earning period within each interest rate period, the effective daily interest rate. Prejudgment interest rates determined by the court are expressed as a nominal or annual rate. This nominal rate must be divided by 365 (or, in the case of leap years, 366) to obtain an effective daily interest rate. In the present case, the defendant has failed to take leap year into account properly by dividing by 365, not 366, to obtain effective daily interest rates for 1996.
[15] The effective daily interest rate is then multiplied by the number of earning period days in the interest rate period under consideration and the product is multiplied by the wage loss which has accumulated to the start of that six-month earning period. The result is the amount of prejudgment interest to be awarded for the days in question.
(Denoni v. Swallow, 1997 CanLII 4311 at para. 11 to 15 (BCSC)).
The above passage refers to the fact that in British Columbia court ordered interest is indexed to a prime rate is set for six-month periods.
That pattern of interest rate changes can be accommodated by the spreadsheet, as can other interest rate regimes.
With respect to the “Interest at start” input field on the Input sheet, as discussed above that allows specification of an amount of historical interest (e.g. pre-judgment interest) that should not be included in the calculation of subsequent interest. This allows calculation of subsequent interest on the principal amount of the court judgment as required by the British Columbia Court Order Interest Act:
I therefore conclude that under British Columbia’s Court Order Interest Act, post-judgment interest is payable on the principal amount of the judgment plus costs, but it is not payable on pre-judgment interest.
(Paul v. CUMIS Life Insurance Company, 2011 BCSC 796 at para. 29).
Although the spreadsheet includes the option to apply compound interest, interest on court judgment in British Columbia is calculated as simple interest (i.e. not compounded) and so the input cells for compounding dates on the Interest sheet should be cleared when calculating interest on British Columbia court judgments:
Section 7(1) defines “interest rate” as “an annual simple interest rate….” The statute is clear that the interest must be simple. Section 8 permits the court to vary the rate of interest but not the way it is calculated.
(Hallam v. Shen, 2002 BCSC 1760 at para. 21).
Where interest is payable under a contract, unless the agreement expressly provides for compound interest, simple interest will apply.
The general rule established in the jurisprudence is that, unless the agreement expressly provides for compound interest, simple interest will be presumed: see Rotelick v. Shaver 1998 CanLII 12301 (Sask. C.A.), (1998). The presumption of simple interest applies to Promissory Notes: see A.W. Rogers, Falconbridge on Banking and Bills of Exchange, (7th ed. 1969), at 500-01; James v. Custer et al, [1941] 2 W.W.R. 582 at 583-84 (Man. K.B.); and MacFarlane v. Briggs, [1976] 15 N.B.R. (2d) 153, [1976] N.B.J. No. 174 at para. 11 (Q.B.) (QL).
Thus in order to decide whether the interest should be compounded, it is necessary to look at the wording of the contract to see if it expressly provides for compound interest: see Tri-City Contracting Ltd. v. Brighton Land Development Corp., [1994] B.C.J. No. 1001 (S.C.) (QL).
(CSRS Ltd. et al. v. Embley et al., 2006 BCSC 700 at para. 88 - 89).
In C. W. Bullock & Associates Ltd. v. Burnett, 1997 CanLII 2165 (B.C.S.C.) the agreement stated that invoices outstanding more than 30 days were to be charged interest “at the rate of 1.5% per month”. Mr. Justice Clancy held at paras. 107 -110 that compound interest was not payable as the agreement did not provide for it.
In Tri-City Contracting Ltd. v. Brighton Land Development Corp., 1994 CanLII 166 (B.C.S.C.) the words “interest at 15.5% per year on the unpaid amount” were found to mean simple interest.
The Law of Interest in Canada describes the default rule of simple and not compounded interest as follows:
The courts developed a presumption that unless an agreement expressly provides for compound interest, only simple interest is payable...[t]he rule has been regularly applied in Canada from at least as early as 1888, right up to the present.
(Waldron, M., The Law of Interest in Canada (1992) at 36).
A problem with inferring compound interest is that the compounding frequency would not be known:
If a court were to conclude that interest should only be compounded, but no time frame was express in the contract, it would then be faced with the perplexing question. How Frequently?... Even where evidence of conduct to support the implication that a particular compounding date was intended by the parties does exist, the courts have nonetheless appeared reluctant to deduce an intention to pay compound interest.
(Waldron, M., The Law of Interest in Canada (1992) at 36).
The word “calculated” when used in the context of an interest provision has been found to mean “compounded”:
In determining how interest rates are to be expressed, the lender must decide how frequently compounding is to take place. Then the frequency of such ... is added as qualifying wording to the interest rate when stating it in the borrowing instrument. An example would be "6% per annum, calculated semi-annually, not in advance". At this point, it should be noted that in interest law, the words "compounded", "calculated", "computed", "converted", or "convertible" are considered to be synonymous terms. They are used interchangeably in the mortgage business but have the same meaning.
(Lynch v. Elford Estates Ltd., 1986 CanLII 970 (B.C.S.C.)).
Conclusion
The spreadsheet available for download includes macros that allow quick assembly of an interest calculation, but as for all computer generated calculations, the output should be carefully reviewed and assessed for accuracy. Audit of the coded in formulas is facilitated by the transparent output produced by the macros, and an overall reasonableness assessment of the resulting output should also be undertaken.
Security issues
The spreadsheet was developed using macros (programmed in visual basic) and in order for the spreadsheet to operate your security settings in Microsoft Excel must be set to allow macros to run.
Macros can pose a security risk because they make your computer perform certain steps automatically, but are typically only dangerous when made to be that way by a computer programmer with a malicious intention – hence the warning when you change the security settings in Excel. However, the macros available in the spreadsheet available for download on this page are made with good intentions and should not harm your computer.
To enable macros in Excel 2016 (similar procedures will apply in other versions of Excel) click:
- File (i.e. the menu on the top left)
- Options
- Trust Centre
- Trust Centre Settings
- Macro settings
- Enable all macros
Modern versions of Excel are very protective in shielding users from files with macros, and you may need to save the spreadsheet in a "trusted location" in order for the macros to run.
To designate the location the file is saved in as a trusted location, in Excel click:
- File (i.e. the menu on the top left)
- Options
- Trust Centre
- Trust Centre Settings
- Trusted Locations
- Add new location.
- Copy and paste the path of the folder containing the spreadsheet in the dialog box to specify that folder as a trusted location.
