Microsoft KB Archive/73170

{|
 * width="100%"|

Calculating the Price and Yield of a Discounted Note in Excel

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 3.0

-

SUMMARY
Microsoft Excel can be used to find the price or yield of a discounted note.

MORE INFORMATION
A note is a written agreement to the buyer to pay a sum of money plus interest for the investment. It is similar to a bond, but it does not have periodic coupons because all the interest is paid at maturity. A discounted note is a note that is purchased below its face or redemption value.

Example
  Enter the following data in a spreadsheet

     A1:     PRICE   B1:     =B3-(B4*B3*((B6-B5)/360)) A2:    YIELD   B2:     =(B3-B1)/B1*(360/(B6-B5)) A3:    RV      B3:     $100.00 A4:    DISC    B4:     8.70% A5:    SETT    B5:     10/14/88 A6:    MAT     B6:     3/17/89

where:

PRICE = purchase price per $100 face value YIELD = yield as an annual percentage RV = redemption value per $100 DISC = discount rate as a percent SETT = settlement date MAT = maturity date  PRICE and YIELD are calculated in cells B1 and B2, respectively.