PRICE Function (LibreOffice Calc)
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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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= annual2= semiannual4= 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
Related Patterns and Alternatives ▾
- 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.