Debt Security Amortization
This template may be used to create amortization schedules for bonds, Treasury notes, and other debt securities with 360 interest payments or less.
Entering Data
All of the data needed to produce the amortization schedule, chart, and related financial statement data is entered on the Input worksheet. The first section of the worksheet requests the following data needed to prepare report headers and financial statement information:
- Client's name
- Security type
- Security classification
- Name of the firm or person preparing the schedule
- Balance sheet date
The financial statement information does not calculate if the security type and balance sheet date are not entered. The disclosures are presented for illustrative purposes only. Additional disclosures may be required if securities have been transferred from the available-for-sale category to the trading category or if securities have been transferred from the held-to-maturity category. Additional disclosures also may be required for financial institutions. For a complete listing of required disclosures related to marketable debt and equity securities, refer to the GAAP disclosure checklist found in PPC's Guide to Preparing Financial Statements, PPC's Guide to GAAP, and other PPC Guides.
For Security type, enter the type of debt security (e.g., Treasury bond, Treasury note, municipal bond, corporate bond). Indicate the security classification by clicking on the arrow in the drop-down list box and selecting the applicable classification from the list.
The following section of the worksheet requests the security's par value, annual coupon rate, issue date, and maturity date. Be sure to enter the original issue date, which may be prior to the actual purchase date. Indicate the day interest is paid by clicking on the arrow in the drop-down list box and selecting either 1st of month, 15th of month, or end of month. Likewise, indicate the interest payment frequency by clicking on the arrow in the next drop-down list box and selecting either monthly, quarterly, semi-annually, or annually.
U.S. Treasury bonds are issued three times per year on the 15th of the month. U.S. Treasury notes are issued at various times throughout the year, either on the 15th or the last day of the month. U.S. Treasury bonds pay interest semi-annually on the 15th of the month. U.S. Treasury notes pay interest semi-annually on either the 15th or the last day of the month, depending on the issue date.
In the next section of the worksheet, enter the security's purchase price, date purchased, and the first interest date after the purchase date.
Entering the first interest date after the purchase date is critical to the accuracy of the amortization schedule because the amortization schedule is designed to begin on that date. To illustrate determining the first payment date, assume that a company purchased a security that was issued August 15, 1998, and pays interest semiannually on the 15th of the month. (Thus, the security pays interest every February 15th and August 15th until it matures or is sold). If the security were purchased on March 1, 2005, the next interest payment date would be August 15, 2005.
The template automatically calculates the security’s quoted yield on the date purchased if the par value, annual coupon rate, maturity date, interest payment frequency, purchase price, and date purchased are entered. In addition, the template automatically calculates the security’s effective yield on the date sold if the par value, annual coupon rate, interest payment frequency, purchase price, date sold, and sales price are entered.
The template cannot calculate the yield on the security in older versions of Excel unless the "Analysis ToolPak" is installed in Excel. Users can install the "Analysis ToolPak" by selecting "Add-Ins" from the "Tools" menu and checking "Analysis ToolPak. " Otherwise, users can override the yield formula and enter the quoted yield on the date the security is purchased. Note that it is not necessary to enter the effective yield on the date the security is sold. That amount is provided for informational purposes only.
In the next section of the worksheet, enter the quoted bid price of the security at the current balance sheet date and the previous balance sheet date. Note that the worksheet automatically enters the balance sheet dates underneath the caption for the quoted bid price, based on the date entered as the current balance sheet date. (If the investment is not held at the current or previous balance sheet date, N/A appears underneath the caption for the quoted bid price.)
If the quoted bid price is entered, the Financial Statement Information worksheet will compute the fair value of the security at the balance sheet date. If the security is not held at the current balance sheet date, it is not necessary to enter the quoted bid price. The quoted bid price for U.S. Treasury bonds and notes can be obtained from the Wall Street Journal by referring to the applicable stated interest rate and maturity date for the security. If the source provides bid prices (or similar source) quoted in 32nds, the numbers to the right of the colon represent 32nds of one point. For example, 101:05 is the same as 101 5/32 and should be entered with a space between the integer and the fraction, such as "101 5/32". In this example, the display will show "101.15625" because the bid prices are displayed in a decimal format. As an alternative, "101.15625" could also be directly entered in the cell.
In the last section of the Input worksheet, enter the date the security is sold, the sales price, and the last interest date on or before the sale date. (If no information is entered in this section, the amortization schedule assumes the security is held to its maturity date.) The sales price is the amount received when the security was sold, exclusive of any amount received related to accrued interest. (The amortization schedule will calculate the accrued interest at the sales date automatically.) If the security is sold on an interest payment date, enter that date as the last interest date. Otherwise, enter the most recent interest payment date prior to the date the security was sold. (For example, if the security pays interest semiannually on February 15th and August 15th and the security was sold on December 31, 2007, the last interest date on or before the sale would be August 15, 2007.)