The Mathematical Exception
In standard bond finance, when you purchase a corporate bond, the corporation pays you a fixed percentage of interest (the “coupon”) every six months until the bond officially expires. Because these interest payments are constantly flowing into your bank account over a span of several years, calculating the true, current market price of the bond requires a massively complex mathematical formula that discounts the future value of every single individual payment.
However, there is a highly specific, mutant class of bonds that completely violates this rule. Some financial instruments guarantee you an interest rate, but they flatly refuse to pay you a single penny until the absolute final day of the bond’s life (the maturity date). On that final day, the corporation pays you the massive face value of the bond plus all the accumulated interest in one giant, single lump sum.
Because the cash flow is entirely restricted to the final day, the standard bond pricing formula will crash and output wildly inaccurate numbers. To instantly and accurately calculate the current market price of a bond that only pays interest at maturity, you must bypass the standard tools and use the highly specialized PRICEMAT (Price at Maturity) function in Microsoft Excel.
Understanding the Syntax
Because the PRICEMAT function does not have to worry about the chaotic math of semi-annual payments, the formula is surprisingly streamlined. It only requires six variables to execute a flawless calculation.
=PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
- settlement: The exact date you are physically purchasing the bond on the secondary market.
- maturity: The exact date the bond officially expires and the massive lump sum is paid out.
- issue: The exact date the bond was originally created and sold by the corporation.
- rate: The annual interest rate guaranteed by the bond’s contract (the coupon rate).
- yld (Yield): The current market yield you demand to earn on this investment.
- [basis]: (Optional) The day-count methodology used by the financial market (e.g.,
0for standard US banking,1for Actual/Actual).
Example 1: Pricing the Lump Sum
Assume you are a financial analyst evaluating a highly unusual municipal bond. The city issued the bond (Issue) on January 1, 2020. The bond guarantees a 4.5% annual interest rate (Rate), but it specifically states that all interest will be withheld until the bond officially expires (Maturity) exactly ten years later, on January 1, 2030.
You are evaluating this bond for purchase on the secondary market today, March 15, 2024 (Settlement). Based on current market conditions, you demand a 5.25% yield (Yld) to justify locking your money up for the next six years with zero interim payments.
To mathematically guarantee you achieve that 5.25% yield, you must calculate exactly how much you should pay for the bond today.
Let’s map out the variables cleanly in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 3, 15) - Cell A2 (Maturity):
=DATE(2030, 1, 1) - Cell A3 (Issue):
=DATE(2020, 1, 1) - Cell A4 (Rate):
4.50% - Cell A5 (Yield):
5.25%
To calculate the exact purchase price, click on cell B1 and type:
=PRICEMAT(A1, A2, A3, A4, A5)
How this works:
- Excel calculates the massive, accumulated lump sum of interest that has been building up since the Issue date (January 1, 2020).
- It then calculates the physical number of days between your Settlement date (March 15, 2024) and the final Maturity date (January 1, 2030).
- It discounts that massive future lump sum backward through time, applying your strict 5.25% yield requirement.
- It instantly outputs 111.4589.
Interpreting the Output
In standard bond finance, the output 111.4589 is the price you must pay per $100 of face value. If you are buying a $100,000 bond, you move the decimal point.
You must pay exactly $111,458.90 today. Because you are buying a bond that has been secretly hoarding unpaid interest for four years, you must pay a massive premium over the base value. However, on January 1, 2030, you will receive the $100,000 face value plus the entire ten years of accumulated 4.5% interest in a single check, perfectly validating your 5.25% yield requirement. By mastering the PRICEMAT function, you gain the ability to instantly decipher the most unusual cash flows in global finance.