Excel financial functions
Financial functions answer loan and investment questions: what is the payment, what will this grow to, how long will it take, what rate am I really paying. Two habits prevent almost every wrong answer. Match the rate to the period (an annual rate divided by 12 for monthly payments), and keep signs consistent: money you pay out is negative, money you receive is positive.
- Functions
- 7
- With a graded exercise
- All 7
- Start with
PMT
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Find the payment on a loan or mortgage | PMT | Returns the fixed payment per period from the rate, number of payments and amount borrowed. |
| See what regular savings will grow to | FV | Compounds deposits and a starting balance forward to a future date. |
| Work out what a future amount is worth today | PV | Discounts future payments or a lump sum back to today. |
| Count how many payments it takes | NPER | Solves for the number of periods to pay off a debt or reach a goal. |
| Find the interest rate behind a deal | RATE | Solves for the rate per period when you know the payment and the amount. |
| Decide whether a project is worth it | NPV | Discounts a series of cash flows; pair it with IRR for the return. |
Try PMT live
The most useful financial function, on a sample sheet you can edit. Change a value and watch the results update.
D2=PMT(B3/12,B4*12,-B2) $489.15 Monthly payment: monthly rate and 60 monthly periodsD3=PMT(B3/12,B4*12,B2) -$489.15 Same payment as a negative: money going outD4=PMT(B3,B4,-B2) $6,015.86 Paying once a year instead: annual rate, 5 periods
All 7 financial functions
| Function | What it does | Practice exercise |
|---|---|---|
FV function | FV calculates what an investment will be worth in the future, given regular deposits, a starting balance and a constant rate of return. | Retirement savings |
IRR function | IRR calculates the internal rate of return: the discount rate at which a series of cash flows has a net present value of zero. | Internal rate of return |
NPER function | NPER calculates how many payment periods it takes to pay off a loan or reach a savings goal, at a fixed payment and interest rate. | Payoff timeline |
NPV function | NPV discounts a series of future cash flows back to today at a given rate, so you can judge whether an investment beats your cost of capital. | Net present value |
PMT function | PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments. | Monthly payment |
PV function | PV calculates what a series of future payments, or a single future amount, is worth today at a given interest rate. | Lump sum today |
RATE function | RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested. | What rate is this loan? |
Learn them in order
These functions are taught step by step in the Finance track. Each lesson ends with graded exercises on real-looking data.
- Loan payments with PMT9 min · Pro
- Future & present value8 min · Pro
- NPV & IRR10 min · Pro