Excel PV Function

last modified April 4, 2025

The PV function calculates the present value of an investment or loan. It’s essential for financial analysis, helping determine current worth of future cash flows. This tutorial provides a comprehensive guide to using the PV function with detailed examples. You’ll learn basic syntax, practical applications, and advanced techniques to master this financial function.

PV Function Basics

The PV function calculates the present value of an investment based on constant payments and interest rate. It’s used for loans, annuities, and other financial calculations. The syntax includes rate, periods, payment, future value, and type.

Component
Description


Function Name
PV


Syntax
=PV(rate, nper, pmt, [fv], [type])


rate
Interest rate per period


nper
Total number of payment periods


pmt
Payment amount per period


fv
Optional future value (default 0)


type
When payments are due (0=end, 1=beginning)

This table breaks down the essential components of the PV function. It shows the function name, syntax format, and detailed descriptions of each argument. Understanding these parameters is crucial for accurate calculations.

Basic PV Example - Loan Calculation

This example demonstrates calculating the present value of a loan with fixed payments. We’ll determine how much can be borrowed based on payment capacity.

Parameter
Value


Annual Interest Rate
5%


Loan Term (Years)
10


Monthly Payment
$1,000


Present Value
=PV(5%/12, 10*12, -1000)

The table shows loan parameters and the PV formula to calculate maximum borrowable amount. Note the negative payment value representing cash outflow.

Basic PV formula for loan

=PV(5%/12, 10*12, -1000)

This formula calculates how much you can borrow with $1,000 monthly payments over 10 years at 5% annual interest. The result is approximately $94,281. Monthly rate is annual rate divided by 12, and periods are years multiplied by 12.

PV Example - Retirement Savings Goal

This example shows how to calculate the lump sum needed today to meet a future retirement goal with regular withdrawals.

Parameter
Value


Annual Return
6%


Retirement Duration
20 years


Annual Withdrawal
$50,000


Required Lump Sum
=PV(6%, 20, 50000, , 1)

The table illustrates retirement planning parameters. The PV function calculates the present value needed to support $50,000 annual withdrawals for 20 years. Type is 1 as withdrawals occur at period beginnings.

PV for retirement planning

=PV(6%, 20, 50000, , 1)

This formula returns approximately $607,906, the amount needed today to fund 20 years of $50,000 annual withdrawals starting immediately. The empty argument before type skips future value (default 0). Payments are positive as they represent cash inflows to the retiree.

PV Example - Comparing Investment Options

This example compares two investment options by calculating their present values. It helps determine which option provides better value today.

Option
Annual Return
Term
Future Value
Present Value


A
7%
5 years
$100,000
=PV(7%, 5, 0, -100000)


B
5%
5 years
$100,000
=PV(5%, 5, 0, -100000)

The table compares two investments both promising $100,000 in 5 years but with different returns. PV calculations show how much each is worth today, enabling direct comparison.

PV for investment comparison

=PV(7%, 5, 0, -100000) =PV(5%, 5, 0, -100000)

The first formula returns $71,299 (Option A) and the second $78,353 (Option B). Despite same future value, Option A is worth less today because its higher return means you’d need to invest less to reach the same goal.

PV Example - Lease Evaluation

This example evaluates a lease agreement by calculating the present value of lease payments. It helps determine if leasing is better than buying.

Parameter
Value


Monthly Payment
$800


Lease Term
3 years


Discount Rate
4% annual


Residual Value
$5,000


Present Value
=PV(4%/12, 36, -800, 5000)

The table shows lease terms including monthly payments, term length, discount rate, and residual value. The PV function calculates the equivalent cash value today of all lease obligations and benefits.

PV for lease evaluation

=PV(4%/12, 36, -800, 5000)

This formula returns $30,309, representing today’s value of 36 $800 payments plus $5,000 residual value at 4% annual discount rate. Negative payment represents cash outflow, while positive residual value is cash inflow at end.

PV Example - Annuity Purchase Decision

This example helps decide whether to purchase an annuity by calculating the present value of its payments compared to its cost.

Parameter
Value


Annual Payment
$10,000


Payment Period
15 years


Discount Rate
5%


Annuity Cost
$100,000


PV of Payments
=PV(5%, 15, 10000)

The table compares an annuity’s cost to the present value of its payments. If PV exceeds cost, the annuity may be worthwhile. Payments are positive as they represent cash inflows to the purchaser.

PV for annuity evaluation

=PV(5%, 15, 10000)

This formula returns $103,796, the present value of 15 annual $10,000 payments at 5% discount rate. Since this exceeds the $100,000 cost, the annuity appears financially attractive based on these assumptions.

The PV function is powerful for financial decision-making. From loan analysis to investment comparisons, it helps evaluate time value of money. Remember that cash outflows (payments) should be negative and inflows positive. Accurate rate and period matching (annual vs. monthly) is crucial for correct results.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.