bond calculator (1)

7/25/2019 Bond Calculator (1)

1/13

Excel Skills | Bond Calculator TemplateAbout this templateBond calculators are sometimes also referred to as home loan calculators or mortgage calculusers to calculate monthly bond repayments, determine the affordability of a home loan, calculinstalments and measure the sensitivity of bond repayments to changes in interest rates.

understanding of home loan amortization and specifically the timing of capital repayments on aAbout our unique templatesOur uniue, practical templates produce results that are more comparable to software solutiononly contain limited functionality but our templates produce complex automated reports basedExcel s$ills in order to use our templates % all our templates only reuire basic user input and incAbout our other templatesThis free template forms part of our uniue range of innovative Excel templates which featurevaluations ' loans, costing ' inventory, personal finance, sales and a lot more. (isit the Temtemplates or register for a full membership to purchase all our uniue templates.Our full membership includes:) Access to all *+ of our uniue Excel templates) -/ days access to our -++ Excel video tutorials

Vie a list of all our templatclick here


2/13

tors. The aim of this free Excel template is to enablete the interest savings that result from increased bondfter using this template, you will also gain a better

ond.

s than regular Excel templates. !ost Excel templateson limited user input. "ou also don#t need advanced

lude comprehensive step by step instructions.

s accounting in Excel, cash flow pro&ections, businesslates pages of our website to view samples of all our

s !e"ister for a full membershipclick here


3/13

0age - of 1-

Excel Skills | Bond Calculator TemplateInstructions

The following sheets are included in this template2

BondCalculator Sheet

Calculation Results:

The total interest paid over the entire bond period is the total amount of interest that will have to be paid over the entire bond period.

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

#et$isposable Sheet

AnnualAmort Sheet

%onthAmort Sheet

&elp

3f you experience any difficulty while using this template, please e%mail us at support4excel%s$ills.com for assistance.

'excel(skills'com

Bond calculators are sometimes also referred to as home loan calculators or mortgage calculators. The aim of this free Exceltemplate 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 capitalrepayments on a bond.

All the calculations in this template are based on the values that are entered in the input cells from cell B* to B5 on theBond6alculator sheet 7except for the net disposable income calculation8. 3nput guidance is displayed below the selected input cell.9e have also added data validation to all input cells to ensure that only valid user input is accepted.

The monthly bond repayment amount is calculated from the bond principle amount 7cell B*8, bond period 7cell B8 and the annualinterest rate 7cell B/8.

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

The monthly net disposable 3ncome is calculated on the :et;isposable sheet % refer to this sheet for more information on thecalculation.

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

The minimum reuired net disposable income is the minimum net disposable income that is reuired in order to ualify for the bondprinciple amount that is entered in cell B*.

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

The increased instalment calculations in row downwards for more information on the input that is reuired in eachinput cell.

This sheet includes an annual amortization table that is based on the bond input values that are entered in cell B* to B on theBond6alculator sheet. 9e recommend that you pay special attention to the outstanding capital percentage in column @ as itindicates how the capital will be repaid over the entire bond period. "ou#ll notice that during the first few years of the bondrepayment period, the monthly bond repayments consist almost entirely of interest.

This sheet includes a monthly amortization table that is based on the bond input values that are entered in cell B* to B on theBond6alculator sheet.
http://www.excel-skills.com/http://www.excel-skills.com/


4/13

0age * of 1-

Bond Calculator

www.excel%s$ills.com

)nput Variables

Bond Amount 1,+++,+++.++

Annual 3nterest ?ate 1+./+

Bond 0eriod in "ears +

Total 3nterest over Bond 0eriod 1,-5,111.=-

Total Bond ?epayment over Bond 0eriod +1,>//.

Ad&usted Bond ?epayment 0eriod 7in months8 1/+.-

Ad&usted Bond ?epayment 0eriod 7in years8 1

!onthly ;ifference -->.+5* + , - . / 0 1 2 *3 ** *+ *, *- *. */ *0 *1 *2

(

+334333'33

-334333'33

/334333'33

1334333'33

*43334333'33

*4+334333'33

*4-334333'33

*4/334333'33

)ncreased )nstalment ( )nterest Sa5in

6ears

* + , - . / 0 1 2 *3 ** *+ *, *- *. */ *0 *1 *2

(

+334333'33

-334333'33

/334333'33

1334333'33

*43334333'33

*4+334333'33Capital !epa7ment

6ears


5/13

0age / of 1-

#et $isposable )ncome Calculation

!emuneration Operational Expenses 8inancin" Expen

@ross Calary 1+,+++ 1 ?ent 0aid % 1> Bond ?epayment

Cubsidies ?eceived % < 9ater, Electricity ' Cervices % 15 0ersonal oan 3n

6ommission ?eceived % - ?ates ' Taxes %

!onthly 0ension % 1= Entertainment % -5

Total Other )ncome ( !aintenance 0ayments % *+

Other % *1

Total Operational Expenses (

#et $isposable )ncome Calculation:et ?emuneration *34333

Add2 Other 3ncome (

ess2 Operational Expenses (

ess2 Financing Expenses (

#et $isposable )ncome *34333

)nput 8ield 9uidance

1 Enter the combined monthly gross household salaries

< Enter total of all subsidies received as part of remuneration, e.g.. housing subsidy

- Enter the average monthly commission received

* Enter the total monthly allowances received as part of remuneration, e.g.. travel allowance, cell phone allowance, etc.

/ Enter the total of any other remuneration received

Enter the total monthly pension fund deductions

= Enter the total monthly medical aid deductions

> Enter the total monthly retirement annuity contributions

5 Enter the total monthly income tax deducted

1+ Enter the total H3F deducted

11 Enter the total C; deducted

Simpl7 eninput cellsdisposab

entered as pinput thatf


6/13

0age of 1-

#et $isposable )ncome Calculation

1< Enter the total of all other deductions, e.g.. funeral plan

1- This amount represents the monthly combined net remuneration

1* Enter the average monthly dividend income

1/ Enter the monthly average rental income received from existing buy to let properties

1 Enter monthly maintenance amount received from a previous spouse

1= Enter the total monthly pension received

1> Enter the total monthly rent paid. 3f a primary residence is being acuired and a property is currently being rented, the rent amount should be excluded from the calculation

15 Enter the monthly average for water, electricity and services paid to local government


7/13

0age = of 1-

Annual Amortiation Table


6ears Capital !epaid

1 1,+++,+++.++ 115,>+/./5 1+*,*,*+.=+ 5>.*

< 5>*,*+.=+ 115,>+/./5 1++ 1=,+/./5 1++,/

* 5*>,+//.5< 115,>+/./5 5>,/*1.>= =5.+ >*.>

> >*=,>=5.+ 115,>+/./5 >=,/+1.

5 >1/,/=.1+ 115,>+/./5 >-,5*-.*/ ==5,=1.+

1+ ==5,=1+/./5 =5,5>5.> -5,>1/.=- =-5,>5.5< =*.+

11 =-5,>5.5< 115,>+/./5 =/,+.*< *.=

1- *,1>.*< 115,>+/./5 /,-+/./5 /5,5*+. 115,>+/./5 -=,+-/.* > -+=,1=+.=< 115,>+/./5 51.*>


8/13

0age > of 1-

%onthl7 Amortiation Table


Capital !epaid

1 1,+++,+++.++ 5,5>-.>+ >,=/+.++ 1,+ 55>,=.>

< 55>,=.-.>+ >,=-5.,=1=.-- 1,-.>+ >,=+. 551,1--.- 5,5>-.>+ >,= 5>5,> 5>.5>

5 5>5,> 5,5>-.>+ >,+.5* 1,->,*55.1< 5>.>/

1+ 5>>,*55.1< 5,5>-.>+ >,*5.-= 1,--*.*- 5>=,1*.5 5>.==,1*.5 5,5>-.>+ >,-=.5 1,-*.11 5>/,>1>./> 5>./>

1< 5>/,>1>./> 5,5>-.>+ >,5 5>*,*+.=+ 5>.*/

1- 5>*,*+.=+ 5,5>-.>+ >,1*.+- 1,-5.== 5>-,+5+.5- 5>.-1

1* 5>-,+5+.5- 5,5>-.>+ >,+1.=/ 5>1,=+5.1> 5>.1=

1/ 5>1,=+5.1> 5,5>-.>+ >,/>5.5 1,-5-.>* 5>+,-1/.-- 5>.+-

1 5>+,-1/.-- 5,5>-.>+ >,/==.= 1,*+.+* 5=>,5+5.5

1= 5=>,5+5.-.>+ >,//.* 1,*1>.-* 5==,*5+.5/ 5=.=/

1> 5==,*5+.5/ 5,5>-.>+ >,//-.+/ 1,*-+.=/ 5=,++.+ >,/*+./- 1,**-.+ >,/,/1/.1 1,*>.* 5=1,5,/+1.*5 5=+,5 5=.++ >,*>5.-/ 1,*5*.*/ 5>,=1.** 5.>=

,=1.** 5,5>-.>+ >,*=.,*-.+> 1,/>.15 5./=

>.15 5,5>-.>+ >,**5.== 1,/-*.+- 5*,1/*.1 5.*+ >,*-.-/ 1,/*=.*/ 5,*+ >,*+5.1/ 1,/=*./ 5/5,*=1.+= 5/.5/

-+ 5/5,*=1.+= 5,5>-.>+ >,-5/.-= 1,/>>.*- 5/=,>>>-.>+ >,->1.*= 1,+-.>+ >,-=.*/ 1,1.-/ 5/*,-.5> 5/.*=

-- 5/*,-.5> 5,5>-.>+ >,-/-.-1 1,-+.*5 5/-,+--.*5 5/.-+

-* 5/-,+--.*5 5,5>-.>+ >,--5.+* 1,**.= 5/1,->>.=- 5/.1*

-/ 5/1,->>.=- 5,5>-.>+ >,--.>+ >,-1+.1- 1,=-.= 5*>,+//.5< 5*.>1

-= 5*>,+//.5< 5,5>-.>+ >,>.-1 5*,-=.1 5*.*-> 5*,-=.1 5,5>-.>+ >,+.=< 1,=+-.+> 5**,*./- 5*.*=

-5 5**,*./- 5,5>-.>+ >,1 1,=1=.5> 5*, 1,=--.+< 5*1,,.1> 5-5,*/.-* 5-.5/

*< 5-5,*/.-* 5,5>-.>+ >,= 5-.==

*- 5-=,=+1.>= 5,5>-.>+ >,5 1,==>.51 5-/,55.-- 1,=5*.*= 5-*,1.*5 5-.*1

*/ 5-*,1.*5 5,5>-.>+ >,1=-.< 1,>1+.1= 5-.-1 5-.-.>+ >,1/=.=5 1,>,1*1.>1 1,>*1.55 5,/+.-1 5=

*> 5,/+.-1 5,5>-.>+ >,1/>.11 5,+5-.+- 1,>5+.== 5,+** 51=,+ >,+ 1,5/=.>< 51/,+ >,++>.>/ 1,5=*.5/ 51-,-


9/13

0age 5 of 1-



Capital !epaid!epa7ment

#umberOpenin"Balance

;oan!epa7ment

)nterestChar"ed

Closin"Balance

< CapitalOutstandin"

< 5+1,1+*.-.>+ =,>>*. 55,++/.+> >5.5+

- >55,++/.+> 5,5>-.>+ =,>.>=./= >5.5

* >5,>>=./= 5,5>-.>+ =,>*=.== 5*,=/1./* >5.*>

/ >5*,=/1./* 5,5>-.>+ =,> 5< >5.5< 5,5>-.>+ =,>1+.5+,*5+,*+ =,=51.,>.> >>>,-.>+ =,==,+1>.>= >>.+

5 >>,+1>.>= 5,5>-.>+ =,=/=.=- >>.->

=+ >>-,=>=.=- 5,5>-.>+ =,=--.1* 1,/-=.+> >>.1/

=1 >>1,/-=.+> 5,5>-.>+ =,=1-.*/ =5,-.>+ =,5-./> =,5=./1 5,5>-.>+ =,=-./* =*,.-.>+ =,/-.-- ==.=-.>+ =,-5,5>*.5- >=.++

= >5,5>*.5- 5,5>-.>+ =,1.=

== >=,1-./+ 5,5>-.>+ =,/51.< /,/,+ =,/=+.5 +>..

=5 >+>.-.>+ =,/*5./= >.+*

>+ >+,-=-.5> 5,5>-.>+ =,/..*/ >/.=5

>1 >/=,51>.*/ 5,5>-.>+ =,/+.=5 //,**1.** >/./*

>< >//,**1.** 5,5>-.>+ =,*>/.11 .5 >//+ =,*-.*=,>=5.+ 5,5>-.>+ =,*1>.5/ / >*/,-1*.=* >*./-

> >*/,-1*.=* 5,5>-.>+ =,-5./+ =.**+ =,-=-.>= *+,11=./< >*.+1

>> >*+,11=./< 5,5>-.>+ =,-/1.+- *.=/ >-.=/

>5 >-=,*>*.=/ 5,5>-.>+ =,--*,>.5* >-.*>

5+ >-*,>.5* 5,5>-.>+ =,-+*.=/ -5 >-.-5 5,5>-.>+ =,1.-1 + =,*

5 >1>,-5>.51 5,5>-.>+ =,1+.55 1/,/=.1+ >1./

5= >1/,/=.1+ 5,5>-.>+ =,1-.11. >1-.>+ =,111.-> =+5,>/.1= >+.5555 >+5,>/.1= 5,5>-.>+ =,+>.+,5/>.1 >+.=+

1++ >+,5/>.1 5,5>-.>+ =,++.>5 +*,+-/.=+ 5,5>-.>+ =,+-/.-1 .*5 >+1,+>=.+.11

1+< >+1,+>=.-.>+ =,++5./1 ,11-.>+ ,5>-.*5 -,+++.-1 =5/,11+ ,5/=.5,+--.+1 =>.5+

1+ =>5,+--.+1 5,5>-.>+ ,5+*.+* -,+=5.= =>/,5/-..+

1+= =>/,5/-.-.>+ ,>==.+5 -,1+.=1 =>*./* =>.

1+> =>*./* 5,5>-.>+ ,>*5.51 -,1--.>5 ==5,=1+ ,>+ ,=5*.>< -,1>>.5= ==-,-+ ,=.5< -,> ==+,1*/.*> ==.+1

11< ==+,1*/.*> 5,5>-.>+ ,=->.== -,-.>+ ,=1+.-> -,1.=* -,-+-.>+ ,/* -,--+.5 =/,55*.+< =/.=+

11 =/,55*.+< 5,5>-.>+ ,-.>+ ,/5*.-+ -,->5./+ =/+,+ ,/*.* -,*15.1 =*,>+ ,/-*.=< -,**5.+> =*-,-=.1> =*.-*

1 5,5>-.>+ ,/+*./* -,*=5.5.5< =-.55

15.5< 5,5>-.>+ ,*=*.1+ -,/+5.=+ =-,->=.+ ,**-.-5 -,/*+.*1 =-*.>1 =-.


10/13

0age 1+ of 1-





;oan!epa7ment

)nterestChar"ed

Closin"Balance


11 5,5>-.>+ ,*1 55,//.+/ 5.5

1-< 55,//.+/ 5,5>-.>+ ,1 5/,5-.- 5./=

1-- 5/,5-.- 5,5>-.>+ ,+>=.-< -,>5.*> 51,=5.>> 5.1>

1-* 51,=5.>> 5,5>-.>+ ,+/-. >=,>.-+ >.=5

1-/ >=,>.-+ 5,5>-.>+ ,+1>.>- -,5*.5= >-,5+1.-* >.-5

1- >-,5+1.-* 5,5>-.>+ /,5>*.1* -,555. =5,5+1.= =.55

1-= =5,5+1.= 5,5>-.>+ /,5*5.1* *,+-*. =/,>=.+1 =./5

1-> =/,>=.+1 5,5>-.>+ /,51-.>* *,+5.5 =1,=5=.+/ =.1>

1-5 =1,=5=.+/ 5,5>-.>+ /,>=>. .==

1*+ =,51.*> 5,5>-.>+ /,>* .-/

1*1 -,/*5.5> 5,5>-.>+ /,>+.+ *,1==.=* /5,-=.- *,1>.*< *.

1*/ *,1>.*< 5,5>-.>+ /,/=.51 *,-5 * --,/= 5,5>-.>+ /,/*-.- *,**+.** .*- -.>+ /,/+*./1 *,*=5../ ./ 5,5>-.>+ /,* *,//>.+< 1/,/-+.- 1.//

1/< 1/,/-+.- 5,5>-.>+ /,->/.>5 *,/5=.51 1+,5-+ /,-*/. *,->.1* +,+ /,-+/.+> *,=>.=< +1,1/.> +.1

1// +1,1/.> 5,5>-.>+ /,5.5.-.>+ /,=,---.>+ /,1-5.1 *,>**.* />=.55 />.=.55 5,5>-.>+ /,+5.== *,>>=.+- /==,++.5 /=.=1+ /==,++.5 5,5>-.>+ /,+/*.+1 *,5+ *,5=.- /,+1.** /1.>1 /.1 5,5>-.>+ *,5-.>+ *,>=5.15 /,1+*.1 //= //.-.>+ *,>-*./< /,1*5. /*=,-=./5 /*.=*

1 /*=,-=./5 5,5>-.>+ *,=>5.*= /,15*.-- /*.1= /,/.- /-1,*=.>* /-.1

15 /-1,*=.>* 5,5>-.>+ *,/1.5< /,--1.>> /-.>+ *,/1+.=- /,*=-.+= /1+,+->.= /1.++

1=- /1+,+->.= 5,5>-.>+ *,** /,/+ /+.*/

1=* /+*,/1=.>+ 5,5>-.>+ *,*1*./- /,/5.,5*>./- *5.>5

1=/ *5>,5*>./- 5,5>-.>+ *,-/.>+ /,1>.++ *5-,--+./- *5.--

1= *5-,--+./- 5,5>-.>+ *,-1.* /,=.1 *>=,-.-> *>.==

1== *>=,-.-> 5,5>-.>+ *,1,5*.- *>.15

1=> *>1,5*.- 5,5>-.>+ *,= *=.= 5,5>-.>+ *,1./= /,>1=.+ *,11/.= /,>>.1- **,*5*./< *.*/

1>1 **,*5*./< 5,5>-.>+ *,+*.-- /,515.*= */>,/=/.+* */.>

1>< */>,/=/.+* 5,5>-.>+ *,+1+ -,5+. ,++.


11/13

0age 11 of 1-





;oan!epa7ment

)nterestChar"ed

Closin"Balance


1>* **,/>+.-.>+ -,5+=./> ,+=./ **+,/+*.+* 5,5>-.>+ -,>/*.*1 ,1 *-*,-=*./ 5,5>-.>+ -,>++.=> ,1>-.+< *,151.- *= *,151.- 5,5>-.>+ -,=*.> ,+ -,-=.+/ ,-*.=/ *+5,-1.+ *+.5-

15+ *+5,-1.+ 5,5>-.>+ -,/>1./< ,*++ -,/.-+ -5,*//.*> -5./

15< -5,*//.*> 5,5>-.>+ -,*>.55 ,/1*.>1 ->5,5*+. ->.55

15- ->5,5*+. 5,5>-.>+ -,*11.5> ,/=1.>< ->-,->.>* ->.-*

15* ->-,->.>* 5,5>-.>+ -,-/*.*> ,-.>+ -,=.-- -=+,+/+ -,1=>.5- ,>+*.>= -/,/+1.*> -/./

15> -/,/+1.*> 5,5>-.>+ -,115.-5 ,>*.*1 -*5,-=.+= -*.5

155 -*5,-=.+= 5,5>-.>+ -,+/5.-< ,5


12/13

0age 1< of 1-





;oan!epa7ment

)nterestChar"ed

Closin"Balance



13/13

0age 1- of 1-





;oan!epa7ment

)nterestChar"ed

Closin"Balance


-+ % % % % % +.++

-+= % % % % % +.++

-+> % % % % % +.++

-+5 % % % % % +.++

-1+ % % % % % +.++

-11 % % % % % +.++

-1< % % % % % +.++

-1- % % % % % +.++

-1* % % % % % +.++

-1/ % % % % % +.++

-1 % % % % % +.++

-1= % % % % % +.++

-1> % % % % % +.++

-15 % % % % % +.++

-

bond calculator (1)

Documents