Excel Tutorial: How to Master the PV Function in Microsoft Excel
📘Part 1: Introduction
💡Definition
The PV Function calculates the present value of an investment based on constant payments and a constant interest rate.
🎯Purpose
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.
📝Remarks
- 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
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| Rate | Nper | Pmt | Fv | Type | Formula | Result | |
| 1 | 0.05 | 12 | -1200 | 0 | 0 | =PV(A1, B1, C1, D1, E1) | -$11,358.24 |
📚Explanation
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 📊
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| Rate | Nper | Pmt | Fv | Type | Formula | Result | |
| 1 | 0.03 | 24 | -1000 | 0 | 0 | =PV(A1, B1, C1, D1, E1) | -$23,116.18 |
| 2 | 0.03 | 24 | -1000 | 0 | 1 | =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 📊
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| Rate | Nper | Pmt | Fv | Type | Formula | Result | |
| 1 | 0.04 | 60 | -200 | 0 | 0 | =PV(A1, B1, C1, D1, E1) | -$9,302.78 |
| 2 | 0.04 | 60 | -200 | 0 | 1 | =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
- Type It Right: Make sure to specify the correct
Type(0 or 1) to reflect the payment schedule accurately. - Optional FV: If you don’t input an
FV, Excel assumes it’s zero.