How to Calculate Present Value in Excel with Different Payments

Sometimes we have to calculate the present value of an asset or a future value in Microsoft Excel. This calculation can be done in several cases. Are you looking for solutions to calculate the present value from a future value in Excel? In this article, we’ll discuss how to calculate present value in Excel with different payments with 5 easy examples.


Download Practice Workbook

You can download the Excel workbook from here.


5 Easy Examples to Calculate Present Value in Excel with Different Payments

Now we will see 5 easy examples with explanations to calculate present values in Excel with different payments using the PV function. Here, we have used 5 types of payments that need calculation of present value. So without further delay, let’s get started.


1. Calculate Present Value for Single Payment

In this example, we will calculate present value in Excel for single payment. If we choose to invest money in a single payment then the present value of the investment will depend on the future value (FV) instead of the periodic payment (PMT). Assuming, we have a dataset in Excel (B4:C8) where the Annual interest rate, No. of years and the Future Value of a single payment is given. Now, we need to calculate the Present Value of the payment for single payment using the PV function.

Easy Examples to Calculate Present Value in Excel with Different Payments

Follow the steps below to calculate the present value from a single payment.

Steps:

  • In the beginning, we need to select cell C8 where we want to keep the present value.
  • Next, to calculate the present value of the given single payment, type the formula:
=PV(C5, C6, C7)
  • Finally, after pressing Enter, we will be able to see the Present Value of the single payment.

Read More: How to Calculate Present Value of Lump Sum in Excel (3 Ways)


2. Count Present Value for Periodic Payment

In order to count the present value for periodic payment, we have to divide the annual rate by the number of periods per year to convert it to a periodic rate. Again, we need to multiply the term in years by the number of periods per year to get the total number of periods. Now, let’s assume, we have a dataset (B4:C9) in Excel. Here, we can see that the investment is $200 per month for 5 years at a 5% annual interest rate.

Easy Examples to Calculate Present Value in Excel with Different Payments

We can calculate the present value of this periodic payment by following the steps below.

Steps:

  • Firstly, select cell C9 where you want to keep the present value.
  • Secondly, to calculate the future value of the given data type the formula:
=PV(C5/C8, C6*C8, C7)
  • Finally, press Enter to get the Present Value.


3. Regular Cash Flow Present Value Calculation

In order to calculate present value in Excel with different payments with regular cash flow, we have a dataset (B4:E12) where we can see some Periods, a Required Return, and some regular Cash Flows of $200 for 4 periods. Let’s say, we need to calculate the Present Value of these even cash flows. To do so, at first we will calculate the present value of the cash flows individually. After that, we will sum up the individual cash flows to get the Total Present Value.

Easy Examples to Calculate Present Value in Excel with Different Payments

Follow the steps below to calculate present value for regular payment.

Steps:

  • First, select cell D8 and type the following formula:
=1/(1+$C$4)^B8
  • Now, press Enter and we will get the Present Value (PV) factor for period 1.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • Second, select cell D8 and drag the Fill Handle till cell D11 and we will get PV factors for all the periods.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • Third, to calculate present value for individual periods, select cell E8 and type the following formula:
=C8*D8
  • Then, press Enter and we will get the present value for period 1.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • Afterward, select cell E8 and drag the Fill Handle till cell E11 and we will get the present values for all the periods.

  • Now, it’s time to sum all the present values to acquire the total present value. For doing this, we are going to use the SUM function. Here, type the formula in cell E12:
=SUM(E8:E11)
  • Finally, press Enter and get the total present value.

Read More: How to Calculate Present Value of Future Cash Flows in Excel


4. Irregular Cash Flow Present Value Calculation

Now we will see how to calculate present value in Excel with irregular cash flow payments. Suppose, we have a dataset (B4:D12) in Excel where we can see some Periods, a Required Return, and some irregular Cash Flows. Let’s say, we need to calculate the Present Value of these uneven cash flows. To do so, at first we will calculate the present value of the cash flows individually. After that, we will sum up the individual cash flows to get the Total Present Value.

Easy Examples to Calculate Present Value in Excel with Different Payments

To calculate the present value for irregular cash flow, follow the steps below.

Steps:

  • To start with, select cell D8.
  • After that, to gain the present value of the cash flow in cell C8 type the formula:
=PV($C$4, B8,,-C8)
  • Subsequently, when we press Enter, we will get the present value of the respective cash flow.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • Similarly, to get all the present values of the cash flows, we need to drag the Fill Handle until we reach the final cash flow.
  • As a result, we have got all the present values of the individual cash flows.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • Now, it’s time to sum up the values of the individual cash flows to acquire the total present value. For doing this, we are going to use the SUM function. Here, type the formula in cell D12:
=SUM(D8:D11)
  • Finally, press Enter and get the total present value.

Read More: How to Calculate Present Value of Uneven Cash Flows in Excel


5. Creating Present Value Calculator

If we wish to create a PV calculator that can handle both periodic and single payments, we’ll need to use the Excel PV function in its entirety. To begin, just like the screenshot below, assign cells for all arguments, along with the optional ones.

Easy Examples to Calculate Present Value in Excel with Different Payments

Then, define the arguments in this manner:

rate (periodic interest rate): C5/C10 (annual interest rate / periods per year)

nper (total number of payment periods): C6*C10 (number of years * periods per year)

pmt (periodic payment amount): C7

pv (initial investment): C8

type (when payments are due): C9

compounding periods per year: C10

Now, follow the steps below to calculate the present value.

Steps:

  • Firstly, we need to select cell C11 and type the formula:
=PV(C5/C10, C6*C10, C7, C8, C10)
  • Lastly, by pressing the Enter key, the desired present value will be visible as shown below.

Read More: How to Apply Present Value of Annuity Formula in Excel


How to Calculate Future Value in Excel with Different Payments

We can also calculate the future value from the present value of something. It can be done in many ways and in many scenarios. We have taken the same dataset below to calculate the future value from the present value of a single payment to show you an example.

Easy Examples to Calculate Present Value in Excel with Different Payments

Follow the steps below to calculate the future value from a single payment.

Steps:

  • Firstly, we need to select cell C8 where we want to keep the future value.
  • Then, to calculate the present value of the given single payment, type the formula:
=FV(C5, C6, C7)
  • Hence, after pressing Enter, we will be able to see the Future Value of the single payment.

Read More: How to Calculate Future Value in Excel with Different Payments


Things to Remember

  • You have to define the arguments properly if you want to calculate the present value by the present value calculator.
  • You have to understand for which case you are calculating a present value from future value and follow a similar example to get the correct result.

Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to calculate present value in Excel with different payments. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo