Now we are going to learn all about formulas and functions. This is the best way to learn them:
Returns the price per $100 face value of a security that pays periodic interest.
PRICE(settlement; maturity; rate; yld; redemption; frequency; [basis])
The PRICE function syntax has the following arguments:
- Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
- Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
- Rate Required. The security’s annual coupon rate.
- Yld Required. The security’s annual yield.
- Redemption Required. The security’s redemption value per $100 face value.
- Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
- Basis Optional. The type of day count basis to use.
BASIS DAY COUNT BASIS 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
- Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
- The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
- Settlement; maturity; frequency; and basis are truncated to integers.
- If settlement or maturity is not a valid date; PRICE returns the #VALUE! error value.
- If yld < 0 or if rate < 0; PRICE returns the #NUM! error value.
- If redemption <= 0; PRICE returns the #NUM! error value.
- If frequency is any number other than 1; 2; or 4; PRICE returns the #NUM! error value.
- If basis < 0 or if basis > 4; PRICE returns the #NUM! error value.
- If settlement => maturity; PRICE returns the #NUM! error value.
- PRICE is calculated as follows:where:
- DSC = number of days from settlement to next coupon date.
- E = number of days in coupon period in which the settlement date falls.
- N = number of coupons payable between settlement date and redemption date.
- A = number of days from beginning of coupon period to settlement date.
Change the white cells data and see the result in the yellow cell.