Master the Excel PRICE Formula: Complete Guide to Bond Valuation
=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])The PRICE formula is a powerful financial function in Excel designed specifically for calculating the price of a security that pays periodic interest, commonly known as bonds. This advanced formula is essential for financial analysts, investment professionals, and anyone involved in fixed-income securities analysis. Understanding bond pricing is fundamental to investment decision-making, as it helps determine whether a bond is trading at a premium, discount, or par value. The PRICE function calculates the market price of a bond based on its settlement date, maturity date, coupon rate, yield, and redemption value. This formula uses complex mathematical principles rooted in present value calculations, discounting future cash flows to their current worth. By mastering the PRICE formula, you'll be able to evaluate bond investments accurately, compare different securities, and make informed financial decisions. Whether you're working in corporate finance, investment banking, or personal wealth management, this formula is indispensable for your Excel toolkit.
Syntax & Parameters
The PRICE formula syntax is: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]). Each parameter plays a crucial role in the calculation. The settlement parameter represents the security's settlement date, expressed as a date serial number or date function result. The maturity parameter specifies when the bond matures and the principal is repaid. The rate parameter is the annual coupon rate as a decimal (for example, 0.05 for 5%), representing the interest paid annually. The yld parameter is the annual yield to maturity, also expressed as a decimal, reflecting the return an investor expects. The redemption parameter indicates the redemption value per $100 face value, typically 100 for par value bonds. The frequency parameter specifies how often coupons are paid: 1 for annual, 2 for semi-annual, 4 for quarterly, or 12 for monthly payments. The optional basis parameter determines the day count convention: 0 (US 30/360), 1 (actual/actual), 2 (actual/360), 3 (actual/365), or 4 (European 30/360). All dates must be valid Excel date values, and all numeric parameters must be positive numbers. The formula assumes the bond pays coupons on regular intervals and calculates the clean price (excluding accrued interest).
settlementmaturityrateyldredemptionfrequencybasisPractical Examples
Corporate Bond Valuation
=PRICE(DATE(2024,1,15), DATE(2029,1,15), 0.04, 0.05, 100, 2, 0)This formula calculates the bond price using standard US 30/360 day count basis. The bond's coupon rate (4%) is lower than the market yield (5%), so the price will be below par value, indicating the bond is trading at a discount.
Government Bond Analysis
=PRICE(DATE(2024,3,1), DATE(2027,3,1), 0.035, 0.03, 100, 4, 1)This example uses quarterly coupon payments (frequency=4) and actual/actual day count basis (basis=1), which is common for government securities. The coupon rate exceeds the yield, resulting in a premium price above $100.
High-Yield Bond Comparison
=PRICE(DATE(2024,6,15), DATE(2028,6,15), 0.065, 0.072, 100, 2, 2)High-yield bonds typically have higher yields relative to coupon rates. The actual/360 day count basis (basis=2) is often used in corporate bond markets. The yield-coupon spread creates a discount to par value.
Key Takeaways
- PRICE calculates bond prices based on settlement date, maturity date, coupon rate, yield, and redemption value, returning the clean price without accrued interest.
- The formula requires six mandatory parameters and one optional basis parameter; all dates must be valid Excel date values and all rates must be expressed as decimals.
- Bond prices move inversely to yields: higher yields produce lower prices (discount), while lower yields produce higher prices (premium).
- Day count basis selection significantly affects results; verify the correct basis for your specific security type (US 30/360, actual/actual, etc.).
- PRICE assumes regular coupon periods and cannot handle irregular first/last periods; use alternative approaches for non-standard bond structures.
Pro Tips
Always verify your settlement and maturity dates are in the correct order (settlement before maturity). Use conditional formatting or data validation to flag invalid date combinations before formula calculation.
Impact : Prevents #NUM! errors and ensures formula results are meaningful and reliable for financial decision-making.
Express all percentage rates as decimals (0.05 for 5%, not 5). Create a helper column with =rate/100 if working with percentage values to avoid calculation errors.
Impact : Eliminates systematic errors in bond pricing that could lead to incorrect investment decisions or portfolio valuations.
Use named ranges for frequency (Annual, Semi-Annual, Quarterly, Monthly) and basis (US30/360, Actual/Actual, etc.) to make formulas self-documenting and reduce errors when copying formulas.
Impact : Improves formula readability, reduces maintenance errors, and makes spreadsheets more professional and auditable.
Create a sensitivity table comparing PRICE results across different yield scenarios (2-8% in 0.25% increments) to visualize the inverse relationship between yields and bond prices.
Impact : Provides visual understanding of interest rate risk and helps stakeholders understand how market conditions affect bond valuations.
Useful Combinations
Bond Price with Accrued Interest (Dirty Price)
=PRICE(settlement, maturity, rate, yld, redemption, frequency, basis) + ACCRINT(settlement, settlement, maturity, rate, redemption, frequency, basis)Combines PRICE (clean price) with ACCRINT to calculate the dirty price including accrued interest. This represents the actual amount a buyer pays, accounting for interest accumulated since the last coupon date.
Bond Price Sensitivity Analysis with Data Table
=PRICE(settlement, maturity, rate, $B$2, redemption, frequency, basis) with varying yld valuesUse PRICE in a Data Table to show how bond prices change with different yield scenarios. This creates a sensitivity analysis showing price volatility relative to interest rate changes, essential for risk management.
Bond Portfolio Valuation
=SUMPRODUCT(PRICE(settlement, maturity, rate, yld, redemption, frequency, basis) * quantity / 100)Combines PRICE with SUMPRODUCT to calculate total portfolio value across multiple bonds. Each bond's price is multiplied by quantity held, then summed for total portfolio valuation in a single formula.
Common Errors
Cause: This error occurs when settlement date is on or after the maturity date, when negative values are provided for rate, yld, or redemption parameters, or when frequency is not 1, 2, 4, or 12.
Solution: Verify that settlement < maturity using date validation. Ensure all percentage rates are positive decimals (0.05 not -0.05). Confirm frequency is one of the valid values: 1, 2, 4, or 12. Check that redemption value is positive and typically 100 for standard bonds.
Cause: This error appears when non-numeric values are provided for rate, yld, redemption, frequency, or basis parameters, or when dates are not recognized as valid date values by Excel.
Solution: Ensure all numeric parameters are formatted as numbers, not text. Use DATE() function or proper date serial numbers for settlement and maturity parameters. Verify that date cells are formatted as dates, not text. Check for leading/trailing spaces in parameter cells that might cause text recognition.
Cause: This error occurs when the formula references deleted cells or ranges, or when cell references are broken due to worksheet deletions or structural changes.
Solution: Verify all cell references in the formula still exist and contain valid data. Use absolute references ($A$1) for fixed parameters to prevent reference shifts. Rebuild the formula using direct cell references or re-enter the formula with current valid references. Check the Name Manager for any broken named ranges used in the formula.
Troubleshooting Checklist
- 1.Verify settlement date is earlier than maturity date using a formula like =IF(settlement<maturity, 'Valid', 'Invalid')
- 2.Confirm all percentage parameters (rate, yld) are expressed as decimals (0.05) not percentages (5), and check cell formatting
- 3.Validate frequency parameter equals 1, 2, 4, or 12 using =IF(OR(frequency=1,frequency=2,frequency=4,frequency=12), 'Valid', 'Invalid')
- 4.Check basis parameter is between 0-4 using =IF(AND(basis>=0, basis<=4), 'Valid', 'Invalid')
- 5.Confirm all date parameters are recognized as date values by Excel, not text strings, using =ISNUMBER(settlement) and =ISNUMBER(maturity)
- 6.Verify redemption value is positive and typically 100 for standard bonds; use =IF(redemption>0, 'Valid', 'Invalid')
Edge Cases
Settlement date equals coupon date
Behavior: PRICE calculates correctly, positioning the settlement exactly on a coupon payment date. The formula treats this as the start of a new coupon period with zero accrued interest.
Solution: No special handling needed; PRICE handles this scenario correctly by design.
This represents the cleanest pricing scenario with no accrued interest complications.
Coupon rate equals yield (4% coupon, 4% yield)
Behavior: PRICE returns exactly 100 (par value), regardless of the time to maturity. The bond is priced at par because coupon payments match market expectations.
This is mathematically correct; when coupon equals yield, the bond trades at par value.
Very short time to maturity (settlement one day before maturity)
Behavior: PRICE returns a value very close to the redemption value (typically 100), plus nearly one full period's accrued coupon interest depending on the day count basis used.
Solution: Verify date calculations and day count basis; consider using ACCRINT separately for precise accrued interest calculation in extreme scenarios.
This edge case is rare but important in bond trading near maturity dates.
Limitations
- •PRICE assumes regular coupon periods and cannot handle bonds with irregular first or last coupon periods, requiring alternative calculation methods for such securities.
- •The formula returns clean price only, excluding accrued interest; users must separately calculate and add accrued interest using ACCRINT for the dirty price that reflects actual settlement amounts.
- •PRICE cannot accommodate bonds with embedded options (callable, putable, or convertible features) that affect actual pricing; these require more complex option-adjusted spread models.
- •The formula assumes all coupon payments are certain and will be made; it cannot model credit risk, default probability, or credit spread changes that affect real-world bond valuations.
Alternatives
Compatibility
✓ Excel
Since 2007
=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])✓Google Sheets
=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])Google Sheets supports PRICE with identical syntax and parameters. Date handling may differ slightly; use DATE() function for consistency across platforms.
✓LibreOffice
=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])