How to Create Effective Interest Method of Amortization in Excel

The practice used to discount a bond is what we refer to as the effective interest in accounting. This expense is generally amortized to expense over the bond’s life. In this article, we will discuss the method to create an effective interest method of amortization format in Excel.

Overview of Effective Interest Method of Amortization

An amortizing loan is a loan where the principal is paid down throughout the life of the loan according to an amortization plan, often by equal payments, in banking and finance. An amortizing bond, on the other hand, is one that repays a portion of the principal as well as the coupon payments. Let’s say, the total value of the car is $200000.00, the annual interest rate is 10%, and you will pay the loan within 1 year. A Loan Amortization Schedule is a schedule showing the periods when payments are made toward the loan. Among the information found in the table is the number of years left to repay the loan, how much you owe, how much interest you are paying, and the initial amount owed.

The effective interest method of amortization just means the percentage of interest or financial product if compound interest amasses over a year with no payments. It is the true interest rate on a loan. Usually, a frequent compounding period results in a higher compounding rate.

How to Create Effective Interest Method of Amortization in Excel: 2 Suitable Examples

We will cover the method for both types of bonds:

Bonds sold on discount
And bonds sold on premium

Although the two methods are almost the same for the most part, both of them are added separately here for a better understanding of the format to calculate the effective interest method of amortization in Excel.

Example 1: Effective Interest Method of Amortization for Bonds Sold on Discount in Excel

In our first example, we have the following details.

Issue Date: 1st Jan 2018

Face Value: $100,000

Stated Rate/Nominal Rate/Coupon Rate/APR: 6%

Market Rate / Effective Annual Interest Rate: 8%

Maturity Period: 3 years

Interest Payment Frequency: Semi-annually

Issue Price: $94,757.86 (the bond is selling at a discount)

Follow the steps to see how we can calculate the effective interest method of amortization for the bond sold on discount in Excel.

Step 1: Enter Values in Journal Entry

This is how you will record the transactions that happened on the date 1st Jan 2018.

The company has received cash of amount $94,757.86. Debits increase assets: so, debit Cash $94,757.86.

It has to pay off the $100,000 (face value of the bond) after 3 years (as the maturity of the bond is 3 years). So, we have credited $100,000 to the account “Bonds Payable”.

Now let’s deal with the “Discount on Bonds Payable” account. We created this account to adjust the discounted amount of the bond.

It is a liability account and we know that debits decrease a liability account (above image). But debits increase this Discount on Bonds Payable account. This is why it is called a contra account. So, the debit Discount on Bonds Payable is $5242.14.

Step 2: Prepare General Ledger

Now rearranging the above data for the general ledger should look like this.

Step 3: Calculate Carrying Value of Bond

To calculate the carrying/book value of this bond, we have to subtract the discounted amount from the bond’s face value.

So, on 1st Jan, 2018, the bond’s book value/carrying value = $100,000 – $5,242.14 = $94,757.86.

But over the next 3 years (the maturity period of the bond), this book value will be adjusted in such a way that it will be $100,000 at the end of the maturity period of the bond.

This slow adjustment (also called amortization) of the book value to its face value can be done in two ways:

Straight-line Method of Amortization (will discuss it in another article)
Effective Interest Rate Method of Amortization

Before showing the effective interest rate method of amortization, let’s see some more transactions.

On 30th June 2018, the company is going to pay the bondholder his first semi-annual interest ($100,000 x 3% = $3000).

But the real Interest Expense = The book value of the bond x (market rate / 2) = $94757.86 x (8%/2) = $3790.31.

Debits increase the expense account. So, we have debited the Interest Expense account (following image) with $3790.31.

The company has paid $3000 cash to the bondholder and credits to decrease the cash (asset) account. So, the credit cash is $3000.

“Discounts on Bonds Payable” account is credited 790.31$. When liabilities decrease, it goes under the debit column. But as this is a contra account, when liabilities decrease it goes under the credit column.

The general ledger will be now as follows.

You see that we have added a new account (“Interest Expense”) in our “General Ledger” now. Why did we add this “Interest Expense” account? Are the company paying any interest to the bondholder?

The company is paying (every 6 months): Face Value x (Nominal Interest Rate/2) = 100,000 x (6%/2) = $3000.

But there is a discount for the bond in such a way that its true return will be 8% every year (the market rate). So, there is a gap between the genuine cost of the fund and the given interest payments. In the “Interest Expense” account, we have entered the true cost of the fund as $94757.86 x (8%/2) = $3790.31.

The difference between the true cost and given interest payment (semi-annually) = $3790.31 – $3000 = $790.31.

This difference ($790.31) is credited to the Discount on Bonds Payable account.

Step 4: Record Carrying Value

Now let’s think about a virtual account where we shall keep the calculations of the carrying value (book value) of the bond.

Carrying value is the value on the basis of which we calculate the true cost of the fund (that we got from selling a bond).

On the issue date of the bond, our bond’s book value (carrying value) was $94757.86 (issue price)

After 6 months, the book value of the bond will be: $94757.86 + $790.31 = $95,548.17.

How?

Our true cost of the fund was $3790.31 but we paid $3000 to the bondholder. The remaining $790.31 was not in payment. So the bondholder will get the interest for this unpaid amount at the market rate (8%).

So, after 6 months, the bond’s carrying value will be $95,548.17.

Step 5: Finalize General Ledger

Next, repeat all of the steps for all of the transactions. The final product of the general ledger for this example will look something like this.

This is the later portion containing “Bonds Payable” and “Interest Expenses”.

Observe this image very carefully. You see that the total cash outflow from your company is: $76,757.86 – $100,000 = -$23,242.14. And this is the total “Interest Expense” for this bond for 3 years.

Step 6: Enter Credit Balance and Book Payable in Amortization Table

First, let’s enter the particular to create the amortization table format for the effective interest method in Excel.

Now insert the credit balance by the following formula.

=$D$5

To insert the book value enter the following formula in cell H14.

=$D$9

Step 7: Calculate Debit Balance

In order to calculate the debit balance of the amortization table format that we are creating for the effective interest method in Excel, we will use the following formula in cell F14.

=G14-H14

Then press Enter.

Step 8: Estimate Interest Payment

To estimate the interest payment, we are using the following formula in cell C15.

=$D$5*($D$6/$D$8)

Then press Enter.

Step 9: Compute Interest Expense

Next, we are going to compute the interest expense of the effective interest method of the amortization table format in Excel. For that, we are using the following formula in cell D15.

=H14*($D$10/$D$8)

After that, press Enter.

Step 10: Calculate Amortization of Discounts on Bonds Payable

Finally, we are going to calculate the amortization of discounts on the bonds payable. To do that, we have chosen column E and entered the following formula in cell E15.

=D15-C15

Then pressed Enter.

Step 11: Fill Out Rest of the Values

Finally, click and drag the fill handle icon for all the cells in each column to fill out the rest of the values in the table.

Also, let’s add some formulas to calculate total interest payments, interest expenses, and amortization. We need the SUM function for this.

First, we have entered the following formula in cell C22.

=SUM(C15:C20)

Then we used the following formula in cell D22.

=SUM(D15:D20)

After that, we used the following formula in cell E22.

=SUM(E15:E20)

Finally, the amortization table format for the effective interest method in Excel is as follows.

Example 2: Effective Interest Method of Amortization for Bonds Sold with Premium in Excel

Here is the amortization table for bonds sold with premiums.

The steps are all the same for creating the effective interest method table format for the amortization table for bonds sold in premium in Excel. As for the difference in calculations:

For bonds that are sold in premium, you have to debit the Premium on Bonds Payable account gradually. Keep in mind that it is not a contra liability account. You can call it an adjunct account because its purpose is to balance the additional amount (for this bond: $102754.06 – $100000 = $2754.06) the company receives from the bonds sold on premium.
To calculate the carrying value of the bond subtract the additional interest (the company pays to the bondholder) from the current book value.
For example, on Jan 01, 2018, the carrying value of the bond was $102754.06. Also, on June 30, 2018, the company paid $3000 to the bondholder. But the genuine cost of the fund ($102754.06) was $102754.06 x (5%/2 ) = $2568.85. So, the company is paying extra of amount: $3000 – $2568.85 = $431.15.
We subtracted this additional interest from the current carrying value of the bond to get the new carrying value: $102754.06 – $431.15 = $102322.91.

This is how you can create an amortization table format for the effective interest method in Excel for bonds with premiums.

Download Practice Workbook

Effective Interest Method of Amortization.xlsx

Conclusion

That concludes our discussion on creating an amortization table format for the effective interest method in Excel. Hopefully, you have grasped the notion of the theory and can use the table format accordingly for your calculations. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below. We will try to reach out to you as best as we can.