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.

### Like this:

Like Loading...