Concerned about the latest vaccine mandate? Visit Pryor's free training resource page to learn more about the latest rulings on the national 100+ vaccine mandate.

Use Excel PMT Formula 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.


Choose a Seminar and Save $10:

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

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Jan. 28

Eastern Time Zone

Feb. 1

Central Time Zone

Feb. 3

Central Time Zone

Feb. 4

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 9

Eastern Time Zone

Feb. 11

Central Time Zone

Feb. 15

Central Time Zone

Feb. 17

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 23

Central Time Zone

Feb. 25

Pacific Time Zone

Mar. 2

Central Time Zone

Mar. 3

Eastern Time Zone

Mar. 8

Central Time Zone

Mar. 10

Central Time Zone

Mar. 15

Central Time Zone

Mar. 17

Central Time Zone

Mar. 18

Eastern Time Zone

Mar. 23

Central Time Zone

Mar. 25

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 30

Central Time Zone

Apr. 1

Pacific Time Zone

Apr. 5

Eastern Time Zone

Apr. 6

Central Time Zone

Apr. 8

Central Time Zone

Apr. 12

Central Time Zone

Apr. 13

Pacific Time Zone

Apr. 14

Eastern Time Zone

Apr. 20

Central Time Zone

Apr. 21

Central Time Zone

Apr. 22

Eastern Time Zone

Apr. 26

Eastern Time Zone

Apr. 28

Central Time Zone

Apr. 29

Pacific Time Zone

May. 3

Eastern Time Zone

May. 5

Central Time Zone

May. 6

Pacific Time Zone

May. 10

Eastern Time Zone

May. 11

Central Time Zone

May. 13

Central Time Zone

May. 17

Central Time Zone

May. 18

Pacific Time Zone

May. 19

Central Time Zone

May. 20

Eastern Time Zone

May. 25

Eastern Time Zone

May. 26

Central Time Zone

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

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Jan. 31

Central Time Zone

Feb. 2

Central Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 8

Eastern Time Zone

Feb. 10

Central Time Zone

Feb. 14

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 22

Central Time Zone

Feb. 24

Pacific Time Zone

Mar. 1

Central Time Zone

Mar. 2

Eastern Time Zone

Mar. 7

Central Time Zone

Mar. 9

Central Time Zone

Mar. 14

Central Time Zone

Mar. 16

Central Time Zone

Mar. 17

Eastern Time Zone

Mar. 22

Central Time Zone

Mar. 24

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 29

Central Time Zone

Mar. 31

Pacific Time Zone

Apr. 4

Eastern Time Zone

Apr. 5

Central Time Zone

Apr. 7

Central Time Zone

Apr. 11

Central Time Zone

Apr. 12

Pacific Time Zone

Apr. 13

Eastern Time Zone

Apr. 19

Central Time Zone

Apr. 20

Central Time Zone

Apr. 21

Eastern Time Zone

Apr. 25

Eastern Time Zone

Apr. 27

Central Time Zone

Apr. 28

Pacific Time Zone

May. 2

Eastern Time Zone

May. 4

Central Time Zone

May. 5

Pacific Time Zone

May. 9

Eastern Time Zone

May. 10

Central Time Zone

May. 12

Central Time Zone

May. 16

Central Time Zone

May. 17

Pacific Time Zone

May. 18

Central Time Zone

May. 19

Eastern Time Zone

May. 24

Eastern Time Zone

May. 25

Central 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

LIVE ONLINE SEMINARS

Feb. 2

Eastern Time Zone

Feb. 4

Central Time Zone

Feb. 8

Pacific Time Zone

Feb. 9

Central Time Zone

Feb. 15

Eastern Time Zone

Feb. 18

Central Time Zone

Feb. 24

Central Time Zone

Mar. 3

Central Time Zone

Mar. 8

Eastern Time Zone

Mar. 11

Central Time Zone

Mar. 16

Pacific Time Zone

Mar. 21

Central Time Zone

Mar. 23

Eastern Time Zone

Mar. 31

Central Time Zone

Apr. 4

Central Time Zone

Apr. 6

Eastern Time Zone

Apr. 12

Eastern Time Zone

Apr. 14

Central Time Zone

Apr. 21

Eastern Time Zone

Apr. 22

Central Time Zone

Apr. 26

Central Time Zone

Apr. 27

Pacific Time Zone

May. 4

Central Time Zone

May. 5

Eastern Time Zone

May. 10

Central Time Zone

May. 12

Pacific Time Zone

May. 17

Eastern Time Zone

May. 20

Central Time Zone

May. 23

Central Time Zone

Related Basic Excel Articles

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.

Categories