PRICE

What it does

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

PRICE(settlement,maturity,rate,yld,redemption,frequency,[basis]) returns a coupon security’s clean price per 100 face value from an annual yield. Rate is the annual coupon rate; frequency is one, two or four payments per year.

Try it

=PRICE(DATE(2027,1,1),DATE(2028,1,1),0.06,0.06,100,2,0) is about 100. Settlement is on a coupon date, the annual coupon and yield are both six percent, and redemption is par.

=PRICE(DATE(2027,4,1),DATE(2027,7,1),0.06,0.08,100,2,0) is about 99.4803921569. Only one coupon remains. The clean price subtracts 1.5 of accrued coupon interest from a dirty price about 100.9803921569.

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. Settlement must precede maturity; negative coupon rate, invalid frequency/basis or nonpositive redemption gives #NUM!. The documented PRICE domain rejects negative yield. Clean price excludes accrued coupon interest.

How to implement

The current code keeps coupon scheduling, the settlement fraction, discount powers and accumulation as separate stages. Unless R is named, the arithmetic below is ordinary binary64 with a store after each operation.

1. Truncate and validate dates, frequency and basis. Build coupon dates backward from maturity in steps of 12/frequency months, retaining the source’s month-end rules. Stop with previous coupon <= settlement < next coupon and count N remaining coupons.
2. Set C=(100*rate)/frequency. Obtain coupon denominator E and accrued span A from the selected day-count helpers. The current denominator is 360/frequency for bases 0,2,4, 365/frequency for basis 3 and the actual coupon-period length for basis 1.
3. Set the internal remaining span DSC=E-A, then off=DSC/E. Do not replace it with a fresh day count from settlement to next coupon. In particular, public COUPDAYSNC at bases 2 and 3 can differ from this derived complement; the retained coupon note records that discrepancy.
4. If N=1, compute den=1+(yld/frequency)*(DSC/E), dirty price (redemption+C)/den, and clean price dirty-(C*A)/E. Validate the positive denominator.
5. If N>1, form base=1+yld/frequency. In forward coupon order, accumulate C/discount(base,off+k) for k=0 through N-1. Add redemption as a separate final term divided by discount(base,off+N-1). Finally subtract accrued (C*A)/E.

The current discount helper uses square-and-multiply for a nonnegative integer exponent below 1024, with binary64 multiplication after each selected product and square. For other exponents it obtains stored public LN(base), forms R(exponent*LN(base)), then calls public EXP on that stored product. This is distinct from blindly calling a host pow or substituting the public POWER wrapper’s root shortcuts.

For the short-bond example, C=3, E=180, A=90 and DSC=90. The denominator is 1+(0.08/2)*0.5=1.02. Dirty price is 103/1.02, about 100.9803921569; accrued is 3*90/180=1.5, giving clean price about 99.4803921569.

The calendar helpers include a credited, changed translation of ExcelFinancialFunctions; the project’s pricing stages are separately constrained by recorded answers. The source retains 25 integer-discount controls over five bonds and two 315-row fractional-discount holdouts on their stated scopes. Calendar seams, unmeasured route boundaries, LN/EXP internals and remaining numerical mismatches stay open. YIELD currently retains a different forward-power path for its solver.

Rust code

1 price_fn "PRICE" (6, 7) [V "settlement", V "maturity", V "rate", V "yld", V "redemption", V "frequency", V "basis"] |s, a, sv| {
    let (settlement, maturity) = (date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?);
    bond_out(s, bond::price_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, num(s, a, 4)?, num(s, a, 5)?, Some(or(s, a, 6, 0.0)?)))
}

pub fn price_kernel(
    settlement: f64,
    maturity: f64,
    rate_: f64,
    yld: f64,
    red: f64,
    frequency: f64,
    basis_: Option<f64>,
) -> Result<f64, BondCoreEvalError> {
    let c = ctx(settlement, maturity, frequency, basis_)?;
    let p = period(c)?;
    // PRICE uses Excel's C-runtime integer-`pow` discount staging (binexp). YIELD's solver
    // (below) still calls the legacy `pcomp` because its inversion schedule is not yet
    // identified and swapping the price kernel under it would regress the current witnesses.
    Ok(pcomp_disc(rate(rate_)?, rate(yld)?, pos(red)?, c, p, true)?.0)
}

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.

• XC-PRICE-001: build 20131 versus build 20228; 2 same-input pairs, 0 differing answers. Compared axes: excel-build. Numeric answers were compared by their recorded binary64 bits; other answers by typed value.

Agreement on these compared inputs does not establish agreement on unmeasured inputs.

Recorded environments and comparison details.

Alternatives

YIELD performs the inverse calculation; ODDFPRICE and ODDLPRICE handle irregular coupons.

Named variants

No named variants are recorded yet.