When you are architecting a complex fixed-income trading algorithm, calculating the absolute market value of a bond that pays periodic interest is mathematically excruciating. To force the Microsoft Excel engine to execute a highly complex pricing calculus and determine the exact price per $100 of face value for a security, you must deploy the PRICE function.
Understanding the PRICE Architecture
The PRICE function is a specialized financial engine. It ingests the core variables of a security (yield, coupon rate, settlement dates) and calculates the exact, mathematically discounted present value of all future cash flows (coupons and the final principal). It outputs the “clean price” (excluding accrued interest) per $100 of par value.
The syntax requires exactly six mandatory arguments, plus one optional modifier: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
- settlement: The absolute date the buyer takes possession of the security.
- maturity: The absolute date the security expires and pays out.
- rate: The exact annual coupon interest rate.
- yld: The annual yield to maturity (the required return).
- redemption: The redemption value per $100 face value (usually 100).
- frequency: The number of coupon payments per year (1=annual, 2=semi-annual, 4=quarterly).
- [basis]: The day-count convention vector (e.g., 0 for US (NASD) 30/360).
Executing the Pricing Extraction
Imagine your firm is evaluating a semi-annual bond. It settles on February 15, 2024 (A1) and matures on November 15, 2032 (A2). The coupon rate is exactly 5.5% (A3), the required yield is 6.5% (A4), and the redemption value is 100 (A5). The frequency is 2 (A6).
To execute the extraction, click an empty cell and type:
=PRICE(A1, A2, A3, A4, A5, A6, 0)The exact millisecond you press Enter, the Excel engine executes the calculus. Because the required yield (6.5%) is mathematically higher than the coupon rate (5.5%), the bond must trade at a discount. The engine calculates the present value and outputs a precise decimal (e.g., $93.42). This provides absolute mathematical proof of the exact price you must pay per $100 of face value to achieve the targeted yield.