By: Eric Bank, MBA, MS Finance | Reviewed by: Ryan Cockerham, CISI Capital Markets and Corporate Finance | Updated January 28, 2019
You can use Excel to create a bond amortization schedule even if you have only modest skills in using the program. You will need to know how to name, format and copy cells as well as enter calculations to complete this table, which assumes straight-line depreciation. You can edit the table for other methods, as it also works for both discount and premium bonds.
You can produce a bond amortization table in Excel by first creating the appropriate cells to store all the relevant information, and then entering the formulas needed to perform the critical calculations.
Creating cells for bond information
Create cells to contain bond information, including bond purchase price, bond face value, years to maturity, coupon rate, and yield to maturity. Give these cells names so that you can refer to them in the table columns by name rather than by address, which you can do by typing the name of the cell in the Name box on the far left of the formula bar. .
Suppose the bond makes semi-annual interest payments and you amortize the bond on the same schedule. User can calculate bond yield using Excel function or trading calculator. Set the bond information cells you just created to the side, away from the table columns.
Establishing columns for data
Configure the columns in the following order: Payment, Cash payment, Interest expense, Amount amortized, and Book value. 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 on the Home menu. Set the Payment column to a whole number by selecting the Payment column, clicking the comma symbol once, and the Decrease Decimal Twice box in the Number Format box.
Complete the first row
The first row contains only a value for the carrying amount of the bond. This is the far right column of the table. Copy this value from the Bond Purchase Price cell. Line 1 reset is empty. For example, if you buy a bond for $ 970, set the book value in row 1 to that value. The value of $ 970 is equal to half of the coupon rate of 7% multiplied by the face value of $ 1,000.
Explore the Payments column
Configure the worksheet to display the payment numbers from row 2. The number of payments is the number of years to maturity divided by two. For example, if a bond matures in four years, the worksheet fills out rows 2 through 9 of the Payment column with values from 1 through 8. Each row represents a semi-annual interest payment.
Complete the cash payments column
Fill in the Cash Payment column with half the coupon rate multiplied by the face value of the bond. For example, if the four-year bond has a face value of $ 1,000 and pays a coupon of 7%, set rows 2 through 9 paid in cash to $ 350 each.
Work on additional data points
Calculate the interest charge on line 2. This is equal to the book value on line 1 multiplied by half the yield to maturity. In the example, the yield is 7.8891 percent. The interest charge for row 2 is equal to half of the yield to maturity of 7.8891% multiplied by the book value of $ 970 of the previous row. The result is $ 38.26. Repeat this for the remaining rows, using the book value from the previous row.
The amortized amount is the cash amount paid from the line minus the interest charge. For discount bonds, the value is negative. Premium bonds have positive values for the amortized amount. In the example, the value of the amortized amount in line 2 is $ 35 in cash paid less $ 38.36 for interest charges, or – $ 3.36. Repeat this calculation for each row.
Line 1 of the book value has been preloaded from the Purchase price cell. On line 2, subtract the depreciated amount from the book value on the previous line. Repeat for the remaining rows. In the example, subtract – $ 3.36 from $ 970, which gives a line 2 book value of $ 973.26.
Define your results
You will know that the spreadsheet is working correctly if the final book value equals the face value of the bond. If you get a different number, check your yield to maturity and make sure it’s correct to four decimal places. Otherwise, you will have to debug the worksheet. In the example, the book value on line 9 is $ 1,000, which is the face value.