PMT with annuities

Using PMT with annuities enable us to calculate what payment we need to put into an investment account per period in order to reach a desired future value.

Annuities with PMT are payments into an investment account to grow to a desired future value over time.

Say we're starting a brand new investment account over the next 10 years that returns 3.5% yearly interest on our investments. Since the account is brand new, our present value (pv) would be 0, our rate would be 3.5%, and our nper would be 10.

Present value = $0
Interest rate = 3.5%
Periods (in years) = 10

As is, here's what our PMT function would look like:

=PMT(0.035, 10, 0)

However, we've forgotten the most important part: the goal of our end investment! For the sake of this example, let's assign that as $10,000.

Present value = $0
Interest rate = 3.5%
Periods (in years) = 10
Future value = $10,000

Now, we need to then assign the [fv] argument as 10000 like so:

=PMT(0.035, 10, 0, 10000)

If we throw in all this PMT function into Excel...

...we get an annual payment of $852.41.

What this means is that in order to accumulate a $10,000 investment from scratch at 3.5% interest over 10 years, we must put $852.41 every year into the investment account.

The [fv] argument allows us to set a desired future balance, typically used with investments/annuities.

PMT with [type]

The [type] argument determines if the payments you're making towards the loan or investment are occurring at the beginning or end of the period.

Oftentimes, problems with the PMT function will deal with payments occurring at the end of periods. That's why the default value for it is "0" (representing "end of period").

However, on the off-chance a problem requires you to make payments at the beginning of each period, you'll need to place a "1" for the [type] argument.

Let's take the investment account example from above, except this time assume that we'll be making payments towards the account at the beginning of each year instead of the end.

=PMT(0.035, 10, 0, 10000, 1)

When we code this in Excel...

...we get a yearly payment towards the investment account of $823.59.

The [type] function determines if you're making payments at the end ("0") or beginning ("1") of each month. The default is end, and any problem that wants beginning will explicitly tell you!