Calculating the Price and Yield of a Discounted Note in Excel

ID: Q73170

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.

REFERENCES

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

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 5.0


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 21, 1999