Calculating Interest
Ins and Outs of interest calculation in Business Functions
| Home |
This discussion overlaps with Using Daycount and Examples of DayCount, but because it deals with a specific type of cash flow, namely interest, you may find it more relevant and accessible.
Interest arises in Business Functions within the Banking category where there are functions that use the variables DayCount and PrdsInt.
The Interest functions use functions in the rest of the library internally so everything said about DayCount and Periods applies here too. In fact the only difference is that the Interest function variable PrdsInt has a default of quarterly in arrear instead of quarterly in advance.
The most basic function, Interest, is no more than a Con in disguise. Con calculates a constant annual rate. The two jobs it does are:
- It calculates what fraction of a year lies between of Start and Finish and lies inside the time period defined by Time and Base
- It Applies a simple interest rate to the balance, call that the annual rate of interest accrual, and multiply that by the fraction of the year.
For functions such as Interest running in cash mode, the usual library methodology applies. Payments can be determined as advance or arrears using PrdsInt (see Using Daycount for details).
Interest is calculated 'simple' in the above functions. This is departure from some other areas of the library, where discount and interest rates are quoted on a consistent, AER (annual equivalent rate) basis. However, in the Interest functions, it is more practical to use simple interest (Interest Accrued = Fraction of Year x Interest Rate), because the fraction of the year will depend very greatly on the DayCount/Periods combination used.
Calculating interest involves more work where the interest rate and/or balance changes during a time period, as so often happens. Functions like FInterest accept an interest rate profile, as specified by FromDatesInt and IntSimple, as well as a profile of balances, as specified in FromDates and Balances. What these functions do is look at each balance in turn, establish the start date and end date of that balance and then apply an interest rate. The interest rate comes from a time-average of the interest rates over the period of that balance. The combination of the average interest rate and the balance, taking into account as well the length of the particular time period, gives you the interest paid FOR THAT BALANCE. Looping through each balance accumulates the interest for each balance.
The result is an interest amount that uses an average balance and an average interest rate. The method of determining the averages is important, as this gives rise to the different methods of calculation banks and companies have. Functions like FInterest use the DayCount/PrdsInt combination to determine the fractions of year between balance changes and interest changes, as well as for the length of time period itself. The following examples describe typical interest 'regimes':
| DayCount | PrdsInt (Accruals) | PrdsInt (Cash) | |
|---|---|---|---|
| Quarterly in arrear, calendar quarters, ACT/365 | Omitted or 3 | Omitted or -4 or 4 or {1.01,4.01,7.01,10.01} or {-1.01,-4.01,-7.01,-10.01} | Omitted or -4 or {-1.01,-7.01,-10.01,-11.01} |
| Quarterly in arrear, particular dates, like bonds | 6.03 | {1.23,4.23,7.23,10.23} or {-1.23,-4.23,-7.23,-10.23} | {-1.23,-4.23,-7.23,-10.23} |
| Monthly in arrear, calendar months, 30/360 | 0 | -12 or 12 | 12 |
Note that the accrual period for in advance and arrears is the same and only the sign is different. However in advance the payment is made at the start of the period on the first day, and in arrears its made on the day AFTER the end of the period.
Finish Dates
In a BF interest function, Interest runs until the day before the final finish date, on the grounds that interest is not paid on the day that a loan is repaid. So if the Start date was 1 Jan 2005 and the Finish date was 1 Jul 2005, interest would be calculated from 1 Jan 2005 to 30 Jun 2005 inclusive. This is also consistent with BF's usual rules about Inclusive and Exclusive Dates. However, there is a way you can specify that interest is paid on the finish date, using ProjMode - for details see ProjMode and Inclusive Dates.