How to Calculate Bond Value and Current Yield
A bond yield and value calculator, created using spreadsheet software, can be used effectively to calculate the expected rate of return on an investment. Once the calculator is assembled, the desired data will automatically appear in the designated cells when the required input values are entered. The following instructions can be used to create a bond yield and value calculator in either Microsoft Excel or Open Office. When separating values within a formula, use a comma in Excel and a semicolon in Open office. This article provides step-by-step instructions on how to use spreadsheet software to calculate the bond yield and value to determine the current yield and rate of return on an investment.
Create the Bond Value Section
Type the column and row headings.Click in cell A1 and type the following text into cells A1 through A8: Bond Yield Data, Face Value, Annual Coupon Rate, Annual Required Return, Years to Maturity, Years to Call, Call Premium, and Payment Frequency, in that order. Skip past cell A9 and type "Value of Bond" in cell A10. Skip over cell A11 and, in cells A12 through A14, type "Current Yield, "Yield to Maturity" and "Yield to Call," in that order.
Format the column heading.Click and drag to select cells A2 and B2. Confirm that both cells are selected and click the "Merge cells" button, and then the "Center Text" button. With cells A2 and B2 still selected, click the "Borders" button and select "All borders."
Format the width of column A.Position the mouse pointer over the line between columns A and B, just above the first column heading. Click and drag the line to widen column A enough to fit the text in the column.
Format the numbering in column B.Press and hold the control key on your keyboard and select cells B2 and B10. With both cells selected, click the "Currency" button ($) on the Formatting toolbar. The cell values will now display in dollar amounts.
- Hold down the control key again and select cells A3, A4, A7, A12, A13 and A14. Confirm that all 6 cells are selected and click the "Percent" button (%) on the Formatting toolbar. The cell values will now display in percentages.
Enter the Bond Yield and Formulas
Enter the bond value formula.Click in cell B10 and type = -PV(B4/B6,B5*B6,B3/B6*B2,B2).
Enter the bond yield formulas.
- Click in cell B12 and type the formula: =(B3*B2)/B10.
- Click in cell B13 and enter the formula: =RATE(B5*B8,B3/B8*B2,-B10,B2)*B8.
- Click in cell B14 and type the formula: =RATE(B6*B8,B3/B8,-B10,B2*(1+B7))*B8.
Test the Bond Yield and Value Calculator
Test the entered formulas for accuracy.Confirm that the formulas have been entered correctly by entering the following values into the corresponding cells.
- Type ,000 in cell B2 (Face Value).
- Type .07 in cell B3 (Annual Coupon Rate).
- Type .099 into cell B4 (Annual Required Return).
- Type 3 in cell B5 (Years to Maturity).
- Type 1 in cell B6 (Years to Call).
Type .06 into cell B7 ( Call Premium).
- Type 2 in cell B8 (Payment Frequency).
Cross-reference the results with the input values.If the calculator has been programmed correctly, the following expected rates of return will appear in column B. The Bond Value should read ,555.08, the Current Yield should be 7.55%, the Yield to Maturity should read 9.84%, and the Yield to Call should read 13.78%. If the values do not match, confirm that the formulas have been entered correctly, correct any syntax errors and recheck.
Video: How to calculate the bond price and yield to maturity
How to Make Pastillas
4 Exercises to Beat Post-Baby Weight Gain
When does Toys R Us officially close
How to Evaluate Business Schools
How to Get Through the Day on Less Than Four Hours of Sleep
3 Editors Reveal Their Eating Diaries
Lanvin Blanche 2015 Bridal Line
El café que triunfa en Instagram es rosa (y bueno para la salud)
How to Practice Good Hand Hygiene
Hashimotos Symptoms and Diagnosis