What does it do?
Bonds are a form of debt finance used by a business. When a bond is issued at a value above or below its par value, a premium or discount is created. In order to account for the bond properly, this premium or discount needs to be amortized over the lifetime of the bond.
This bond amortization calculator can be used for any bond up to a maximum term of 200 interest payment periods. The calculator will calculate both straight line method amortization, and will also produce an effective interest method bond amortization schedule, setting out for each period, the bond book value opening balance, amount of interest, periodic payment, closing bond balance, and the bond premium or discount amortization.
Bond Amortization Calculator Instructions
The Excel bond amortization calculator, available for download below, is used to calculate bond premium or discount amortization by entering details relating to the bond rate, term, payment periods, bond amount, and the market interest rate at the time the bond is issued. The bond amortization calculator is used as follows:
Step 1
Enter the annual bond rate.
Step 2
Enter the term of the bond in years.
Step 3
Enter the number of times interest payments are made on the bond each year. For example, a semi-annual bond has two interest payments each year and the number 2 would be entered.
Note: The total number of periods (term in years x number of payments each year) must not exceed 200, otherwise a error message will be shown.
Step 4
Enter the par value of the bonds issued.
Step 5
Enter the annual market interest rate at the date the bond is issued.
The bond amortization calculator calculates the bond issue price, which is a function of both the bond rate and the market rate.
Note: If the market rate is not known and the bond issue price is given, then use the Excel goal seek feature to set the bond issue price value by changing the market rate cell.
Step 6
The bond amortization calculator calculates the total premium or discount over the term of the bond. the straight line method amortization for each period, and produces an effective interest method amortization schedule showing the premium or discount to be amortized each period.
Bond Amortization Calculator Download
The Excel bond amortization calculator, available for download below, allows for any bond up to a maximum term of 200 interest payment periods, and is used by simply entering values for the bond, rates, and term.
The bond amortization schedule calculator is one type of tvm calculator used in time value of money calculations, discover another at the links below.
Users use this bond amortization calculator template at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of a amortization schedule calculator that you might use. It is purely illustrative of a bond amortization schedule excel. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the bond amortization calculator, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.