How to Calculate Bond Value in Excel

Spice42

Editor

posted on 13 years ago — updated on 1 second ago

31
views

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.

Steps

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.Calculate Bond Value in Excel Step 4Bullet1 Version 2.jpg