Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

IPMT, PPMT, EFFECT and NOMINAL Equivalents in Python

Split loan payments into interest and principal and convert between nominal and effective rates in Python, matching Excel: PMT, IPMT, PPMT and CUMIPMT/CUMPRINC from first principles, Excel's sign convention, payments at the start or end of the period, EFFECT and NOMINAL for compounding frequencies, a vectorised schedule in pandas, and a check against the spreadsheet functions.
Excel functions in pandas

Loan models in Excel lean on a small family of functions: PMT for the level payment, IPMT and PPMT to split a given payment into interest and principal, CUMIPMT and CUMPRINC to total those parts over a range of periods, and EFFECT and NOMINAL to convert between a quoted annual rate and the rate actually earned or paid after compounding. NumPy once shipped these as numpy.pmt and friends; they moved to the separate numpy-financial package, which is not always available or wanted in a production script. They are also short enough to write from first principles, which has the advantage of making Excel's conventions — negative signs for money paid out, the type argument for payments at the start of a period — explicit. This page implements each function, uses them for a loan schedule in pandas, and checks every value against the spreadsheet. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

Bash
pip install pandas openpyxl

The functions need only standard Python. The example is a 25,000 loan at 6.9% a year, repaid monthly over four years. LibreOffice (soffice) recalculates the same spreadsheet formulas for the comparison in Step 5. PMT, NPV and IRR are covered in more depth in PMT, NPV and IRR in Python.

Step 1 — PMT and Excel's sign convention

Everything else builds on the payment. Excel's functions treat money received as positive and money paid out as negative, so a loan received as pv=25000 produces a negative payment:

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

RATE, NPER, PV = 0.069 / 12, 48, 25_000
print("monthly payment:", round(pmt(RATE, NPER, PV), 2))
print("payment at start of month:", round(pmt(RATE, NPER, PV, when=1), 2))

The monthly rate is the annual rate divided by 12 and the number of periods is 48 months. The result, about −597.5, is negative because it is money the borrower pays out. when=1 corresponds to Excel's type=1 — payments at the start of each period, as with leases and rent — which makes each payment slightly smaller because interest has less time to accrue. Keeping Excel's signs in the Python functions means figures can be compared directly with an existing workbook; flip the sign only when presenting the numbers.

Step 2 — IPMT and PPMT: split a payment

The interest part of payment per is the rate times the balance outstanding at the start of that period; the principal part is the rest of the payment. Computing the balance with the future-value formula avoids looping:

Python
def fv(rate, nper, pmt_, pv, when=0):
    if rate == 0:
        return -(pv + pmt_ * nper)
    factor = (1 + rate) ** nper
    return -(pv * factor + pmt_ * (1 + rate * when) * (factor - 1) / rate)

def ipmt(rate, per, nper, pv, fv_=0.0, when=0):
    payment = pmt(rate, nper, pv, fv_, when)
    if when == 1 and per == 1:
        return 0.0
    balance = fv(rate, per - 1, payment, pv, when)          # balance after per-1 payments (Excel signs)
    interest = balance * rate
    return interest / (1 + rate) if when == 1 else interest

def ppmt(rate, per, nper, pv, fv_=0.0, when=0):
    return pmt(rate, nper, pv, fv_, when) - ipmt(rate, per, nper, pv, fv_, when)

for per in (1, 2, 24, 48):
    print(f"payment {per:2}: interest {ipmt(RATE, per, NPER, PV):9.2f}  principal {ppmt(RATE, per, NPER, PV):9.2f}")

In month 1 about 144 of the 597 payment is interest and 454 repays principal; by month 48 almost the whole payment is principal. Both parts carry Excel's negative sign. fv gives the balance after any number of payments in one formula, so IPMT for period 30 does not need to walk through 29 earlier periods. With payments at the start of the period, the first payment contains no interest at all — nothing has been borrowed for any time yet — and later interest figures are discounted by one period, which is how Excel defines type=1.

Each payment splits into interest and principal Every monthly payment of 597.50 is the same, but the split changes. In month 1, 143.75 is interest and 453.75 principal. In month 24, 79.79 is interest and 517.71 principal. In month 48 almost all of the payment is principal. A B 1 Month Payment 2 1 −597.50 3 24 −597.50 4 48 −597.50 Interest Principal −143.75 −453.75 −79.79 −517.71 −3.42 −594.08 payment split IPMT + PPMT = PMT How the loan functions build on each other PMT gives the level payment. FV gives the balance after any number of payments. IPMT is the balance before a period times the rate, and PPMT is the payment minus IPMT. CUMIPMT and CUMPRINC sum IPMT and PPMT over a range of periods. PMT level payment FV balance after k payments IPMT / PPMT balance × rate; remainder CUMIPMT / CUMPRINC sums over a range

Step 3 — CUMIPMT and CUMPRINC: totals over a range

CUMIPMT(rate, nper, pv, start, end, type) and CUMPRINC total the interest and principal over a range of periods — typically a year of a loan, for a tax statement or a budget:

Python
def cumipmt(rate, nper, pv, start, end, when=0):
    return sum(ipmt(rate, p, nper, pv, when=when) for p in range(start, end + 1))

def cumprinc(rate, nper, pv, start, end, when=0):
    return sum(ppmt(rate, p, nper, pv, when=when) for p in range(start, end + 1))

for year in range(1, 5):
    s, e = 12 * (year - 1) + 1, 12 * year
    print(f"year {year}: interest {cumipmt(RATE, NPER, PV, s, e):9.2f}  principal {cumprinc(RATE, NPER, PV, s, e):10.2f}")
print("principal over the whole loan:", round(cumprinc(RATE, NPER, PV, 1, NPER), 2))

Interest falls from about 1,549 in year 1 to about 261 in year 4, while principal rises, and the principal over all 48 periods totals exactly −25,000 — the loan repaid in full. Unlike IPMT and PPMT, Excel's CUMIPMT and CUMPRINC require the type argument and reject a zero or negative present value; the Python versions here are more forgiving, so add the same checks if the code must reject the same inputs.

Step 4 — EFFECT and NOMINAL: compounding

A rate quoted as "6.9% a year, compounded monthly" is a nominal rate; the rate actually charged over a year, the effective rate, is higher because interest is charged on interest. EFFECT and NOMINAL convert between the two:

Python
def effect(nominal_rate, npery):
    return (1 + nominal_rate / npery) ** npery - 1

def nominal(effect_rate, npery):
    return npery * ((1 + effect_rate) ** (1 / npery) - 1)

for n, label in ((1, "annually"), (4, "quarterly"), (12, "monthly"), (365, "daily")):
    print(f"6.9% compounded {label:9} → effective {effect(0.069, n):.4%}")
print("nominal monthly rate for 7.12% effective:", f"{nominal(0.0712, 12):.4%}")

Monthly compounding turns a nominal 6.9% into an effective rate of about 7.12%; daily compounding gives slightly more. NOMINAL reverses the conversion, which is what you need when a contract states an effective (or "annual percentage") rate and the model works in monthly periods. Excel truncates the npery argument to an integer; pass whole numbers to stay comparable.

The same 6.9% nominal rate, compounded differently A nominal annual rate of 6.9 percent gives an effective annual rate of 6.90 percent when compounded once a year, about 7.08 percent quarterly, about 7.12 percent monthly and about 7.14 percent daily. annually 6.90% quarterly 7.08% monthly 7.12% daily 7.14%

Step 5 — A pandas schedule, checked against the spreadsheet

Build the split as a DataFrame for every period, then write the equivalent formulas into a workbook, recalculate and compare:

Python
import subprocess
import pandas as pd
from openpyxl import Workbook, load_workbook

periods = range(1, NPER + 1)
sched = pd.DataFrame({"Period": list(periods),
                      "Interest": [ipmt(RATE, p, NPER, PV) for p in periods],
                      "Principal": [ppmt(RATE, p, NPER, PV) for p in periods]})
sched["Balance"] = PV + sched["Principal"].cumsum()
print(sched.round(2).iloc[[0, 1, 23, 47]].to_string(index=False))

wb = Workbook()
ws = wb.active
for i, p in enumerate(periods, start=1):
    ws.append([f"=IPMT(0.069/12,{p},48,25000)", f"=PPMT(0.069/12,{p},48,25000)", f"=IPMT(0.069/12,{p},48,25000,0,1)"])
ws["E1"], ws["E2"] = "=CUMIPMT(0.069/12,48,25000,13,24,0)", "=CUMPRINC(0.069/12,48,25000,13,24,0)"
ws["E3"], ws["E4"] = "=EFFECT(0.069,12)", "=NOMINAL(0.0712,12)"
wb.save("loan.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "loan.xlsx"], check=True, capture_output=True)
c = load_workbook("calc/loan.xlsx", data_only=True).active
diffs = [abs(c.cell(p, 1).value - ipmt(RATE, p, NPER, PV)) for p in periods]
diffs += [abs(c.cell(p, 2).value - ppmt(RATE, p, NPER, PV)) for p in periods]
diffs += [abs(c.cell(p, 3).value - ipmt(RATE, p, NPER, PV, when=1)) for p in periods]
diffs += [abs(c["E1"].value - cumipmt(RATE, NPER, PV, 13, 24)), abs(c["E2"].value - cumprinc(RATE, NPER, PV, 13, 24)),
          abs(c["E3"].value - effect(0.069, 12)), abs(c["E4"].value - nominal(0.0712, 12))]
print(f"{len(diffs)} values compared, largest difference: {max(diffs):.2e}")

The schedule shows the balance falling from 25,000 to zero — the running total of principal repayments added to the original loan. The comparison covers interest and principal for all 48 periods, interest with payments at the start of the period, the cumulative functions for year 2, and both rate conversions: every value agrees with the spreadsheet to within floating-point precision. The same functions drive a full amortisation report in Build a Loan Amortization Schedule in Excel with Python.

Common pitfalls

  • Annual rates with monthly periods. Divide the rate by 12 and multiply the term by 12.
  • Ignoring the sign convention. Payments out are negative; keep Excel's signs until presentation.
  • Payments at the start of the period. Pass when=1; the first payment has no interest.
  • Nominal and effective rates mixed up. Convert with EFFECT and NOMINAL before using a rate.
  • Assuming numpy.pmt exists. It moved to numpy-financial; or use the formulas above.

Conclusion

Excel's loan functions follow from two formulas — the level payment and the balance after a number of payments — and Python implementations of PMT, IPMT, PPMT, CUMIPMT and CUMPRINC take a few lines each, as do EFFECT and NOMINAL for compounding. Keep Excel's sign convention and type argument so results can be compared with existing workbooks, convert annual rates to the payment period, build schedules with pandas, and check the functions against the spreadsheet's own results on every period.

Frequently asked questions

What is the Python equivalent of Excel's IPMT and PPMT? Compute the payment with the PMT formula, the balance before the period with the future-value formula, and the interest as that balance times the rate; the principal is the payment minus the interest. numpy-financial also provides ipmt and ppmt.

Why are my IPMT results negative? Excel's convention treats money paid out as negative. A loan entered as a positive present value gives negative payments, interest and principal.

How do I convert a nominal rate to an effective rate in Python? Use (1 + nominal / n) ** n - 1, where n is the number of compounding periods per year. This is Excel's EFFECT function.

What does the type argument change? Type 1 means payments at the start of each period. Payments are slightly smaller, and the first payment contains no interest.

More on excel functions in pandas

More on excel functions in pandas →