How to Create a Bond Amortization Table in Excelby Tom McNulty
Microsoft Excel has functions that make building a bond amortization table easy to do. The key is to lay out the spreadsheet in a clear, logical fashion and use the formulas correctly.
Build an Amortization Table
Set up the inputs for the amortization table. In cell A1, enter "Face Value." In cell B2, enter 1,000,000 to represent a bond with a face value of $1,000,000.
In cell A2, enter "Annual Interest." Then in cell B2, enter the number .07 to represent an annual interest rate of 7 percent.
Enter the word "Periods" into cell A3, and then enter the number 10 into cell B3 to represent 10 years.
Enter the word "Period" into cell A4, and then enter the numbers 1 through 10 into cells A5 through cell A14.
Create the amortization table labels by entering the words "Starting Balance" into cell B4, the word "Principal" into cell C4, the word "Interest" into cell D4 and the words "Ending Balance" into cell E4.
Enter the formula "=B1" into cell B5.
Enter the formula "=E5" into cell B6, and then copy the cell down through cell B14.
Enter the formula "=PPMT($B$2,A5,$B$3,$B$1)" into cell C5, and then copy it down through cell C14.
Enter the formula "=IPMT($B$2,A5,$B$3,$B$1)" into cell D5, and then copy it down through cell D14.
Enter the formula "=B5+C5" into cell E5, and then copy it down through cell E14. The amortization table is complete, and the value in cell E14 should be 0.