What is the missing financial value?

How the financial calculator works

Five values describe a stream of level payments: N (the number of payment periods), I/Y (the nominal yearly interest rate in percent), PV (the amount at the start), PMT (the payment each period) and FV (the amount after the last period). Two more say how often things happen: P/Y, the payments a year, and C/Y, the times a year interest compounds. Compute picks the one of the five to work out, like the CPT key of a financial calculator; the other four are typed. An empty PV, PMT or FV counts as 0; N and I/Y must be typed when they are not the value computed.

Sign rule. Money you receive is positive and money you pay is negative. A loan you take out has PV > 0 and PMT < 0; a savings plan has PV ≤ 0, PMT ≤ 0 and FV > 0.

Rate per period. I/Y is a nominal yearly rate compounded C/Y times a year. The rate per payment period, as a decimal, is

i = (1 + I/Y ÷ (100 × C/Y))^(C/Y ÷ P/Y) − 1

When C/Y = P/Y this is I/Y ÷ (100 × P/Y).

The equation. With t = 0 for payments at the end of each period (END) and t = 1 for payments at the start (BGN):

PV × (1 + i)^N + PMT × (1 + i × t) × ((1 + i)^N − 1) ÷ i + FV = 0

When i = 0 the equation is PV + PMT × N + FV = 0. N does not have to be a whole number.

Computing each value. With g = (1 + i)^N and a = (1 + i × t) × (g − 1) ÷ i (a = N when i = 0):

• FV = −(PV × g + PMT × a)
• PV = −(FV + PMT × a) ÷ g
• PMT = −(PV × g + FV) ÷ a
• N = ln(g) ÷ ln(1 + i), where g = (PMT × (1 + i × t) − FV × i) ÷ (PMT × (1 + i × t) + PV × i). At i = 0, N = −(PV + FV) ÷ PMT.
• I/Y has no formula. The calculator looks for every yearly rate above −100% and at most 1,000% where the left side of the equation changes sign, and refines each one by bisection. It checks the rate every 0.01% from −20% to 50%, every 0.1% from −99.9% to −20% and from 50% to 1,000%, and at −99.95%, −99.99%, −99.999%, −99.9999%, −99.99999% and −99.999999%, so two rates closer together than those steps can be missed. With one sign change in the cash flows there is one rate; with two there can be two, and both are shown.

Other results.

• Rate per period is 100 × i.
• Effective yearly rate is 100 × ((1 + i)^(P/Y) − 1), the rate over a whole year with compounding.
• Total of payments is N × PMT, with the payment's sign.
• Net interest is PV + N × PMT + FV: positive for interest you earn, negative for interest you pay.

Precision and overflow. The rate search reads the sign of the equation from the form above when (1 + i)^N is at most 1, and from the same equation divided by (1 + i)^N when it is above 1, so no step overflows; a rate at which (1 + i)^N is not a finite number above 0 in 64-bit floating point is dropped. With PV, PMT and FV all 0 (or empty) the equation holds at every rate, so there is no rate. The other computed values use the same divided equation when (1 + i)^N is above 1, so an answer inside the limits never fails on a larger number on the way. The total of payments and the net interest are exact decimal arithmetic on the typed amounts and the computed value, each read as the decimal it prints as, rounded once at the end. The computed value, the rates and the powers are 64-bit floating point, so a value that is exactly half a cent in decimals can land just below it and show rounded down.

Limits. N is above 0 and at most 12,000. I/Y is above −100% and at most 1,000%. PV, PMT and FV are between −1 trillion and 1 trillion dollars. P/Y and C/Y are whole numbers from 1 to 365. The same limits hold for the computed value: a computed N of 0 or less or over 12,000, or a computed amount beyond ±$1 trillion, is no answer. There is also no answer when (1 + i)^N is not a finite number above 0 in 64-bit floating point (larger than about 1.8 × 10^308, or so small it rounds to 0), when N is computed at 0% with no payment, when the logarithm for N has no real value (g ≤ 0 or g = 1), and when no rate fits.

Display. Money is shown to the cent, rounded half up from the value worked out. N shows at most 2 decimals, I/Y and the effective rate at most 4, the rate per period at most 6. The unrounded values are kept, and a shown answer typed back into its box gives back the other values, to the rounding shown.

Assumptions

• The interest rate is the same in every period and every payment is the same size.
• No fees or taxes are included.
• This is an estimate for planning, not financial advice.

Worked examples by hand

Mortgage payment (compute PMT). N = 360, I/Y = 6, PV = 200,000, FV = 0, P/Y = C/Y = 12, END. i = 0.06 ÷ 12 = 0.005. g = 1.005^360 = 6.022575. a = (g − 1) ÷ 0.005 = 1,004.515. PMT = −(200,000 × 6.022575) ÷ 1,004.515 = −$1,199.10.

Excel's PMT example (compute PMT). N = 10, I/Y = 8, PV = 10,000, FV = 0, monthly, END. i = 0.08 ÷ 12 = 0.0066667; g = 1.0066667^10 = 1.068703; a = 10.305396. PMT = −(10,000 × 1.068703) ÷ 10.305461 = −$1,037.03. The total of payments is 10 × −1,037.03 = −$10,370.32, so the net interest is 10,000 − 10,370.32 = −$370.32.

Excel's FV example (compute FV). N = 10, I/Y = 6, PV = −500, PMT = −200, monthly, BGN. i = 0.005; g = 1.005^10 = 1.051140; a = 1.005 × 0.051140 ÷ 0.005 = 10.279167. FV = −(−500 × 1.051140 − 200 × 10.279167) = $2,581.40.

Excel's NPER example (compute N). I/Y = 12, PV = −1,000, PMT = −100, FV = 10,000, monthly, BGN. i = 0.01, so PMT × (1 + i) = −101. g = (−101 − 10,000 × 0.01) ÷ (−101 − 1,000 × 0.01) = −201 ÷ −111 = 1.810811. N = ln 1.810811 ÷ ln 1.01 = 59.67.

Excel's RATE example (compute I/Y). N = 48, PV = 8,000, PMT = −200, FV = 0, monthly, END. The monthly rate that balances 8,000 = 200 × (1 − (1 + i)^−48) ÷ i is i = 0.770147%, so I/Y = 12 × 0.770147% = 9.2418%.

Semi-annual compounding, monthly payments (compute PMT). N = 300, I/Y = 5, PV = 300,000, FV = 0, P/Y = 12, C/Y = 2, END. i = 1.025^(2 ÷ 12) − 1 = 0.412392%. g = 1.00412392^300 = 3.437109; a = (g − 1) ÷ 0.00412392 = 590.9696. PMT = −(300,000 × 3.437109) ÷ 590.9723 = −$1,744.81. The effective yearly rate is 1.025² − 1 = 5.0625%.