PV Function in Excel

Excel Tutorial: How to Master the PV Function in Microsoft Excel

📘Part 1: Introduction


The PV Function calculates the present value of an investment based on constant payments and a constant interest rate.


It helps you determine how much future cash flows are worth in today’s value, assisting in investment analysis.

⌨️Syntax & Arguments

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

🗂Arguments Explained 

  • Rate: Annual interest rate for the investment.
  • Nper: The total number of payment periods.
  • Pmt: The payment made each period.
  • Fv: The future value remaining after the final payment (optional).
  • Type: When payments are due; 0 for end-of-period, 1 for beginning-of-period (optional).

📊Return Value 

This function will return the present value as a numerical figure.


  • The units for “Rate” and “Nper” should be consistent. For example, if “Rate” is monthly, then “Nper” should be in months.
  • Usually, outflows are represented as negative values, while inflows are positive.

Part 2: Examples

Example 1: Calculating Present Value for Lease Payments

🎯Purpose of Example 

To evaluate whether leasing a new office space is a good financial decision.

📊 Data Sheet and Formulas 

10.0512-120000=PV(A1, B1, C1, D1, E1)-$11,358.24


The formula considers an annual interest rate of 5%, 12 payment periods (one per month), and monthly payments of $1,200.

Example 2: Early Investment Savings

Purpose of Example 🎯

To compare the present value of a savings plan with monthly deposits made at the beginning vs. the end of the period.

Data Sheet and Formulas 📊

10.0324-100000=PV(A1, B1, C1, D1, E1)-$23,116.18
20.0324-100001=PV(A2, B2, C2, D2, E2)-$23,391.29

Explanation 📚

This example shows how early investment savings yield a higher present value ($-23,391.29) when made at the beginning of the period (Type 1) compared to the end of the period (Type 0, $-23,116.18).

Example 3: Loan Repayment Plans

Purpose of Example 🎯

To analyze the present value of two different loan repayment plans.

Data Sheet and Formulas 📊

10.0460-20000=PV(A1, B1, C1, D1, E1)-$9,302.78
20.0460-20001=PV(A2, B2, C2, D2, E2)-$9,427.28

Explanation 📚

The example illustrates how paying at the beginning of the period (Type 1, $-9,427.28) gives a higher present value than paying at the end of the period (Type 0, $-9,302.78).

Part 3: Tips and Tricks

  1. Type It Right: Make sure to specify the correct Type (0 or 1) to reflect the payment schedule accurately.
  2. Optional FV: If you don’t input an FV, Excel assumes it’s zero.

Leave a Comment