Payment Function =PMT

Say, for example, you want to save money. You would like to go on a vacation to Disney World or save for a car. You aren’t sure your budget allows for this but you want to know if you can swing it. Why not try anyway?

Excel has a neat financial formula called the payment function. It takes a fixed interest rate, multiplies it with a specific length of time, and then assigns either a future or present value. Let’s take a look.

Say we have an interest rate of 6%, a term (short because we want to go to Disney World right NOW) of 10 months, and a future value of $10,000 because that is the amount we need for it. Keep in mind, this is a rough estimate. Your formula should read something like this:

=PMT(Rate, nper, present value, future value)

or

=PMT(6%/12, 10, 0, 10000)

=$977.71

We need to save $977.71 every month to go to Disney World.

In this case we want to have $10,000 in 10 months, our present value is zero since we have not saved anything. Our future value is what we want when the term is over, we want to make sure we have this amount available at the end of the year. However, be very careful because depending on where the zero is, it can affect the outcome of your formula. Take a look at the formula below, it is the same as the previous one but the zeros are switched around. That is a difference of $50, not huge, but if you’re dealing with a company worth billions of dollars, that could be a really big number.

=PMT(6%/12, 10, 10000,0)

=$1,027.71

The last thing I want to mention is that years are always multiplied by 12 to get months, and interest rates are divided by 12 for the same reason. That way if you write,¬†=PMT(6%/12, 10, 10000,0), Excel will assume it is in months. A big issue if you’re putting away $111.02 for the trip instead of $977.71

Be sure to play with the numbers and have fun! Excel can teach us a lot more than we give it credit for.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s