YIELDMAT

What it does

YIELDMAT(settlement, maturity, issue, rate, pr, [basis])

YIELDMAT(settlement,maturity,issue,rate,pr,[basis]) returns the annual yield of a security that pays its interest at maturity. Rate is the interest rate from issue and pr the clean purchase price per 100 face value.

Try it

=YIELDMAT(DATE(2027,4,1),DATE(2027,7,1),DATE(2027,1,1),0.06,100,0) is about 0.0591133005. Under 30/360, interest from issue to maturity is three and accrued interest at settlement is 1.5.

Quirks

Basis defaults to zero: 0 US 30/360, 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360. Dates and selectors are truncated under their documented rules. Use DATE values rather than locale-dependent date text.

Microsoft documents #VALUE! for invalid dates and #NUM! for a negative interest rate, nonpositive price, invalid basis or settlement at/after maturity. The current helper additionally requires issue strictly before settlement. Accrued interest changes the invested amount; using clean price alone in the denominator is a different calculation.

The current remaining day count is the difference between its issue-to-maturity and issue-to-settlement day counts. Recomputing a fresh settlement-to-maturity 30/360 count can change calendar-boundary behaviour.

How to implement

The current recovered arithmetic keeps normalized price and interest terms separate. Use R at every named operation below.

1. Validate dates in the order required by the host and require issue<settlement<maturity. Admit nonnegative rate and positive price.
2. Compute DIM=days(issue,maturity,basis) and A=days(issue,settlement,basis) with the source’s day-count helper. Set DSM=R(DIM-A). Obtain B from days_in_year_for_mat(issue,settlement,basis), using that interval’s calendar rules.
3. Calculate interest=R(R(DIM/B)*rate), accrued=R(R(A/B)*rate) and price_norm=R(price/100).
4. Store invested=R(price_norm+accrued). Reuse this stored value in gain=R(R(1+interest)-invested).
5. Return R(R(gain/invested)*R(B/DSM)). The gain division and year-ratio division are separate stores before multiplication.

For the example, DIM=180,A=90,DSM=90,B=360. The reconstructed stored stages are:

The mathematical expression can also be written (103/101.5-1)*4, but that expression does not preserve the identified subtraction and reuse of invested. The table evaluates the reconstruction; it is not a new Excel reading. The source records a 1,250-input control, including a held-out sweep, for its stored graph. Calendar helpers, publication and environment transfer retain their own scope.

Rust code

6 yieldmat_fn "YIELDMAT" (5, 6) [V "settlement", V "maturity", V "issue", V "rate", V "pr", 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::yieldmat_kernel(settlement, maturity, issue, num(s, a, 3)?, num(s, a, 4)?, Some(or(s, a, 5, 0.0)?)))
}

pub fn yieldmat_kernel(
    settlement: f64,
    maturity: f64,
    issue: f64,
    rate_: f64,
    price: f64,
    basis_: Option<f64>,
) -> Result<f64, BondCoreEvalError> {
    let settlement = dser(settlement)?;
    let maturity = dser(maturity)?;
    let issue = dser(issue)?;
    let rate_ = rate(rate_)?;
    let price = pos(price)?;
    let basis_ = basis(basis_.unwrap_or(0.0))?;
    if !(issue < settlement && settlement < maturity) {
        return Err(derr(WorksheetErrorCode::Num));
    }
    // W109 identification (2026-07-11, live build 20131, 1250/1250 bit-exact
    // incl. held-out sweep): the arithmetic is the legacy x87 spill loop —
    // every assignment double-rounded (`RN53(RN64(op))`) — with the PUBLISHED
    // formula's association: `term1 = (1 + DIM/B·rate) - term2`, reusing
    // `term2 = price/100 + A/B·rate` (the former F#-style left chain
    // `dim/b*rate + 1 - price/100 - a/b*rate` is 1-2 ULP off and ruled out).
    use crate::ported::excel_numeric::{excel_x87_add, excel_x87_div, excel_x87_mul, excel_x87_sub};
    let b = days_in_year_for_mat(issue, settlement, basis_)?;
    let dim = dd(issue, maturity, basis_)?;
    let a = dd(issue, settlement, basis_)?;
    let dsm = excel_x87_sub(dim, a);
    let dbr = excel_x87_mul(excel_x87_div(dim, b), rate_);
    let accr = excel_x87_mul(excel_x87_div(a, b), rate_);
    let p_norm = excel_x87_div(price, 100.0);
    let term2 = excel_x87_add(p_norm, accr);
    let term1 = excel_x87_sub(excel_x87_add(1.0, dbr), term2);
    let quotient = excel_x87_div(term1, term2);
    let year_ratio = excel_x87_div(b, dsm);
    Ok(excel_x87_mul(quotient, year_ratio))
}

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

PRICEMAT solves price; YIELD handles periodic coupons; YIELDDISC handles a discounted redemption without this accrued-interest model.

Named variants

No named variants are recorded yet.