Calculation of Loan Repayment and Split-ups - PMT function

published on 01 May 2024

Let’s say you are planning to get a loan from the bank. In order to have an understanding of how it’s going to financially affect the Company, it’s important to know about the repayment schedule and amount.

In Excel or Google Sheets, we can simply calculate the monthly payment and the monthly split-up of the EMI between interest and principal paid back using the PMT() function

PMT() Function

PMT(rate, nper, pv, [fv], [type])

where:

rate (required): the constant interest rate per period. Can be supplied as percentage of decimal number

nper (required): the number of payments for the loan, i.e., the total number of period over which the loan should be paid

pv (required): the present value, i.e., the total amount that all future payments are worth now. In case of a loan, it’s simply the original amount borrowed.

fv (optional): the future value, or the cash balance you wish to have after the last payment is made. If omitted, the future value of the loan is assumed to be zero.

type (optional): specifies when the payments are due:

  • 0 or omitted - payments are due at the end of each period
  • 1 - payments are due at the beginning of each period

A spreadsheet is given below that helps to calculate the monthly EMI for a loan. You can duplicate the above spreadsheet and make your own calculations

Read more