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 and thusÂ incurring a debt which is to be repaid Â "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.Â (Read more in: Bond Basics).

To determine 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 an Excel spreadsheet.

The present value (PV) of a bond represents the sum of all the future cash-flow from that contract until it matures with a full repayment of the par value.

The Clean Bond Price of a bond does not include the accrued interest to maturity that each coupon paid would gain until maturity.

The Dirty Bond Price of a bond however, does include the accrued interest to maturity that each coupon paid would gain until maturity.

**Bond Value =Â ****Sum of the**** Present Value (PV) of Interest Payments + (PV)Â of Principal PaymentÂ **

We will discuss the calculation of the Present Value of a bond for

**Â Â Â A) A Zero Coupon Bond**

**Â Â Â Â Â B) A Bond with Annual annuities**

**Â Â Â Â Â C) A Bond with Bi-Annual annuities**

**Â Â Â Â Â D) A Bond with Continuous Compounding**

**Â Â Â Â Â E) Dirty Bond Pricing**

**A. A Zero Coupon Bond**

**A Zero Coupon Bond does not deliver any coupon payment during the life of the bond but sells at a discount from the par value of the bond.**

**Example 1:**Â Zero Coupon Bonds

A bond maturing in 20 years with face value of $1000 while incurring no interest is known as a Zero-Coupon Bond. Â For example, in this case the bond's value 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 with the help of Microsoft Excel.

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 we have a Zero Coupon Bond 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 maturity date.

**B. A Bond with Annuities**

**Example 2:**Â Bond with annual coupon payments

Company 1 issues a bond with principal $1000 a rate of 2.5% annually with maturity 20 years and a discount rate of 4%.

The bond provides coupons annually and pays a Coupon amount of 0.025*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 cost $796.14

**C. A Bond with Bi-annual Annuities**

**Example 3:**Â Bond with bi-annual coupons cash flow

Company 1 issues a bond with principal $1000 a rate of 2.5% annually with maturity 20 years and a discount rate of 4%.

The bond provides coupons annually and pays a coupon amount of 0.025*1000 / 2= $25/2 = $12.5

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. A Bond with Continuous Compounding**

**Example 5:**Â Bond with continuous compounding

Continuous Compounding refers to a constant compounding. 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 reflecting a constant compounding. The cash flow is discounted by the exponential factor.

**F). Dirty Bond Pricing**

**Example 6:**Â Dirty Bond Pricing

The Clean Price of a bond is the price that does not include accrued interest. This is the pricing 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 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 * elapsed days since last paid Coupon ) / Coupon Day Period

Â i) Company 1 issues a bond with principal $1000 a rate of 5% annually with maturity 20 years and a Discount rate of 4%. Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ii) Â The coupon is paid semi-annually: Jan 1st, and July 1st. Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â iii) The bond is sold for $100,Â 30thÂ April 2011 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â iv) Since the last coupon issued, there have been 119 days of accrued interest. **Thus theÂ ****Accrued interest = 5 *(119/(365/2) )Â = 3.2603**

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