FV, PV, RATE and NPER Equivalents in Python

Four spreadsheet functions answer most everyday finance questions. FV: how much will I have if I save 200 a month for ten years? PV: what lump sum is a stream of payments worth today? NPER: how many months to pay off a loan at a given instalment? RATE: what interest rate does a "pay 500 a month for four years on a 20,000 loan" offer actually imply? They share one equation and one sign convention, and once that is clear, each is a few lines of Python — useful when a report outgrows the spreadsheet but the numbers must still match it exactly. This page builds the four functions, checks them against the spreadsheet, and applies them to whole DataFrame columns. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install numpy pandas scipy openpyxl

LibreOffice (soffice) recalculates the reference formulas in Step 6. The numpy-financial package implements the same functions; writing them out shows exactly what they assume, and avoids a dependency for four short formulas.

Step 1 — The equation and the sign convention

All four functions solve one equation that balances the present value pv, a regular payment pmt over nper periods at rate, and a future value fv:

pv·(1+rate)^nper + pmt·(1 + rate·type)·((1+rate)^nper − 1)/rate + fv = 0

The equation equals zero because cash paid out and cash received have opposite signs. Saving 200 a month means pmt = -200 (money leaves your account) and the resulting balance is positive (money you get back). Borrowing 20,000 means pv = +20000 (received) and the instalments are negative. type is 0 when payments happen at the end of each period, 1 when they happen at the start. Getting the signs wrong is the most common reason a Python version "disagrees" with Excel.

Step 2 — FV and PV

import math

def fv(rate, nper, pmt, pv=0.0, type=0):
    if rate == 0:
        return -(pv + pmt * nper)
    growth = (1 + rate) ** nper
    return -(pv * growth + pmt * (1 + rate * type) * (growth - 1) / rate)

def pv(rate, nper, pmt, fv=0.0, type=0):
    if rate == 0:
        return -(fv + pmt * nper)
    growth = (1 + rate) ** nper
    return -(fv + pmt * (1 + rate * type) * (growth - 1) / rate) / growth

print(f"Save 200/month for 10 years at 5%, starting with 1,000: {fv(0.05/12, 120, -200, -1000):,.2f}")
print(f"  ...paying at the start of each month instead:        {fv(0.05/12, 120, -200, -1000, type=1):,.2f}")
print(f"Lump sum worth 450/month for 5 years at 6%:            {pv(0.06/12, 60, -450):,.2f}")
print(f"No interest, ten payments of 100:                       {fv(0, 10, -100):,.2f}")

The savings plan reaches 32,703.47; paying at the start of each month earns one extra month of interest on every deposit and gives 32,832.87. The payment stream of 450 a month for five years is worth 23,276.50 today at 6%. Monthly rates and periods are the annual figures divided and multiplied by 12 — the same convention Excel users follow. The rate == 0 branch matters: the general formula divides by the rate, and a zero-interest promotion would otherwise raise ZeroDivisionError.

Step 3 — NPER: how many periods

Solving the equation for nper gives a logarithm:

def nper(rate, pmt, pv, fv=0.0, type=0):
    if rate == 0:
        return -(fv + pv) / pmt
    z = pmt * (1 + rate * type) / rate
    ratio = (-fv + z) / (pv + z)
    if ratio <= 0:
        raise ValueError("payment never repays the balance (Excel returns #NUM!)")
    return math.log(ratio) / math.log(1 + rate)

months = nper(0.07/12, -500, 20000)
print(f"20,000 at 7% with 500/month: {months:.2f} months ({math.ceil(months)} payments)")
try:
    nper(0.07/12, -100, 20000)
except ValueError as exc:
    print("100/month:", exc)

The loan takes 45.68 months — 46 payments, the last one smaller. With only 100 a month, the payment does not even cover the interest (20,000 × 7% / 12 ≈ 117), the balance grows for ever, and the logarithm has no real answer; Excel shows #NUM!, and the function raises a clear error instead of returning nan.

Step 4 — RATE: the implied interest rate

RATE has no closed form; Excel searches for it. A bracketing root-finder on the same equation does the job reliably:

from scipy.optimize import brentq

def balance_equation(r, nper_, pmt, pv_, fv_, type_):
    if abs(r) < 1e-12:
        return pv_ + pmt * nper_ + fv_
    growth = (1 + r) ** nper_
    return pv_ * growth + pmt * (1 + r * type_) * (growth - 1) / r + fv_

def rate(nper_, pmt, pv_, fv_=0.0, type_=0, low=-0.99, high=1.0):
    return brentq(balance_equation, low, high, args=(nper_, pmt, pv_, fv_, type_), xtol=1e-14)

monthly = rate(48, -500, 20000)
print(f"20,000 repaid at 500/month for 48 months: {monthly:.6%} per month, {monthly * 12:.4%} per year")
print(f"  ...with payments in advance (type=1):    {rate(48, -500, 20000, type_=1) * 12:.4%} per year")

The offer implies 0.770% a month, 9.24% a year (nominal, monthly compounding); if the first instalment is due on signing, the implied rate rises to 9.66%, because the borrower effectively receives less money for the same payments. The bracket from −99% to +100% per period covers any realistic deal. Comparing offers by their implied rate rather than their headline monthly payment is a common use — see PMT, NPV and IRR in Python for the payment side.

Step 5 — Whole columns at once

Reports apply these functions to a table of accounts or offers. NumPy versions of FV and PV vectorise directly; np.where handles the zero-rate rows:

import numpy as np
import pandas as pd

plans = pd.DataFrame({
    "Plan": ["Basic", "Plus", "Promo", "Long"],
    "AnnualRate": [0.03, 0.05, 0.0, 0.045],
    "Years": [5, 10, 2, 25],
    "Monthly": [100, 200, 300, 150],
})
r = plans["AnnualRate"].to_numpy() / 12
n = plans["Years"].to_numpy() * 12
p = -plans["Monthly"].to_numpy()
with np.errstate(divide="ignore", invalid="ignore"):
    growth = (1 + r) ** n
    fv_vec = np.where(r == 0, -(p * n), -(p * (growth - 1) / np.where(r == 0, 1, r)))
plans["FutureValue"] = fv_vec.round(2)
plans["Deposited"] = plans["Monthly"] * n
plans["Interest"] = plans["FutureValue"] - plans["Deposited"]
print(plans.to_string(index=False))

np.errstate silences the division warning for the zero-rate row, whose result comes from the other branch of np.where. The interest column makes the effect of time obvious: the 25-year plan earns 37,950 in interest on 45,000 of deposits, while the 5-year plan earns under 500. For RATE and NPER on a column, apply the scalar functions row by row with a list comprehension — they are cheap, and keeping the error handling per row is worth more than vectorising.

Step 6 — Check against the spreadsheet

import subprocess
from openpyxl import Workbook, load_workbook

cases = {
    "=FV(0.05/12,120,-200,-1000,0)": fv(0.05/12, 120, -200, -1000),
    "=FV(0.05/12,120,-200,-1000,1)": fv(0.05/12, 120, -200, -1000, 1),
    "=PV(0.06/12,60,-450,5000,0)": pv(0.06/12, 60, -450, 5000),
    "=NPER(0.07/12,-500,20000)": nper(0.07/12, -500, 20000),
    "=RATE(48,-500,20000)": rate(48, -500, 20000),
}
wb = Workbook()
for i, formula in enumerate(cases, start=1):
    wb.active[f"A{i}"] = formula
wb.save("tvm.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "tvm.xlsx"],
               check=True, capture_output=True)
ws = load_workbook("recalc/tvm.xlsx", data_only=True).active
for i, (formula, mine) in enumerate(cases.items(), start=1):
    print(f"{formula:32} sheet {ws[f'A{i}'].value:>14.6f}  python {mine:>14.6f}")

Every pair agrees to six decimal places, including the PV with a non-zero future value (a balloon payment of 5,000 at the end). Keeping this comparison as a test means a future edit to the functions cannot drift away from the spreadsheet unnoticed.

Common pitfalls

• Signs. Payments out are negative, money in is positive; mixing them flips or breaks results.
• Annual versus monthly. Divide annual rates by 12 and multiply years by 12 for monthly payments — and convert RATE's monthly answer back.
• Zero rates. The general formulas divide by the rate; handle rate == 0 separately.
• Impossible schedules. A payment smaller than the interest has no NPER; raise instead of returning nan.
• Nominal versus effective rates. RATE × 12 is the nominal annual rate; the effective rate is (1 + rate)**12 - 1.

Conclusion

FV, PV, NPER and RATE are four views of one equation linking a present value, a regular payment, a number of periods, a rate and a future value, with money out negative and money in positive. Write FV and PV directly, NPER with a logarithm, and RATE with brentq; handle zero rates, the start-of-period type, and schedules that never repay; vectorise with NumPy for whole columns; and confirm against the spreadsheet's own formulas with a LibreOffice recalculation.

Frequently asked questions

How do I calculate Excel's FV in Python? Use fv = -(pv*(1+rate)nper + pmt(1+ratetype)((1+rate)**nper - 1)/rate), or -(pv + pmtnper) when the rate is zero. Pass payments as negative numbers, as Excel does.

Why does my Python NPER or RATE give a different answer from Excel? Usually the signs or periods differ. Excel expects money paid out as negative and money received as positive, and rates and periods in the same unit, for example a monthly rate with a number of months.

How do I calculate RATE in Python without numpy-financial? Solve the time-value equation for the rate numerically, for example with scipy.optimize.brentq between -0.99 and 1.0 per period, then multiply a monthly result by 12 for the nominal annual rate.

What does the type argument do in FV and PV? type=0 means payments at the end of each period and type=1 at the start. Payments at the start earn or cost one extra period of interest, so the results are slightly larger.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in pandas and Python.
• XIRR and XNPV Equivalents in Python — cash flows on irregular dates.
• Build a Loan Amortization Schedule in Excel with Python — the period-by-period view of a loan.
• Goal Seek in Python Instead of Excel — the general technique behind RATE.