How to Create an Effective Interest Amortization Method in Excel

Example 1 – Effective Interest Method of Amortization for Bonds Sold on Discount

These are the 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)

Calculate the effective interest method of amortization for the bond sold on discount:

Step 1: Enter Values in the Journal

Record the transactions on 1st Jan 2018.

general journal first entry for effective interest method of amortization excel

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

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

In the “Discount on Bonds Payable” account, the discounted amount of the bond is adjusted.

It is a liability account and debits decrease a liability account, but they increase this Discount on Bonds Payable account. This is why it is called a contra account. The debit Discount on Bonds Payable is $5242.14.

Step 2: Prepare a General Ledger

Rearrange the above data for the general ledger:

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

Step 3: Calculate the Carrying Value of the Bond

To calculate the carrying/book value of this bond, subtract the discounted amount from the bond face value.

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

Over the next 3 years (the maturity of the bond), this book value will be adjusted: it will be $100,000 at the end of the maturity period.

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
Effective Interest Rate Method of Amortization

Consider the following 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, the Interest Expense account was debited $3790.31.

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

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

The general ledger will be:

A new account (“Interest Expense”) was added to the “General Ledger”.

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: 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, the true cost of the fund was entered as $94757.86 x (8%/2) = $3790.31.

The difference between the true cost and the 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 the Carrying Value

Consider a virtual account to keep the calculations of the carrying value (book value) of the bond.

The carrying value is the value on the basis of which the true cost of the fund is calculated.

On the issue date of the bond, its 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

The true cost of the fund was $3790.31, but $3000 were paid 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%).

After 6 months, the bond carrying value will be $95,548.17.

Step 5: Finalize the General Ledger

Repeat the steps for all the transactions. This is the final output.

final general ledger for effective interest method of amortization excel

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

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

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

Enter data to create the amortization table.

table particulars of effective interest method of amortization excel

Enter the credit balance using the following formula.

=$D$5

To enter the book value, use the following formula in H14.

=$D$9

Step 7: Calculate the Debit Balance

Use the following formula in F14.

=G14-H14

calculating debit balance in effective interest method of amortization excel

Press Enter.

Step 8: Estimate the Interest Payment

Use the following formula in C15.

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

calculating interest payment in effective interest method of amortization excel

Press Enter.

Step 9: Compute the Interest Expense

Use the following formula in D15.

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

calculating interest expense in effective interest method of amortization excel

Press Enter.

Step 10: Calculate the Amortization of Discounts on Bonds Payable

Chose column E and enter the following formula in E15.

=D15-C15

calculating amortization in effective interest method of amortization excel

Press Enter.

first entry values in effective interest method of amortization excel

Step 11: Enter the Rest of the Values

Click and drag the fill handle icon for all the cells in each column to fill the rest of the values.

final table of effective interest method of amortization excel

Enter the following formula in C22.

=SUM(C15:C20)

summing interest payment

Use the following formula in D22.

=SUM(D15:D20)

Enter the following formula in E22.

=SUM(E15:E20)

summing total amortization

The amortization table for the effective interest method is complete.

effective interest method of amortization excel

Example 2 – Effective Interest Method of Amortization for Bonds Sold with Premiums

This is the amortization table for bonds sold with premiums.

effective interest method of amortization excel for premium bonds

The steps are the same described in Example 1.

There’s a difference in calculations:

For bonds that are sold in premium, you have to debit the Premium on Bonds Payable account gradually. It is not a contra liability account. You can call it an adjunct account because its purpose is to balance the additional amount received (for this bond: $102754.06 – $100000 = $2754.06).
To calculate the carrying value of the bond subtract the additional interest (paid to the bondholder) from the current book value.

For example, on Jan 01, 2018, the carrying value of the bond was $102754.06. On June 30, 2018, $3000 were paid to the bondholder. But the cost of the fund ($102754.06) was $102754.06 x (5%/2 ) = $2568.85. The company is paying an extra amount: $3000 – $2568.85 = $431.15.