Quick, clean, and to the point

Payment for annuity

Excel formula: Payment for annuity
Generic formula 

To solve for an annuity payment, you can use the PMT function. In the example shown, C9 contains this formula:


The PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. An annuity is a series of equal cash flows, spaced equally in time.

The goal in this example is to have 100,000 at the end of 10 years, with an interest rate of 5%. Payments are made annually, at the end of each year. The formula in cell C9 is:



  • rate - from cell C6, 5%.
  • nper - from cell C7, 25.
  • pv - from cell C4, 0.
  • fv - from cell C5, 100000.
  • type - 0, payment at end of period (regular annuity).

With this information, the PMT function returns -$7,950.46. The value is negative because it represents a cash outflow.

Annuity due

With an annuity due, payments are made at the beginning of the period, instead of the end. To calculate the payment for an annuity due, use 1 for the type argument. In the example shown, the formula in C11 is:


which returns -$7,571.86 as the payment amount. Notice the only difference in this formula is type = 1.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.