Excel Tutorial: PMT Function

On this tutorial I am going to show you how to use the PMT (Payment) function of Excel. PMT function is a financial function that is used to calculate the value of periodic payments for an investment or a bank loan.
To find this function in Excel, you just need to click Formulas ribbon tab, click the Financial button, and choose PMT from the drop-down list.

Note that there are a few arguments for this function, that will be explained in the following section.

The Arguments of the Function

The following arguments appear on the right side of the function name, in parenthesis:

• rate
• nper
• pv
• [fv]
• [type]

The following table describes every argument of this function:

Argument	Description
Rate	Describes the interest rate of the investment/loan.
Nper	Describes the total number of payments for the investment/loan.
Pv	Describes the present value, or the amount that a series of future payments values now.
Fv	Describes the future value that remains after performing the last payment. This argument is optional, and if it is left blank, will be considered zero.
Type	This argument needs to be set to zero if the payment is executed at the end of each period or one if the payment is done at the beginning of each period. This argument is optional.

Using the PMT Function

Let's imagine that you want to invest a constant amount of money every year in a deposit with an interest rate of 12% per year. After 10 years, you want to get the final amount of 80.000 US dollars. 

How much money you need to invest?

To find out the solution, write a small table in excel like the one from the following screenshot:

Before submitting the values for arguments, select the B4 cell to spawn the PMT function result on that cell. Next, open the PMT function and choose the following values for the function's arguments:

• For Rate choose B1.
• For Nper choose B2.
• For PV choose zero because the beginning value is equal to zero.
• For Fv choose B3, because you want to attain that amount of money.
• Leave the Type argument undefined. 

The result will be -$4.558,73, meaning that you need to invest that amount of money to get 80.000 $ after 10 years with 12% annual interest rate. Quite a lot of money, isn't it?
Let's take a look at the following scenarios:
How much money do you need to invest, if the interest rate is applied at every six months?

• For Rate choose B1 divided by two, due to the fact that the interest rate is calculated at every six months.
• For Nper choose B2 multiplied by two, because the number of payments has been doubled. 
• For PV choose zero.
• For FV choose B3.

The result will be -2.174,76 US dollars. 

How much money do you need to invest, if the interest rate is calculated on a monthly basis?

• For Rate choose B1 divided by 12.
• For Nper choose B2 multiplied by 12. 
• For PV choose zero.
• For FV choose B3.

The result will be -347.77 US dollars. 

After you performed the calculations for each scenario, you should have a spreadsheet similar with the one provided below:

That's it for today's tutorial. If you have any questions, don't hesitate to contact me and I'll answer you as soon as possible. Also, don't forget to send me your feedback with what should be improved for the future tutorials :).