Calculating the Price and Yield of a Discounted Note in Excel

• 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

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

2. PRICE and YIELD are calculated in cells B1 and B2, respectively.

REFERENCES

"Hewlett-Packard Business Calculator Owner's Manual," Model HP-17B, page 205