In this article, we will learn to perform **floating rate bond valuation in Excel**. The floating rate bond pays coupons that vary over their maturity. The floating rate of coupons is determined periodically. Today, we will demonstrate **2 **cases of floating rate bond valuation in Excel. Using these cases, you can also find the bond value. So, without further delay, let’s start the discussion.

## Download Practice Workbook

You can download the workbook from here.

## What Is Floating Rate Bond?

Floating rate bond delivers variable interest. This interest is determined by a coupon rate that changes according to the market rate. Thus it offers potential gains to investors.

The calculation of a floating rate bond may look critically at a first glance. But it becomes easy if you consider the principal **N**, as being redeemed together with the coupon payment (which is **x **times **N**) and using the same discount rate (**y**). We have demonstrated this in **Case 2**. The equation is:

**Bond Value = ((xN)/(1+y))+((N/(1+y))**

Also, we can use the basic concept and formula to find the bond value for the floating rate. In the following sections, we will discuss both cases.

## 2 Cases of Floating Rate Bond Valuation in Excel

To explain the cases, we will use two different datasets. In the first case, the dataset will contain the **Principal**, **Last Fixed Coupon Rate**, **Years **(**t**), and **Market Rate**. We will calculate the **Cash Flow **(**CF**), **Discounted CF, **and **Bond Value** using this information.

In the second case, the dataset will contain the **Principal**, **Last Fixed Coupon Rate**, and **Market Rate **of the first year. We will calculate the **Coupon **amount, **Total CF**, and **Bond Value **using them.

### Case 1: Bond Valuation in Excel When Floating Rates Are Known

In the first case, we will perform bond valuation in Excel when floating rates are known. This means we will find the cash flows as if we knew the floating rates in the future. In the dataset below, you can see the **Principal**, **Last Fixed Coupon Rate**, and year-wise **Market Rate**. We need to find the **Cash Flow **(**CF**), **Discounted CF**, and **Bond Value **using this information.

Let’s follow the steps below to see how we can perform floating rate bond valuation in Excel.

**STEPS:**

- In the first place, select
**Cell D8**and type the formula below:

`=$D$4*$D$5`

- Press
**Enter**to see the result.

Here, we have multiplied the **Last Fixed Coupon Rate **with the **Principal **to get the **Cash Flow **for the first year.

- Secondly, select
**Cell D9**and type the formula below:

`=$D$4*C9`

- Hit
**Enter**.

To find the **Cash Flow **for the second year, we need to multiply the **Principal **by the **Market Rate**.

- Thirdly, drag the
**Fill Handle**down to**Cell D11**.

- After that, select
**Cell D12**and type the formula below:

`=$D$4*C12+D4`

- Press
**Enter**.

- In the following step, we will calculate the
**Discounted CF**. We need to be careful because we need to apply a different formula for each year. - To calculate the
**Discounted CF**for the first year, select**Cell E8**and type the formula below:

`=D8/(1+C8)`

- Press
**Enter**to get the result.

- Now, for the second year, select
**Cell E9**and type the formula below:

`=D9/((1+C8)*(1+C9))`

- Hit
**Enter**.

Here, we have multiplied the **Market Rate** of the second year by the previous one and divided the **Cash Flow** by it.

- Similarly, type the formula below in
**Cell E10**:

`=D10/((1+C8)*(1+C9)*(1+C10))`

- Also, use the below formula in
**Cell E11**:

`=D11/((1+C8)*(1+C9)*(1+C10)*(1+C11))`

- To find the
**Discounted CF**for the final year, use the formula below in**Cell E12**:

`=D12/((1+C8)*(1+C9)*(1+C10)*(1+C11)*(1+C12))`

- Hit
**Enter**to get the result.

- Now, we need to sum the
**Discounted Cash Flows**to get the**Bond Value**. - So, in
**Cell E13**, we have used the formula below:

`=SUM(E8:E12)`

Here, we have used **the SUM function **to calculate the **Bond Value**.

- Finally, press
**Enter**to get the**Bond Value**.

**Read More:** **How to Calculate Bond Price in Excel (4 Simple Ways)**

**Similar Readings**

**Preparing Bond Amortization Schedule in Excel (with Easy Steps)****How to Calculate Present Value of a Bond in Excel (3 Easy Ways)****Calculate the Issue Price of a Bond in Excel****How to Make Treasury Bond Calculator in Excel (2 Easy Ways)****How to Calculate YTM of a Bond in Excel (4 Suitable Methods)**

### Case 2: Floating Rate Bond Valuation Considering Principal Being Redeemed

In the first case, we used different formulas for different years. The calculation becomes difficult when you need to insert a different formula each time. But we can make it easy if we consider the principal **N**, as being redeemed together with the coupon payment and using the same discount rate. Here, we will calculate the **Coupon **amount, **Total CF**, and **Bond Value**.

So, let’s pay attention to the steps below to learn the method.

**STEPS:**

- First of all, select
**Cell D8**and type the formula below:

`=D4*D5`

- Hit
**Enter**to see the**Coupon**amount.

- Secondly, type the formula below in
**Cell D9**:

`=D4+D8`

- Press
**Enter**to find the**Total CF**.

- After that, select
**Cell D11**and type the formula below:

`=D9/(1+D6)`

- Finally, press
**Enter**to get the result.

Here, we can see that the **Bond Value **has increased. So, the investor gains some profit.

**Read More:** **How to Calculate Bond Payments in Excel (2 Easy Methods)**

## Conclusion

In this article, we have discussed **2 **cases of **Floating Rate Bond Valuation in Excel**. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

**Related Articles**

**Calculate Bond Price from Yield in Excel (3 Easy Ways)****How to Calculate Face Value of a Bond in Excel (3 Easy Ways)****How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)****Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)****How to Calculate Clean Price of a Bond in Excel (3 Easy Ways)****How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)**