Suppose the settlement date of a bond you purchased is November 30, 2001; the maturity date of the bond is December 31, 2028; the bond has a coupon rate of 6.25% and interest is paid semi-annually; the face value of the bond is $1000; and actual days per month/year is used for the day-count basis (not 30/360). Suppose investors currently want an 8.3% return for this type of bond. What price should they be willing to pay?

To use Excel, you will first go to the Function Wizard. The Function you are going to use is in the function category of FINANCIAL and is PRICE. If you do not have the PRICE function, go to TOOLS--ADD-INS and click on ANALYSIS TOOLPAK.

To use the PRICE function, you need to complete the following (you may have to scroll down to get everything):

(1) SETTLEMENT is the settlement date.
You have to type in DATE(2001, 11, 30) . Click the Tab key (not the <Enter> key). This gives you the number of days from 1/1/1900.

(2) MATURITY is the maturity date.
You have to type in DATE(2028, 12, 31). Click the Tab key.
This gives you the number of days from 1/1/1900.

(3) RATE is the coupon rate.
You type in .0625. Click the Tab key.

(4) YIELD is the desired yield to maturity (or current market rate of interest).
You type in .083. Click the Tab key.

(5) REDEMPTION is the redemption value per $100 of value
You type in 100 since you will not be given more than $100 per $100 of face value (even if the face value of the bond is $1000). Click the Tab key.

(6) FREQUENCY is how often interest is paid; 2 for semi-annual and 1 for annual. You type in 2 (the default) since interest is paid semi-annually. Click the Tab key.

(7) BASIS Type of day-count basis to use: 0 means 30/360, 1 is actual/actual, etc.
Type in 1. Click the Tab key.

(8) Click on FINISH

The answer I got was 78.02187. This means that investors are only willing to pay me 78.02 per 100. For a $1000 bond (multiply by 10), investors will only pay me $780.22.   Investors are only willing to pay about $780 for a bond with a $1000 face value.   Why? [Because the coupon rate is below the desired yield to maturity sought by investors].