When you are architecting a complex financial model for corporate or government bonds that distribute periodic interest payments (coupons), determining the fair market value of that security requires intense geometric discounting of all future cash flows. To force the Microsoft Excel engine to execute this complex calculus and determine the absolute clean price per $100 of face value, you must deploy the highly specialized PRICE function.
Understanding the PRICE Architecture
The PRICE function is a rigid, advanced algorithmic engine. It calculates the present mathematical value of a bond by simultaneously ingesting its chronological lifespan, its annual coupon payout, and your required annual yield. It then executes a reverse discount matrix, outputting the exact dollar amount you should mathematically pay per $100 of face value to achieve your target yield.
The syntax requires exactly six mandatory arguments (and one optional modifier): =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
- settlement: The absolute chronological date the bond is purchased.
- maturity: The absolute chronological date the bond expires.
- rate: The annual coupon (interest) rate paid by the bond.
- yld: Your required annual yield to maturity.
- redemption: The absolute redemption value per $100 face value (usually 100).
- frequency: The number of coupon payments per year (1 for annual, 2 for semi-annual, 4 for quarterly).
Executing the Price Extraction
Imagine your firm wants to purchase a semi-annual corporate bond. The settlement date is February 15, 2024 (Cell A1). The maturity date is November 15, 2028 (Cell A2). The bond pays a 5.5% annual coupon (Cell A3). The current market yield you demand is 6.2% (Cell A4). The redemption value is $100 (Cell A5). The payments are semi-annual, so the frequency is 2 (Cell A6).
To execute the precise extraction, click an empty cell and type:
=PRICE(A1, A2, A3, A4, A5, A6)The exact millisecond you press Enter, the Excel engine executes the calculus. It calculates the absolute geometric distance between the dates. It maps out every future semi-annual cash flow. It discounts those flows backward against your 6.2% yield requirement. It outputs the highly precise absolute decimal integer: 97.165.
This provides mathematical proof that, to achieve a 6.2% yield on this 5.5% bond, you must purchase it at a discount, paying exactly $97.165 for every $100 of face value.