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.