Preparing Bond Amortization Schedule in Excel (with Easy Steps)

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.

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 references 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 semi-annually. 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.

Setting Up Dataset to Prepare Bond Amortization Schedule in Excel

  • 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.


Using DATE Function to Prepare Bond Amortization Schedule in Excel

  • 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.


Read More: Amortization Schedule with Irregular Payments in Excel

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).


Creating Bond Amortization Schedule to Prepare Bond Amortization Schedule in Excel

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.


Inserting Initial Carrying Value to Prepare Bond Amortization Schedule in Excel

  • Thirdly, type this formula to find the value of cash paid.


  • Fourthly, type the following formula to calculate the interest amount.


  • Next, type this formula to return the amortization value.


  • Next, type this formula to calculate the carrying value.


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.

Animated GIF Showing AutoFill Feature

Read More: Amortization Schedule Excel Template with Extra Payments

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.


  •  Firstly, type all the details in the values column. The explanation column is there to clarify the arguments.

Bond Yield Calculator in Excel


  • 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.

Download Practice Workbook

You can download the Excel file from the link below.


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.

Related Articles

<< Go Back to Amortization Schedule | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF