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)
