Excel Financial Functions: PMT, Amortization Schedules, NPV and IRR | Free Excel Course: From Beginner to Advanced

What you'll learn

Calculate a loan payment with PMT and build its amortization schedule.
Use the right monthly rate for an APR (loans) and an APY (savings).
Evaluate a project with NPV (net present value) and IRR.
Project savings with FV and find out how much you can borrow with PV.
Decide whether an extra payment should lower your payment or shorten your loan.

Welcome to Level 4: Expert. You've learned to organize, calculate, look up, summarize and automate data. Now you'll use Excel for what finance and management teams value most: decisions that involve money. Is this loan a good deal? Does this project create value? How much will I have if I save every month? A handful of financial functions answer all of it.

6functions (PMT, FV, PV, RATE, NPV and IRR) handle most money decisions

$2,972.64in interest on a $15,000 loan over 24 months at 18% APR

19.56%is what an 18% APR with monthly compounding really costs per year

The time value of money
PMT and rates
Amortization schedule
FV, PV and RATE
NPV and IRR

Educational content, not financial advice: the examples use illustrative rates. Before signing a real loan, read the lender's Truth in Lending disclosure.

The key idea: money changes value over time

A thousand dollars today is worth more than a thousand dollars a year from now, because today you can invest it and earn interest. So you can't add up cash flows from different dates as if they were equal: you bring them to the same point in time with an interest rate. Every function in this lesson does that.

Function	Question it answers	Main arguments
`PMT`	What's the fixed payment?	rate, number of periods, loan amount (pv)
`FV`	How much will I have at the end?	rate, number of periods, payment per period
`PV`	What is a series of payments worth today?	rate, number of periods, payment
`RATE`	What interest rate am I really paying?	number of periods, payment, loan amount
`NPV`	What are a project's future cash flows worth today?	rate, cash flows
`IRR`	What return does the project earn?	cash flows (including the investment)

The sign rule

Excel treats money you receive as positive and money you pay as negative. When you borrow $15,000 you receive money, so the payment PMT returns is negative because you pay it. To see it as a positive number, enter the loan amount with a minus sign: =PMT(1.5%, 24, -15000).

PMT: a loan payment

Ashley takes out a $15,000 personal loan for 24 months at 18% APR. U.S. loans quote an annual percentage rate that's compounded monthly, so the monthly rate is simply 18% ÷ 12 = 1.5%. With B1 = 15000, B2 = 1.5% and B3 = 24:

=PMT(B2, B3, -B1)        → 748.86

She'll make 24 payments of $748.86, or $17,972.64 in total: $2,972.64 of that is interest. That's the number to compare across loan offers.

APR vs. APY: the rate mistake that costs money

The rate and the periods must use the same unit. Using the annual rate with monthly periods (=PMT(18%, 24, -15000)) returns a payment of $2,751.81, which is absurd. But "converting to monthly" depends on what kind of rate you have:

APR (loans, credit cards): a nominal rate. Monthly rate = APR ÷ 12.
APY (savings accounts, CDs): an effective rate that already includes compounding. Monthly rate = (1 + APY)^(1/12) − 1. Dividing an APY by 12 slightly overstates what you earn each month.

Monthly rate from an APR of 18%:   =18%/12                 → 1.5%
Monthly rate from an APY of 4%:    =(1+4%)^(1/12)-1         → 0.3274%   (not 0.3333%)
Effective annual cost of 18% APR:  =(1+18%/12)^12-1         → 19.56%

So an 18% APR compounded monthly really costs 19.56% a year (credit cards usually compound daily, which costs slightly more).

1. A personal loan is quoted at 18% APR with monthly payments. Which rate goes into PMT?

18%
18% / 12 = 1.5%
(1 + 18%)^(1/12) − 1 ≈ 1.389%

A U.S. loan APR is a nominal annual rate compounded monthly, so you divide it by 12. The root formula is for effective rates like an APY, not for a loan's APR.

The amortization schedule

An amortization schedule shows, payment by payment, how much goes to interest and how much reduces the balance. Building one lets you check your lender's numbers or simulate an extra payment.

Month	Payment	Interest	Principal	Balance
0				15,000.00
1	748.86	225.00	523.86	14,476.14
2	748.86	217.14	531.72	13,944.42
3	748.86	209.17	539.70	13,404.72
…	…	…	…	…
24	748.86	11.07	737.79	0.00

With the inputs in B1:B3 and the schedule starting in row 6 (A6 = 0, E6 = =B1), the formulas in row 7 are:

A7  =A6+1
B7  =PMT($B$2, $B$3, -$B$1)
C7  =E6*$B$2                 ← interest = previous balance × rate
D7  =B7-C7                   ← how much the balance goes down
E7  =E6-D7                   ← new balance

Copy row 7 down to row 30 and the final balance will be 0 (or something like 0.00000001 from rounding). Notice the pattern: early on, most of the payment is interest; by the end, almost all of it is principal. Here's how the same $748.86 payment splits over the life of the loan:

Payment 1: 30% interest, 70% principal.

Payment 12: 17.6% interest, 82.4% principal.

Payment 24: 1.5% interest ($11.07), 98.5% principal.

That's why an extra payment early in a loan saves far more than one near the end.

Shortcut: IPMT and PPMT

If you only need the interest or principal of one specific payment, skip the schedule: =IPMT(1.5%, 1, 24, -15000) returns 225.00 and =PPMT(1.5%, 1, 24, -15000) returns 523.86 for payment 1.

Worked example: lower the payment or shorten the loan?

In month 6 Ashley gets her tax refund and wants to put $3,000 toward the loan. Her schedule shows a balance of $11,736.58 after payment 6; the extra payment drops it to $8,736.58. Here are her two options:

Option	Formula	Result	Interest still to pay
No extra payment	18 payments of 748.86	Paid off in month 24	About $1,743
A. Same term, lower payment	`=PMT(1.5%, 18, -8736.58)`	18 payments of $557.44	About $1,297 (saves about $446)
B. Same payment, shorter term	`=NPER(1.5%, -748.86, 8736.58)`	12.92 → 13 payments (the last one $689.74)	About $940 (saves about $803)

NPER calculates how many periods it takes to pay off a balance with a given payment. Option B saves nearly twice as much because the balance falls faster and generates less interest. In practice, most U.S. lenders apply extra payments to principal and keep your payment the same (option B); lowering the payment, called a recast, is mostly offered on mortgages and sometimes for a fee.

FV, PV and RATE: saving and borrowing

FV (future value) projects regular savings. If you put $400 a month for 5 years into a high-yield savings account paying 4.00% APY (an example rate), first convert the APY to a monthly rate:

=FV((1+4%)^(1/12)-1, 60, -400)     → 26,471.61

You contributed $24,000; the other $2,471.61 is compound interest (the compound interest lesson in our personal finance course explains why time matters so much).

PV (present value) asks the reverse question: if I can afford $500 a month for 36 months on a car loan at 12% APR (1% a month), how much can I borrow?

=PV(1%, 36, -500)                  → 15,053.75

And RATE exposes the real rate behind an offer: if a lender quotes 24 payments of $750 on a $15,000 loan, =RATE(24, -750, 15000) returns 1.513% a month, or an APR of about 18.16% (× 12). Now you can compare it with offers presented in completely different ways.

2. Which function tells you how much you can borrow if the most you can pay is $500 a month?

PV calculates today's value of a series of future payments: exactly the loan amount those payments can cover.

3. You type =RATE(24, 750, 15000) and Excel returns #NUM!. Why?

Because RATE only works with annual periods
Because the payment and the loan have the same sign: one of them must be negative
Because 24 payments are too many to calculate a rate

You receive $15,000 (positive) and pay $750 a month (negative). If both are positive, no rate can balance them and Excel finds no solution. =RATE(24, -750, 15000) returns 1.513% a month.

NPV and IRR: is the project worth it?

A print shop in Milwaukee is considering a $50,000 digital press that would generate cash flows of $15,000, $18,000, $20,000 and $22,000 over the next four years. Its cost of capital (the return it requires from its investments, sometimes called the hurdle rate) is 12%. With the investment in B2 (as -50000) and the cash flows in B3:B6:

NPV:  =NPV(12%, B3:B6) + B2        → 5,959.35
IRR:  =IRR(B2:B6)                  → 17.19%

NPV = $5,959.35How much value it creates, in today's dollars

Rule: Positive: creates value. Negative: destroys it.
Reading: After recovering the investment and earning the required 12%, $5,959.35 is left over.
Use it to: Choose between projects: the highest NPV wins.

Creates value

IRR = 17.19%The return it earns

Rule: If it beats the required rate, the project is worth it.
Reading: 17.19% > 12%: the project earns more than it's asked to.
Use it to: Explain the project with one easy-to-compare number.

Beats 12%

The NPV trap

Despite its name, Excel's NPV assumes the first cash flow happens at the end of period 1 and discounts it. If you include today's investment inside the range, you discount it by mistake: in this example you'd get $5,320.85 instead of $5,959.35. Add the time-0 investment outside the function: =NPV(rate, flows 1..n) + investment. IRR, on the other hand, takes every cash flow, including the investment.

If cash flows don't arrive at regular yearly intervals (for example, payments on specific dates), use XNPV and XIRR, which take a column of dates along with the amounts. This is the same logic behind a discounted cash flow valuation, covered in the business valuation lesson of our bookkeeping course.

4. A project has an IRR of 9% and the company requires 12%. What does that tell you?

It's worth it because the IRR is positive.
There's an error, because the IRR always beats the required rate.
It isn't worth it: it earns less than required, and its NPV at 12% will be negative.

IRR is compared with the cost of capital. If it's lower, discounting at 12% gives a negative NPV: the project doesn't pay what's required of it.

Common mistakes with financial functions

Using an annual rate with monthly periods: =PMT(18%, 24, -15000).

Rate and periods in the same unit: APR ÷ 12 for loans, (1 + APY)^(1/12) − 1 for savings.

Putting the initial investment inside NPV.

Add it outside: =NPV(rate, flows 1..n) + investment.

Calculating IRR on cash flows that are all positive.

IRR needs at least one negative flow (the investment); otherwise it returns #NUM!.

Comparing loans only by their interest rate.

Compare the APR, which by law includes certain fees, and the total cost of the loan. Your credit score drives the rate you're offered: see how to improve your credit score.

Practice exercise: compare two business loans

You need $20,000 for your business. Bank A offers 36 monthly payments at 22% APR. Lender B offers 24 payments of $1,050. Which is cheaper?

Calculate Bank A's payment (convert the APR to a monthly rate).
Calculate Lender B's monthly rate with RATE and its APR (× 12).
Calculate the total interest for each option.
Build Bank A's amortization schedule and check that the final balance is 0.

See solution

Bank A
Monthly rate  =22%/12                    → 1.8333%
Payment       =PMT(22%/12, 36, -20000)   → 763.81
Interest      =763.81*36-20000           → 7,497.16

Lender B
Monthly rate  =RATE(24, -1050, 20000)    → 1.9379%
APR           =1.9379%*12                → 23.26%
Interest      =1050*24-20000             → 5,200.00

Here's the catch: Lender B charges a higher rate (23.26% APR vs. 22%), yet you pay less total interest ($5,200 vs. $7,497.16) because the term is shorter. If you're comparing the price of money, Bank A is cheaper; if you can afford a $1,050 payment, finishing sooner saves interest. The best combination is Bank A's rate with a short term: ask Bank A for a 24-month loan (=PMT(22%/12, 24, -20000) → $1,037.56). That's a lower payment than Lender B and about $4,901 in total interest.

Summary

PMT calculates the payment; the amortization schedule splits interest (balance × rate) from principal.
Rate and periods in the same unit: APR ÷ 12 for loans; (1 + APY)^(1/12) − 1 for savings.
FV projects savings, PV tells you how much you can borrow and RATE reveals the real rate.
NPV = NPV(rate, flows 1..n) + initial investment; if it's positive, the project creates value.
Compare IRR with the required rate: if it's higher, the project is worth it.
With an extra payment, shortening the term usually saves more interest than lowering the payment; NPER tells you how many payments are left.

Next lesson: What-If Analysis in Excel: Goal Seek, Scenarios, Data Tables and Solver. You'll make Excel answer "what if" questions to find the best decision.