I’ve had numerous requests to show how the constant yield rate for debt cost amortization is computed in the sample Excel effective interest method calculations. The idea is pretty simple once you have the formulas set up. The objective is to determine the rate that drives the amortization balance to zero on the maturity date of the note. All of the cell formulas are available in the downloaded spreadsheet, but here are two important calculations in words:

Periodic amortization (quarterly in the example) is:

Beginning principal balance x annual rate / # calculation periods in the year (we are dividing by 4 in the example since there are 4 quarters)

Debt issuance cost at the end of each quarter is therefore:

Beginning issuance cost balance – periodic (i.e., quarterly) interest amortization

Since the effective interest method is be definition a constant interest rate, all you are doing is solving for the rate that drives the interest cost balance to zero by the notes maturity date. The screen shots below show how to use Excel’s Goal Seek feature.

Before the Goal Seek function is run:

Setup of the Goal Seek Inputs:

After the Goal Seek function has run: