Financial formulas in Excel
Choose an Excel financial formula for loan payments, savings or discounted cash flows. Compare PMT, FV, PV and NPV with worked examples. PMT finds a constant payment; FV finds a future balance; PV values equal payments today; NPV handles different cash amounts at equally spaced dates. Start with the quantity you need, then match the interest rate to the payment interval. For monthly payments, divide an annual rate by 12 and multiply years by 12. The examples below state their timing and cash-flow signs so you can check the result before adapting the inputs.
The formula
=PMT(6%/12,3*12,-12000)365.06 per month=FV(5%/12,5*12,-150,0,0)10,200.91 after 60 monthly deposits=PV(5%,3,-1000,0,0)2,723.25 present value=NPV(8%,2000,2200,2400)-5000643.19 net present valueA worked example
For the PMT example, enter an annual rate of 6% in B2, a term of 3 years in B3 and a loan amount of 12,000 in B4. The rate stays fixed, payments fall at each month-end and the final balance is zero.
=PMT(B2/12,B3*12,-B4)
365.06 per month when displayed to two decimal places. Negating B4 makes the returned payment positive for this repayment display. With positive B4, PMT returns -365.06 instead: the same payment expressed as cash paid out. This amount covers principal and interest; fees, taxes and insurance are excluded.
Which one do I need?
| If you want to… | Use |
|---|---|
| Calculate the same loan payment every month | PMT: rate, payment count and loan amount |
| Find the balance after regular savings deposits | FV: future value with a fixed rate and payment |
| Value a series of equal future payments today | PV: present value with a fixed rate and payment |
| Discount different annual cash flows and an upfront cost | NPV: keep the time-zero cost outside the function |
| Compare a new amount with an old amount | Percentage change: (new-old)/old |
| Add amounts for a category and a second condition | SUMIFS: sum range first, then each criteria range and condition |
Frequently asked questions
When should I use PV instead of NPV?
Use PV when the recurring payment and rate stay constant. Use NPV for cash amounts that can differ but arrive at equal intervals. In the NPV example, 2,000, 2,200 and 2,400 arrive at the ends of years 1, 2 and 3. The 5,000 paid today is subtracted outside NPV. Cash flows on irregular calendar dates need a date-based function such as XNPV.
Why do financial formulas return a negative number?
Excel uses opposite signs for cash received and cash paid out. In the FV example, each 150 deposit is negative and the resulting savings balance is positive. In the PV example, three payments of -1,000 at year-end correspond to 2,723.25 received today at 5% per year. Choose a consistent perspective instead of removing every minus sign.
What changes when payments are made at the start of the month?
PMT, FV and PV use type=0, or an omitted type argument, for payments at the end of each period. Use type=1 for payments at the beginning. The examples use end-of-period payments; changing that argument changes the result. Their assumed rates stay fixed for every period.
Function references
New guides and tools, once a month
DE + EN · double opt-in · no spam