Calculate Bond Value in Excel
A bond value calculator capable of accurately determining the current value of a bond, can be easily assembled in a Microsoft Excel spread sheet. Once created, the desired data will automatically appear in the designated cells when the required input values are entered. This article provides step-by-step instructions on how to create a bond value calculator in an Excel spreadsheet.
Contents
Steps
Format the Bond Value Calculator
- Type the column heading and data labels. Beginning with cell A1, 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. Skipping over cell A9, type "Value of Bond" in cell A10.
- Format the text in column A. Move the mouse pointer over the line separating columns A and B, just above the Bond Yield Data column heading. Click and drag the line to widen column A enough to fit the text in the column.
- Format the column headings. Click and drag to select cells A2 and B2. Hold down the control key on your keyboard. Confirm that both cells are selected, click the "Merge cells" button, and then click the "Center Text" button. With cells A2 and B2 still selected, click the "Borders" button and select "All borders."
- Set the numerical formatting in column B. Hold down the control key on your keyboard and select cells B2 and B10. With both cells selected, click the "Currency" button ($) on the "Quick format" tool bar. The formatted cell values will display as a dollar amount.
- Hold down the control key and select cells A3, A4 and A7. With all 3 cells selected, click the "Percent" button (%) on the "Quick format" tool bar. The formatted cell values will display as a percentage.
Insert the Formulas for the Bond Yield Calculator
- Enter the bond yield formulas.
- Click in cell B13 and type the formula: =(B3*B2)/B10.
- Click in cell B14 and enter the formula: =RATE(B5*B8,B3/B8*B2,-B10,B2)*B8.
- Click in cell B15 and type the formula: =RATE(B6*B8,B3/B8*B2,-B10,B2*(1+B7))*B8.
Test the Bond Yield Calculator
- Enter the following values in the corresponding cells to test the functionality of the bond yield calculator.
- Type 10,000 in cell B2 (Face Value).
- Type .06 in cell B3 (Annual Coupon Rate).
- Type .09 into cell B4 (Annual Required Return).
- Type 3 in cell B5 (Years to Maturity).
- Type 1 in cell B6 (Years to Call).
- Type .04 in cell B7 ( Call Premium).
- Type 2 in cell B8 (Payment Frequency).
- Type 9999.99 into cell B10 (Value of Bond).
- Cross-reference the results of the input values. If the formulas have been entered correctly, the following results will appear in column B, under the Bond Yield Calculations heading. The Current Yield should be 6.0%. The Yield to Maturity should read 6.0%, and the Yield to call should read 9.90%. If the values in the bond yield calculator match the figures listed above, the formulas have been entered correctly. If the values do not match, double check that the formulas have been entered correctly. The bond yield calculator has been tested.
Related Articles
- Calculate Bond Spread
- Calculate Bond Yield in Excel
- Calculate Bond Value and Current Yield
- Calculate Average Growth Rate in Excel
- Make a Spreadsheet in Excel
- Make a Personal Budget on Excel
- Calculate an Irr on Excel
Sources and Citations
- Go with Microsoft, Excel 2007-Comprehensive First Edition, Shelly Gaskin and Karen Jolly. Published in 2008, Pearson Education Inc., Upper Saddle River, NJ.
- http://www.tvmcalcs.com/calculators/apps/excel_bond_yields
