Learn 5 Financial Functions in Excel (PMT, RATE, NPER, PV, FV)

In this tutorial, we will introduce five commonly used financial functions in Excel: PMT, RATE, NPER, PV, and FV. These functions are used to calculate payments, interest rates, the number of periods, the present value, and the future value, respectively.

Finding EMI

Equated Monthly Installment (EMI) is a fixed amount of money that a borrower is required to pay to a lender each month in order to repay a loan. EMI is calculated based on the loan amount, the interest rate, and the loan term. To calculate the EMI, we will use the PMT function

The PMT function has the following syntax:

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

where:

  • rate: the interest rate for the loan
  • nper: the total number of payments for the loan
  • pv: the present value or principal of the loan
  • fv (optional): the future value of the loan, which is the amount you want to have left after the last payment is made. If omitted, it is assumed to be 0.
  • type (optional): a value that indicates when payments are due. If type = 0 (default), payments are due at the end of the period. If type = 1, payments are due at the beginning of the period.

Step-by-step instructions

  1. Type the “=PMT(
  2. Select the cell that contains the interest rate and insert a comma “,” to move to the next argument.
  3. Select the cell that contains the number of payments and insert a comma “,” to move to the next argument.
  4. Select the cell that contains the present value of the loan and close with the closed parentheses “)” to complete the function.
  5. Press the “Enter” key to calculate the result.

Note: The function assumes that the rate is yearly. To calculate the monthly payment, simply divide the rate by 12.

Calculating the Rate of Interest

The rate refers to the interest rate charged on a loan or credit. It is the percentage of the loan amount that the borrower is charged for borrowing the money. The interest rate is used to calculate the amount of interest that will be added to the loan balance over time.

To calculate the rate, we will use the RATE function. The function has the following syntax:

RATE(nper, pmt, pv, [fv], [type], [guess])

Where:

  • nper: the total number of payments for the loan or investment
  • pmt: the fixed payment made each period
  • pv: the present value or principal of the loan or investment
  • fv (optional): the future value of the loan or investment, which is the amount you want to have left after the last payment is made. If omitted, it is assumed to be 0.
  • type (optional): a value that indicates when payments are due. If type = 0 (default), payments are due at the end of the period. If type = 1, payments are due at the beginning of the period.
  • guess (optional): an estimated rate of interest, used as a starting point for the calculation. If omitted, it is assumed to be 10%.

Step-by-step instructions

  1. Type “=RATE(” to begin the RATE function.
  2. Select the cell that contains the number of payments and insert a comma “,” to move to the next argument.
  3. Select the cell that contains the fixed payment and insert a comma “,” to move to the next argument.
  4. Select the cell that contains the present value of the loan or investment and close with the closed parentheses “)” to complete the function.
  5. Press the “Enter” key to calculate the result.

Note: Since we’re basing it on EMI, the rate will also be monthly. To find the annual rate of the payment, simply multiply the function by 12.

Calculating the Periods of Payments

The period of payments refers to the length of time over which payments are made on a loan or credit. The payments can be made monthly, bimonthly, quarterly, semi-annually, or annually. To calculate how many months are to be paid on a loan, we will use the NPER function.

The function has the following syntax:

NPER(rate, pmt, pv, [fv], [type])

where:

  • rate: the interest rate per period
  • pmt: the fixed payment made each period
  • pv: the present value or principal of the loan or investment
  • fv (optional): the future value of the loan or investment, which is the amount you want to have left after the last payment is made. If omitted, it is assumed to be 0.
  • type (optional): a value that indicates when payments are due. If type = 0 (default), payments are due at the end of the period. If type = 1, payments are due at the beginning of the period.

Step-by-step instructions

  1. Type “=NPER(” to begin the NPER function.
  2. Select the cell that contains the interest rate and insert a comma “,” to move to the next argument.
  3. Select the cell that contains the fixed payment and insert a comma “,” to move to the next argument.
  4. Select the cell that contains the present value of the loan or investment and close with the closed parentheses “)” to complete the function.
  5. Press the “Enter” key to calculate the result.

Note: The function assumes that the rate is yearly. To find the payment period in months, simply divide the interest rate by 12.

Calculating the Principal Value

The present value (PV) or principal value of a loan or investment refers to the initial amount borrowed or invested. It is the amount of money that is used to calculate the interest and payments on a loan or investment. The PV is the starting point from which the interest and payments are calculated and can be thought of as the “face value” of the loan or investment.

To calculate the principal value of a loan, we will use the PV function. The function has the following syntax:

PV(rate, nper, pmt, [fv], [type])

Where:

  • rate: the interest rate per period
  • nper: the total number of payments
  • pmt: the fixed payment made each period
  • fv (optional): the future value of the loan or investment, which is the amount you want to have left after the last payment is made. If omitted, it is assumed to be 0.
  • type (optional): a value that indicates when payments are due. If type = 0 (default), payments are due at the end of the period. If type = 1, payments are due at the beginning of the period.

Step-by-step instructions

  1. Type “=PV(” to begin the PV function.
  2. Select the cell that contains the interest rate and insert a comma “,” to move to the next argument.
  3. Select the cell that contains the total number of payments and insert a comma “,” to move to the next argument.
  4. Select the cell that contains the fixed payment and close with the closed parentheses “)” to complete the function.
  5. Press the “Enter” key to calculate the result.

Note: The function assumes that the rate is yearly. Since we have the EMI in months, the rate should also be converted into months by simply dividing the rate by 12.

Calculating the Future Value

The future value (FV) of a loan or investment refers to the amount that will be present at a certain point in the future, assuming a certain rate of return. The future value takes into account the interest earned on the initial investment or principal value over a period of time. In other words, it represents the value of an asset or a liability at a future date based on the current value and the rate of return.

To calculate the future value, we will use the FV function. The function has the following syntax:

FV(rate, nper, pmt, [pv], [type])

Where:

  • rate: the interest rate per period
  • nper: the total number of payments
  • pmt: the fixed payment made each period
  • pv (optional): the present value of the loan or investment, which is the amount you start with. If omitted, it is assumed to be 0.
  • type (optional): a value that indicates when payments are due. If type = 0 (default), payments are due at the end of the period. If type = 1, payments are due at the beginning of the period.

Step-by-step instructions

  1. Type “=FV(” to begin the FV function.
  2. Select the cell that contains the interest rate and insert a comma “,” to move to the next argument.
  3. Select the cell that contains the total number of payments and insert a comma “,” to move to the next argument.
  4. Select the cell that contains the fixed payment and close with the closed parentheses “)” to complete the function.
  5. Press the “Enter” key to calculate the result.

Note: The function assumes that the rate is yearly. Since we have the EMI in months, the rate should also be converted into months by simply dividing the rate by 12.

Conclusion

Excel provides several financial functions that are useful for solving various financial problems. The PMT, RATE, NPER, PV, and FV functions are all important tools that can help you calculate various financial metrics, such as loan payments, interest rates, future values, and more.