# How to Perform Floating Rate Bond Valuation in Excel (2 Cases)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

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

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

## Related Articles

Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF