Bond Amortization Schedule In Excel Template

If you have trouble with amortization, try this bond amortization schedule template. Bond amortization schedule purpose is for calculating the amortization based on the value and maturity years. It is really helpful if you want to:

  • Calculate Amortization value for both loan or intangible assets
  • Helping in accountancy calculation
  • Determine a strategy to obtain intangible assets.

In Accountancy, amortization is similar like depreciation. Only, amortization specifies in intangible assets such as brands, patent, and other. Meanwhile, depreciation is for tangible and permanent assets. Meanwhile, bond amortization meaning is financial certificate that has been reduced in value for recording on financial statements.

The bond amortization excel can be used for small to big company or individuals. It can be used regardless of the industry type. You can turn the bond amortization schedule calculator into depreciation calculator. However, you may define the formula by your own especially straight line method.

bond amortization excel

THE MENUS

There are two sheets in bond amortization schedule template. Both sheets are same, only one has dark design on the spreadsheet. The other one has light design for printing with more white color, which saves more ink.

There are only two elements in the sheets:

1. Information fields: for entering the amortization information. The information is for calculating the fields on Amortization Calculator. The fields are:

  • Bond purchase price: purchasing amount
  • Face value of bond: nominal value or dollar value of a security stated by the issuer.
  • Years until maturity: their maturity terms.
  • Coupon rate: amount of interest paid per year as a percentage of the face value or principal.
  • Effective rate: Actual interest paid on a loan, or earned on a deposit accounts.

2. Amortization Calculator: table for displaying the amortization rate in both methods. The columns are:

  • Payments: the periods of payment. The payments period is set on every 6 months.
  • Cash Paid: amount of cash that should be paid in each period.
  • Expense: amount of interest paid or earned from the bond.
  • Amortization of (Discount) or Premium: amount of amortization after paid cash and expense.
  • Carrying amount: the balance of the bonds value left after amortization.
Related Template:  Wedding Budget Calculator Excel Template
bond amortization schedule excel effective interest method

HOW TO USE

It is totally simple to use bond amortization schedule template. You only need to enter the Information field. The calculator will automatically calculate the result.

Payments period is set on every 6 months, so the Payment schedule will be doubled after the maturity years field. It is not suggested to change the interval.

Cash Paid come from multiplication between Face Value with Coupon Rate.

Expense use Effective Rate and Purchase price multiplication for determining the amount.

Amortization of (Discount) or Premium gained after the Cash Paid subtracted with Expense column.

Meanwhile, carrying amount comes from Purchase Price subtracted with Amortization of (Discount) or Premium column.

bond amortization schedule calculator

The bond amortization formula can determine the maximum payment schedule period too. So, if you enter unrealistic maturity year, it will only calculate until the maximum period.

The provided bond amortization schedule calculation can be used for analyzed.

Bond Amortization Schedule Use Straight Line Method

bond amortization schedule straight line method

This bond amortization schedule excel use effective interest method. However, you can turn it to Straight Line Method:

1. Delete the Expense and Amortization of (Discount) or Premium columns

2. On Cash Paid column, make sure that the formula is this:

=([@PAYMENTS]>0)*FaceValue*CouponRate*0.5

NOTE: 0.5 come from maturity years which is biyearly

3. On Carrying amount, change the formula into this:

=IF([@PAYMENTS]=1,FaceValue-[@[CASH PAID]],$F14-[@[OR PREMIUM]])*([@PAYMENTS]>0)

4. Copy them into following rows and columns.

ADDITIONAL NOTES

On Print Version, there may be an error regarding Years of Maturity field. The said field may not be linked with Digital version. To link it, just select the field. Then, press equals (=). Go to Digital Version and click the Year of Maturity field.

bond amortization formula

CONCLUSION

The bond amortization schedule template is easy to use. However, to use and modify you may need to learn or know about accountancy. Or, you know about financial management regarding bonds and securities.

You can download bond amortization schedule template free after you click one of this button:

Related templates

  • amortize schedule template for purchased intangibles