When you are architecting a financial model for a specific class of security that pays its entire interest payload at absolute maturity (rather than in periodic coupon payments), standard pricing formulas will mathematically crash. To force the Excel engine to calculate the exact, pristine price per $100 face value for this highly specific asset class, you must use the PRICEMAT function.
Understanding the PRICEMAT Architecture
The PRICEMAT (Price at Maturity) function is a deeply specialized financial engine. It calculates the theoretical market price of a security that accrues interest over its lifespan but only physically pays that interest on the exact date of maturity.
The syntax requires exactly five mandatory arguments and one optional argument: =PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
- settlement: The absolute date the buyer takes possession of the security.
- maturity: The absolute final date the security expires and pays out.
- issue: The exact chronological date the security was originally issued to the market.
- rate: The security’s guaranteed annual interest rate.
- yld: The annual yield mathematically required by the buyer.
- [basis]: An optional integer (0 to 4) that forces the engine to use a specific day-count methodology. If omitted, Excel defaults to the US (NASD) 30/360 system.
Executing the Financial Calculation
Imagine you are evaluating a bond issued on January 1, 2020 (A2). You plan to purchase it on February 15, 2024 (A3). It matures on January 1, 2030 (A4). The stated interest rate is 6% (A5), but the current market yield you require is 7% (A6). You want to use the Actual/Actual day-count basis (A7, value 1).
To calculate the exact purchase price, click an empty cell and type:
=PRICEMAT(A3, A4, A2, A5, A6, A7)The exact millisecond you press Enter, the Excel engine executes the calculus. It determines exactly how much interest has accrued since the issue date, discounts the massive final payout against your 7% yield requirement, and outputs the pristine price (e.g., $93.45). This proves mathematically that you should pay $93.45 for every $100 of face value to achieve your desired yield.
CRITICAL ARCHITECTURAL WARNING: You must format the rate and yld arguments as explicit decimals (e.g., 0.06 and 0.07). Inputting raw integers will cause a catastrophic calculation failure.