_ap_ufes{"success":true,"siteUrl":"taxguru.in","urls":{"Home":"http://taxguru.in","Category":"http://taxguru.in/category/chartered-accountant/","Archive":"http://taxguru.in/2016/05/","Post":"http://taxguru.in/income-tax/comply-req-furnishing-details-assets-liabilites-itr-form.html","Page":"http://taxguru.in/notifications-filters/","Attachment":"http://taxguru.in/finance/metaphor-accounting-standards.html/attachment/urvish-mehta_photo/","Nav_menu_item":"http://taxguru.in/income-tax/497562.html","Acf":"http://taxguru.in/?acf=acf_judiciary","Rp4wp_link":"http://taxguru.in/rp4wp_link/"}}_ap_ufee

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


More Under Finance

Posted Under

Category : Finance (2952)
Type : Articles (9221)
Tags : Excel Tips (31)

Search Posts by Date