Syntax. PRICEMAT(Settlement,Maturity,Issue,Rate,Yield,Basis)
Definition. This function returns the price (as a percentage, that is, based on a nominal value) of a security that has a simple interest yield in arrears (no compound interest).
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The maturity date of the security.
Issue (required) The date of the emission of the security.
Rate (required) The agreed-upon nominal interest rate of the security based on an entire year.
Yield (required) The current market interest rate or the expected yield of the security (as an interest rate based on an entire year).
Basis (optional) The method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is omitted, Excel calculates with Basis = 0.
The arguments of the function have the following requirements:
Settlement, Maturity, and Issue require date specifications without a time; that is, decimal places are truncated. Any decimal places of Basis are also truncated to make the value an integer.
If Account, Maturity, or Issue do not have a valid date, PRICEMAT() returns the
#VALUE!error. The account date must be earlier than the maturity date.If Rate or Yield is less than zero, PRICEMAT() returns the
#NUMBER!error.Basis must be a number that can be evaluated to a number from 0 through 4, otherwise PRICEMAT() returns the error
#NUMBER!.
Important
Excel Help contains the following note:
“Dates should be entered with the DATE() function or as results of other formulas or functions. However, problems might occur if dates are entered as text.”
Background. To implement the finance mathematical benefit principle
Payment of creditor = Payment of debtor
for the start of the transaction, the price of a fixed-interest security plus potential accrued interest equals the cash value of the debtor’s future payments certified in this security. The price is the percentage of the security’s par value, that is, as if the par value was 100 monetary units. In this case, the future payment is the repayment plus the nominal interest. There is no compound interest, because evaluations only take place for the intra-annual timeframe. A divided interest rate according to the duration is used for the discount calculation instead of a divided duration in the exponent (ISMA method).
Example. A debt of $1,000.00, which is due in full with a nominal interest rate of 4 percent p.a. and a repayment by December 1, 2010, was agreed upon on June 1, 2010. On August 9, 2010, there is a change in creditors.
At this date, a real interest rate of 2.5 percent p.a. for the outstanding time until repayment is customary. What price does the person who takes over the debt pay?
You get the price of $100.46, which must be multiplied by 10 because of the par value of $1,000.00, by using
=PRICEMAT(C3,C4,C2,4%,2.5%,4)
with C3 as the purchase date, C4 as the maturity date, and C2 as the day of issue. Now add the accrued interest of $7.56, which you can calculate manually or by using ACCRINT(). You can find further explanations in the example for the function ACCRINT().