The PMT Function in Excel

WPS Spreadsheet could be an alternative to Microsoft Office Excel. It includes 100's of built-in formulas, pivot tables, and more.

· Description:

The PMT function can return the amount of payment for a loan based on constant payments and a constant interest rate.

· Syntax:

PMT( rate, nper, pv, [fv], [type] )

· Arguments:

Rate: The interest rate.

Nper:The total number of payments periods for the loan.

PV: The present value or principal of the loan.

FV: [Optional]. The future value or the loan amount you want to attain after all payments are made. If omitted, 0 is used.

Type: [Optional].

1: payment at the beginning of the period

0: payment at the end of the period. If omitted, 0 is used.

· Example:

Assume that we want to return a monthly payment on a ＄150,000 loan at an annual rate of 6.5%, which should be paid off within 20 years. And all payments to be made at the beginning of the period. To get this, we can use the PMT function in WPS Spreadsheet.

1. Open your table in WPS Spreadsheet, click cell D3.

2. Head to the Formulas tab > Insert Function > enter PMT in the edit box.

In the pop-up box, we want to insert the PMT function.

1)Rate: The interest rate. We need the monthly rate, so B3-the annual rate should be divided by 12 So we enter B3/12 atRate.

2) Nper: The total number of payments periods for the loan. C3 represents the total number of payments periods, we want the monly payment, so it should times 12. Let's enter C3*12 at Nper.

3) PV: The present value or principal of the loan. The loan amount is 150,000, which should be entered in the third argument.

4) FV:The future value or the loan amount you want to attain after all payments are made. In this case, we choose to omit FV because we need to pay off the loan.

5)Type: 1: payment at the beginning of the period. Considering we want the payments to be made at the begining of every month, enter 1 at Type.

So, in the pop-up box, enter B3/12 atRate,C3*12 at Nper,and A3 at PV.Omit FV because we need to pay off the loan, and enter 1 at Type.

And we can get the result: -$1,112.

4.We prefer to see the result as a positive number, let's add a minus sign before the formula. So, the monthly payment for the loan is $1,112.