How to Use Excel's NPV Function in Pandas

NPV is a core financial metric used in capital budgeting to assess the profitability of an investment or project. It's the present value of cash inflows minus the present value of cash outflows over a period of time.

This page guides you through using Python and pandas to calculate the NPV.

Implementing the Net Present Value function in Pandas#

In Python, using pandas, the calculation of NPV can be done in various ways, depending on the nature of your cash flows and discount rates. Here are some common implementations:

Calculate NPV with consistent cashflows#

To calculate NPV with consistent cashflows, use a single discount rate and a series of regular cash flows.

In Excel, you would use the NPV function with consistent cash flow values. For example: =NPV(0.1, 100, 100, 100).

In pandas, this can be achieved using a simple Python function that multiplies each cash flow with the corresponding discount factor.

discount_rate = 0.1
cash_flows = [100, 100, 100]
npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(cash_flows, 1))

Copy!

Calculate NPV with variable cashflows#

Calculating NPV with variable cashflows involves using a series of different cash flow amounts.

In Excel, this would be similar to applying the NPV function with different cash flow values: =NPV(0.1, 100, 150, 200).

In pandas, the NPV calculation can be done by iterating over the cash flows and applying the discount rate to each.

discount_rate = 0.1
cash_flows = [100, 150, 200]
npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(cash_flows, 1))

Copy!

Calculate NPV with variable cashflows and variable discount rates#

This method involves calculating NPV with different cash flows and different discount rates for each period.

In Excel, this is more complex but can be achieved using a series of calculations.

In pandas, you can use a loop to apply each discount rate to the corresponding cash flow.

discount_rates = [0.1, 0.15, 0.2]
cash_flows = [100, 150, 200]
npv = sum(cf / (1 + dr) ** i for i, (cf, dr) in enumerate(zip(cash_flows, discount_rates), 1))

Copy!

Calculate NPV using a pandas Dataframe#

If you have a pandas DataFrame with a single column of cash flows, you can use that series to calculate NPV.

# Create dataframe with cash flows
df = pd.DataFrame({'cash_flows': [100, 100, 100]})

# Create list from cash_flows column
cash_flows = df['cash_flows'].tolist()

# Calculate NPV
discount_rate = 0.1
npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(cash_flows, 1))

Copy!

Calculate NPV for a Series with variable discount rates#

In pandas, you can calculate NPV for a Series with variable discount rates by applying the NPV function to each value.

This is similar to using the NPV function in Excel with a series of cash flows.

In pandas, you can use the apply method to apply the NPV function to each value.

discount_rates = [0.1, 0.15, 0.2]
cash_flows = [100, 150, 200]
s = pd.Series(cash_flows)
npv = s.apply(lambda cf: np.npv(discount_rate, cf))

Copy!

Calculate NPV for a DataFrame with variable discount rates#

In pandas, you can calculate NPV for a DataFrame with variable discount rates by applying the NPV function to each row.

This is similar to using the NPV function in Excel with a series of cash flows.

In pandas, you can use the apply method to apply the NPV function to each row.

discount_rates = [0.1, 0.15, 0.2]
cash_flows = [100, 150, 200]
df = pd.DataFrame({'cash_flows': cash_flows})

Copy!

Calculating Inflation-adjusted NPV#

Adjusting NPV calculations for inflation involves modifying the cash flows to reflect their present value.

Just like in Excel, we can adjust each cash flow amoutn for inflation before calculating the NPV.

inflation_rate = 0.03
real_cash_flows = [cf / (1 + inflation_rate) ** i for i, cf in enumerate(cash_flows, 1)]
npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(real_cash_flows, 1))

Copy!

Calculating NPV for Loan Amortization#

Calculating the NPV for loan amortization involves considering periodic loan repayments and interest rates.

In Excel, this would use a series of payment amounts over time, adjusted for the loan's interest rate.

In pandas, you can simulate the loan repayments as a series of cash flows and calculate the NPV accordingly.

loan_payments = [-1000, 100, 100, 100, 100, 100]
npv = sum(payment / (1 + discount_rate) ** i for i, payment in enumerate(loan_payments, 1))

Copy!

XNPV function to calculate the Net Present Value with Payment Schedule#

In Excel, the XNPV function can be used to calculate the NPV with a payment schedule.

In pandas, you can use the XNPV function from the XIRR package to calculate the NPV with a payment schedule.

from datetime import datetime

def xnpv(rate, cashflows, dates):
    min_date = min(dates)
    xnpv_value = sum([cf / (1 + rate) ** ((date - min_date).days / 365) for cf, date in zip(cashflows, dates)])

    return xnpv_value

# Example Usage
rate = 0.1  # 10% discount rate
cashflows = [-1000, 300, 420, 680]  # Cash flows
dates = pd.to_datetime(['2023/1/1', '2023/6/1', '2024/1/1', '2024/6/1'])  # Corresponding dates

xnpv_value = xnpv(rate, cashflows, dates)

Copy!

Common mistakes when using NPV in Python#

While implementing NPV calculations in pandas, certain common pitfalls can lead to inaccurate results. Here are a couple of them and how to avoid these mistakes.

Confusing NPV with PV#

It's important to distinguish between NPV and PV. NPV considers both the inflows and outflows of cash, while PV only considers the present value of future cash inflows.

In Excel, PV is calculated without considering any initial investments, whereas NPV includes these.

Ensure that you're using the correct formula in pandas to reflect whether you're calculating NPV or just PV.

Incorrect Discount Rate Format#

Using the incorrect format for the discount rate, such as a percentage instead of a decimal, can lead to wrong NPV calculations.

In Excel, the discount rate should be entered as a decimal (e.g., 10% as 0.1). The same applies to pandas calculations.

Always convert your discount rate to a decimal format before using it in the NPV formula.

# Correct discount rate format
discount_rate = 10 / 100
cash_flows = [100, 150, 200]
npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(cash_flows, 1))

Copy!

Understanding the Net Present Value Formula in Excel#

In Excel, the NPV function calculates the net present value of an investment based on a discount rate and a series of future cash flows.

=NPV(rate, value1, [value2], ...)

NPV Excel Syntax

Examples