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
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.
bond amortization schedule excel effective interest method
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.

Related Template:  Sales Dashboard Excel Template

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

bond amortization schedule calculator
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
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
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

  • bond amortization schedule
  • bond amortization schedule excel
  • bond amortization schedule excel template
  • excel bond amortization formula
  • carrying amount and schedule
  • bond amortization schedule template
  • excel bond amortization template
  • excel bond template
  • excel bond calculation template
  • DAILY BOND AMORTIZATION SCHEDULE
  • straight line amortization schedule excel
  • excel bond amortization schedule
  • straight line amortization method for bond excel sample
  • bond amortization schedule in excel
  • bond amortization excel
  • bond ammortization table
  • straight-line bond amortization schedule
  • straight line method for bonds in excel
  • excel examples of straight line amortization
  • excel spreadsheet samples muni bonds
  • excel template bond premium amortization
  • How to Bond Amortization schedule to calculate payments per year in Excel
  • how to put bond amortization in excel
  • Schedule of Bond using straight-line amortizatioon
  • straight line amortization for bonds in excel
  • excel bond schedule
  • effective interest method amortization table
  • amortization effective interest schedule excel
  • amortization schedule sample using the interest method
  • benefit of straight line bond and discount bond excel
  • bond amorization excel spreadsheet
  • bond amortization excel template
  • bond amortization table finding the interest
  • bond debt schedule principal interest excel
  • bond premium amortization excel example
  • bond premium amortization excel template
  • bond valuation excel template
  • accounting bond amortization template
  • template for computing interest on bonds