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


Generic 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 Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.