How do you calculate the value of a bond in Excel?
Mia Ramsey
Published Feb 18, 2026
Select the cell you will place the calculated price at, type the formula =PV(B20/2,B22,B19*B23/2,B19), and press the Enter key. Note: In above formula, B20 is the annual interest rate, B22 is the number of actual periods, B19*B23/2 gets the coupon, B19 is the face value, and you can change them as you need.
What valuation methods are used to evaluate the price of the bond?
There are different methods and techniques used in the bond valuation process. We can value a bond using: a market discount rate, spot rates and forward rates, binomial interest rate trees, or matrix pricing. The ‘market discount rate’ method is the simplest one.
How do you calculate the current yield of a bond?
The current yield of a bond is calculated by dividing the annual coupon payment by the bond’s current market value. Because this formula is based on the purchase price rather than the par value of a bond, it more accurately reflects the profitability of a bond, relative to other bonds on the market.
How do you calculate a bond discount?
The sum of the present value of coupon payments and principal is the market price of the bond. Market Price = $862.30 + $96.39 = $958.69. Since the market price is below the par value, the bond is trading at a discount of $1,000 – $958.69 = $41.31. The bond discount rate is, therefore, $41.31/$1,000 = 4.13%.
How is yield calculated on a bond?
The simplest way to calculate a bond yield is to divide its coupon payment by the face value of the bond. This is called the coupon rate. If a bond has a face value of $1,000 and made interest or coupon payments of $100 per year, then its coupon rate is 10% ($100 / $1,000 = 10%).
What is the formula for calculating current yield?
Calculating Current Yield The current yield is equal to the annual interest earned divided by the current price of the bond. Suppose a bond has a current price of $4,000 and a coupon of $300. Divide $300 by $4,000, which equals 0.075. Multiply 0.075 by 100 to state the current yield as 7.5 percent.
