Effective Interest Rate Calculation

Most South African bonds contain a variable interest rate which is linked to the prime interest rate. This rate fluctuates based on the fiscal policy determined by the South African Reserve Bank and it is therefore not a simple exercise for home owners to calculate an effective interest rate for a bond over the entire bond period.

This calculation consists of two main components - bond repayments which have already been effected since the start of the bond repayment period and bond repayments which will have to be paid over the remaining bond period.

In order for the calculation to be accurate, bond repayments which have already been made need to be included using the actual interest rate that was in effect for the specific repayment period. In most cases, the bond interest rate is linked to the prime interest rate and the applicable bond interest rate can therefore be easily determined by simply looking up the appropriate prime interest rate for the period and then applying a discount factor (if applicable).

The bond interest rate applicable to future periods is a lot more subjective. The current prime interest rate can be used for this component of the calculation or you may wish to include forecasted interest rates over the next couple of years.

Once the interest rates which are to be used in the calculation have been established, the total bond repayment amount over the entire bond period needs to be calculated. This amount is then divided by the number of repayment periods in the bond term to calculate the average bond repayment amount.

The average bond repayment amount is then included in a financial calculation together with the original bond amount and the entire bond period to determine the effective interest rate applicable over the entire bond period.

This all sounds quite complicated - we have therefore created an Excel model to facilitate the effective interest rate calculation over a ten year period. We will now provide guidance on using this model as a flexible tool for calculating the effective interest rate associated with a bond.

Our Excel Model

The model contains four worksheets (click on the link at the top of the page to download the Excel model). Let's start with the Prime sheet - this sheet contains the month end historical prime interest rates since 31 January 1997 and also provides the facility to enter forecasted interest rates for future periods.

The ActualAmort sheet is used to enter the original bond details - purchase price, bond interest rate, bond period and deposit amount. The bond start date and discount rate (enter 2.00 for 2%) can also be entered on this sheet. All input cells are displayed using a light blue background colour.

The original bond amount is calculated by deducting the deposit amount from the purchase price. This amount is then amortized over the bond period based on the interest rates displayed in column I. These interest rates are looked up from the interest rates entered on the Prime sheet. A complete amortization table applicable to the input details entered is then calculated.

The Summary sheet contains the calculation of the effective interest rate over the bond period. The cumulative bond repayments at the end of each year is determined from the detailed amortization table and the interest rate in effect at the end of each year is used to determine the bond repayments applicable to the remaining bond period.

The total of the cumulative and remaining bond repayments are then calculated before determining the average bond repayment amount over the entire bond period. This amount is then included in the Excel PMT function together with the total bond period and original bond amount variables to calculate the effective interest rate for the bond.

The EffIntAmort sheet contains an amortization table calculated based on the average bond repayment over the entire bond period. The purpose of this sheet is to indicate that the total interest paid on the effective interest rate calculated is exactly the same as the total interest paid using variable interest rates over the entire bond period (as per the ActualAmort sheet).

Using the Effective Interest Rate

When the return on investment for an existing property investment needs to be calculated, one of the problems are that you need to establish an interest rate over the entire bond period. This could be problematic because interest rates are subjected to continuous change and it is therefore difficult to base the calculation on an accurate estimate of the interest rate over an investment period of a number of years.

The calculation of an effective interest rate therefore would provide a much more accurate result than using for instance the current prime interest rate. We should point out however that even though the interest calculated over the entire bond period will be exactly the same as the interest calculated using variable interest rates at different times during the bond period, the amount of interest incurred at various stages in the bond term could differ significantly.

A detailed amortization table like the one on the ActualAmort sheet provides a more accurate calculation of bond repayments and interest over the entire bond period, but even this detailed calculation is not 100% accurate - refer to the Bond Statement vs Amortization case study for more detail on the reasons for this. The only solution able to facilitate a 100% accurate inclusion of bond interest in the calculation of investment return is the Property Reality software solution which provides the functionality to enter actual bond repayments over the entire investment period.

However, if the requirement is to simply determine the effective interest rate which will be in applicable to a bond, the Excel template provided on this page is the best solution available for the South African residential property market!

• Back to About Page
• Back to Home Page