How to Calculate an Annuity in Excel

Excel has a built-in function for each unknown in an annuity calculation: PMT finds the regular payment, PV finds the lump sum you need, FV finds a future balance, NPER finds how long it lasts, and RATE finds the interest rate. This guide walks through each one using a single running example: a $100,000 lump sum, a 5% annual rate, 20 years, and end-of-month payments.

What an Annuity Calculation Means in Excel

In Excel, an annuity calculation models a regular series of equal payments made at a constant interest rate. Microsoft’s financial functions are built on that fixed-rate, fixed-payment idea, so every example in this guide works under those conditions.

Every annuity problem has the same five moving parts: the payment, the present value (a lump sum today), the future value (a balance at the end), the number of periods, and the interest rate. If you know four of them, Excel can solve for the fifth. Here is the quick map:

• Payment unknown: use PMT.
• Lump sum unknown: use PV.
• Future balance unknown: use FV.
• Number of periods unknown: use NPER.
• Interest rate unknown: use RATE.

Set Up Your Inputs Before Writing Any Formula

Most annuity errors come from inputs, not formulas. Before you type a function, decide what each argument means.

The arguments you will see

• rate: the interest rate per period (not per year, unless payments are yearly).
• nper: the total number of payment periods.
• pmt: the payment made each period.
• pv: the present value, or lump sum today.
• fv: the future value you want after the last payment (optional, defaults to 0).
• type: 0 or omitted for end-of-period payments, 1 for start-of-period payments.

Converting annual figures to monthly

If payments are monthly and the 5% annual rate compounds monthly, enter the rate as 5%/12 and the number of periods as 20*12. That gives a monthly rate of about 0.4167% and 240 periods. If your plan or lender compounds on a different basis, confirm it first, because dividing by 12 is only correct for monthly compounding.

Keep inputs in labeled cells

Put each input in its own labeled cell so your formulas stay readable and easy to audit:

• B1: Annual rate (5%)
• B2: Years (20)
• B3: Lump sum (100000)

Method 1: Calculate the Regular Payment with PMT

Use PMT when you know the lump sum, the rate, and the number of periods, and you want to know the regular payment. The syntax is PMT(rate, nper, pv, [fv], [type]).

Worked example

With the inputs above (assuming monthly compounding and end-of-month payments):

=PMT(B1/12, B2*12, -B3)

This returns a payment of about $659.96 per month. That figure depends entirely on the stated inputs: $100,000, 5% annual rate, 20 years, and end-of-month payments. Note that the lump sum is entered as a negative number here (cash you hand over), so the result comes back as a positive payment you receive.

Why a result can appear negative

Excel’s financial functions use a cash-flow sign convention: money going out is negative and money coming in is positive. If you enter the lump sum as a positive number, PMT returns a negative payment. The number is the same; only the sign differs. Flip the sign on the lump sum or put a minus in front of the function if you prefer a positive display.

Method 2: Find the Lump Sum Needed with PV

Use PV when you want to know how much you need today to receive a set payment each month. The syntax is PV(rate, nper, pmt, [fv], [type]).

As a consistency check on the PMT example, enter the monthly payment of about $659.96 as a positive amount with the same monthly rate and 240 periods:

=PV(B1/12, B2*12, 659.96)

The result should be close to -$100,000, shown as a negative number because the lump sum is an outflow under the sign rule. This is simply the mirror image of the PMT result, which is a handy way to confirm your inputs are set up correctly.

To answer a real planning question, replace 659.96 with the monthly amount you want to receive, keeping the same rate and periods. The sign convention check: if the payment is positive (money you receive), PV is negative (money you must put in).

Method 3: Project a Future Balance with FV

Use FV when you make regular contributions at a fixed rate and want to know the balance at the end of the term. The syntax is FV(rate, nper, pmt, [pv], [type]).

Set up labeled cells for the monthly rate, the number of months, and the monthly contribution, then enter the contribution as a negative number because it is an outflow:

• E1: Monthly rate (annual rate/12, assuming monthly compounding)
• E2: Number of months
• E3: Monthly contribution

=FV(E1, E2, -E3)

The result is positive, meaning the balance you would hold at the end. If you enter the contribution as a positive number, the result will be negative; the magnitude is identical. This guide does not state a result for FV because it depends on the contribution amount you choose.

Method 4: Work Out How Long Payments Last with NPER

Use NPER when you know the rate, the payment, and the balance, and you want to know the number of periods. The syntax is NPER(rate, pmt, pv, [fv], [type]).

With labeled placeholder cells for the monthly rate, the monthly payment, and the lump sum:

=NPER(E1, E4, -E5)

The result is a number of months, because the rate and payment are monthly. Divide by 12 to convert to years. Be sure the payment is large enough to cover the interest each period; otherwise the balance never runs down and the formula will not give a sensible answer.

Method 5: Solve for the Interest Rate with RATE

Use RATE when you know the number of periods, the payment, and the lump sum, but not the interest rate. Its inputs are the number of periods, the payment, and the present value, with optional future value and type arguments. Confirm the exact argument order in Excel’s built-in function tooltip as you type.

With labeled placeholder cells:

=RATE(E2, E4, -E5)

The result is a rate per period. If your periods are months, the result is a monthly rate. A monthly result is commonly multiplied by 12 to express a nominal annual rate; confirm the compounding basis your plan or lender uses before treating that figure as comparable to a quoted rate.

Ordinary Annuity vs. Annuity Due (the Type Argument)

The type argument tells Excel when each payment happens. Type 0 (or omitted) means payments at the end of each period, which is called an ordinary annuity. Type 1 means payments at the start of each period, which is called an annuity due.

Feature	Ordinary annuity (type 0)	Annuity due (type 1)
Payment timing	End of each period	Start of each period
Excel input	Type omitted or 0	Type = 1
Effect on payment for the same lump sum	Higher payment (about $659.96 in the running example)	Lower payment (about $657.22, calculated from the same inputs, rounded; verify in your own worksheet)
Typical use	Payments made after each month is complete	Payments made at the beginning of each month

The direction is intuitive: when each payment arrives one period earlier, it has an extra period to earn interest, so a smaller payment is enough for the same lump sum. To test it yourself, add ,0,1 to your PMT formula so that the fv is 0 and type is 1.

The Negative Sign Explained and Common Mistakes

Excel treats cash you pay out as negative and cash you receive as positive. A negative result is not an error; it is Excel telling you the direction of the cash flow.

Checklist before you trust a result

• Mismatched units: if payments are monthly, both the rate and the number of periods must be monthly.
• Wrong sign: make sure the lump sum and the payments have opposite signs.
• Wrong type: confirm whether payments occur at the start or end of the period.
• Forgotten fv: if you want a balance left over at the end, enter it; otherwise leave it at 0.
• Mixing annual and monthly inputs: typing 5% with 20 periods is a classic error.

Quick sanity check

Multiply the payment by the number of periods and compare it with the lump sum. In the running example, $659.96 × 240 is about $158,390 in total payments against a $100,000 lump sum. The difference illustrates the interest earned over the term under fixed-rate assumptions; it is not a real-world payout.

What Excel Annuity Results Do Not Tell You

• Fixed rate and payments only: Microsoft describes these functions in terms of a constant rate and constant payments. If your rate or payments change over time, these formulas alone cannot model them directly.
• No taxes, reserves or fees: PMT results may not include taxes, reserves, or fees, so actual amounts can differ.
• Not a forecast: a spreadsheet result is not a forecast of any pension benefit or an insurer’s guaranteed payout. Confirm real figures with your plan or provider.

Frequently Asked Questions

Which function calculates annuity payments in Excel?

PMT calculates the regular payment from a lump sum, a rate, and a number of periods. PV, FV, NPER, and RATE solve for the other unknowns.

How do I enter monthly payments with an annual rate?

Divide the annual rate by 12 and multiply the years by 12, assuming monthly compounding. For example, use B1/12 for the rate and B2*12 for the periods.

Why is my result negative?

Excel uses a cash-flow sign convention in which outflows are negative and inflows are positive. Reverse the sign of an input, or place a minus sign in front of the function, to change the display.

What is the difference between beginning and end of month payments?

Type 0 (or omitted) assumes payments at the end of each period, and type 1 assumes payments at the start. For the same lump sum, start-of-period payments are slightly lower.

Can I use these formulas for a variable-rate annuity?

Not directly. The functions are described for constant rates and payments, so a variable-rate or variable-payment annuity needs a different setup, such as a period-by-period schedule.

References

Join our community