PRICEMAT()

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

The arguments of the function have the following requirements:

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().

See Also

ACCRINT(), COUPDAYS(), YIELDMAT()