Financial Function PMT in Excel
Have you ever thought that financial functions in Excel are for accountants or finance officers to handle their workload? The fact is that there are many financial functions that help people for making correct and wise personal financial decisions. PMT is such a financial function which can be used to calculate how much you need to pay monthly for a particular loan if the payments and interest rate are constant. Suppose you plan to borrow $10,000 from a bank for a period of 5 years. Then you should check whether the loan instalments are affordable for you, whether it is weekly, monthly, quarterly or yearly instalments. You can check this with PMT function.
The syntax of PMT function is PMT (rate, nper, pv, fv, type). Here rate is the interest rate, nper is the total number of payments, pv is the principal amount or the total amount that a set of future payments is worth now, fv is the cash balance you want to attain after the last payment is made and type is a logical value to identify whether the payment is due at the beginning of the period (type =1) or at the end of the period (type =0). The last two arguments (fv and type) are optional. The value of both these arguments would be considered 0 if omitted.
To illustrate the function, assume that you plan to borrow $10,000 for a period of 5 years and the interest rate offered by the bank is 30% per year. Now we are going to calculate the monthly loan cost with the help of PMT function. We have the interest rate per year, 30%. But, as you plan to make monthly payments, PMT function requires interest rate per month. So the rate value is 30% divided by 12 which is equal to .025. You can directly pass the value 30%/12 as the first argument (instead of entering the calculated result). As we are planning to pay every month, the value of nper (total number of payments), will be 5*12. In other words, you will have to make 60 payments. Here pv is the loan amount, $10,000. The fourth and fifth parameters are not necessary to calculate monthly payments. Now, enter the formula =PMT(.025,60,10000) or =PMT(30%/12,5*12,10000) in any of the cells. Now you could see the result -$323.53 (The symbol of the currency would be different based on your Excel formatting). You could find that there is a minus sign just before the $ symbol. This minus sign indicates that it is a debt: an amount you owe to someone.
Now, if you wish to know how much you actually pay for the loan amount of $10000 in 5 years. It is very simple. You just have to multiply the monthly payment with 60. Thus, you can easily know that this loan costs you an amount of $19,412.04 ($323.53*60). If you plan to pay it back in 10 years, then this cost will still be higher, though the monthly instalment cost would be lower. Instead of directly entering the formula, you can have five different columns in an Excel sheet and create the formula referencing these cell values. The advantage is that you will not have to modify the formula if any of the arguments, say the number of payments, principal amount or interest rate changes.
Suppose you borrow an amount of $5000 at an interest rate of 6%. You plan to pay back the amount in 3 years making weekly payments. Then, you need to make minor changes in the formula calculation. It would be =PMT(6%/52, 3*52, 5000). Here, instead of 12 (12 months), we change the value to 52 (52 weeks). If you are planning to make annual payments instead of weekly payments, then formula would become =PMT(6%/1, 3*1, 5000). Suppose, the bank offers a remaining balance on the loan of $1000 at the end of 3 years when you borrow $5000 at the rate of 6% deciding to make monthly payments, then you should pass this value as fourth argument fv and the formula becomes =PMT(6%/12, 3*12, 5000, -1000).
Read More about Excel here