Browse

PRICE Function (LibreOffice Calc)

Financial Advanced LibreOffice Calc LibreOffice 3.0

The PRICE function returns the price per 100 currency units of face value for a coupon-paying security. It is essential for bond valuation, yield analysis, and professional fixed-income modeling.

The **PRICE** function calculates the **price per 100 currency units of face value** for a coupon-paying bond. It uses settlement date, maturity date, coupon rate, yield, redemption value, coupon frequency, and day-count basis to compute the clean price of the bond.

What the PRICE Function Does

  • Computes the clean price of a coupon-paying bond
  • Uses coupon frequency (annual, semiannual, quarterly)
  • Supports multiple day-count basis systems
  • Works with real dates, serial numbers, and DATEVALUE
  • Fully compatible with Excel’s PRICE function

It is designed to be precise, finance‑grade, and essential for fixed‑income valuation.

Syntax

PRICE(settlement; maturity; rate; yield; redemption; frequency; [basis])

Arguments

  • settlement:
    The date the security is traded to the buyer.

  • maturity:
    The date the security matures.

  • rate:
    Annual coupon rate (e.g., 0.05 for 5%).

  • yield:
    Annual yield to maturity (e.g., 0.045 for 4.5%).

  • redemption:
    Redemption value per 100 face value (usually 100).

  • frequency:
    Number of coupon payments per year:

    • 1 = annual
    • 2 = semiannual
    • 4 = quarterly

  • basis (optional):
    Day-count convention:

basis Day-count convention
0 US 30/360
1 Actual/Actual
2 Actual/360
3 Actual/365
4 European 30/360

Basic Examples

Price of a semiannual coupon bond

=PRICE("2024-03-15"; "2029-03-15"; 0.05; 0.045; 100; 2)

Using Actual/Actual

=PRICE(A1; A2; 0.04; 0.045; 100; 2; 1)

Using text dates

=PRICE(DATEVALUE(A1); DATEVALUE(A2); Rate; Yield; 100; 2)

Advanced Examples

Quarterly coupon bond

=PRICE("2024-02-10"; "2027-02-10"; 0.06; 0.055; 100; 4)

From imported CSV timestamps

=PRICE(DATEVALUE(LEFT(A1;10)); DATEVALUE(LEFT(A2;10)); Rate; Yield; Redemption; Freq)

From Excel serial dates stored as text

=PRICE(DATE(1899;12;30)+VALUE(A1); DATE(1899;12;30)+VALUE(A2); Rate; Yield; Redemption; Freq)

Compute dirty price manually

=PRICE(A1;A2;Rate;Yield;Redemption;Freq;Basis) + ACCRINT(Issue;FirstInt;Settlement;Rate;100;Freq;Basis)

Compute yield from price (inverse)

=YIELD(Settlement; Maturity; Rate; Price; Redemption; Freq; Basis)

Price a premium bond

=PRICE(A1;A2;0.07;0.05;100;2)

Price a discount bond

=PRICE(A1;A2;0.03;0.05;100;2)

Edge Cases and Behavior Details

PRICE returns a numeric value (price per 100 face value)

Accepts:

  • Real dates
  • Serial numbers
  • DATEVALUE outputs

Invalid text → Err:502

Behavior details

  • Settlement < Maturity must hold
  • Frequency must be 1, 2, or 4
  • Basis must be 0–4
  • Uses clean price (excludes accrued interest)
  • Coupon schedule is calculated backward from maturity
  • Time components ignored

PRICE of an error → error propagates

Common Errors and Fixes

Err:502 — Invalid argument

Cause:

  • Dates not recognized
  • Frequency not 1, 2, or 4
  • Basis outside 0–4

Fix:

  • Wrap dates with DATEVALUE
  • Validate frequency and basis

Err:504 — Invalid date sequence

Cause:

  • Settlement after maturity

Fix:

  • Correct date order

Unexpected price

Cause:

  • Wrong yield
  • Wrong coupon rate
  • Incorrect basis
  • Incorrect redemption value

Fix:

  • Verify inputs carefully
  • Confirm day-count convention

Best Practices

  • Use Actual/Actual (basis 1) for government bonds
  • Use 30/360 for corporate bonds
  • Normalize text dates with DATEVALUE
  • Validate coupon frequency carefully
  • Use PRICE with YIELD to build full valuation models
  • Use COUP* functions to debug coupon schedules
PRICE is your clean‑price engine — essential for valuation, yield analysis, and any professional fixed‑income model.
  • Use YIELD to compute yield from price
  • Use ACCRINT to compute accrued interest
  • Use COUPDAYS, COUPDAYSNC, COUPNUM, COUPPCD, COUPNCD for coupon schedule logic
  • Use YEARFRAC for fractional year calculations

By mastering PRICE and its companion functions, you can build powerful, accurate, and fully professional fixed‑income valuation models in LibreOffice Calc.