Excel has a built-in function for each time value of money (TVM) calculation: =FV for future value, =PV for present value, =NPV and =IRR for project cash flows, and =PMT, =NPER, and =RATE for annuities and loans. Each takes a rate per period, a number of periods, a payment, and a present or future value, with cash outflows entered as negative numbers. The principle behind all of them: a dollar today is worth more than a dollar later because it can earn a return. This guide gives the syntax and a worked example for each function.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
What Is the Time Value of Money?
The Time Value of Money (TVM) is a financial concept that states money available today is worth more than the same amount in the future. This is because money can be invested to earn a return. The two primary drivers of TVM are:
- Opportunity cost: Money received today can be invested immediately to generate returns.
- Inflation: The purchasing power of money typically decreases over time as prices rise.
Understanding TVM is essential for:
- Comparing investment opportunities with different time horizons
- Pricing bonds, stocks, and other financial instruments
- Making capital budgeting decisions
- Planning for retirement and other financial goals
- Evaluating loan terms and mortgage options
Simple Interest vs. Compound Interest
TVM calculations rest on the difference between simple and compound interest.
Simple Interest
Simple interest is calculated only on the original principal amount. The formula is:
Simple Interest = Principal × Rate × Time
For example, $1,000 invested at 5% simple interest for 3 years earns:
- Year 1: $1,000 × 0.05 = $50
- Year 2: $1,000 × 0.05 = $50
- Year 3: $1,000 × 0.05 = $50
- Total interest: $150 | Ending balance: $1,150
Compound Interest
Compound interest is calculated on the principal plus all accumulated interest. This is the basis for most real-world financial calculations.
Compound Amount = Principal × (1 + Rate/n)^(n × Time)
Where n = number of compounding periods per year.
The same $1,000 at 5% compound interest (annually) for 3 years:
- Year 1: $1,000 × 1.05 = $1,050.00
- Year 2: $1,050 × 1.05 = $1,102.50
- Year 3: $1,102.50 × 1.05 = $1,157.63
- Total interest: $157.63 | Ending balance: $1,157.63
The extra $7.63 comes from earning interest on previously earned interest.
Effect of Compounding Frequency
The more frequently interest compounds, the greater the effective return:
| Compounding Frequency | Formula Adjustment | $1,000 at 10% for 1 Year |
|---|---|---|
| Annually (n=1) | Rate/1, periods × 1 | $1,100.00 |
| Semi-annually (n=2) | Rate/2, periods × 2 | $1,102.50 |
| Quarterly (n=4) | Rate/4, periods × 4 | $1,103.81 |
| Monthly (n=12) | Rate/12, periods × 12 | $1,104.71 |
| Daily (n=365) | Rate/365, periods × 365 | $1,105.16 |
| Continuous | Pe^(rt) | $1,105.17 |
Effective Annual Rate (EAR)
To compare investments with different compounding frequencies, convert to the Effective Annual Rate:
EAR = (1 + Nominal Rate / n)^n − 1
In Excel:
=EFFECT(0.10, 4) // 10% nominal, compounded quarterly → 10.38% EAR
The reverse, converting EAR to a nominal rate:
=NOMINAL(0.1038, 4) // 10.38% effective → ~10% nominal quarterly
Future Value (FV) in Excel
Future Value calculates what a present sum of money will be worth at a future date, given a specific interest rate.
Mathematical formula: FV = PV × (1 + r)^n
Excel FV Function
=FV(rate, nper, pmt, [pv], [type])
Parameters:
- rate: Interest rate per period
- nper: Total number of payment periods
- pmt: Payment made each period (0 for lump sum)
- pv: Present value (initial investment, entered as negative)
- type: 0 = end of period (default), 1 = beginning of period
Example 1: Lump Sum Investment
You invest $10,000 today at 7% annual interest for 10 years. What will it be worth?
=FV(0.07, 10, 0, -10000)
Result: $19,671.51
Example 2: Monthly Savings
You save $500 per month for 20 years at 8% annual return. How much will you accumulate?
=FV(0.08/12, 20*12, -500, 0)
Result: $294,510.21
Example 3: Lump Sum Plus Regular Contributions
You invest $5,000 today and add $200 per month for 15 years at 6% annual return.
=FV(0.06/12, 15*12, -200, -5000)
Result: $70,434.21
Present Value (PV) in Excel
Present Value calculates what a future sum of money is worth today, discounted at a given rate. It answers: "How much would I need to invest today to have X dollars in the future?"
Mathematical formula: PV = FV / (1 + r)^n
Excel PV Function
=PV(rate, nper, pmt, [fv], [type])
Example 1: Lump Sum Discounting
You need $50,000 in 8 years. How much must you invest today at 5% annual return?
=PV(0.05, 8, 0, -50000)
Result: $33,841.97
Example 2: Valuing a Stream of Payments
You will receive $1,000 per month for 5 years. What is the present value at a 6% discount rate?
=PV(0.06/12, 5*12, -1000, 0)
Result: $51,725.56
Example 3: Bond Pricing
A bond pays $40 semi-annually (8% coupon on $1,000 face) and matures in 10 years. If the market rate is 10%, what is the bond worth?
=PV(0.10/2, 10*2, -40, -1000)
Result: $875.38
The bond trades at a discount because the coupon rate (8%) is below the market rate (10%).
Net Present Value (NPV) in Excel
Net Present Value calculates the total present value of a series of future cash flows minus the initial investment. It is the primary tool for capital budgeting decisions.
Decision rule: NPV > 0 → Accept the project; NPV < 0 → Reject the project
Excel NPV Function
=NPV(rate, value1, value2, ...)
Important: Excel's NPV function assumes cash flows start at the end of period 1. To include an initial investment at time 0, add it separately.
Example: Project Evaluation
A project requires a $100,000 initial investment and generates the following cash flows:
| Year | Cash Flow |
|---|---|
| 0 | -$100,000 |
| 1 | $25,000 |
| 2 | $35,000 |
| 3 | $40,000 |
| 4 | $30,000 |
| 5 | $20,000 |
At a 10% discount rate:
=NPV(0.10, 25000, 35000, 40000, 30000, 20000) + (-100000)
Result: $14,614.31
Since NPV is positive, the project creates value and should be accepted (from a financial perspective).
NPV with Varying Discount Rates
For projects where the discount rate changes over time, you can calculate PV for each cash flow individually:
=CF1/(1+r1)^1 + CF2/((1+r1)*(1+r2)) + ...
Internal Rate of Return (IRR) in Excel
The Internal Rate of Return is the discount rate that makes the NPV of a series of cash flows equal to zero. It represents the project's expected annual rate of return.
Decision rule: IRR > Required Rate of Return → Accept; IRR < Required Rate → Reject
Excel IRR Function
=IRR(values, [guess])
Example: Same Project
Using the cash flows from the NPV example above, set them up in cells A1:A6:
| Cell | Value |
|---|---|
| A1 | -100000 |
| A2 | 25000 |
| A3 | 35000 |
| A4 | 40000 |
| A5 | 30000 |
| A6 | 20000 |
=IRR(A1:A6)
Result: 15.69%
Since 15.69% > 10% (our required return), the IRR agrees with the positive NPV.
Modified Internal Rate of Return (MIRR)
IRR assumes reinvestment at the IRR itself, which may not be realistic. MIRR allows you to specify separate finance and reinvestment rates:
=MIRR(values, finance_rate, reinvest_rate)
=MIRR(A1:A6, 0.10, 0.08)
Result: 12.12%
This is often a more conservative and realistic measure.
XIRR for Irregular Cash Flows
When cash flows do not occur at regular intervals, use XIRR:
=XIRR(values, dates, [guess])
This is particularly useful for real-world investments where payments and receipts occur on specific dates rather than at regular intervals.
Annuity Calculations in Excel
An annuity is a series of equal payments at regular intervals. There are two types:
Ordinary Annuity (Payments at End of Period)
Most loans, bonds, and savings plans use ordinary annuities.
Annuity Due (Payments at Beginning of Period)
Rent payments and insurance premiums are typically annuities due.
Annuity Payment (PMT) Function
=PMT(rate, nper, pv, [fv], [type])
Example 1: Loan Payment
You take a $250,000 mortgage at 4.5% for 30 years. What is the monthly payment?
=PMT(0.045/12, 30*12, -250000, 0)
Result: $1,266.71 per month
Example 2: Savings Goal
You want $1,000,000 in 25 years and can earn 7% annually. How much must you save per month?
=PMT(0.07/12, 25*12, 0, -1000000)
Result: $1,234.46 per month
Example 3: Annuity Due
Same mortgage as above, but payments are due at the beginning of each month:
=PMT(0.045/12, 30*12, -250000, 0, 1)
Result: $1,261.96 per month (slightly less because each payment earns one extra month of "credit")
NPER Function: How Many Periods?
How long to pay off a $20,000 car loan at 5% with $400/month payments?
=NPER(0.05/12, -400, 20000, 0)
Result: 56.2 months (about 4.7 years)
RATE Function: What Interest Rate?
You invest $10,000 and it grows to $25,000 in 10 years. What was the annual return?
=RATE(10, 0, -10000, 25000)
Result: 9.60%
Comparison of TVM Excel Functions
| Function | Purpose | Key Inputs | Output | Common Use Case |
|---|---|---|---|---|
| FV | Future Value | rate, nper, pmt, pv | Future amount | Retirement savings projection |
| PV | Present Value | rate, nper, pmt, fv | Today's value | Bond pricing, investment valuation |
| NPV | Net Present Value | rate, cash flows | Net value created | Capital budgeting, project evaluation |
| IRR | Internal Rate of Return | cash flows | Percentage return | Comparing investment alternatives |
| MIRR | Modified IRR | cash flows, rates | Adjusted return | Realistic reinvestment scenarios |
| PMT | Payment Amount | rate, nper, pv, fv | Periodic payment | Loan payments, savings goals |
| NPER | Number of Periods | rate, pmt, pv, fv | Period count | Payoff timeline planning |
| RATE | Interest Rate | nper, pmt, pv, fv | Rate per period | Determining investment returns |
| XIRR | IRR for irregular dates | values, dates | Percentage return | Real-world investment returns |
| XNPV | NPV for irregular dates | rate, values, dates | Net value | Irregular cash flow projects |
Real-World Applications of TVM in Excel
Retirement Planning
One of the most common applications of TVM is retirement planning. Here is a complete example:
Scenario: You are 30 years old, want to retire at 65, and need $2,000,000.
Step 1: How much to save monthly assuming 7% annual return?
=PMT(0.07/12, 35*12, 0, -2000000)
Result: $1,110.46/month
Step 2: What if you already have $50,000 saved?
=PMT(0.07/12, 35*12, 50000, -2000000)
Result: $791.03/month (the $50,000 head start, compounding for 35 years, cuts the required saving by about $319/month)
Step 3: What monthly income will $2,000,000 provide if you spend it down over 20 years while it earns 5%?
=PMT(0.05/12, 20*12, -2000000, 0)
Result: $13,199.11/month
Real Estate Investment Analysis
TVM formulas help evaluate rental properties:
Purchase price: $300,000 with $60,000 down payment Mortgage: $240,000 at 5% for 30 years Expected annual cash flows after expenses: $12,000 for years 1-5, selling at $400,000 in year 5
// Monthly mortgage payment
=PMT(0.05/12, 30*12, -240000) // $1,288.37
// NPV of the investment (10% required return)
=NPV(0.10, 12000, 12000, 12000, 12000, 412000) + (-60000)
Comparing Lease vs. Buy
TVM is essential for lease-vs-buy decisions. Calculate the PV of lease payments and compare to the PV of purchase costs:
// PV of 36 monthly lease payments of $450 at 6% discount rate
=PV(0.06/12, 36, -450) // $14,791.96
// Compare to purchase price minus expected residual value
Integrating TVM with MarketXLS
Excel's built-in functions do the TVM math; the MarketXLS add-in supplies market data (prices, history, and fundamentals) to use as inputs.
Using Real Stock Returns in TVM Calculations
Instead of assuming a return rate, use actual historical data:
=GetHistory("AAPL", "2019-01-01", "2024-01-01", "Daily")
Formula documentation: GetHistory
This pulls historical price data that you can use to calculate actual returns and use those as inputs for your FV and PV projections.
Current Price Data for Valuations
=Last("AAPL")
Formula documentation: Last
Use the current market price as a starting point for forward-looking TVM analysis, such as projecting future stock values based on historical growth rates.
Fundamental Data for DCF Models
Discounted Cash Flow (DCF) analysis is TVM applied to company valuation. Use MarketXLS functions to pull revenue and earnings data:
=hf_revenue("AAPL", 2024, 2) // Revenue for specific period
=Revenue("AAPL") // Annual revenue
=PERatio("AAPL") // Price-to-earnings ratio
=MarketCapitalization("AAPL") // Current market cap
Formula documentation: hf_revenue, Revenue, PERatio, MarketCapitalization
Then apply NPV to projected future cash flows to estimate intrinsic value.
Building a DCF Template with MarketXLS
- Pull current financials using
=Revenue(),=hf_revenue(), and=Last() - Project future cash flows using growth rate assumptions
- Calculate terminal value using the perpetuity growth model
- Discount all cash flows back to present using
=NPV()or individual PV calculations - Compare intrinsic value to
=Last("TICKER")to assess whether the stock is over or undervalued
Common TVM Mistakes to Avoid
-
Mismatching rate and period: If payments are monthly, the rate must be monthly too (annual rate / 12). This is the most common TVM error.
-
Forgetting the sign convention: In Excel TVM functions, cash outflows are negative and inflows are positive. If you forget to make the initial investment negative, you will get incorrect results.
-
NPV timing error: Excel's NPV function discounts the first value by one period. If your first cash flow is at time 0, add it separately outside the NPV function.
-
Ignoring inflation: Nominal returns include inflation; real returns do not. For long-term planning, use real rates (nominal rate minus inflation) for more accurate projections.
-
Using IRR blindly: IRR can give multiple solutions when cash flows change sign more than once. Always cross-check with NPV.
-
Confusing EAR and nominal rate: When comparing investments, make sure you are comparing effective annual rates, not nominal rates with different compounding frequencies.
Frequently Asked Questions
What is the Time Value of Money in Excel?
Time Value of Money in Excel refers to using built-in financial functions like PV, FV, NPV, IRR, and PMT to calculate the present and future values of money based on interest rates and time periods. Excel provides a complete suite of TVM functions that allow you to perform complex financial calculations without manual formulas. These functions are essential for investment analysis, loan calculations, retirement planning, and business valuation.
How do I calculate Present Value in Excel?
To calculate Present Value in Excel, use the PV function: =PV(rate, nper, pmt, fv, type). For a lump sum, set pmt to 0 and enter the future value. For example, to find the present value of $10,000 due in 5 years at 6% annual interest: =PV(0.06, 5, 0, -10000) which returns $7,472.58. For a series of payments, enter the payment amount in the pmt argument.
What is the difference between NPV and IRR?
NPV (Net Present Value) tells you the dollar amount of value created by an investment at a given discount rate; if NPV is positive, the investment creates value. IRR (Internal Rate of Return) tells you the percentage return at which NPV equals zero. NPV is generally preferred because it accounts for the scale of investment and uses a realistic discount rate. IRR is useful for quick comparison but can be misleading when cash flows change direction multiple times.
How do I calculate a loan payment in Excel?
Use the PMT function: =PMT(rate, nper, pv, fv, type). For a $200,000 mortgage at 5% for 30 years: =PMT(0.05/12, 360, -200000) returns $1,073.64 per month. The rate must match the payment frequency, so divide the annual rate by 12 for monthly payments. Set type to 1 for payments at the beginning of each period (annuity due).
Can I use TVM formulas with real market data?
Yes. MarketXLS allows you to pull real market data directly into Excel. Use =Last("TICKER") for current prices, =GetHistory("TICKER", startDate, endDate, periodicity) for historical data, and =Revenue("TICKER") or =hf_revenue("TICKER", year, quarter) for fundamental data. You can then use this real data as inputs to Excel's TVM functions for more accurate investment analysis and DCF valuations.
What is an annuity and how do I calculate it in Excel?
An annuity is a series of equal payments at regular intervals. An ordinary annuity has payments at the end of each period (most loans), while an annuity due has payments at the beginning (most leases). In Excel, use PMT to calculate payment amounts, PV to find the present value of an annuity stream, and FV to find the future value. Set the type parameter to 0 for ordinary annuity or 1 for annuity due.
Getting Started with Financial Analysis in Excel
Time Value of Money calculations are the foundation of financial analysis, and Excel makes them accessible to everyone. PV, FV, NPV, IRR, and PMT cover most investment, retirement, and project decisions.
To feed market data into TVM models, see MarketXLS pricing and plans. MarketXLS adds functions for stock prices (15-minute delayed on the Standard plan, real-time on Advanced and Business), historical data, and fundamentals to Excel. Visit marketxls.com to learn more.
Disclaimer
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made.
