RATE in Excel: Solve a Periodic Rate and Check the Cash-Flow Residual

=RATE(nper, pmt, pv, [fv], [type], [guess])

nper is the number of periods, pmt the constant periodic payment, and pv the present value. fv defaults to zero. type is zero or omitted for end payments and one for beginning payments. guess is an initial periodic-rate estimate, defaulting to 10%.

The reference labels pmt required but permits it to be omitted when fv is supplied. These examples use an explicit zero for pmt when no periodic payments occur. Keep cash-flow signs consistent: an initial receipt is positive and outgoing payments negative in the loan illustration.

RATE solves the shared annuity balance equation for rate. Microsoft says it uses iteration and can have zero or more solutions. A returned number is a candidate solution to the specified cash flows, not proof that an underlying contract has that all-inclusive cost.

Put 48 in B2, −200 in B3, 8000 in B4 and 1% in B5. Enter =RATE(B2,B3,B4,0,0,B5) in B7.

The independent root is approximately 0.007701472488202, or 0.7701472488% per period. Since the payments are monthly, this is a monthly rate. Microsoft’s source displays that monthly result as 1% because of coarse formatting; it should not be retyped as an exact 1%.

The nominal annualized rate =B7*12 is independently 9.2417669858%. The effective annual compound rate =(1+B7)^12-1 is 9.6434564589%. These answer different questions and should have different labels.

With the returned rate in B7, calculate =FV(B7,B2,B3,B4,0). Its mathematical target is 0, the requested terminal value. A direct balance recurrence also starts with 8000, grows it by 1+rate and applies the negative 200 payment for 48 periods.

Use the full stored rate. Replacing it with the displayed 1% creates different cash flows and a nonzero ending balance. A small numerical residual is different from evidence of an exact contractual match; choose a tolerance appropriate to the model and its precision.

A second independent check discounts all 48 outgoing payments using the solved rate and compares their magnitude with 8000. This tests the same equation from the present-value perspective.

=RATE(8,0,-1000,1100,0,1%) has independent mathematical target 1.1985024140% per period, because (1100/1000)^(1/8)-1 gives the same value.

Here no periodic payments occur, so type does not affect the equation. The eight periods could be years or another stated interval; the function itself does not attach a calendar unit. An initial outflow and later receipt have opposite signs.

For a zero-rate control, =RATE(10,-100,1000,0,0,1%) has mathematical root 0. This verifies the balance equation, not a guarantee that every initial guess or native implementation reaches the same root.

Microsoft documents a #NUM! result if successive RATE estimates do not converge within 0.0000001 after 20 iterations. It gives a default guess of 10% and suggests trying other guesses when convergence fails. Those are source statements, not a measured iteration trace from this review.

The guess must use the same periodic units as the returned rate. A monthly model should not automatically use an annual guess. Try plausible alternative guesses and verify each candidate with the forward residual; agreement of one displayed rate alone does not establish uniqueness.

Inspect signs, timing and period units before treating a failure as merely a bad guess. Cash flows that cannot balance under the desired assumptions may have no appropriate solution. The dedicated source does not enumerate every invalid-input error or provide a full domain proof.

RATE assumes a constant periodic payment and regular spacing. Irregular dates, changing payments and fees require an explicitly specified cash-flow model. The independent root calculation used here is not Excel’s native iterative algorithm, and no convergence guarantees or extreme-domain behavior are inferred from it.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft RATE reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.