Referring to my previous article (http://emfps.blogspot.com/2011/06/case-analysis-of-nike-inc-cost-of.html), someone who had received my spreadsheet, had the problem to calculate the Cost of Debt by using of IRR method (Method 2). As the matter of fact, this method can be utilized to obtain the required return rate on Bonds in which you can use it even in your own business.
How can we use from this spreadsheet to calculate the required return rate of Bonds?
Here I am willing to explain it step by step as follows:
Please look at the spreadsheet.
-Enter your new Coupon rate (annually) on Cell C9 and Price of Bond based on par value equal $100 on Cell C10
-You should use the try and error method by changing on required return rate speculated by you on Cell C21 and C22 in which finally Cell C23 will be equal to Cell C17 (C23 = C17).
How can you guess the required return rate?
I have already depicted it on below link:
Where I stated: “Which method is to calculate cost of debt better than others? It is important to find the relationship between the required return and the coupon interest rate. When the required return is greater than the coupon interest rate, the bond value ……”
Let me bring you an example as follows:
Assume the company has issued the $500,000 nominal amount of 8% rate of Bonds from 1999 to 2009 was issued by a subsidiary at $95.068 per $100 par value. How can we calculate the required return rate by using of my spreadsheet?
-Enter on Cell C6: 1999
-Enter on Cell C6: 2009
-Enter on Cell C9: 8%
-Enter on Cell C10: 95.068
-Since the price of Bond is less than Par value, our speculate on required return rate should be more than Coupon rate (annually)
-Click on Cell C21where we can see this formula: = - PV (rate, number of years, Coupon payment). Of course, the previous numbers on Cell C21 are: = - PV (0.1415, 25,135)
-Replace the amount for required return more than Coupon rate, for instance, 8.5%. Where we have: = - PV (0.085, 10, 80) or = - PV (0.085, C19, C18)
-Click on Cell C22 where we can see this formula: = Par Value / (1+required return) ^n. Of course, the previous numbers on Cell C22 are: = 1000/ ((1+0.1415) ^ 25)
-Replace the same amount of required return (8.5%) and power equal to 10. Where we have: = 1000 / ((1+0.085) ^ 10 or = C20 / ((1+0.085) ^ C19
-We can see that Cell C23 is more than Cell C17 (967.19 > 950.68). Therefore, we should increase again the required return rate to 8.8%
-All previous steps should be repeated. Now, we have: C23 = 948.20 which is less than C17= 950.68
-In this case, we should decrease the required return for instance to 8.76%. Finally we will have C23 approximately equal to C17 (C23 = 950.71 and C17 = 950.68)
In the result, the required return rate will be equal to 8.76%.
