How to Calculate PV of a Different Bond Type With Excel

A bond is a type of loan contract between an issuer (the seller of the bond) and a holder (the purchaser of a bond). The issuer is essentially borrowing or incurring a debt that is to be repaid at “par value“ entirely at maturity (i.e., when the contract ends). In the meantime, the holder of this debt receives interest payments (coupons) based on cash flow determined by an annuity formula. From the issuer’s point of view, these cash payments are part of the cost of borrowing, while from the holder’s point of view, it’s a benefit that comes with purchasing a bond.

The present value (PV) of a bond represents the sum of all the future cash flow from that contract until it matures with full repayment of the par value. To determine this—in other words, the value of a bond today—for a fixed principal (par value) to be repaid in the future at any predetermined time—we can use a Microsoft Excel spreadsheet.

Bond Value

∑

PVI

PVP

where:

Number of future interest payments

PVI

Present value of future interest payments

PVP

Par value of principal

begin{aligned} &text{Bond Value} = sum_{ p = 1 } ^ {n} text{PVI}_n + text{PVP} \ &textbf{where:} \ &n = text{Number of future interest payments} \ &text{PVI}_n = text{Present value of future interest payments} \ &text{PVP} = text{Par value of principal} \ end{aligned}

$Bond Value = p = 1 \sum n PVI_{n} + PVP where: n = Number of future interest payments PVI_{n} = Present value of future interest payments PVP = Par value of principal$

Specific Calculations

We will discuss the calculation of the present value of a bond for the following:

A) Zero Coupon Bonds

B) Bonds with annual annuities

C) Bonds with bi-annual annuities

D) Bonds with continuous compounding

E) Bonds with dirty pricing

Generally, we need to know the amount of interest expected to be generated each year, the time horizon (how long until the bond matures), and the interest rate. The amount needed or desired at the end of the holding period is not necessary (we assume it to be the bond’s face value).

A. Zero Coupon Bonds

Let’s say we have a zero coupon bond (a bond which does not deliver any coupon payment during the life of the bond but sells at a discount from the par value) maturing in 20 years with a face value of $1,000. In this case, the bond’s value has decreased after it was issued, leaving it to be bought today at a market discount rate of 5%. Here is an easy step to find the value of such a bond:

Here, “rate” corresponds to the interest rate that will be applied to the face value of the bond.

“Nper” is the number of periods the bond is compounded. Since our bond is maturing in 20 years, we have 20 periods.

“Pmt” is the amount of the coupon that will be paid for each period. Here we have 0.

“Fv” represents the face value of the bond to be repaid in its entirety at the maturity date.

The bond has a present value of $376.89.

B. Bonds with Annuities

Company 1 issues a bond with a principal of $1,000, an interest rate of 2.5% annually with maturity in 20 years and a discount rate of 4%.

The bond provides coupons annually and pays a coupon amount of 0.025 x 1000= $25.

Notice here that “Pmt” = $25 in the Function Arguments Box.

The present value of such a bond results in an outflow from the purchaser of the bond of -$796.14. Therefore, such a bond costs $796.14.

C. Bonds with Bi-annual Annuities

Company 1 issues a bond with a principal of $1,000, an interest rate of 2.5% annually with maturity in 20 years and a discount rate of 4%.

The bond provides coupons annually and pays a coupon amount of 0.025 x 1000 ÷ 2= $25 ÷ 2 = $12.50.

The semiannual coupon rate is 1.25% (= 2.5% ÷ 2).

Notice here in the Function Arguments Box that “Pmt” = $12.50 and “nper” = 40 as there are 40 periods of 6 months within 20 years. The present value of such a bond results in an outflow from the purchaser of the bond of -$794.83. Therefore, such a bond costs $794.83.

D. Bonds with Continuous Compounding

Example 5: Bonds with continuous compounding

Continuous compounding refers to interest being compounded constantly. As we saw above, we can have compounding that is based on an annual, bi-annual basis or any discrete number of periods we would like. However, continuous compounding has an infinite number of compounding periods. The cash flow is discounted by the exponential factor.

E. Dirty Pricing

The clean price of a bond does not include the accrued interest to maturity of the coupon payments. This is the price of a newly issued bond in the primary market. When a bond changes hands in the secondary market, its value should reflect the interest accrued previously since the last coupon payment. This is referred to as the dirty price of the bond.

Dirty Price of the Bond = Accrued Interest + Clean Price. The net present value of the cash flows of a bond added to the accrued interest provides the value of the Dirty Price. The Accrued Interest = ( Coupon Rate x elapsed days since last paid coupon ) ÷ Coupon Day Period.

For example:

Company 1 issues a bond with a principal of $1,000, paying interest at a rate of 5% annually with a maturity date in 20 years and a discount rate of 4%.
The coupon is paid semi-annually: Jan 1 and July 1.
The bond is sold for $100 on April 30, 2011.
Since the last coupon was issued, there have been 119 days of accrued interest.
Thus the accrued interest = 5 x (119 ÷ (365 ÷ 2) ) = 3.2603.

The Bottom Line

Excel provides a very useful formula to price bonds. The PV function is flexible enough to provide the price of bonds without annuities or with different types of annuities, such as annual or bi-annual.