PMT Function in excel and how to use it?

Untitled design

Author: Sarthak Bhalerao

Table of Contents

  1. What is the PMT Function?
  2. How does the PMT function work?
  3. Example

What is the PMT function?

The PMT function is categorised under financial Excel functions. This function helps in calculating the total payment required to settle a loan or an investment with a fixed interest rate over a specific time period. It can be used as a worksheet function (WS) and a VBA function in Excel. The PMT function can be entered as a part of the formula in a cell of a worksheet.

How does the PMT function work?

 

  • Syntax:

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

  • Parameters:
  1. Rate – The interest rate for the loan
  2. Nper – The number of payments for the loan
  3. PV – The present value or principal of the loan
  4. FV – It is the future value of the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes an FV value of 0
  5. Type – It indicates when the payments are due. If this parameter is omitted, it assumes a Type value of zero. If the Type value is, one, payments are due at the beginning of the period, and if the Type value is zero, the payments are due at the end of the period.
  • Returns:

The PMT function returns a numeric value

  • Applies to:

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

 

Example

Let’s assume that we need to invest in such a manner that, after two years, we’ll receive $75,000. The rate of interest is 3.5% per year and the payment will be made at the start of each month. The time period is entered in months so 2 years are 2*12=24 months. The details are:

 

 

The formula used is:

 

 

We get the results below:

 

Default image
Sarthak Bhalerao

Leave a Reply