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.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


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.


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

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.

general journal first entry for effective interest method of amortization excel

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.

first entry of general ledger in effective interest method of amortization excel

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.

calculating carrying value in effective interest method of amortization excel

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.

final general ledger for effective interest method of amortization excel

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.

table particulars of effective interest method of amortization 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

calculating debit balance in effective interest method of amortization excel

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)

calculating interest payment in effective interest method of amortization excel

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)

calculating interest expense in effective interest method of amortization excel

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

calculating amortization in effective interest method of amortization excel

Then pressed Enter.

first entry values in effective interest method of amortization excel

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.

final table of effective interest method of amortization excel

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)

summing interest payment

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)

summing total amortization

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

effective interest method of amortization excel


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

Here is the amortization table for bonds sold with premiums.

effective interest method of amortization excel for premium bonds

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.


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.

For more guides like this, visit Exceldemy.com.

Kawser

Kawser

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 how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment
  1. Hi,

    I’m trying to calculated amortization using your template but unfortunately i didn’t get the figures.

    May I know how to fill the boxes with the information below:-

    as example:

    Government Bond
    Unit : 3,000,000
    Coupon Rate : 3.422
    Issued Date : 31/03/2020
    Maturity Date : 30/09/2027
    Purchased Date : 31/03/2020
    Settlement Date : 01/04/2020

    Purchase Price : 99.934
    Purchase YTM : 3.432

    Cost : 2,998,032.00
    Accured Interest : 280.49
    Total Paid : 2,998,312.49

    Amortization for end of April Report is (16.42) and I’m trying to get this figures but i failed to get its.

Leave a reply

ExcelDemy
Logo