Google Exceltip.com
Account Icon
Shopping Cart
CheckOut

» Formulas to calculate Loan payments in Microsoft Excel

Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box. In the screen shot is a list of functions and necessary syntax for loan calculations. See the formulas in the gray cells, and the syntax in rows 12-16.

Syntax for loan calculation functions

Rate - The interest rate per period.

Per - The period for which the interest rate is calculated.

Nper - The total number of payments.

Pv - The present value, the total amount that a series of future payments is worth now.

Type - The timing of the payment, either at the beginning or end of the period. Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period, and the number 1 represents payment at the beginning of the period. The default (empty argument) is 0. The calculation is at the end of the period.

PMT (Rate, Nper, -Loan Amount)

  • Calculates the payment for a loan based on constant payments and a constant interest rate.
  • Returns the regular monthly payment on the loan (principal + interest) when the interest for each of the monthly payments is constant.
  • Example: The principal of a loan is $100,000, and the term of the loan is three years. The monthly payment during the term of the loan is calculated at $3,227; see column B in the figure below.

PPMT (Rate, Which Period, Nper, -Loan Amount)

  • Returns the amount on the principal for a given period for a loan based on periodic, constant payments and a constant interest rate.
  • Returns the sum of the principal within the monthly payment (the monthly payment is comprised of the principal + interest). See various examples of calculations in column C, rows 8:10, and the formula syntax in rows 14:16.
  • IPMT (Rate, Which Period, Nper, -Loan Amount)
  • Returns the interest payment for a given period for a loan based on periodic, constant payments and a constant interest rate.
  • Returns the amount of the interest within the monthly payment (the monthly payment is comprised of the principal + interest). See the calculation in cell G11 and the formula syntax in G13.

NPER (Rate, Pmt, -Loan Amount)

  • Returns the number of loan payments with a constant interest rate. See the formula syntax in D12.

RATE (Nper, Pmt, -Loan Amount)

  • Returns the interest rate per period of a loan. RATE is calculated by iteration and can have zero or more solutions.
  • Returns the percentage of interest on the loan, when the number of payments is constant.

PV (Rate, Nper, Pmt)

  • PV is the present value — the total amount that a series of future payments is worth now.
  • Returns the current value for a series of payments with a constant interest rat
Screenshot // Formulas to calculate Loan payments in Microsoft Excel

Formulas to calculate Loan payments in Microsoft Excel
Rate this tip
12 34 5
  RATING: 2.86
  VIEWS: 290179
Accounts Officer
Muahmmad Atteique Anwar wrote on December 31, 1969 19:00 EST
i wanan to learn more and more about excell i am just beginer
Excel financial Formulas
Jeyaram wrote on December 31, 1969 19:00 EST
I want excel formula in financial and cost and management accounting and loan calculate etc
loan payment
Asian wrote on December 31, 1969 19:00 EST
I need somebody to help me to create a table to show how much the interest payment and how much the payment to the principle are made in every month for my loan
excel
mubarak wrote on December 31, 1969 19:00 EST
Sir,
I want to free excel syntax & formula for about industry level calculation.
Hi
junx wrote on December 31, 1969 19:00 EST
Hi is there anyone know how to calculate the Electric bill from Present and previous reading?
wrote on December 31, 1969 19:00 EST
i want to ask you about how can i add a menubar, button to excel window
numbers to be changed to words
Rehana wrote on December 31, 1969 19:00 EST
If i use a column and get the sum of it at the end ,I want this to automatically change to words also.
Eg: 275
Two hundredand seventy five only
Date Selected Work Sheet
neil22fdo@yahoo.com wrote on December 31, 1969 19:00 EST
I want to know how the =Choos ( formular works) becuase i need to see the report based on the date i select this report should give me the figures for Today, Month to date & Year to date

Thanks
formula for calculation of evaluation generation
hajano wrote on December 31, 1969 19:00 EST
how to generate formula for calculation of evaluation



REGISTERED USERS click here to post comments


GUESTSclick here to Register
Name
Comment Title
Comments


Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com
Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation