PMT Function in SQL: Calculate Loan Payments by Period

SQL does not have a universally available built-in PMT function. To calculate a fixed loan payment, use the PMT-style formula in a query: for a positive periodic interest rate, payment = principal × rate ÷ (1 − (1 + rate)^{−number of payments}). The example below uses SQL Server and returns a positive payment due at the end of each period. First convert the annual interest rate to the rate for one payment period.

This approach calculates principal and interest for a fixed-rate loan with regular, equal payments. It is useful for estimates, reports, and payment comparisons, but it is not a substitute for the lender’s schedule: fees, rate conventions, rounding, and payment dates can change the actual amount owed.

What You’ll Need

A SQL database and permission to run a query. The worked example uses Microsoft SQL Server and T-SQL; other database products may require different syntax or a custom function.
The loan principal, annual interest rate, number of payments per year, and loan term. Enter a rate of 6% as 0.06, not 6.
A clear payment convention. This example assumes a fixed rate, monthly payments, no extra fees, no balloon payment, and payments made at the end of each period.

Step-by-Step

Confirm what you are calculating. A PMT-style result is the same fixed amount paid in each period, assuming the interest rate and payment timing stay constant. It is not the total cost of borrowing, an amortization schedule, or a calculation of a changing-rate loan. Here, the principal is $10,000, the annual nominal rate is 6%, and the term is three years with monthly payments. That means 36 payments and a monthly rate of 0.06 ÷ 12, or 0.005 (0.5%).
Set the inputs and calculate the period values. In SQL Server, declare variables for the annual rate, payment frequency, term, and principal. Multiply payment frequency by years to get the number of periods. Divide the annual rate by the payment frequency to get the rate per period. The decimal types keep the inputs suitable for financial calculations; the result is rounded for display at the end.
```
DECLARE @AnnualRate decimal(9,6) = 0.06;
DECLARE @PaymentsPerYear int = 12;
DECLARE @Years int = 3;
DECLARE @Principal decimal(18,2) = 10000.00;

DECLARE @Periods int = @PaymentsPerYear * @Years;
DECLARE @RatePerPeriod decimal(18,12) = @AnnualRate / @PaymentsPerYear;
```
These declarations assume the rate is a nominal annual rate divided evenly across the payment periods. If the contract defines an effective annual rate or uses daily interest, do not assume simple division matches its method.
Use the payment formula in a SELECT statement. For a positive rate and end-of-period payments, the formula is principal × periodic rate divided by one minus one plus the periodic rate raised to the negative number of periods. The negative exponent expresses the present value of the future payments. This version also handles a zero-interest loan separately, where each payment is simply the principal divided by the number of periods.
```
SELECT CAST(
    CASE
        WHEN @Periods <= 0 THEN NULL
        WHEN @RatePerPeriod = 0
            THEN @Principal / NULLIF(@Periods, 0)
        ELSE
            (@Principal * @RatePerPeriod)
            / (1 - POWER(1 + @RatePerPeriod, -@Periods))
    END
    AS decimal(18,2)
) AS PaymentPerPeriod;
```
For the sample inputs, the result is about $304.22 per month. The unrounded calculation is approximately $304.219, so multiplying the displayed amount by 36 gives $10,951.92. Multiplying the unrounded payment instead gives a slightly different total because the displayed payment has been rounded. The approximate interest over the term is about $951.92 when using the rounded payment amount.
Run and inspect the result. Execute the declarations and SELECT together in SQL Server Management Studio or another T-SQL client. Check that the output is one row with a PaymentPerPeriod value of 304.22. If your query editor accepts only a SELECT statement, use parameters supplied by your application or replace the variables with correctly typed values. Avoid putting a changing rate or principal directly into an application-built SQL string.
Check the payment frequency against the term. A three-year loan paid monthly has 36 periods; a three-year loan paid quarterly has 12. If the payment frequency is 12 but the periods variable is mistakenly set to 3, the query treats the term as three months, not three years. Derive the period count from the actual payment schedule rather than entering an unrelated number.
Decide how the result should be rounded. The example rounds the displayed payment to two decimal places. Some systems keep extra precision internally and round each actual payment to cents; some adjust the final payment to clear the remaining balance. If you need a full repayment schedule, calculate each period’s interest and balance and apply the lender’s rounding rules instead of treating a rounded PMT result as a complete schedule.
Adapt the query only after confirming your SQL dialect. SQL is implemented differently across database systems. SQL Server supports T-SQL variables and POWER, but the declaration syntax shown here is not portable to every database. Check your product’s documentation for its numeric types, parameter syntax, exponent function, and stored-function rules. If you need to reuse the calculation in many queries, create a tested database function or calculate it in the application rather than assuming a function named PMT exists.

Common Mistakes

Looking for a built-in function that is not there. Excel has a PMT worksheet function, but that does not mean a database accepts PMT(rate, periods, principal). SQL has no single standard PMT function available across products, and SQL Server does not provide a built-in T-SQL PMT function. A query that calls PMT may fail with an unknown-function error unless your database or organization has defined its own function.

Using the annual rate as the period rate. If payments are monthly, passing 0.06 directly into the formula charges 6% each month, not 6% per year. For a nominal annual rate and monthly payments, use 0.06 / 12. Also make sure a percentage entered as 6 is converted to 0.06 before calculation.

Confusing years with payment periods. The exponent uses the total number of payments, not the number of years. For monthly payments, use years × 12. For other payment intervals, use the corresponding count and ensure the rate is expressed for that same interval.

Getting unexpected signs or treating an estimate as a quote. This example returns a positive payment because it reports an amount owed. Excel’s PMT commonly returns a negative cash flow when the present value is entered as positive. Either convention can be useful, but label it clearly. The formula also omits fees, taxes, insurance, irregular dates, and contract-specific rounding, so it may not match a lender’s billed amount.

Tips

Keep rate, term, and payment frequency as separate inputs, and give the output a descriptive column alias such as PaymentPerPeriod. That makes it easier to audit a query and reduces the chance that someone will mistake an annual rate for a monthly one. If your data stores 6 rather than 0.06, convert it explicitly and document the unit.

Use decimal or other appropriate exact numeric types for stored money values, and round at the point your reporting rule requires. SQL Server’s POWER calculation and intermediate arithmetic can have precision and scale behavior that differs from the final displayed type. Test values near the expected range, and compare the result with an independent calculator or a known amortization example before using it in a report.

For a database table containing many loans, calculate each loan with its own rate, principal, and period count, rather than reusing one set of variables. Validate every row first: a missing rate, a nonpositive term, or a zero payment frequency should be handled explicitly. A SQL NULL result for invalid inputs is safer than silently presenting a misleading payment.

If payments are made at the beginning of each period, the payment differs from the end-of-period result because each payment reduces the balance earlier. For a standard positive rate, the beginning-of-period payment is the end-of-period payment divided by 1 plus the periodic rate. Confirm the contract’s timing before applying that adjustment; irregular first or final periods need more detailed date-based calculations.

Troubleshooting

Error saying PMT is not recognized: remove the PMT call and use the formula in this guide, or check whether your organization has installed a custom function and what its exact name and argument order are. Confirm the database product and version before adapting examples from Excel or another SQL dialect.

The query returns NULL: in the sample, a period count of zero or less intentionally returns NULL. Check that the payment frequency and term are present and positive. Also inspect whether input columns contain NULL values; arithmetic with NULL normally produces NULL. Decide whether to reject those rows, filter them, or return a clearly identified missing-value result.

The result is too high or too low: verify the rate’s units and period count first. Check that 6% is 0.06, the monthly rate is 0.005, and three years of monthly payments means 36 periods. Then check whether the stated rate is nominal or effective, whether payments occur at the beginning or end of periods, and whether a fee or balloon amount is part of the agreement.

The result errors or has poor precision: confirm that the exponent function accepts the numeric types in your SQL product and that the inputs are not text. Use appropriate decimal types for principal and rates, and avoid integer division when calculating a rate. If results are used in production, test zero interest, very small rates, long terms, and invalid inputs, and compare representative cases with the lender’s method.

Frequently Asked Questions

Does SQL have a built-in PMT function?
No universal SQL PMT function exists, and SQL Server does not include a built-in T-SQL PMT function. Some systems may offer a product-specific feature or a user-defined function, so check your database documentation.

What does the SQL PMT-style formula calculate?
It calculates the equal periodic payment needed to repay a fixed principal and interest over a set number of periods, assuming a fixed rate and consistent payment timing. It does not automatically include fees or other loan costs.

How do I calculate monthly payments from an annual rate?
For a nominal annual rate with monthly payments, divide the rate by 12 and multiply the number of years by 12. For example, 6% becomes 0.005 per month, and a three-year term becomes 36 payments. Check the lender’s rate convention before relying on that conversion.

Why does Excel PMT give a negative answer?
Excel uses cash-flow signs: money received and money paid are represented with opposite signs. The SQL example returns the payment as a positive amount owed. Negate the result if your reporting convention requires an outgoing payment to be negative.

Can I use this formula for an interest-free loan?
Yes. When the periodic rate is zero, payment equals principal divided by the number of payments. The example handles that case separately to avoid a zero denominator.

Will this match my lender’s payment exactly?
Not necessarily. The calculation assumes fixed periodic rates and regular payments, and rounds the displayed result to cents. Fees, day-count rules, payment dates, rate changes, and final-payment adjustments can alter the actual schedule. Confirm the terms and figures with the lender or a qualified financial professional.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.