FV & PV Time Value of Money Formulas
Learn how to solve time value of money equations in Excel with PV, FV, NPER, and RATE. Calculate retirement goals, loan tenors, and compounding interest cleanly.
Wealth planners, corporate treasurers, and mortgage underwriters model investments based on the Time Value of Money (TVM): a dollar in your hand today is worth more than a dollar promised in the future because today's dollar can earn interest.
Every compound interest problem connects five core variables. If you know any four of these variables, Excel solves for the fifth using its TVM formula quartet: PV, FV, NPER, and RATE.
Quick Comparison: The TVM Formula Quartet
| Formula | What It Solves For | Typical Question It Answers |
|---|---|---|
PV(rate, nper, pmt, [fv], [type]) |
Present Value | "How much money must I invest today to reach a future goal?" |
FV(rate, nper, pmt, [pv], [type]) |
Future Value | "If I save $500 monthly at 7% interest, what will my account hold in 15 years?" |
NPER(rate, pmt, pv, [fv], [type]) |
Number of Periods | "How many months will it take to pay off this debt?" |
RATE(nper, pmt, pv, [fv], [type]) |
Interest Rate Per Period | "What effective annual interest rate is this lender actually charging me?" |
The Balance Sheet Sign Rule
Always maintain consistent cash flow signs in your TVM formulas:
- Money moving away from you (deposits into savings or payments to a lender) is negative.
- Money coming toward you (loans received today or future withdrawals) is positive.
If you enter both PV and PMT as positive numbers when calculating NPER, Excel returns a #NUM! error because a loan with positive outflows can never be paid off.
Aligning Periods and Interest Rates
If your compounding occurs monthly, your rate must be monthly (Annual Rate / 12) and your periods must be in months (Years * 12).
Hands-on Practice Playground
Use the financial planning scenarios below to solve for future wealth, initial lump sums, and lease values. Follow each objective to complete the exercise.
In cell F2, calculate the future accumulated wealth for retirement using FV with monthly rate B2/12 and periods C2*12.
In cell F2, calculate the total future value accumulated for Retirement Accumulation using the FV formula: divide annual rate B2 by 12, multiply years C2 by 12 for total monthly periods, and reference monthly deposit D2.
In cell E3, calculate the required lump sum deposit today using PV.
In cell E3, calculate the initial lump sum deposit needed today to reach $100,000 in 10 years using the PV formula with annual rate B3 and years C3.
In cell E4, calculate the present value of monthly lease payments.
In cell E4, calculate the present value of monthly lease payments D4 over 5 years (C4*12 periods) at monthly interest rate B4/12 using PV.
Ready to apply FV & PV Time Value of Money Formulas in a real workbook?
Put professional financial formulas into practice inside an executive cash flow model.
Open 12-Month Cash Flow StatementShare This Lesson
Spread the word with your peers and challenge your friends