Effective Interest Rate Method Excel Template
“No Macro used. No installation is necessary. Just download and start using.” – Kawser Ahmed (Template Developer)
Created using Excel 2016
License: Personal Use (Not for resale or distribution)
Let us know (in the comment box) your criteria or the problems that you’re facing while using this template. We shall update the template.
You will get two templates inside the workbook:
- Bonds that are sold in discount
- Bonds that are sold in premium
You will have to provide very few inputs. The inputs are:
- The face value of the bond
- Stated Interest Rate / Nominal Interest Rate
- Maturity Period of the Bonds (in Years)
- Payment Frequency of the Bond in Every Year
- Market Rate or Effective Interest Rate of the Bond
This template will output the Issue price of the Bond and show the other calculations.
Effective Interest Rate Method Excel Template Examples
Example 1 – For Discounted Bonds
Take a look at the following image. It shows the amortization table for a bond with the following details:
- Face Value: $100,000
- Stated Rate: 6%
- Maturity Period: 3 Years
- Payment Frequency: Semi-annual
- Market Rate/Effective Interest Rate: 8%
The template shows that the Issue Price of the bond will be $94,757.86
We will change the maturity period from 3 years to 5 years and payments will be done quarterly.
- Face Value: $100,000
- Stated Rate: 6%
- Maturity Period: 5 Years
- Payment Frequency: Quarterly
- Market Rate/Effective Interest Rate: 8%
Read More: How to calculate effective interest rate on bonds using Excel
Example 2 – For Premium Bonds
See how the Excel template works for bonds that are sold in premium with the following information:
- Face Value: $100,000
- Stated Rate: 7.000%
- Maturity Period (Years): 5
- Payment Frequency: 2
- Market Rate/Effective Rate: 5.000%
Check out the following image. The template provides issue price as $108752.06 and other calculations for the bond maturity period.
If you don’t know the basics of the effective interest method, you can check out this article: Effective Interest Method of Amortization in Excel
Read More: How to Calculate Effective Interest Rate in Excel with Formula
Related Articles
- Effective Interest Rate Formula Excel + Free Calculator
- How to Calculate Interest Rate in Excel (3 Ways)
- Calculate Future Investment Value with Inflation, Tax and Interest Rates
- Effective Interest Method of Amortization Calculator (Free Download)
- Nominal vs Effective Interest Rate in Excel (2 Practical Examples)
Greetings,
Please update the template for a bond purchased at a discount using the effective interest rate method.
Hello Michelle G,
Thank you for reaching out to us. We request your attention to the above article to get your desired template on bonds purchased at a discount using the effective interest rate method.
Your requested update is incorporated into our template. Please refer to the sheet named ‘Effective Interest (Discount)’ for the template on bonds purchased at a discount using the effective interest rate method. Click on the three dots (…) if you don’t find the template in your downloaded file.
Regards
ExcelDemy