How to Create a Bond Amortization Table in Excel

You can use Excel to create a bond amortization table even if you have only modest skill in using the program. You will need to know how to name, format and copy cells as well as how to enter calculations to complete this table, which assumes straight-line amortization. You can modify the table for other methods, as it also works for discount and premium bonds.

Creating Cells for Bond Information

Create cells to hold bond information, including Bond Purchase Price, Bond Face Value, Years Until Maturity, Coupon Rate and Yield to Maturity. Give names to these cells so that you can refer to them in the table columns by name rather than address, which you can do by typing in the cell's name in the Name Box on the far left of the Formula Bar.

Assume the bond makes semi-annual interest payments and that you amortize the bond on the same schedule. The user can calculate the bond’s yield using an Excel function or a business calculator. Put the bond information cells you've just created off to the side, away from the table columns.

Establishing Columns for Data

Set up columns in the following order: Payment, Cash Paid, Interest Expense, Amortized Amount and Carrying Amount. Format all of these columns as dollar values with two decimal places by selecting the columns and then clicking the dollar sign symbol in the Format Number box of the Home menu. Set the Payment column to be an integer by selecting the Payment column, clicking the comma symbol once and the Decrease Decimal box twice within the Format Number box.

Completing the First Row

The first row contains only a value for the bond’s Carrying Amount. This is the far right table column. Copy this value from the Bond Purchase Price cell. The reset of Row 1 is blank. For example, if you buy a bond for $970, set the Carrying Amount in Row 1 to this value. The value of $970 is equal to 1/2 of the 7 percent Coupon Rate times the $1,000 Face Value.

Exploring the Payments Column

Set the spreadsheet to display payment numbers beginning in Row 2. The number of payments is the years until maturity divided by two. For example, if a bond matures in four years, the spreadsheet fills the Payment column Rows 2 through 9 with values from 1 to 8. Each row represents a semi-annual interest payment.

Completing the Cash Paid Column

Fill the Cash Paid column with one-half the Coupon Rate times the Bond Face Value. For example, if the four-year bond has a face value of $1,000 and pays a 7 percent coupon, set Cash Paid Rows 2 through 9 to $350 each.

Working on Additional Data Points

Calculate Row 2 Interest Expense. This is equal to the Row 1 Carrying Amount times half the Yield to Maturity. In the example, the yield is 7.8891 percent. The Row 2 Interest Expense equals 1/2 of the 7.8891 percent Yield to Maturity times the $970 Carrying Amount from the previous row. The result is $38.26. Repeat this for the remaining rows, using the Carrying Amount from the previous row.

The Amortized Amount is the row’s Cash Paid minus Interest Expense. For discount bonds, the value is negative. Premium bonds have positive values for Amortized Amount. In the example, the Amortized Amount value in Row 2 is $35.00 Cash Paid minus $38.36 Interest Expense, or $-3.36. Repeat this calculation for each row.

Row 1 of Carrying Amount was pre-loaded from the Purchase Price cell. In Row 2, subtract the Amortized Amount from the previous row’s Carrying Amount. Repeat for the remaining rows. In the example, subtract $-3.36 from $970, giving a Row 2 Carrying Amount of $973.26.

Defining Your Results

You’ll know the spreadsheet is operating properly if the final Carrying Amount is equal to the bond’s Face Value. If you get a different number, check your Yield to Maturity and ensure that it is correct to four decimal places. Otherwise, you’ll have to debug the spreadsheet. In the example, the Carrying Amount in Row 9 is $1,000, which is the Face Value.