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.
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 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 Coupon Rate in Excel
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
Download Practice Workbook
You can download the workbook from here.
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. 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
- Determine Yeild to Maturity in Excel
- How to Calculate Face Value of a Bond in Excel
- Calculate the Issue Price of a Bond in Excel
- How to Calculate Clean Price of a Bond in Excel
- Calculate Price of a Semi Annual Coupon Bond in Excel
- Calculate Bond Price with Negative Yield in Excel
- How to Calculate Duration of a Bond in Excel
- Calculate Present Value of a Bond in Excel
<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
hi,
Aren’t you mixing the term of coupon and rate in the formula? Cashflow was calculated with the rate and you also used it to discount it.
Dear NAM,
Thank you for engaging with our article and I appreciate your insightful observation. Allow me to address your query and offer clarification on the terminologies utilized in the formulas.
In the article, the term “Coupon Rate” refers to the fixed coupon rate applied to the principal amount during the last fixed period before the bond transitions to a floating rate. Conversely, “Market Rate” refers to the variable interest rate set by the market for the floating periods.
I acknowledge your concern regarding the terms, and I wish to explain that the Cash Flow computation entails multiplying the Principal by the Last Fixed Coupon Rate for the initial fixed period. Subsequently, for the floating periods, the Cash Flow is calculated by multiplying the Principal by the respective Market Rates for each year.
In the Discounted Cash Flow calculation, the appropriate discount rates used are the Market Rates for every corresponding year. This aligns with the procedure of discounting future cash flows to their present value using applicable interest rates.
I hope this clarification addresses your concerns. If you have any further questions, please feel free to let me know.
Kind Regards,
Sumaiya Mirza
ExcelDemy