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***, and**

*MONTH***functions in this step.**

*DAY*- 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**,

**, and**

*ABS***functions in this step. Moreover,**

*IF***will be used to**

*the Fill Handle***.**

*AutoFill the formulas*- Firstly, type this formula in cell
to get the value of the*F11***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
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.*PRICE*

- 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
will be executed.*F14-E15* - Then, when the condition is false, the
will be the output.*F14+E15*

- Lastly, select the cell range
and fill the rest of the cells as shown in the following animated image.*D15:F15*

**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

**to display the formula. The frequency of payment is semi-annually in this case, and we have used the value 2 for that reason.**

*the FORMULATEXT function***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
to*C14***calculate the bond’s yield to maturity.**

`=YIELD(C5,C6,C7,C8,C9,C10,C11)`

- Lastly, press
. So, We will get the yield. Now, you can change the values of the argument and it will return the revised yield.*ENTER*

**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!