Loan present value
=PV(0.05/12, 360, -1000) estimates present value of a 30-year mortgage payment stream at 5% annual rate compounded monthly.
What is PV in Google Sheets?
PV returns the present value of an investment or loan given a constant interest rate and periodic payments. You pass rate per period, number of periods, payment amount, optional future value, and end-or-beginning timing. Finance models use PV to compare deals, price annuities, and sanity-check loan offers in Sheets.
When to use it
Use PV when comparing payment plans, valuing steady cash flows, or teaching time-value-of-money in a spreadsheet model with transparent inputs.
When to skip it
Skip PV for irregular cash flows, daily compounding edge cases, or anything requiring full DCF with varying rates. XNPV or a dedicated model may fit better.
How it works
-
1
Put rate per period in the same units as nper, for example monthly rate with monthly periods.
-
2
Set nper to total number of payment periods over the life of the loan or investment.
-
3
Pass pmt as the fixed payment per period. Use negative for cash out if your model treats outflows as negative.
-
4
Optional fv is the lump sum left after the last payment. Optional type is 0 for end of period, 1 for beginning.
-
5
Build an Inputs tab with rate, nper, pmt, and fv clearly labeled so stakeholders can stress-test scenarios.
-
6
Compare PV output against an online calculator once before trusting a client-facing proposal tab.
Examples in Google Sheets
Investment lump sum needed
=PV(0.08/12, 120, 0, -50000) finds how much to invest today to reach 50000 in ten years at 8% monthly compounding.
Annuity due
=PV(0.06/12, 24, -200, 0, 1) values payments at the beginning of each month instead of the end.
Build this without starting from a blank cell
Use a Better Sheets tool for PV, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Mixing annual rate with monthly nper without dividing rate by twelve.
- Inconsistent cash flow signs so PV returns the opposite sign you expect.
- Using PV for variable payments each month without building a custom NPV schedule.
- Showing PV outputs without documenting whether results are from borrower or lender perspective.
- Forgetting that empty fv defaults to zero when you meant to include a balloon payment.
Frequently asked questions
- PV vs NPV?
- PV assumes constant payments and rate. NPV handles a series of uneven cash flows at a discount rate.
- PV vs FV?
- PV finds today's value of future flows. FV finds what today's money grows into over time.
- Why is PV negative?
- Sign convention. Outflows and inflows must be opposite signs. Flip pmt sign if the result looks backward.
- PV for leases?
- Yes when payments are level and rate is known. Real leases may need extra fees in a custom schedule.
- PV with zero payments?
- Use pmt of 0 and set fv to the future lump sum you are discounting back to today.
- PV in dashboards?
- Common in CFO tabs paired with charts showing sensitivity to rate changes via manual scenario tables.
- PV and GOOGLEFINANCE?
- GOOGLEFINANCE pulls market rates. PV uses the rate you type into the model for planning scenarios.
Related Tutorials
Watch how PV works
Duplicate Everything For Each Unique Value
Create Navigation Like A Book or Presentation
Spreadsheet Automation 101 Lesson 2: Get Values - Introduction
Dylan Asks: How to Automatically Delete Rows If Cell Contains Value
Related blog posts
Guides that explain PV in more depth.
Simplifying Interest Gain Calculations with the EFFECT() Formula
This highlights how essential it is to have a dynamic and scalable tool that can adapt to real-world variables, such as changing interest rates and investment periods
Read post →Roasting Google Sheets
Dive into Google Sheets with Expert Insights. Free Tutorials, Templates & Tools Await You. Enhance Your Skills Today!
Read post →Related glossary terms
GOOGLEFINANCE
GOOGLEFINANCE connects your sheet to Google market data. Pass a ticker symbol, an attribute like price or volume, and optional date range arguments for historical quotes. Portfolio trackers and FX dashboards use it to avoid manual price copy-paste.
Read guide →budgets in Google Sheets
A budget sheet compares planned spending or revenue to actuals over time. Rows are categories, columns are months or quarters, and formulas SUM detail lines into subtotals. Variance columns show where you are over or under plan.
Read guide →Google Sheets formulas
A formula is an instruction in a cell that calculates a result from other cells, ranges, or built-in functions. Every formula begins with =. When inputs change, the sheet recalculates so dashboards and models stay live without manual copy-paste. Formulas are the default automation layer inside the grid before Apps Script or add-ons.
Read guide →Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Done reading about PV?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.