Evaluation of bond value in Excel

In this article, we will learn about how to evaluate bond value in Excel. 

For Instance, we have given some data to get the bond value at the end of the period as future value. The bond value amount can be calculated using the excel formula. 

The above stated can be calculated using the two functions shown below independently.

  1. PV function
  2. PRICE function 

Let's get some learning about the above functions. It will help us to understand the bond valuation query generated. 

PV function returns the present value of the fixed amount paid over a period of time at a constant interest rate.

Syntax:

= PV (rate, nper, pmt, [fv], [type])

rate: Interest rate per period

nper: total no. of payment period.

pmt: amount paid each period.

fv - [optional] The future value of present payments, can be entered as a negative number.

Example:

Let’s understand this function using the PV function in an example.

Here we have given future value, annual coupon rate, rate of return, total period of maturity & number of payment paid every year as shown in the snapshot below.

Here settlement date & issue date is not provided to us.

We need to construct a formula to gain the bond value. Here we get the rate, nper, pmt & fv values from the following data. See below for explanation.

rate : B3 / B5

nper : B4 * B5

pmt : B2 / B5 * B1 

fv : B1

You can use the formula to obtain the bond value.

Use the formula:

= - PV ( B3/B5 , B4*B5 , B2/B5 * B1 , B1 )

All the values to the function are provided as cell reference.

The above process used while using the PV function. You can perform the same via one more method. Let’s understand this formula using the PRICE function in an example. For that first get something about the PRICE function first.

The PRICE function is a built-in financial function which returns the price per $100 face value of a security that pays periodic interest.

Syntax:

= PRICE (settlement, maturity, rate, yld, redemption, frequency, [basis])

Settlement : Settlement date of the security

Maturity : Maturity date of the security. The maturity date is the date when the security expires

Rate : The security's annual coupon rate

Yld : The security's annual yield

Redemption : Redemption value per $100 face value

Frequency : Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4.

basis : [optional] Day count basis

Example:

Now the example explained below will clear all remaining doubts.Here we have values in column B and their description in A column.

Here we have values to calculate the bond value PRICE per $100 face value of a security that pays periodic interest.

Use the formula:

=PRICE ( B1 , B2 , B3 , B4 , B5 , B6 , 0 )


Note: Dates in the formula must be entered using cell reference. Excel date format creates problem while computing results.

Now Press Enter to get the Price.

The Bond price comes out to be $ 94.63 for $100 redemption value.

As you can see the formula returns the price per $100 face value of a security that pays periodic interest.

Here are some of the observational outcomes for the formula explained above.

Notes:

  1. The function returns #VALUE! Error, if any argument to the function is non - numeric.
  2. Be careful while using Dates & percent number as cell reference
  3. Dates in the formula must be entered using cell reference. Excel date format creates problem while computing results. 
  4. The function sometimes returns negative value as outcome, it happens as you provided other payments as positive. Any change on one side, leads the other affecting.

Hope you understood how to evaluate bond value in Excel. Explore more articles on Excel function here. Please feel free to state your query or feedback for the above article.

Related Articles

How to use the NPER Function in Excel

How to use the PRICE Function in Excel

How to Use RATE Function in Excel

How to Use Compound Interest Function in Excel

How to Use IRR function in Excel

How to Use PV function in Excel

How to Use FV function in Excel

How to calculate interest on a loan

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with wildcards

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube