PV Function

The PV function in Excel calculates the present value of an investment or loan based on its future value.

The importance of the present value of an investment or a loan stems from a critical concept in finance called the ‘time value of money.’

It is said that a dollar that you have today is far more valuable than a dollar tomorrow.

There lies an uncertainty with respect to the money that we might receive tomorrow despite being larger in value than a certain amount of money we will receive today.

However, sometimes the financial deals offered for the future might look lucrative, which makes it essential to evaluate them.

You can calculate the future cash flow and compare them to different investments or financial opportunities regarding their current worth.

Ultimately, the calculation of present value helps in making better decisions in terms of investments and allocating the money to the asset portfolio.

What is the PV function?

The PV is categorized as a financial function that finds the present value of an investment or loan based on its future value.

The PV can be calculated as illustrated below:

There are three parameters that you might need to focus on: 

1. Future cash flows (FV) - the amount of money expected to be received or paid out in the future. These future cash flows can be either a lump sum, a series of payments, or a combination of both.
2. Discount rate (R) - the rate of return used to discount the future cash flows to its present value. 
3. Time period (T) - the length of time for which future cash flows are expected to occur.

Not surprisingly, the syntax for the function is based on this three-parameter.

The syntax for the PV function is:

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

where

• rate: (required) the interest rate for the time period. The rate may vary depending on whether it is annual, quarterly, or monthly.
• nper: (required) the total number of payments made in the time period
• pmt: (required) the payments made in each period. Usually, the pmt is expressed as a negative number. If pmt argument is included, fv can be skipped.
• fv: (optional) the future value of the investment. It is expressed as a negative number. If the fv argument is included, pmt can be skipped.
• type: (optional) defines when the payments are made. It can accept two values - 0 (payments are disbursed at the end of the time period) and 1 (payments are disbursed at the beginning of the time period)

Example of the Pv Function

Finally, let's use the PV function. Although it's not rocket science, you still need to overlook a few things, or else it might just lead to contrasting results.

First, we will see a simple example to understand how the function works.

Suppose this stock is expected to give $500 in two years. The discount rate on the stock is 5%. What is its present value?

The data looks as illustrated below:

We will use the formula =PV(C3,C4,,-C2,0) in cell C6, giving the result $453.51. Thus, the present value of the stock would be $453.51. The stock is expected to appreciate by $46.49 in two years.

Would you take the bet? This is an entirely different question that can be answered based on your risk profile and investment appetite.

Suppose there was another stock Microsoft Inc that's giving you the gains of $100 on the same investment amount of $500 for two years; then it becomes a better alternative than the former.

Different ways to use the PV function

As iterated earlier, the PV function allows you to input lumpsum payment, monthly payment, or a combination of both.

Based on these inputs, you can easily manipulate values and find the present investment value accordingly.

This section will guide you on how different payment scenarios change the present value of the loan or investment accordingly.

a. PV for a lump sum + monthly payments.

Assume that the monthly payments of $100 will create a corpus of $2000 by compounding annually at 6% after two years. Then, by summarizing the data in Excel, we get the following:

Since the payments are made monthly, we need to return the rate and time period into monthly counterparts.

The formula becomes =PV(C4/12,C5*12,C2,C3,0), which gives the present value of the lumpsum & monthly payments are $1615.81.

Another assumption that we have made is that the payments are disbursed at the end of the period using the value of 0 for the type argument.

b. PV for lumpsum

Calculating the present value from any lumpsum value is easy. When we speak of lumpsum amount, it means the future value of an investment or loan.

Suppose we have the data as illustrated below:

Since monthly payments are not involved, we will keep the rate and period in their annualized counterparts.

The formula becomes =PV(C3,C4,,C2,0) in cell C6 which gives the result as $1,679.24.

c. PV for monthly payments

For calculating the present value using only monthly payments, we have to keep the lumpsum amount, i.e., the future value, which will ultimately give us the present value for those payments.

The data looks as illustrated below:

Since we are talking about the monthly payments, we need to use the monthly rates and the time period.

The formula becomes =PV(C3/12,C4*12,C2,,0) to give the result as $3,287.10. Thus an investment wherein we are making $100 for three years and growing at a 6% compounded annual growth rate will have a present value of $3,287.10.

Practical Example of the PV Function

One of the ways to use the present value in investment decisions is while calculating the NPV of an investment based on its future cash flows.

The Net Present Value also called the NPV, results from the difference between the cash inflows and the outflows.

If the NPV is positive, it is said that the inflows exceed the outflow and thus generate profit for the shareholders in the process. On the other hand, when the outflows exceed the inflows, the investment is expected to generate a loss.

Suppose you purchase a rental property for an initial investment of $20,000. The property is expected to return $4,500 annually for five years. The discount rate for investing in the property is 8%.

Thus, the data looks as illustrated below:

To calculate the PV of the future cash inflows from year 1, we will use the formula =PV($H$4,B4,-D4,0) in cell E4 and drag it down till cell E8 which gives the result as

As you can see, the farther we go away from the present, the value of the future cash flows fall due to uncertainty as well as inflation.

If we add all those cash flows, we get the total future cash inflows as $17,967.

However, to get the net present value, we need to subtract inflows and outflows and hope the answer is a positive number for the project to be profitable.

By making similar calculations here, i.e., $17,967 - $20,000, we get the result as -$2,033, a negative number.

Thus, despite the rental property earning a cash inflow of $4,500 each year with an 8% rate, it still isn't enough to cover the initial investment of $20,000.

In such a case, it's best to stay away from such investment opportunities or look for other alternatives with positive NPV.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• OFFSET Function
• OR Function
• PEARSON Function
• PERMUT Function
• PHI Function