Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If your company needs to raise some cash and has determined that that issuing stock isn't in its best interest, you might borrow money by issuing bonds. Pricing bonds for sale is a tricky business. In essence, you're betting that you can earn a higher rate of return on the borrowed money than you pledged to pay bondholders. Once you know the parameters of the bond you would like to issue, you can use the PRICE function to find the break-even issue price. To do that you use the PRICE function, which has seven arguments, Settlement Date, Maturity Date, Percent Coupon which is the interest rate, percent Yield, Redemption Value, Frequency, and Basis.
The Settlement Date is the date you gain ownership of the security. The Settlement Date is the date that a buyer gains ownership of the security. That date will probably be different from the bonds issuance date, which is the date the bond was made available for sale. The Maturity Date is the date the bond will be paid off. So we've a Settlement Date in cell B3 and a Maturity Date in cell B4. Percent Coupon is the bond's interest rate. Yield is the bond's annual yield and Redemption is the bond's redemption value per $100 of face value, most commonly that's $100.
And Frequency is the number of interest or coupon payments per year. So we've the rate or Percent Coupon in cell B5, the Yield in cell B6, the Redemption Value in B7 and the Frequency in B8. Basis reflects the way you count the days of the year. The western calendar can have months of 28, 29, 30, or 31 days and years of either 365 or 366 days. So many investments base their calculations on a year that is presumed to have 12 months of 30 days each for a 360-day year.
The Excel help system describes the other options available including the European 30-360 system, which is slightly different from the US system. So I'll just type in B9, close out the formula and there we have a result. The break-even issuance price given the parameters stated would be $90.36. Like the YIELD function, the PRICE function assumes bondholders always reinvest their interest and that the bond's interest rate never changes. Those assumptions are rarely true. But the PRICE function offers a good first look at what you should charge your bondholders with the goal of making a profit on the transaction.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.