PMT (loan stuff)

 
Home
Excel Welcome
Spreadsheet Tutorial
Why on a Computer?
Basics of a Spreadsheet
COLUMN Defined
ROW Defined
CELL Defined
150Data Types
Labels in Excel
Constants in Excel
Formulas in Excel
Basic Formulas in Excel
Change in Formulas
Basic Math Functions
Methods of Selecting Cells
Sum Function
Average Function
Max Function
Min Function
Count Function
CountA Function
IF Function
PMT (loan stuff)
SIN COS TAN etc.
Function Wizard
Copying Formulas
Fill Down
Absolute Positioning
Fill Right
Formatting Text
I P PMT (loan stuff) N

The PMT function returns the periodic (in this case monthly) payment for an annuity (in this case a loan). This is the PMT function that was used for the car purchase in the first example. There are a few things that we must know in order for this function to work. To calculate the loan we must know a combination of the following

  • (rate) interest rate per period
  • (NPER) number of payments until repaid
  • (PV) present value of the loan (amount we are borrowing)
  • (FV) future value of the money (for saving or investing)
  • (type) enter 0 or 1 to indicate when payments are due.

 

=PMT(rate, NPER, PV, FV, type)

 

 
equation goes into c7 =PMT(C4/12,C5,-C3)

C4 is the yearly interest and since it's compounded monthly we divide by 12

C5 is the number of months (# of payments)

-C3 is the amount of money we have (borrow - negative)


Note that the rate is per period. If we have an annual interest rate of 9.6% and we are calculating monthly payments, we must divide the annual interest rate by 12 to calculate the monthly interest rate.

1096219

  info@exceltalk.net