Sheet 1: About



Sheet 2: Instructions

	
	

		
Property Reality | Bond Calculator Template
	
	

		
Instructions
	
	

		
www.propertyreality.co.za
	
	

		


	
	

		
Bond calculators are sometimes also referred to as home loan calculators or mortgage calculators. The aim of this free Excel template is to enable users to calculate monthly bond repayments, determine the affordability of a home loan, calculate the interest savings that result from increased bond instalments and measure the sensitivity of bond repayments to changes in interest rates. After using this template, you will also gain a better understanding of home loan amortization and specifically the timing of capital repayments on a bond.
	
	

		


	
	

		
The following sheets are included in this template:
	
	

		


	
	

		
BondCalculator Sheet
	
	

		


	
	

		
All the calculations in this template are based on the input values that are entered in the cells from cell B4 to B9 on the BondCalculator sheet (except for the net disposable income calculation). Input guidance is displayed below the selected input cell. We have also added data validation to all input cells to ensure that only valid user input is accepted.
	
	

		


	
	

		
Calculation Results
	
	

		


	
	

		
The monthly bond repayment amount is calculated from the bond amount (cell B4), bond period (cell B6) and the annual interest rate (cell B5).
	
	

		
The total interest incurred over the entire bond period is the total interest which has to be paid over the entire bond period.
	
	

		
The total bond repayments over the bond period is the sum of all the monthly bond repayment amounts. This amount consists of all interest charges and capital repayments.
	
	

		
The monthly net disposable income is calculated on the NetDisposable sheet - refer to this sheet for more information on the calculation.
	
	

		
The maximum bond qualification amount is calculated based on the net disposable income, annual interest rate and bond period. It represents an estimate of the maximum bond amount that applicants can qualify for based on their combined monthly net disposable income. There are a number of other factors that financial institutions will consider when determining the maximum bond qualification amount - our calculation should therefore only be seen as an estimate which cannot be guaranteed.
	
	

		
The minimum required net disposable income is the minimum net disposable income that is required in order to qualify for the bond amount that is entered in cell B4.
	
	

		
The interest rate safety percentage indicates the percentage by which interest rates have to increase before the monthly net disposable income would not be sufficient to cover the required monthly bond repayments.
	
	

		
The increased instalment calculations in row 21 to 26 are based on the additional monthly bond repayment that is entered in cell B7. The assumption is made that the full additional bond repayment is deducted from the outstanding capital balance, thereby resulting in a shorter bond repayment period and a saving in interest. Note that the present value of the interest saving is calculated by discounting the monthly interest savings by the average annual inflation rate (specified in cell B9) over the entire bond period. It therefore represents the value of future interest savings in today's monetary terms.
	
	

		
The interest rate sensitivity calculation measures the effect that changes in the bond interest rate have on monthly bond repayments. The interest rate sensitivity percentage that is entered in cell B8 is used for this purpose. Refer to the BondRepayment sheet for an analysis of monthly bond repayments.
	
	

		
The capital repayment chart is a visual display of the timeline of capital repayments over the entire bond period.
	
	

		
The increased instalment interest saving chart is a visual display of the interest savings that result from effecting increased monthly bond instalments.
	
	

		


	
	

		
BondRepayment Sheet
	
	

		


	
	

		
This sheet includes an analysis of monthly bond repayments based on the input values that are entered on the BondCalculator sheet. No user input is required on this sheet. The monthly bond repayment calculations are based on bond periods of 15, 20 and 30 years and include interest rates in a range of between 3% more and less than the annual bond interest rate that is specified on the BondCalculator sheet.
	
	

		


	
	

		
NetDisposable Sheet
	
	

		


	
	

		
This sheet includes a detailed calculation of the monthly net disposable income. All values should be entered as positive values. Refer to the guidance included from row 38 downwards for more information on the input which is required in each input cell.
	
	

		


	
	

		
AnnualAmort Sheet
	
	

		


	
	

		
This sheet includes an annual amortization table which is based on the bond input values that are entered in cell B4 to B6 on the BondCalculator sheet. No user input is required on this sheet. We recommend that you pay special attention to the outstanding capital percentage in column G because it indicates how the capital will be repaid over the entire bond period. You'll notice that during the first few years of the bond repayment period, the monthly bond repayments consist almost entirely of interest charges and during the latter years the interest is reduced as the outstanding bond balance is reduced and the capital portion of the bond repayment therefore increases.
	
	

		


	
	

		
MonthAmort Sheet
	
	

		


	
	

		
This sheet includes a monthly amortization table that is based on the bond input values that are entered in cell B4 to B6 on the BondCalculator sheet. No user input is required on this sheet.
	



Sheet 3: BondCalculator

	
	
	

		
Bond Calculator
		


		


		

 
		
		
	

		
© www.propertyreality.co.za
		


		


		
	

		
Input Variables
		


		


		
	

		
Bond Amount
		
 2,500,000.00 
		


		
	

		
Annual Interest Rate
		
9.00%
		


		
	

		
Bond Period in Years
		
 20 
		


		
	

		
Additional Monthly Bond Repayment
		
 2,000.00 
		


		
	

		
Annual Interest Rate Sensitivity
		
10.00%
		


		
	

		
Average Annual Inflation Rate
		
6.0%
		


		
	

		


		


		


		
	

		
Calculation Results
		


		


		
	

		
Monthly Bond Repayment
		
 22,493.15 
		


		
	

		
Total Interest Incurred over Bond Period
		
 2,898,355.74 
		


		
	

		
Total Bond Repayments over Bond Period
		
 5,398,355.74 
		


		

 
		
		
	

		


		


		


		
	

		
Net Disposable Income
		
 25,000 
		


		
	

		
Maximum Bond Qualification Amount
		
 2,778,623.85 
		


		
	

		
Minimum Required Net Disposable Income
		
 22,493.15 
		


		
	

		
Interest Rate Safety
		
1.52%
		


		
	

		


		


		


		
	

		
Increased Instalment Repayment Amount
		
 24,493.15 
		


		
	

		
Total Adjusted Interest over Bond Period
		
 2,254,351.06 
		


		
	

		
Adjusted Bond Repayment Period (in months)
		
 194.11 
		


		
	

		
Adjusted Bond Repayment Period (in years)
		
 16.18 
		


		
	

		
Interest Saving
		
 644,004.67 
		


		
	

		
Present Value of Interest Saving
		
 304,347.08 
		


		
	

		


		


		


		
	

		
Monthly Bond Repayment @ 9.00%
		
 22,493.15 
		


		
	

		
Monthly Bond Repayment @ 10.00%
		
 24,125.54 
		


		


		


		


		


		


		


	
	

		
Monthly Difference
		
 1,632.39