I made an Excel template that you can use as the effective interest method of amortization calculator.

Download the template from the following link.

Download Template

Know more how to use this template from this article: Effective Interest Rate Method Excel Template (Free)

Effective interest method of amortization calculator (user guide)

1) For a bond that is sold in discount

Say your company is selling a Bond with the following details:

• Face Value: $50,000
• Stated Rate: 8%
• Maturity Period (Years): 5
• Payment Frequency/Year: 2
• Market Rate/Effective Rate: 10%

You might ask yourself what will be the issue price of the bond?

You see from the following image; the template is providing you the issue price of the bond.

You will also find all the calculations related to the bond for the next 5 years (the maturity period of the bond).

You will get the following information (automatically calculated for you) from the template:

• Cash account status
• Interest Expenses account status
• Discount on Bonds Payable account status
• Balance with the Discount on Bonds Payable account
• Book value or carrying value of the bond

2) For a bond that is sold in premium

Here are the details of a bond that is sold in premium:

• Face Value: $100,000
• Stated Rate: 7%
• Maturity Period (Years): 5
• Payment Frequency: 2
• Market Rate/Effective Rate: 5%

What will be the issue price of this bond? The template calculates it for you and it is $108752.06.

And here are the other calculations of the bond.

So, it is so easy to see all the calculations with just a few keystrokes.

What you have to input in this template are:

• The face value of the bond
• Stated rate or nominal interest rate. It is also called the annual percentage rate (APR)
• The maturity period of the bond (in years)
• Payment frequency. Whether the bondholder will get paid monthly, quarterly, semi-annually or yearly
• The market rate of the bond. It is also called an effective interest rate.

So, it is very easy and good fun to get all the calculations from just a few inputs.

This template will be a really great help for recording and processing all your accounting procedures for the lifetime of the bond. Just one problem is: you have to know how this effective interest rate of amortization process works for a bond.

Download the Excel Template

The post Effective Interest Method of Amortization Calculator (Free Download) appeared first on ExcelDemy.