- admin
- | Finance
- 04 Apr 2012
- 7,953 Views
- 0 comment

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**

- Should we paint entire journalist profession with same brush?
- Practical Aspects in Income Tax Scrutiny proceedings
- Risk Based Auditing In Information Technologies Environment
- Inter-linking provisions in Customs, Central Excise & Service Tax
- Organizational Set Up of CBEC after Cadre Restructuring
- 6 Suggestions on Revised Model Draft GST Law
- Transitional Provisions under Revised Model GST law
- Approach to Regulation of Tax Professionals
- Guidance Note on FATCA and CRS updated till 30.11.2016
- Income Tax Investigations Unearth Mis-use of Jan-Dhan Accounts

- Top 17 #Demonetization Myths Busted
- Tax Terrorism at its peak under BJP regime
- Transparent Bank Locker Facility needed to Curb Black Money
- Cash in hand as on 8th November – An Asset or Liability?
- IT dept starts issuing notices for cash deposits on or after 09/11/2016
- Issue of Imposition of Penalty u/s 270A of Income Tax Act,1961
- Prosecution for allowing use of Bank A/C by others for Black Money Conversion
- Action on Black Money by Central Government
- Open letter to make India free from corruption
- Section 115BBE of Income Tax, 1961 requires Immediate change

- Direct and Indirect Tax Due date calendar -December, 2016
- Download Free Book on Goods and Services Tax In India
- FAQs on withdrawal of ₹ 500 & ₹ 1000 Banknotes (01.12.2016)
- GST Registration Procedure : Who is required to register for GST number?
- Govt. presents bill in Lok Sabha to tax Black Money
- Beyond rumours :True story of real and fake coin of Rs. 10
- No restriction on cash withdrawal of deposit made in legal tender notes
- Lok Sabha Approves bill to tax cash deposit of black Money
- Let’s understand Proposed Income Tax 2nd Amendment Bill 2016
- No tax on jewellery acquired-out of disclosed sources or exempt income

Can you forward a sample excel sheet of the above

Thanks