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: where:
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
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.
