PMT

What it does

PMT(rate, nper, pv, [fv], [type])

PMT(rate, nper, pv, [fv], [type]) returns a fixed payment at a constant rate over equal periods. fv defaults to zero. Type zero (the default) pays at period end; type one pays at the beginning. Use a rate per payment period.

Try it

=PMT(0,4,1000) returns -250: four equal outflows repay a positive principal. =PMT(0.25,1,1000,0,0) is about -1250, repaying the principal and one period’s interest.

Quirks

Cash inflows and outflows have opposite signs. A monthly payment needs a monthly rate and a count of months. Taxes and fees are outside this formula. Exactly zero rate has a separate linear branch; a merely small rate must retain the discount computation. Zero periods and invalid growth domains need error handling.

How to implement

The following is the current type-zero positive-rate discount route, with the recovered logarithm series used on -1/3<=rate<=1/2. The series reproduces 1,181 of 1,184 inferred logarithm values in the note; it still has exceptions inside that range. Its negative-rate and outer-domain limitations are stated below. Let D(op) mean ordinary binary64 round-to-nearest/even, and R(op) the stored 64-bit-significand operation.

1. Reject zero periods with #NUM!. At exactly r=0, return -(pv+fv)/n. Otherwise reject a growth base 1+r<=0.
2. Form s=D(r/D(2+r)), s2=D(s*s). Start T=D(1/23). For k=10 down to 1, assign T=D(D(T*s2)+D(1/(2*k+1))). Form a=D(2*s) and L=D(a+D(a*D(s2*T))). This is a twelve-term atanh expansion, not a correctly rounded log1p call.
3. Store t=-D(n*L). Obtain the measured public exponential u=EXP(t). If u==1, let em=t. Otherwise set d=R(u-1); for abs(t)<1, use em=R(R(d*t)/LN(u)), where LN is the measured public stored result; otherwise use em=d. Reject zero or nonfinite em with #NUM! before division. This Kahan correction avoids cancellation in exp(t)-1.
4. On the positive-rate, positive-period discount arm (t<0), form b=R(pv+fv), q=R(R(b/em)+fv), and return R(q*r) for type zero. Each addition and the division is a separate store. With fv=0 this reduces to quotient first, rate last.

Worked zero-future-value case, r=0.25,n=1,pv=1000: the stated reconstruction gives the following binary64 values. The stored correction moves em one representable step from the direct subtraction.

With the same rate, period and principal but fv=250, the first stored balance is 1250. Dividing by em and then adding 250 gives q=-6000.000000000001; the final payment is -1500.0000000000002. An algebraic one-period calculation rounds differently. These are worked evaluations of the reconstruction, not additional Excel captures.

The nonzero-future-value discount fold is supported by 192 paired calls in the current source; 60 and 99 separately selected readings distinguish its two additions. This later result supersedes that part of the older note's open list. The common loan PMT(0.05/12,360,200000) has recorded result -1073.6432460242781 in FN-PMT-001; replaying the stated series and stored quotient also gives that value.

The later R31 source also isolates the negative-rate type-zero balance fold. For a valid negative rate and finite nonzero em, use the same b=R(pv+fv), q=R(R(b/em)+fv), payment=R(q*r) stores. Its source records 192 paired future-value calls for that rate sign, 84 and 114 ties for the two additions, and 32 independently measured final-product ties. This closes that composition stage; it does not make the logarithm or every route exact.

Worked negative-rate composition: r=-0.25,n=1,pv=1000,fv=250. The series gives L=-0.2876820724517809, t is positive and stored EXP is 1.3333333333333333. The correction changes the direct difference 0.33333333333333326 to em 0.3333333333333333. The stored balance is 1250, q is 4000 and payment is -1000. These are reconstruction values, not new Excel readings. The interactive calculator is pinned separately and can predate this later source update.

The positive-rate code separately handles opposite equal balances as interest-only. Negative-rate calls now retain the common stored fold instead. Rates outside the series’ chosen convergence range, its in-range exceptions and remaining last-bit cases are not solved by this recipe. The note retains an in-range exception at rate 0.419; a global reduction boundary has not been identified. The series range is an implementation choice, not a measured global Excel boundary. The current type-one coefficient has its own measured stores; it must not be folded into the type-zero recipe by algebra alone. The old annuity write-up’s initial “blocked” section is superseded by its update and later code.

Rust code

3 pmt_fn "PMT" (3, 5) [V "rate", V "nper", V "pv", V "fv", V "type"] |s, a, sv| {
    let t = timing(s, a, 4)?;
    tv_out(s, tv::pmt(num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?, opt(s, a, 3, 0.0)?, t))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Retained controls from the indexed captures:

These are earlier recorded answers, not new Excel runs or an additional cross-build comparison. The function JSON retains each source locator, exact argument bits where available, and input-route status. Call notation does not imply that captured binary64 values were entered as decimal formula literals.

Alternatives

IPMT and PPMT split interest and principal; PV and FV solve for a balance; RATE solves for the periodic rate.

Named variants

No named variants are recorded yet.