Khan Muhammad Kawser
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.
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)
- Now, press Enter and you will get the Issue Price.
- Next, go to cell B13 and insert the following formula, and press Enter:
=IF(ROW()-13<=$D$6*$D$7,ROW()-13,"")
- 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),"")
- Now, double-click on cell D14 and insert this formula :
=IF(B14<=$D$6*$D$7,H13*($D$8/$D$7),"")
- 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)
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.
