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 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. (Read more in: Bond Basics).

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.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. Basically, Bond Value = Sum of the Present Value (PV) of Interest Payments + (PV) of Principal Payment.

## Specific Calculations

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) A Bond 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. A Zero Coupon Bond

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 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 maturity date.

The bond has a present value of \$376.89.

B. A Bond with Annuities

Company 1 issues a bond with 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. A Bond with Bi-annual Annuities

Company 1 issues a bond with 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. A Bond with Continuous Compounding

Example 5: Bond 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.  F). 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 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:

1. Company 1 issues a bond with principal of \$1,000, paying interest at a rate of 5% annually with maturity date in 20 years and a discount rate of 4%.
2. The coupon is paid semi-annually: Jan 1st and July 1st.
3. The bond is sold for \$100 on 30th April 2011.
4. Since the last coupon was issued, there have been 119 days of accrued interest.
5. 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. http://www.investopedia.com/articles/investing/051315/present-value-different-bond-types-using-excel.asp?utm_campaign=rss_articles&utm_source=rss_www&utm_medium=referral