Rafiul Haq
We can perform various finance related tasks in Microsoft Excel very easily. In this article, you will learn about how to prepare a bond amortization schedule in Excel. We will be using the Microsoft 365 version, however, you can follow this article using any Excel version from 2003.
Download Practice Workbook
You can download the Excel file from the link below.
What Is Bond Price?
A fixed-income tool used by investors to borrow money from the capital market is called a bond. Companies, governments, and business entities use bonds to raise funds from the capital market. The owners of bonds are the debtholders, creditors, or bond issuers. Therefore, the bond price is the present discounted value of the future cash stream generated by a bond. It refers to the accumulation of all likely coupon payments and the present value of the par value at maturity.
The principal amount of the bond is called the face value of the bond. This reflects how much a bond is worth when it matures. This is also known as the par value.
Step-by-Step Procedures to Prepare Bond Amortization Schedule in Excel
There will be two easy steps to create a bond amortization schedule in Excel. Firstly, we will set up the dataset for this. Moreover, we will use different types of cell reference in this article. Lastly, we will prepare the schedule using various formulas.
Step 1: Setting Up Dataset
We will input the required values in this step. The maturity value of the bond is $500,000. The duration is 3 years. The coupon rate and yield are 15% and 12%, respectively. The interest payment is due semiannually. Moreover, the settlement date is January 1, 2019. Moreover, we will use the DATE, YEAR, MONTH, and DAY functions in this step.
- To begin with, type the following values.
- Then, Type this formula to enter the settlement date. You can type this manually, however, it is better to input the date using this function.
=DATE(2019,1,1)
- Next, type another formula to calculate the maturity date. We are finding the year value from the settlement date and adding the terms to it. Then, we are keeping the month and day the same as the settlement date.
=DATE(YEAR(F9)+F5,MONTH(F9),DAY(F9))
Read More: How to Calculate Present Value of a Bond in Excel (3 Easy Ways)
Step 2: Creating Bond Amortization Schedule
We will create the amortization schedule using the calculated values from the previous step. Additionally, we will use the PRICE, ABS, and IF functions in this step. Moreover, the Fill Handle will be used to AutoFill the formulas.
- Firstly, type this formula in cell F11 to get the value of the bond price (selling).
=PRICE(F9,F10,F6,F7,100,F8)*F4/100
Formula Breakdown
- PRICE(F9,F10,F6,F7,100,F8)
- Output: 107.376.
- Here, we have put 100 inside the PRICE function because by definition this function “returns the price of $100 par value of a bond”. The output from the function is the percentage value.
- So, the formula reduces to → 107.376*F4/100
- Output: 536879.932.
- So, we have multiplied that value by the maturity value and then divided it by 100 to get the bond price.
- Secondly, type another formula in cell F14.
=F11
- Thirdly, type this formula to find the value of cash paid.
=F$4*F$6/F$8
- Fourthly, type the following formula to calculate the interest amount.
=F14*F$7/F$8
- Next, type this formula to return the amortization value.
=ABS(C15-D15)
- Next, type this formula to calculate the carrying value.
=IF(F$4<F$11,F14-E15,F14+E15)
Formula Breakdown
- This is a conditional formula. The condition is that the “maturity value is less than the bond selling price”. When this is true, the F14-E15 will be executed.
- Then, when the condition is false, the F14+E15 will be the output.
- Lastly, select the cell range D15:F15 and fill the rest of the cells as shown in the following animated image.
Read More: How to Calculate Face Value of a Bond in Excel (3 Easy Ways)
Bond Yield Calculator in Excel
We will use the YIELD function to create a bond yield calculator in Excel. Additionally, we will use the FORMULATEXT function to display the formula. The frequency of payment is semi-annually in this case, and we have used the value 2 for that reason.
Steps:
- Firstly, type all the details in the values column. The explanation column is there to clarify the arguments.
- Secondly, type this formula in cell C14 to calculate the bond’s yield to maturity.
=YIELD(C5,C6,C7,C8,C9,C10,C11)
- Lastly, press ENTER. So, We will get the yield. Now, you can change the values of the argument and it will return the revised yield.
Read More: Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)
Conclusion
We have shown you two quick steps on how to create a bond amortization schedule in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
