How to Use Excel's PRICE Function in Pandas

Related Functions

YIELD DURATION PV

Excel's PRICE function calculates the price of a security based on its coupon, yield, and other parameters. Understanding security pricing is essential for investors, analysts, and finance professionals.

This page explains how to calculate security prices in Python using pandas and other finance-related libraries.

Implementing the Security Pricing function in Pandas#

Security pricing in Python can be tricky. Not only can the financial formulas be confusing and complicated, but any small mistake can lead to large and important outcome differences. Luckily we've implemented some copy-and-paste ready common use cases below.

Pricing a Securtiy#

The basic formula for security pricing is the present value of its future cash flows, which includes the present value of its future coupon payments and the present value of its face value.

In Excel, you might use the `PV` and `FV` functions in tandem with `PRICE`. In Python, the `numpy` library can be handy for this.

Here, we use `numpy's` `pv` function for the present value calculation:

import numpy as np

# Define the security characteristics
face_value = 100
coupon_rate = 0.05
years_to_maturity = 10
payment_frequency = 2
annual_yield_to_maturity = 0.04

# Calculate periodic coupon payment
coupon_payment = (face_value * coupon_rate) / payment_frequency

# Compute the price of the security
def calc_security_price(face_value, coupon_payment, annual_yield_to_maturity, years_to_maturity, payment_frequency):

    # Calculate periodic yield
    periodic_ytm = ytm / payment_frequency

    # Compute present value of future coupon payments
    coupons_pv = sum([coupon_payment / (1 + periodic_ytm)**(i) for i in range(1, int(years_to_maturity*payment_frequency) + 1)])

    # Compute present value of face value paid at maturity
    face_value_pv = face_value / (1 + periodic_ytm)**(int(years_to_maturity*payment_frequency))

    # Return total security price
    return coupons_pv + face_value_pv

# Calculate security price and store it in the dataframe
security_price = calc_security_price(
    face_value,
    coupon_payment,
    annual_yield_to_maturity,
    years_to_maturity,
    payment_frequency
)

print(security_price)

Copy!

Pricing a security with variable payments#

In some cases, the coupon payments may vary over time. In this case, the security price is the present value of the future cash flows, which includes the present value of its future coupon payments and the present value of its face value.

import numpy as np

# Define the security characteristics
face_value = 100
years_to_maturity = 10
payment_frequency = 2
annual_yield_to_maturity = 0.04

# Variable coupon payments over the security's lifetime
# Example: Starting with a 5% annual coupon for the first 5 years and 6%
# for the next 5 years (with semi-annual payments).
df = pd.DataFrame({'coupon_rates': [0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05,
                                0.06, 0.06, 0.06, 0.06, 0.06, 0.06, 0.06, 0.06, 0.06, 0.06]})

# Create a 'coupon_payments' column in the dataframe. One entry for each coupon payment received.
df['coupon_payments'] = face_value * df['coupon_rates'] / payment_frequency

# Compute the price of the security
def calc_security_price(face_value, coupon_payments, annual_yield_to_maturity, payment_frequency):

    # Calculate periodic yield
    periodic_ytm = annual_yield_to_maturity / payment_frequency

    # Compute present value of future coupon payments
    coupons_pv = sum([coupon / (1 + periodic_ytm)**(i) for i, coupon in enumerate(coupon_payments, 1)])

    # Compute present value of face value paid at maturity
    face_value_pv = face_value / (1 + periodic_ytm)**(len(coupon_payments))

    # Return total security price
    return coupons_pv + face_value_pv

# Calculate security price and store it in the dataframe
security_price = calc_security_price(
    face_value,
    df['coupon_payments'],
    annual_yield_to_maturity,
    payment_frequency
)

print(security_price)

Copy!

Zero-Coupon Bond Pricing#

A zero-coupon security doesn't make periodic interest payments. Instead, it's sold at a discount and pays the face value at maturity.

In Excel, you might determine the price by using `=(Face Value)/(1+Yield)^Years`. You can calculate this in Python using the following code:

import numpy as np

# Define the bond characteristics
face_value = 100
years_to_maturity = 10
annual_yield_to_maturity = 0.04

# Compute the price of the bond
def calc_security_price(face_value, annual_yield_to_maturity, years_to_maturity):

    # Compute present value of face value paid at maturity
    face_value_pv = face_value / (1 + annual_yield_to_maturity)**years_to_maturity

    # Return total bond price
    return face_value_pv

# Calculate bond price
security_price = calc_security_price(
    face_value,
    annual_yield_to_maturity,
    years_to_maturity
)

print(security_price)

Copy!

Common mistakes when using PRICE in Python#

As you can tell from the code snippets abvoe, calculating security prices in pandas and Python can be a bit complicated. Here are some common mistakes and how to address them.

Incorrect Interest Rate Period#

Often, there's confusion between annual and semi-annual rates, leading to incorrect security price calculations.

Just like in Excel, in Python, you need to make sure that you're using the interest rate the corresponds to the number of periods.

For example, the following code adjusts the annual rate to a semi-annual rate:

annual_rate = .5
periods_per_year = 2
period_rate = annual_rate / periods_per_year

Copy!

Ignoring Currency Exchange Rate#

If dealing with foreign bonds, the currency exchange rate plays a significant role in determining the security's price in the home currency.

In Excel, you might multiply the security's price by the current exchange rate. Similarly, in Python, ensure that the exchange rate is factored into the calculation:

Here's how you can account for the currency exchange rate in the security pricing:

bond_price_in_foreign_currency = 100
exchange_rate = 1.1
bond_price_in_home_currency = bond_price_in_foreign_currency * exchange_rate

Copy!

Understanding the Security Pricing Formula in Excel#

The PRICE function in Excel calculates the price of a security given its rate, periods, yield, redemption value, and basis.

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

PRICE Excel Syntax

Parameter	Description	Data Type
settlement	The security's settlement date.	date
maturity	The security's maturity date.	date
rate	The security's annual coupon rate.	number
yld	The security's annual yield.	number
redemption	The security's redemption value at maturity.	number
frequency	Number of coupon payments per year.	number
basis	(Optional) The day count basis to use.	number

Examples

Formula	Description	Result
=PRICE("1/1/2020", "1/1/2025", 0.05, 0.04, 100, 2)	Calculates the price of a security with a 5% coupon rate, 4% yield, and 100 redemption value, with semi-annual payments.	104.4912925

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito