Using The Excel Payment (PMT) Function

Excel function 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

Here is the entire spreadsheet:

You can also download the spreadsheet here


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss