How to calculate interest on a loan

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-financial-formulas/formulas-to-calculate-loan-payments-in-microsoft-excel.html">
SHARE




In this article we are going to learn about how to calculate loan payments. Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box.

Below are the Monthly payment formula in Excel:-

Syntax for loan calculation formula:-

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

Below are the links where you can download the Excel of Loan calculator along with the steps:-

Loan Calculator | Loan Amortization Schedule

These are the tips where will get know how to use loan calculation formula to create the Excel loan Calculator.

Please follow and like us:
7


5 thoughts on “How to calculate interest on a loan

Leave a Reply

Your email address will not be published. Required fields are marked *


1 × = four

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>