Preparing Bond Amortization Schedule in Excel: 2 Methods

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.

Method 1 – Setting Up Dataset

We will input the required values in this step. The bond’s maturity value is $500,000, and its duration is 3 years. The coupon rate and yield are 15% and 12%, respectively. The interest payment is due semi-annually, and the settlement date is January 1, 2019. Use the DATE, YEAR, MONTH, and DAY functions in this step.

  • Type the following values.

Setting Up Dataset to Prepare Bond Amortization Schedule in Excel

  • Type this formula to enter the settlement date. You can type this manually, it is better to input the date using this function.


Using DATE Function to Prepare Bond Amortization Schedule in Excel

  • Type another formula to calculate the maturity date. We are finding the year value from the settlement date and adding the terms. We are keeping the month and day the same as the settlement date.


Method 2 – Creating Bond Amortization Schedule

We will create the amortization schedule using the calculated values from the previous step. Use the PRICE, ABS, and IF functions in this step. The Fill Handle will be used to AutoFill the formulas.

  • 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.
    • 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.
  • The formula reduces to → 107.376*F4/100
    • Output: 536879.932.
    • We multiplied that value by the maturity value and then divided it by 100 to get the bond price.
  • Type another formula in cell F14.


Inserting Initial Carrying Value to Prepare Bond Amortization Schedule in Excel

  • Type this formula to find the value of cash paid.


  • Type the following formula to calculate the interest amount.


  • Type this formula to return the amortization value.


  • 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.
  • Select the cell range D15:F15 and fill the remaining cells as shown in the following animated image.

Animated GIF Showing AutoFill Feature


Bond Yield Calculator in Excel

We will use the YIELD function to create a bond yield calculator in Excel. Use the FORMULATEXT function to display the formula. The frequency of payment is semi-annually we used the value 2 for that reason.


  •  Type all the details in the values column. The explanation column is there to clarify the arguments.

Bond Yield Calculator in Excel


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

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