Bond Price Excel Function
Written by:Abraham A.Learn to find Bond Price with MS Excel formula, download easy to use Excel template. Bonds, How to use MS Excel to Find market price of zero and non zero coupon rate bonds. Get Free Template. On this web page we show you how to use MS Excel formula or function to compute market price of bonds.
What is Bond's Current or Market Price
What is Bond's Current or Market Price? The answer is that bonds market price has a correlation with interest rates, as interest rates rise the market price or current price of the bond takes a dip and when interest rates fall the price on bond prices go upwardly. This is due to the way bond valuation formula works, the formula which states that current price or market price of the bonds is nothing more than the sum of present value of the interest payments until its maturity and the present value of the maturity value of the bond. Thus when interest rates are high the discounted value of interest payments shrink and so does the discounted value of the terminal or maturity value of the bond. And when interest rates fall, the discounted sum is higher.
Price of a Bond MS Excel Example
We will illustrate how you can use PV formula in MS Excel to find market price of zero coupon and non zero coupon rate bonds, The PV function is used is different scenarios such finding present value of an annuity or lump sum. We extend this functionality of Excel's PV function since market price is nothing more than the sum of present value of bond's interest payment and present value of its terminal value or maturity value. I will start off with an example calculation of market price of bonds that will show you how the PV formula in Excel is used to find bond's price.
Callaghan Motors' bonds have 10 years remaining. interest is paid annually, the bonds have a $1,000 par value, and the coupon rate is 8 percent. The bonds have a yield to maturity of 9%. What is the current market price of the bonds. Now assume interest rates have dropped and the yield to maturity has dropped to 7%. What is the market price at 7%
In Cell A1, Type the text Par Value. In Cell B1 enter the value 1000. In Cell C1 type 1000
In Cell A2, Type the text YTM, In Cell B2 type 15%. In Cell C2 type 6%
In Cell A3, Type the text Coupon Rate, In Cell B3 type 9%. In Cell C3 type 9%
.
In Cell A4, Type the text Years to Maturity. In Cell B4 type 4. In Cell C4 type 4
.
In Cell A5, Type the text Market Price.
In Cell B5 type in the following formula
.
In Cell C5 type in the following formula
.
Semiannual Compounding
Most bonds in the US make semi annual payments thus if the bond interest rate has semi annual compounding we will tweak the above mention formula a bit as follows to take into consideration the twice a year interest payment.
In Cell B6 type in the following formula
.
In Cell C6 type in the following formula
.
MS Excel Price of a Bond Template
Download this MS Excel Template to find Price of a Bond| A | B | C | D | |
| 1 | Par Value | $1,000 | $1,000 | |
| 2 | YTM | 9%% | 7% | |
| 3 | Coupon Rate | 8% | 8% | |
| 4 | Years to Maturity | 10 | 10 | |
| 5 | Market Price | ($935.82) | ($1070.24) | |
| 5 | Market Price (Semi Annual) | ($934.96) | ($1071.06) |
Related Bonds Excel Templates
Following is a list of related Excel templates that cover other aspects of bond valuation used in financial management:Bond Price Calculator
You can opt to use this Bond Price Calculator that calculates price of a non zero coupon bond when you provide bond's maturity value, coupon rate, yield to maturity on bond, and years to maturity on bonds. It also lets you select the frequency of interest payments whether annual or semi-annual.