PV: Value Your Money Now

Understanding the concept of present value can be one element in our financial decision making process. From evaluating loans and subscriptions to complex financial instruments like reverse mortgages, Excel’s PV function provides an accessible, effective tool for comparing future payments in today’s dollars. In this post, we’ll explore how to use the Excel PV function through three examples, building your competence and confidence step-by-step.

Video

What Is the Excel PV Function?

The PV (Present Value) function calculates the current worth of a stream of future cash flows, assuming a constant interest rate. This is valuable when we compare multiple financial choices that involve periodic payments. Excel’s syntax for this function is:

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

• rate – Interest rate per period
• nper – Total number of payment periods
• pmt – Amount paid each period
• fv – (Optional) Future value or a cash balance you want after the last payment
• type – (Optional) Payment type: 0 for end of period (default), 1 for beginning

Let’s walk through three practical exercises together using this function.

Exercise 1: Comparing Subscription Payment Options

Let’s assume we’re signing up for a gym membership and are offered two plans:

• Option A: Pay $1,000 up front for the year.
• Option B: Pay $100 per month for 12 months.

So, which option is ‘better?’ Well, there are many factors here, including do we have $1,000 now, and if we did, would we want to invest it in a mutual fund or buy this membership? Well, let’s set aside all of the other economic factors here such as current bank account balance, inflation and opportunity costs. Let’s focus on which of these two options is ‘cheaper.’ Both provide the same benefit, a year of gym access. But which one is cheaper. The problem is, it can be instinctively difficult to compare these two because they have different terms.

For example, we know how much cash we would need today for Option A … $1,000. But, Option B is paid out over time, so, what is the value of that in today’s dollars? Let’s see if we can break that down.

At face value, Option B totals $1,200 ($100 × 12). But, $1,200 over time is not really the same as $1,200 today. Our intuition may tell us that $1,200 today is more valuable than $1,200 over time. Indeed, our instincts tell us that we’d probably be be willing to accept less than $1,200 if we could get it all today. That is, there is a ‘discount’ we’d be willing to accept if we could get all of the money now. One of the elements at play here is interest. The good news is that Excel has a function that can help us determine the current (present) value of a series of future payments while factoring in a specific interest rate. It is called the present value (PV) function.

The PV function helps us compute the present value of a series of future cash flows, assuming a steady interest rate. We can use it to compute the present value of the monthly payment plan. We can then compare it to the up-front option.

Step-by-Step in Excel

In a new worksheet, we’ll set up some input cells for our payment, rate, and term variables:

We enter our values like the interest rate (5%), number of years (1), and monthly payment amount ($100).

We can the write the following formula in C16:

=PV(C13/12,C14*12,C12)

Excel returns $1,168.62. This is saying the present value of a future series of cash flows of $100/month, for 12 months, at 5% interest, is $1,168.62. Now that we have that, we can compare this to Option A’s $1,000 one-time payment. This comparison helps us make a more informed decision about our options.

Exercise 2: Adding a Future Value—Lease Buyout

Imagine we’re leasing a vehicle under these terms:

• Monthly payment: $100
• Lease term: 12 months
• Final buyout (future value): $200
• Annual interest rate: 5%

First, we set up our input cells like this:

To find the lease’s present value, including the final buyout cost, we use this formula:

=PV(C13/12,C14*12,C12,C15)

This returns $1,358.39. Now we’ve translated the series of payments and final lump sum into an equivalent one-time cost today (present value).

Exercise 3: Evaluating Reverse Mortgage Offers

Here’s the scenario. We are 75 years old and own our home free and clear. We need a little extra money each month and we do not want to sell our house. We also don’t love the idea of borrowing a large chunk of money now and having the dreaded feeling of an ongoing monthly mortgage payment to the bank.

But, we heard about a ‘reverse mortgage’ and are interested in learning more. So, we talk to a couple banks and they explain that basically, they loan money in a series of future monthly payments to us. They do charge interest, but only on the amount they have distributed to us each month. The loan is secured by the house. And they will wait until the house is sold through our estate to settle up.

So, we receive two competing reverse mortgage offers from two banks.

• Bank A: $1,000/month for 10 years at 5%
• Bank B: $1,100/month for 10 years at 6%

There are obviously several factors at play here, including the monthly payment amount, inflation, our overall estate, our heirs, our monthly expenses, other income, taxes, bank charges, origination fees, opportunity costs, and more. But, if we wanted a simple way to compare these two options in a consistent way, we could use the PV function. It would provide the present value of each option.

Step-by-Step Comparison

We set up our input cells as follows:

Then we use the PV function for Bank A in C13 as follows:

=PV(C10/12,C11*12,C9)

We fill the formula right into D13 for Bank B.

This shows that, in today’s dollars, Bank B’s offer is worth more — about $5,000 more. Of course, this doesn’t consider macroeconomic factors or personal financial situations, but it’s a solid analytical starting point.

Wrapping Up the PV Function

The PV function is a powerful Excel tool. Whether we’re comparing subscription plans, evaluating leases, or negotiating complex financial contracts, present value helps us make smarter, more informed decisions.

As always, consistency is key: the rate, number of payments, and cash flows all need to reflect the same time periods — monthly, annually, etc.

This educational breakdown is designed to build practical experience with a feature that has real-world applications. The more we practice, the more comfortable these analysis techniques become.

Let’s keep building those Excel muscles — because powerful decisions are built on strong data analysis skills. See you in the next tutorial at Excel University!

Legal Disclaimer

The content in this post is provided for educational purposes only and is not intended to be financial advice. The explanation of Excel’s PV function is meant to enhance your understanding of the tool and its applications. Always consult a qualified financial professional before making any investment or financial decisions. We are not responsible for any actions taken based on the information presented.

Download the Practice File

Frequently Asked Questions

• Q: What does the Present Value (PV) function in Excel do?
  A: The PV function calculates the current worth of a series of future cash flows, such as loan payments or investment returns, based on a specified interest rate.
• Q: How do I use the PV function to compare different payment options?
  A: You can input the interest rate, number of periods, and recurring payment amount into the PV function to calculate the present value of each option, allowing for a direct comparison in today’s dollars.
• Q: Why is the result of the PV function sometimes negative?
  A: The result is negative because Excel follows a cash flow convention where outgoing payments (like an investment or purchase) are shown as negative, and incoming cash flows (like loan payments received) are positive.
• Q: What’s important to keep consistent when using time-based arguments in the PV function?
  A: The interest rate, number of periods, and payment frequency must all align—for example, if using monthly payments, convert the annual interest rate to a monthly rate and use the number of months as periods.
• Q: How can I include a future value (FV) or balloon payment in the PV calculation?
  A: Use the optional fourth argument in the PV function to input the future value. This is useful for scenarios like a car lease with a buyout payment at the end.
• Q: Can I use the PV function to evaluate reverse mortgage offers?
  A: Yes, by calculating the present value of each bank’s monthly payment offer, you can determine which proposal is more valuable in today’s dollars.
• Q: How do changes in the interest rate affect the PV calculation?
  A: A higher interest rate decreases the present value of future cash flows, while a lower rate increases it. This is because money today is considered more valuable than money in the future.
• Q: Is using the PV function in Excel considered financial advice?
  A: No, the use of the PV function in this context is strictly educational. It helps understand value comparisons but doesn’t replace personalized financial advice.
• Q: What’s a real-life example where the PV function is useful?
  A: Comparing subscription plans with upfront vs. monthly payments, evaluating lease buyout options, or analyzing reverse mortgage offers.
• Q: How can I check if my PV result is realistic?
  A: One way is to temporarily set the interest rate to 0%. The PV should then equal the total sum of the payments, providing a good sanity check.