Financial Analysis & Forecasting

Excel's financial functions implement the standard time-value-of-money (TVM) model used in corporate finance and consumer lending: a dollar today is worth more than a dollar tomorrow because today's dollar can be invested to earn interest. The five core variables — rate, nper, pmt, pv, and fv — are the same five variables that appear in any financial calculator (HP-12C, BA II Plus), and they are tied together by a single underlying equation. If you know any four of them, Excel can solve for the fifth.

Two ideas dominate this lesson and trip up most exam candidates:

• Sign convention — Excel treats cash into your pocket as positive and cash out of your pocket as negative. A loan you receive is positive; a payment you make is negative. Mixing the signs is the most common cause of "the answer looks wrong" results.
• Rate–period consistency — The rate argument and the nper argument must use the same time unit. If you make monthly payments, divide the annual rate by 12 and multiply the loan term by 12. Failing to convert is the second-most-common cause of wrong results.

Part 1: Core Calculation Functions

1. PMT(): The Payment Calculator

• What it does: Calculates the constant periodic payment required to settle a loan based on the rate, total number of periods (nper), and the current loan amount (pv).
• Formula: =PMT(rate, nper, pv)
• Practical Example: You take a $530,000 loan at 6% annual interest to be paid back over 5 years.
• Filled Formula: =PMT(6%/12, 5*12, 530000)
• Result: -$10,246.54
• What it means: You must pay the bank $10,246.54 every month for 5 years to fully repay the $530k.

2. NPER(): The Timeline Calculator

• What it does: Calculates the number of periods (nper) required to reach a goal or pay a debt based on the rate, your monthly payment (pmt), and your starting balance (pv).
• Formula: =NPER(rate, pmt, pv, [fv])
• Practical Example: You owe $530,000 at 6% interest. You can only afford to pay $5,000 per month.
• Filled Formula: =NPER(6%/12, -5000, 530000)
• Result: 148.2
• What it means: It will take you 148 months (about 12.3 years) to pay off the loan at that lower payment rate.

3. RATE(): The Interest Uncoverer

• What it does: Calculates the interest rate per period, given the total time (nper), the payment amount (pmt), and the loan amount (pv).
• Formula: =RATE(nper, pmt, pv)
• Practical Example: You borrow $530,000 and agree to pay $12,000 a month for 5 years. What is the bank actually charging you?
• Filled Formula: =RATE(5*12, -12000, 530000)
• Result: 1.11% (Monthly)
• What it means: You are paying a 13.3% annual interest rate (1.11% × 12 months).

Part 2: Amortization (Breaking Down the Payment)

IPMT() & PPMT()

• What they do: These break your PMT into two parts: the Interest (IPMT) you pay the bank and the Principal (PPMT) that actually reduces your debt.
• Formula: =IPMT(rate, per, nper, pv) and =PPMT(rate, per, nper, pv)
• Practical Example: In the 1st month of your $530,000 mortgage (6% for 5 years):
• Filled Formulas:
  • IPMT: =IPMT(6%/12, 1, 60, 530000) → -$2,650.00
  • PPMT: =PPMT(6%/12, 1, 60, 530000) → -$7,596.54
• What it means: Out of your first $10,246 payment, $2,650 is pure interest for the bank, while $7,596 actually pays down your $530k debt.

Part 3: Professional Valuation (XNPV & XIRR)

XNPV() & XIRR()

• What they do: Calculate value based on irregular dates.
• Formula: =XNPV(rate, values, dates) and =XIRR(values, dates)
• Practical Example: You invest $530,000 on Jan 1, 2026. You get back $200k in June, $200k in December, and $200k in March 2027.
• Filled Formula (XIRR): =XIRR({-530000, 200000, 200000, 200000}, {"2026-01-01", "2026-06-01", "2026-12-01", "2027-03-01"})
• Result: 11.2%
• What it means: Your investment is earning an 11.2% annual return.

Part 4: Sign Convention Pitfalls

The pv, pmt, and fv arguments share a single rule: money flowing into your pocket is positive, money flowing out is negative. Excel applies this convention strictly, and getting it wrong inverts the sign of the result without warning.

The two valid setups for a loan

A consumer loan can be modelled from either side of the transaction. Both are correct as long as the signs are internally consistent.

=PMT(6%/12, 60, 530000)     →  -10,246.54   borrower view (default)
=PMT(6%/12, 60, -530000)    →  +10,246.54   lender view

Both formulas describe the same loan. The result simply tells you which direction the cash flows.

Why the negative number in the practical examples above

In every example in Part 1, pv is entered as a positive number (the loan you received) and the function returns a negative number (the payment you make). This is the borrower's view, and it is the most common framing in personal-finance and exam questions.

If you want the displayed payment to come out positive (e.g., for a clean dashboard), you have two options:

=-PMT(6%/12, 60, 530000)     →  +10,246.54   negate the function call
=PMT(6%/12, 60, -530000)     →  +10,246.54   negate the pv argument

Part 5: The Annual vs. Periodic Rate Trap

The single most frequent error in exam tasks (and in real-world spreadsheets) is forgetting that rate and nper must use the same time unit. A 30-year mortgage with monthly payments has nper = 360, not 30 — and rate = APR/12, not the full APR.

The conversion table

What goes wrong if you forget

A $200,000 mortgage at 6% APR over 30 years with monthly payments:

Correct:    =PMT(6%/12, 30*12, 200000)   →  -$1,199.10  per month
Wrong:      =PMT(6%, 30, 200000)         →  -$14,529.00 per year (or per month, depending on what you tell yourself it means)
Wrong:      =PMT(6%/12, 30, 200000)      →  -$731.39    nonsense — monthly rate but yearly nper

The third version is the trap that catches exam candidates: it looks reasonable, the formula doesn't error, and the number is plausibly "in the ballpark." But it is silently wrong by an order of magnitude.

Part 6: The Financial "Rule of Signs" Cheatsheet

Part 7: Common Exam Patterns

The MO-211 exam frames financial-function tasks as small business or personal-finance scenarios. Practice rewriting each of these in the language of rate, nper, pv, pmt, and fv until the translation is automatic.

Pattern 1 — Calculate a monthly mortgage payment

"Calculate the monthly payment to pay off a $250,000 mortgage over 30 years at 6.5% APR."

Solve for pmt. Convert: rate = 6.5%/12, nper = 30*12, pv = 250000.

=PMT(6.5%/12, 30*12, 250000)

Pattern 2 — Solve for the number of payments

"How many months will it take to repay a $15,000 personal loan at 9% APR if you can pay $400 per month?"

Solve for nper. Note pmt is negative (cash out).

=NPER(9%/12, -400, 15000)

Pattern 3 — Reverse-engineer an interest rate

"What annual interest rate is the dealership charging on a $28,000 car loan paid off in 60 monthly payments of $560?"

Solve for rate and multiply by 12 to annualise.

=RATE(60, -560, 28000) * 12

Pattern 4 — Split a payment into interest vs. principal

"In month 1 of the $250,000 mortgage above, how much of the payment is interest versus principal?"

Use IPMT and PPMT with the same rate, nper, and pv, plus per = 1.

=IPMT(6.5%/12, 1, 30*12, 250000)
=PPMT(6.5%/12, 1, 30*12, 250000)

Pattern 5 — Annualised return on irregular cash flows

"An investor put $100,000 into a startup on 2024-01-15 and received $40,000 back on 2024-09-30 and $90,000 on 2025-12-01. What is the annualised return?"

Use XIRR because the dates are irregular.

=XIRR({-100000, 40000, 90000}, {"2024-01-15", "2024-09-30", "2025-12-01"})

Part 8: Cross-References

• Lesson 3.4 What-If Analysis — Goal Seek is the natural companion to RATE and NPER when you need to solve a "what input hits this target?" question against any output cell.
• Lesson 3.7 Troubleshoot Formulas — Use Evaluate Formula to step through a financial calculation when the answer looks wrong; it is often the fastest way to spot a sign or rate-conversion error.
• Module 2 lesson 2.4 Custom Number Formats — Apply a currency or accounting format to financial outputs so that negative payments show in parentheses or red, matching finance-team conventions.