# Using The Excel Payment (PMT) Function

### Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

One of the very useful financial functions in excel is the PMT one which can calculate how much you need to set aside or invest. It can be used in a variety of ways but today I wanted to take a look at a common situation. I got the following email:

“I have to reimburse a 50K loan on January 31st 2015 and only have \$21,000 saved up. How much do I need to set aside every month to make it?”

The PMT function is perfect for this.

=PMT (Rate, Nper, PV, FV)

Rate = here I’ll use the return that I’m getting on my money. Since I’m looking for a monthly number, I’ll convert the 7% into a monthly return:

Return monthly = (1+yearly)^(1/12)-1

Nper= here is a screenshot of how I’m calculating the number of months

PV= current balance..this can be linked to the portfolio value if you have it in excel

FV= what I’ll have to pay so -\$50,000

***************************************************

***************************************************