Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Example of COUNTIFS with variable table column in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Managing Conditional Formatting Rules in Excel
  • Number and Text Filters Examples in Excel
  • Excel Pie Chart

References

  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Perform case-sensitive Lookup in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation no punctuation
  • Excel Data validation allow weekday only
  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only

PMT, RATE, NPER, PV and FV Financial Functions in Excel

by

Learn all about Excel’s PMT, RATE, NPER, PV and FV

To illustrate Excel’s most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that’s what you hope to achieve when you pay off a loan).

We make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods). If we make annual payments on the same loan, we use 6% for Rate and 20 for Nper.

PMT:

Calculates the payment for a loan based on constant payments and a constant interest rate.Syntax for PMT:

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

Select cell A2 and insert the PMT function.

excel-financial-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

Note: the last two arguments are optional. For loans, Fv can be omitted (the future value of a loan equals 0, however, it’s included here for clarification). If Type is omitted, it is assumed that payments are due at the end of the period.

Worked Example:   Calculate periods for annuity in Excel

Result. The monthly payment equals $1,074.65.

pmt-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)? We pay off a loan of $150,000 (positive, we received that amount) and we make monthly payments of $1,074.65 (negative, we pay). Visit our page about the PMT function for many more examples.

RATE:

Returns the interest rate per period of an annuity.Syntax for RATE:

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

If Rate is the only unknown variable, we can use the RATE function to calculate the interest rate.

Worked Example:   RATE function: Description, Usage, Syntax, Examples and Explanation

rate-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

NPER:

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Syntax for NPER:

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

Or the NPER function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, it takes 240 months to pay off this loan.

nper-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

We already knew this, but we can change the monthly payment now to see how this affects the total number of periods.

nper-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

Conclusion: if we make monthly payments of $2,074.65, it takes less than 90 months to pay off this loan.

PV:

Returns the present value of an investment.

Worked Example:   How to calculate compound interest in Excel

Syntax for PV:

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

Or the PV (Present Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, how much can we borrow? You already know the answer.

pv-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

FV:

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax for FV:

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

And we finish this chapter with the FV (Future Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, do we pay off this loan? Yes.

fv-function PMT, RATE, NPER, PV and FV Financial Functions in Excel

But, if we make monthly payments of only $1,000.00, we still have debt after 20 years.

PMT, RATE, NPER, PV and FV Financial Functions in Excel

Post navigation

Previous Post:

Find, Select, Replace and Go To Special in Excel

Next Post:

What-If Analysis: Scenarios and Goal Seek in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get month from date in Excel
  • Get fiscal quarter from date in Excel
  • Convert decimal hours to Excel time
  • Count birthdays by month in Excel
  • How to calculate future date say 6 months ahead in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel
  • Group arbitrary text values in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign people to groups in Excel

General

  • AutoFit Column Width, AutoFit Row Height in Excel
  • Convert column letter to number in Excel
  • How to count total number of cells in a rectangular range in Excel
  • List sheet names with formula in Excel
  • How to set or clear a print area in Excel Worksheet
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning