Using the Excel PMT Function to Determine Loan Interest

We start out with a basic premise that we borrow $10,000.00 (D1). Our interest is 3.0% (D2) and the monthly payments are forty-eight months (D3).

Excel_PMT_Formula_figure1

The PMT formula calculates what to pay back on a monthly payment loan. There are three arguments in this function; RATE,NPER,PV. Two other arguments shown are not required for a basic loan payment; FV and TYPE.

=PMT(RATE,NPER,PV)

Let’s discuss the components of this calculation.

Rate is the interest rate on the loan. Payments are usually monthly, and interest is usually annual. You must change annual to monthly by dividing the interest by twelve. This gives you a monthly interest rate matching the monthly payment. So, if the interest rate is 3% you enter D2/12 for the RATE portion of the calculation.

Excel_PMT_Formula_figure2

NPER is the number of periods for which the loan is to be repaid. This is usually in months. In this example, you would click D3 to represent 48 months.

PV is the present value. This entry is the amount that was borrowed. You would select D1 here to represent the $10,000 loan.

When you do the above steps, your answer will be a negative. You owe that money, thus payment on the loan is a debt and the Excel PMT formula shows all debts as a negative value, automatically.

Excel_PMT_Formula_figure3

Should you want to show this as a positive, you have a few options. Multiply the entire function by negative one (-1), or place a negative sign (-) between the equal (=) and the P in PMT (this will make the entire function a negative function, resulting in a positive answer.

Excel_PMT_Formula_figure4

Excel_PMT_Formula_figure5

For more complex PMT formula, there are two additional arguments that allow further customization to your calculations.

FV is the future value. At the end of the payment, it might be possible that you want to have an unpaid balance, for whatever reason. You can enter an amount in FV and it can calculate the loan to have that unpaid balance. It lowers your payments, but this FV is still due.

Type is a logical (TRUE/FALSE) entry. It allows you to select when the payment will be paid, either at the beginning of the payment (month) period (enter 1) or at the end of the month (enter 0, or omitted).

These last two are optional, and lower case, and not required for a basic loan.

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

Dec. 8

Eastern Time Zone

Dec. 9

Pacific Time Zone

Dec. 14

Central Time Zone

Dec. 16

Eastern Time Zone

Dec. 20

Pacific Time Zone

Dec. 21

Central Time Zone

Jan. 5

Eastern Time Zone

Jan. 6

Central Time Zone

Jan. 10

Central Time Zone

Jan. 12

Eastern Time Zone

Jan. 13

Pacific Time Zone

Jan. 18

Pacific Time Zone

Jan. 19

Central Time Zone

Jan. 20

Eastern Time Zone

Jan. 24

Eastern Time Zone

Jan. 25

Central Time Zone

Jan. 26

Pacific Time Zone

Jan. 31

Central Time Zone

Feb. 2

Pacific Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 10

Pacific Time Zone

Feb. 14

Pacific Time Zone

Feb. 15

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 22

Eastern Time Zone

Feb. 23

Pacific Time Zone

Feb. 24

Central Time Zone

Feb. 28

Central Time Zone

Mar. 2

Eastern Time Zone

Mar. 7

Pacific Time Zone

Mar. 8

Eastern Time Zone

Mar. 10

Central Time Zone

Mar. 15

Central Time Zone

Mar. 16

Pacific Time Zone

Mar. 17

Eastern Time Zone

Mar. 21

Pacific Time Zone

Mar. 23

Central Time Zone

Mar. 24

Eastern Time Zone

Mar. 28

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 31

Pacific Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

Dec. 8

Pacific Time Zone

Dec. 13

Central Time Zone

Dec. 15

Eastern Time Zone

Dec. 19

Pacific Time Zone

Dec. 20

Central Time Zone

Jan. 4

Eastern Time Zone

Jan. 5

Central Time Zone

Jan. 9

Central Time Zone

Jan. 11

Eastern Time Zone

Jan. 12

Pacific Time Zone

Jan. 17

Pacific Time Zone

Jan. 18

Central Time Zone

Jan. 19

Eastern Time Zone

Jan. 23

Eastern Time Zone

Jan. 24

Central Time Zone

Jan. 25

Pacific Time Zone

Jan. 30

Central Time Zone

Feb. 1

Pacific Time Zone

Feb. 2

Eastern Time Zone

Feb. 6

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 9

Pacific Time Zone

Feb. 13

Pacific Time Zone

Feb. 14

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 21

Eastern Time Zone

Feb. 22

Pacific Time Zone

Feb. 23

Central Time Zone

Feb. 27

Central Time Zone

Mar. 1

Eastern Time Zone

Mar. 6

Pacific Time Zone

Mar. 7

Eastern Time Zone

Mar. 9

Central Time Zone

Mar. 14

Central Time Zone

Mar. 15

Pacific Time Zone

Mar. 16

Eastern Time Zone

Mar. 20

Pacific Time Zone

Mar. 22

Central Time Zone

Mar. 23

Eastern Time Zone

Mar. 27

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 30

Pacific Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

Dec. 9

Central Time Zone

Dec. 15

Eastern Time Zone

Dec. 21

Pacific Time Zone

Jan. 4

Central Time Zone

Jan. 9

Pacific Time Zone

Jan. 10

Eastern Time Zone

Jan. 17

Central Time Zone

Jan. 18

Eastern Time Zone

Jan. 26

Central Time Zone

Jan. 27

Pacific Time Zone

Jan. 30

Eastern Time Zone

Feb. 3

Central Time Zone

Feb. 7

Pacific Time Zone

Feb. 8

Eastern Time Zone

Feb. 15

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 21

Central Time Zone

Feb. 23

Pacific Time Zone

Feb. 28

Eastern Time Zone

Mar. 3

Pacific Time Zone

Mar. 7

Pacific Time Zone

Mar. 8

Central Time Zone

Mar. 13

Eastern Time Zone

Mar. 16

Central Time Zone

Mar. 22

Pacific Time Zone

Mar. 30

Central Time Zone

Mar. 31

Eastern Time Zone

Related Basic Excel Articles

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

Categories