Calculator for Effective Interest Method of Amortization

Written by Kawser Ahmed
Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 2 practical examples to create an effective interest method of amortization calculator in excel. Throughout this tutorial, you will also learn valuable excel tools and functions which will be useful in other excel related tasks.


Download Practice Workbook

You can download the practice workbook from here.


What Is the Effective Interest Method of Amortization?

The effective interest method is used to distribute the interest expense over the whole life period of a financial instrument. It is done using the standard rate and the market rate of the instrument. The effective interest method can have 3 cases namely discount, premium, and at par. The standard rate and the market rate vary between the 3 cases.


2 Practical Examples to Create a Calculator for Effective Interest Method of Amortization

We have taken a relatively concise dataset for this tutorial to explain the steps clearly. This excel dataset has approximately 16 rows and 7 columns. Note that, to enter the percentages values, you have to first format those specific cells as Percentages.

Effective Interest Method of Amortization Calculator


1. Issuing Bond at Discount

One of the use cases of the effective interest method of amortization calculator is when you issue a bond at discount. Let us see how to build our calculator in such cases.

Steps:

  • First, go to cells D4 to D8 and insert all the values as in the image below.
  • Then, enter the following formula in cell D9:
=-PV($D$8/$D$7,$D$6*$D$7,$D$4*($D$5/$D$7),$D$4)

calculating issue price to create effective interest method of amortization calculator

  • Now, press Enter and you will get the Issue Price.

issue price to create effective interest method of amortization calculator

  • Next, go to cell B13 and insert the following formula, and press Enter:
=IF(ROW()-13<=$D$6*$D$7,ROW()-13,"")

payment number calculation to create effective interest method of amortization calculator

  • Then, navigate to cell C14 and type in the below formula, and press Enter:
=IF(B14<=$D$6*$D$7,$D$4*($D$5/$D$7),"")

interest payment calculation to create effective interest method of amortization calculator

  • Now, double-click on cell D14 and insert this formula :
=IF(B14<=$D$6*$D$7,H13*($D$8/$D$7),"")

interest expense calculation to create effective interest method of amortization calculator

  • Here, press the Enter key and go to the next column.
  • Now, double-click on cell E14, enter the below formula, and press Enter:
=IF(B14<=$D$6*$D$7,D14-C14,"")

  • After that, click on cell F13 and type in the below formula:
=G13-H13

  • Similarly, go to cell F14 and enter the formula below:
=IF(B14<=$D$6*$D$7,F13-E14,"")

  • Now, move to cell G13 and insert this formula, and press Enter:
=IF(B13<=$D$6*$D$7,$D$4,"")

  • Next, insert this formula in cell H13 and again press Enter:
=$D$9

  • Finally, go to cell H14 and insert this final formula:
=IF(B14<=$D$6*$D$7,G14-F14,"")

Read More: How to Calculate Effective Interest Rate On Bonds Using Excel


2. Bond Issued at Premium

This case occurs when the buyer has paid a higher rate than the par value of a financial instrument. So, as you can see below, the Stated Rate is higher than the Market Rate. In this case, you need to modify the Issue Price formula and insert it like below:

=-PV($D$8/$D$7,$D$6*$D$7,$D$4*($D$5/$D$7),$D$4)

premium bond to create effective interest method of amortization calculator

Now, if you press the Enter key, you should get the updated Issue Price and all other necessary parameters in the two tables.


Things to Remember

  • The effective interest method is not applicable when the bond is issued at par.
  • The $ dollars signs in the formula denote absolute referencing.
  • Note that the PV function is a financial function and returns the present value of an investment but only assumes periodic, constant payments and a constant interest rate.
  • Throughout this tutorial, by financial instruments, we mean certain documents and contracts which act as financial assets.

Conclusion

I hope that you were able to apply the methods I showed in the tutorial to create an effective interest method of amortization calculator in excel. Also, you should try changing the input values to some extent and see how the table values change. If you get stuck in any steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo