# Calculating the issue price of a bond using the NPV function in Excel Extra Credit

Harold Averkamp (CPA, MBA) has worked as a university accounting instructor, accountant, and consultant for more than 25 years. Adam Hayes, Ph.D., CFA, is a financial writer with 15+ years Wall Street experience as a derivatives trader. Besides his extensive derivative trading expertise, Adam is an expert in economics and behavioral finance.

So this is just the second part of the annual coupon pay formula as discussed earlier. The first part, \$183.14, is the present value of the sum of discounted coupons. The first part, \$181.49, is the present value of the sum of discounted coupons.

• From your NPV cell (B14) begin by clicking on the fx button to the left of the formula bar and a pop-up menu titled Insert Function will appear.
• In the secondary market, other factors come into play such as creditworthiness of issuing firm, liquidity and time for next coupon payments.
• Calculating the value of a coupon bond factors in the annual or semi-annual coupon payment and the par value of the bond.

The only trick is a shortcut due to the day count convention; we assume here a round number of days for the various periods which don’t exactly match the calendar. If the slight error doesn’t match the payments on your bond, we suggest you calculate them on your own using our guidelines but substituting for your inputs. Therefore, in box Value6, you will input cell number B12 rather than B11.

Bonds issued by government or corporates are rated by rating agencies like S&P, Moody’s, etc. based on the creditworthiness of issuing firm. The ratings vary from AAA (highest credit rating) to D (junk bonds) and based on the rating the yield to maturity varies. Bonds which are traded a lot and will have a higher price than bonds that are rarely traded. Time for next payment is used for coupon payments which use the dirty pricing theory for bonds. The dirty price of a bond is coupon payment plus accrued interest over the period.

## Step 1. Determine the Interest Paid by the Bond

It sums the present value of the bond’s future cash flows to provide price. As a best practice, create an input area (shown in blue here) where users can enter the bond facts and then use this information to calculate the bond issue price (in yellow). First, enter the bond criteria given, including its maturity date, the face value of the bond, the number of compounding periods, the stated rate of the bond, and the market rate at the time the bonds were issued. A convertible bond is a debt instrument that has an embedded option that allows investors to convert the bonds into shares of the company’s common stock. At its most basic, the convertible is priced as the sum of the straight bond and the value of the embedded option to convert. The longer the term to maturity, the lower the value of the bond, all else equal.

The market rate is determined by the risk of the bond and is the same for bonds with similar risks. As in our yield to maturity calculator, this is a hard problem to do by hand. The trading price of a bond should reflect the summation of future cash flows. Bond valuation is a technique for determining the theoretical fair value of a particular bond. Bond valuation includes calculating the present value of a bond’s future interest payments, also known as its cash flow, and the bond’s value upon maturity, also known as its face value or par value.

## A longer term to maturity will decrease the value of the bond.

In order for that bond paying 5% to become equivalent to a new bond paying 7%, it must trade at a discounted price. Likewise, if interest rates drop to 4% or 3%, that 5% coupon becomes quite attractive and so that bond will trade at a premium to newly-issued bonds that offer a lower coupon. A bond will always mature at its face value when the principal originally loaned is returned. Calculating the value of a coupon bond factors in the annual or semi-annual coupon payment and the par value of the bond. Now that your input area is finished you can use the information to calculate the issue price of the bond using the NPV function in Excel. From your NPV cell (B14) begin by clicking on the fx button to the left of the formula bar and a pop-up menu titled Insert Function will appear.

Bond pricing formula depends on factors such as a coupon, yield to maturity, par value and tenor. These factors are used to calculate the price of the bond in the primary market. In the secondary market, other factors come into play such as creditworthiness of issuing firm, liquidity and time for next coupon payments. Bond valuation looks at discounted cash flows at their net present value if held to maturity.

Again, use cell references so that if the data changes, your final payment information will also change. It then amortizes the premium over the remaining period of the bond, which results in a reduction in the recognized amount of interest expense. It then amortizes the discount over the remaining period of the bond, which results in an increase in the recognized amount of interest expense.

This happens because the company and the credit ratings assess its required return and issue a coupon rate that matches the prevailing interest rate as required by the bonds with similar risk. However, as time passes and the required return changes due to market forces and changes in the risk in the company, the bond can be valued at less or more than the par value. Both stocks and bonds are generally valued using discounted cash flow analysis—which takes the net present value of future cash flows that are owed by a security.

Adam received his master’s in economics from The New School for Social Research and his Ph.D. from the University of Wisconsin-Madison in sociology. He is a CFA charterholder as well as holding FINRA Series 7, 55 & 63 licenses. He currently researches and teaches economic sociology and the social studies of finance at the Hebrew University in Jerusalem. The new accounting standard provides greater transparency but requires wide-ranging data gathering.

## A higher coupon rate will increase the value of the bond.

When the coupon rate is higher, there is less discounting, and the bond trades at a higher price than the par/face value and is known as a premium bond. Similarly, when the coupon rate is lower, more discounting leads to bond trading at less than the par/face value and is known as a discount bond. A 5-year bond with a coupon payment of 5% and a par value of \$1,000 was issued a year ago. The required rate of return for bonds with similar risk currently is 4.00%.

Bond valuation, in effect, is calculating the present value of a bond’s expected future coupon payments. The theoretical fair value of a bond is calculated by discounting the future value of its coupon payments by an appropriate discount rate. The discount rate used is the yield to maturity, which is the rate of return that an investor will get if they reinvested every coupon payment from the bond at a fixed interest rate until the bond matures. It takes into account the price of a bond, par value, coupon rate, and time to maturity.

Since bonds are an essential part of the capital markets, investors and analysts seek to understand how the different features of a bond interact in order to determine its intrinsic value. Like a stock, the value of a bond determines whether it is a suitable investment for a portfolio and hence, is an integral step in bond investing. Once you click OK, the NPV will be calculated and displayed in the NPV cell. For example, if a bond pays a 5% interest rate once a year on a face amount of \$1,000, the interest payment is \$50. The size of the U.S. municipal bond market, or the total amount of debt outstanding, at the end of 2018, according to the Securities Industry and Financial Markets Association (SIFMA), an industry group.

The value of the bond is the price an investor would pay to another to purchase the bond. Bond valuation is a process of determining the fair market price of the bond based on factors such as interest rates, bond payments, and time periods. Let’s calculate the price of a bond which has a par value of Rs 1000 and coupon payment is 10% and the yield is 8%. A bond that pays a fixed coupon will see its price vary inversely with interest rates. This is because receiving a fixed interest rate, of say 5% is not very attractive if prevailing interest rates are 6%, and become even less desirable if rates can earn 7%.

## AccountingTools

In the particular case where the coupon rate is equal to the discount rate, then the bond’s price is the same as its par value (since the bond cannot command a premium or require a discount). A change in any of these variables (coupon, discount rate, or time to maturity) will influence the price of the bond. For example, find the present value of a 5% annual coupon bond with \$1,000 face, 5 years to maturity, and a discount rate of 6%. You should work this problem on your own, but the solution is provided below so you can check your work. The required return on a bond of the company should adjust for its risk. If a company invests in a project that is riskier than its own overall risk (i.e., the required return on the project), it should include the risk premium to account for increased risk.

Duration instead measures a bond’s price sensitivity to a 1% change in interest rates. Longer-term bonds will also have a larger number of future cash flows to discount, and so a change to the discount rate will have a greater impact on the NPV of longer-maturity bonds as well. A zero-coupon bond makes no annual or semi-annual coupon payments for the duration of the bond. The difference between the purchase price and par value is the investor’s interest earned on the bond.

To calculate the value of a zero-coupon bond, we only need to find the present value of the face value. Carrying over from the example above, the value of a zero-coupon bond with a face value of \$1,000, YTM of 3% and 2 years to maturity would be \$1,000 / (1.03)2, or \$942.59. The weighted average cost of capital (WACC) is the average after-tax cost of capital from all the sources of capital, such as debt, equity, and preferred stocks. It can also be used as a discount rate as it is a company’s required return on investment. Since this is the return demanded by the investors, it is usually used as the discount rate in bond valuation because investors form the market.

In the box beside the words “Or select a category,” click the drop-down menu and select Financial. Valuation of a bond refers to determining the intrinsic value of the bond. This is usually carried out by discounting the cash flows to be received by the bond. Next, you need to determine the cash interest payments that will be paid to the bondholders each compounding period (in this case, annually). Recall that to calculate this, you multiply the stated rate of the bond by its face (or maturity) value.