The `PRICEMAT` function in Excel is used to calculate the price per $100 face value of a security that pays interest at maturity. This function is particularly useful for dealing with investments such as Treasury bills or zero-coupon bonds where interest is paid at the end of the investment term.
Here’s how you use the `PRICEMAT` function:
Syntax
PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
Arguments
- `settlement`: The settlement date of the security, or the date on which the security is traded to the buyer. This date must be after the issue date.
- `maturity`: The maturity date of the security, or the date upon which the security expires.
- `issue`: The issue date of the security, which is the date on which the security was first issued.
- `rate`: The interest rate of the security.
- `yld`: The annual yield of the security.
- `[basis]`: An optional argument that specifies the day count basis to use. It can be any of the following:
- `0` or omitted – US (NASD) 30/360
- `1` – Actual/actual
- `2` – Actual/360
- `3` – Actual/365
- `4` – European 30/360
Example
Suppose you have a security with the following details:
- Issue date: January 1, 2023
- Settlement date: March 1, 2023
- Maturity date: March 1, 2024
- Annual interest rate: 5%
- Annual yield: 6%
In Excel, the formula would look like this:
=PRICEMAT("2023-03-01", "2024-03-01", "2023-01-01", 0.05, 0.06, 0)
Notes
- Dates should be entered using the `DATE` function or as results of other formulas or functions. If you enter dates as text, it might result in errors due to misinterpretation of the date formats.
- Ensure that your Excel environment handles date inputs according to the region’s settings for accurate results.
- The `PRICEMAT` function assumes a par value of $100.
This function will return the price per $100 face value of the security, considering the given dates, interest rate, and yield. Adjust the `basis` argument as necessary to reflect the specific day count convention applicable to your investment scenario.
Unlock Your Potential
Excel
Access
Power BI
