Money Math Five

Mastering 5 Key Financial Functions: PMT, PV, FV, NPER, RATE

Whether we’re planning to buy a car, save for retirement, or pay off a loan, numbers play a central role in our financial journey. Excel gives us powerful tools to analyze and plan financial scenarios. In this post, we’ll walk through five key Excel financial functions that can evaluate both loans and savings: PMT, PV, FV, NPER, and RATE. These functions allow us to solve nearly any payment plan equation: helping us figure out how much our goals will cost, how long it’ll take, or how much we need to save per month. Let’s break them down together.

This post is brought to you by Finatical Software

• QuickBooks Online users! We know how essential Excel is for creating custom reports in the exact format you need. With Finatical, you can pull live, refreshable QBO financial data directly into your favorite Excel reports, no more copy, paste, export, repeat. It’s fast, easy, and built to streamline your workflow.
• A FREE TRIAL is available, so check it out risk-free today!

Video

Step-by-Step Walkthrough

These 5 functions are tightly related. So much so in fact, they are arguments of each other. In other words, you can solve for one if you know the values of the others. For example, here is a list of the functions AND their arguments so you can see what I mean (I’ve left out some of the optional arguments):

• PMT(rate, nper, pv, fv) calculates the payment
• NPER(rate, pmt, pv, fv) calculates the number of periods
• RATE(nper, pmt, pv, fv) calculates the interest rate
• PV(rate, nper, pmt, fv) calculates the present value
• FV(rate, nper, pmt, pv) calculates the future value

As you can see, these are closely related functions. They are designed to help us calculate one of these values when the others are known.

One note about Excel’s financial functions. They are built around time-based cash flows: what’s coming in (inflows) are expressed as positive values, and what’s going out (outflows) are expressed as negative numbers. For example, if you borrow money, this is an inflow to you (positive number), whereas the subsequent monthly payments are outflows to you (negative number). This will solidified as we explore each function.

1. PMT – Payment Amount

Use Case: Loan Repayment or Savings Contributions

The PMT function calculates the periodic payment for a loan or a savings plan. It uses a fixed interest rate and number of periods. Here’s a basic formula:

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

• rate: interest rate per period
• nper: total number of periods
• pv: present value (loan amount or current investment)
• [fv]: optional, future value or balloon payment
• [type]: optional, 0 (end of period), 1 (start of period)

Important: All time-based inputs (rate, periods, and payment frequency) must align. For example, for monthly payments, get the interest rate into a monthly rate by dividing annual rate by 12, and get the number of periods in months by multiplying the number of years by 12 if needed.

=PMT(AnnualRate/12, Years*12, PresentValue)

Tip: Results may be negative if Excel interprets them as cash outflows. Use =ABS(PMT(...)) for a positive display.

Example:

You want to compute the monthly payment, given the other variables (present value is the loan amount of $10,000 for a 20 year term at 5% annual interest rate):

=PMT(.05/12, 20*12, 10000)

This returns a monthly payment of about $66 (rounded).

2. NPER – Number of Periods

Use Case: Determining How Long to Pay Off a Loan or Reach a Goal

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

If we know the loan amount, payment amount, and interest rate, this formula tells us how many payment periods we need.

Example:

Borrow $10,000, pay $66/month at 5% annual interest:

=NPER(5%/12, -66, 10000)

Returns about 240 months (or 20 years).

3. RATE – Interest Rate

Use Case: Figuring Out the Effective Interest Rate

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

If we’re given the number of months, monthly payment, and loan amount, this function uncovers the underlying interest rate.

Example:

$66 monthly payment on a $10,000 loan over 20 years:

=RATE(20*12, -66, 10000)*12

Returns about 5% annually.

Tip: Multiply the resulting monthly rate by 12 to annualize it.

4. PV – Present Value

Use Case: Comparing Lump Sum Payment to Installments

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

This tells us how much a future series of payments is worth today. Useful when selecting between one-time vs. recurring payments.

Example:

What’s the value today of paying $66/month for 20 years earning 5% annual interest?

=PV(.05/12, 20*12, -66)

Returns about $10,000.

5. FV – Future Value

Use Case: Will Our Retirement Savings Add Up?

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

This helps calculate how much we’ll have in the future, given regular contributions and a starting balance.

Example:

What if … instead of paying back a loan with a $66/month payment, we were to invest the same $66/month into a savings account.

Save $66/month, 5% interest, 20 years, starting with $0 today (present value):

=FV(.05/12, 20*12, -66, 0)

Returns about $27,128. What!?! I know … right! You could borrow $10,000 today and pay it back at $66/month for twenty years … or … save $66/month for 20 years and have $27,128 saved up! That is the power of savings and compound interest.

Summary

These five financial functions, PMT, NPER, RATE, PV, and FV, unlock massive power inside Excel. By aligning time periods and correctly formatting cash flow signs, we get accurate, flexible models for loans and investments. Once we set up the formulas, tweaking assumptions becomes a breeze. Excel becomes our financial calculator, planner, and decision-making tool all at once.

Download the Example Workbook

Want to practice with all of these values already set up? Download the example file and start plugging in your own scenarios.

Frequently Asked Questions

What’s the most important thing to remember when using financial functions in Excel?

Time period consistency. Make sure all values share the same interval (monthly, quarterly, annual) and adjust accordingly in your formula.

Why are some formula results negative?

Excel uses a cash flow model. Money out (payments) is negative; money in (receipts/loans) is positive. Flip the sign if needed using ABS() or by applying - in front of the formula.

Can I use these functions for quarterly or weekly payments?

Yes, as long as all relevant inputs (rate, periods) reflect the same frequency. For quarterly, divide annual interest rate by 4 and multiply years by 4.

What’s the difference between PV() and FV()?

PV returns the value today of future payments. FV projects how much future value will be generated from current contributions.

How do I compare a lump-sum payment to monthly installments?

Use PV(). Calculate the present value of monthly payments and compare it to the one-time payment.

Is Excel assuming payments happen at the end or start of the period?

By default, Excel assumes payments happen at the end. Set the optional type argument to 1 for beginning-of-period payments.

How do balloon payments work in Excel?

Use the optional fv argument in PMT or NPER. Enter the balloon value as a negative number.

Can I calculate the rate of return I need for my investment goal?

Yes, with RATE(), plug in your starting balance, contribution, and target value to solve for ROI.

Can I calculate how long it’ll take to become a millionaire?

Definitely. Use NPER() with your monthly contribution, estimated rate of return, and savings goal.

How can I flip a result from negative to positive?

Use =ABS(formula) or apply a – to invert the sign.

Disclosures and Notes

• This is a sponsored post for Finatical Software. All opinions are my own. Finatical Software is not affiliated with nor endorses any other products or services mentioned.
• 100% of sponsor proceeds fund the Excel University scholarship and financial aid programs … thank you!