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.
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:
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
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.
