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

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


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

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.