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
***************************************************
