PRICEMAT

What it does

PRICEMAT(settlement, maturity, issue, rate, yld, [basis])

PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) returns the clean price per 100 of face value of a security that pays all of its interest at maturity. Interest accrues at rate from issue, the buyer settles part-way through, and the whole of it arrives with the principal.

With B the year length under the chosen basis, and three day counts

• A, from issue to settlement,
• DIM, from issue to maturity,
• DSM, from settlement to maturity,

the price is

PRICEMAT = (100 + (DIM/B) × rate × 100) / (1 + (DSM/B) × yld) − (A/B) × rate × 100

Microsoft's page prints its equation as an image that could not be read, and defines the four terms in words. The expression above is the one the notes below record from Excel's own answers.

Read from left to right, it has three parts:

• 100 + (DIM/B) × rate × 100 is the single payment at maturity: principal plus all interest since issue.
• Dividing by 1 + (DSM/B) × yld discounts that payment back to settlement at simple interest, not compound. The yield applies only to the span the buyer actually holds.
• (A/B) × rate × 100 removes the interest already accrued, which is paid to the seller separately. What is left is the price quoted without accrued interest.

The interest is simple throughout. A security with rate = yld does not price at exactly 100, even though the two rates agree, because the numerator earns from issue and the denominator discounts only from settlement. Microsoft's example shows this: settlement 15 February 2008, maturity 13 April 2008, issue 11 November 2007, rate 6.1%, yield 6.1%, basis 0. Here A = 94, DIM = 152, DSM = 58, and

(100 + 152/360 × 6.1) / (1 + 58/360 × 0.061) − 94/360 × 6.1 = 99.98449…

which Microsoft shows as $99.98.

Arguments

Note the order: issue is the third argument, after maturity, although it comes first in time. A call that passes the dates chronologically puts issue in the settlement slot, and the #NUM! it then produces says nothing about which argument is out of place.

The documented basis values are the standard five, as on the DISC page: 0 or omitted = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360. All three dates and basis are documented as truncated to integers.

Microsoft's example labels rate "Percent semiannual coupon". Nothing in the function is semiannual: there is one interest payment, at maturity. The label is best ignored.

There is no redemption argument. The security redeems at 100 plus interest, and the page's result is per 100 of face value.

Numeric coercion of the argument slots follows the shared call model; see Coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number, a price per 100 of face value, excluding accrued interest.

• yld = 0 is admissible. The documented refusal is yld < 0, and a recorded row with yld = 0 returns a number: 105.472… for a 5% rate with 394 days to run on basis 0. With zero yield there is no discounting, and the result is 100 plus the interest still to come.
• rate = 0 is admissible under Microsoft's wording, which refuses only rate < 0. It reduces the formula to a plain simple-yield discount of 100. No recorded row exercises it.
• issue on or after settlement is not on Microsoft's list, but see Errors below.
• The fractional part of a date is dropped. 208 of the 2,404 rows behind the notes carry a fractional date, and each fits the integer day counts.
• Empty, missing and error arguments are expected to follow the shared call model; see The value universe. No reading records this for PRICEMAT.

Errors

As documented on Microsoft's PRICEMAT page, with what the recorded rows add (Excel 16.0 build 20430, Windows 64-bit, AMD Ryzen 7 4800H):

Microsoft's list says nothing about the order of issue and settlement, and the page checks issue only for validity. One recorded row, with issue 22 January 2010 and settlement 14 October 2009, returns #NUM!. Both statements are shown because they differ. That row does not settle issue = settlement.

How to implement

Numerical notes

The year length at basis 1. Excel's B comes from the span from issue to settlement, not from settlement to maturity. All 526 basis 1 rows fit this rule, to at least ten significant figures:

• for a span of at most one year, if both dates are in one calendar year, B is that year's length; otherwise B is 366 if a 29 February lies in the span and 365 if not;
• for a span of more than one year, B is the average length of the calendar years from the issue year to the settlement year, both included.

Rust code

5 pricemat_fn "PRICEMAT" (5, 6) [V "settlement", V "maturity", V "issue", V "rate", V "yld", V "basis"] |s, a, sv| {
    let (settlement, maturity, issue) = (date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?, date_1900(s, a, 2, sv)?);
    bond_out(s, bond::pricemat_kernel(settlement, maturity, issue, num(s, a, 3)?, num(s, a, 4)?, Some(or(s, a, 5, 0.0)?)))
}

pub fn pricemat_kernel(
    settlement: f64,
    maturity: f64,
    issue: f64,
    rate_: f64,
    yld: f64,
    basis_: Option<f64>,
) -> Result<f64, BondCoreEvalError> {
    let settlement = dser(settlement)?;
    let maturity = dser(maturity)?;
    let issue = dser(issue)?;
    let rate_ = rate(rate_)?;
    let yld = rate(yld)?;
    let basis_ = basis(basis_.unwrap_or(0.0))?;
    if !(issue < settlement && settlement < maturity) {
        return Err(derr(WorksheetErrorCode::Num));
    }
    let b = days_in_year_for_mat(issue, settlement, basis_)?;
    // Published EFF getMatFactors uses numerator-position day counts. This
    // distinction matters for a February-end issue and a 31st maturity.
    // Its original price association with stored-PC64 operations reproduces
    // all 2,404 recorded PRICEMAT bank outcomes at R23.
    let dim = days_between_num(issue, maturity, basis_)?;
    let a = days_between_num(issue, settlement, basis_)?;
    let dsm = excel_x87_sub(dim, a);
    let future = excel_x87_add(100.0, excel_x87_mul(excel_x87_mul(excel_x87_div(dim, b), rate_), 100.0));
    let accrued = excel_x87_mul(excel_x87_mul(excel_x87_div(a, b), rate_), 100.0);
    let den = excel_x87_add(1.0, excel_x87_mul(excel_x87_div(dsm, b), yld));
    if den <= 0.0 {
        return Err(derr(WorksheetErrorCode::Num));
    }
    Ok(excel_x87_sub(excel_x87_div(future, den), accrued))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• YIELDMAT — the inverse: given the price, recover yld. The formula is linear in 1 + (DSM/B) × yld, so it can be solved in closed form.
• ACCRINTM — the accrued-interest term in the same form, over the same span: ACCRINTM(issue, settlement, rate, 100, basis). Whether the two always take the same year length at basis 1 has not been compared.
• PRICE — the same question for a security with periodic coupons, with the yield compounded over a schedule.
• PRICEDISC — a security that pays no interest and is quoted at a discount. PRICEMAT is interest-bearing and quoted at a yield.
• RECEIVED and INTRATE — the discount-family functions for fully invested securities, which have no accrual term and no issue date.
• YEARFRAC and DAYS360 — the day counts exposed directly.

Named variants

No named variants are recorded yet.