When you are architecting a complex financial model dealing with fixed-income securities that pay periodic interest, standard discounting formulas are mathematically insufficient. To force the Microsoft Excel engine to execute the correct chronological calculus and determine the absolute mathematical price per $100 face value of a bond, you must deploy the specialized PRICE function.
Understanding the PRICE Architecture
The PRICE function is a highly advanced algorithmic engine. It ingests the chronological parameters of the bond, the coupon rate, the desired yield, the redemption value, and the exact geometric frequency of the coupon payments. It then executes a complex discounted cash flow (DCF) calculation to output the absolute current price of the security.
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 security is purchased.
- maturity: The absolute chronological date the security expires.
- rate: The annual coupon interest rate.
- yld: The required annual yield of the security.
- redemption: The absolute redemption value of the security per $100 face value.
- frequency: The exact number of coupon payments per year (1 = annual, 2 = semi-annual, 4 = quarterly).
- [basis]: An optional integer defining the specific day-count basis algorithm (e.g., 0 for US 30/360).
Executing the Price Extraction
Imagine your firm purchases a corporate bond. The settlement date is February 15, 2024 (Cell A1). The maturity date is November 15, 2030 (Cell A2). The annual coupon rate is 5.75% (Cell A3), and the current market yield is 6.50% (Cell A4). The redemption value is strictly $100 (Cell A5). The bond pays interest semi-annually (Cell A6 = 2). The contract specifies a US (NASD) 30/360 day-count basis (Code 0).
To execute the precise 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. It calculates the exact geometric distance between the settlement and maturity dates, factors in the 14 remaining semi-annual payment nodes, discounts those cash flows against the 6.50% yield requirement, and outputs the absolute integer: 95.8459.
This provides mathematical proof that, given the current yield environment, you should pay exactly $95.85 for every $100 of face value.