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.
How to Calculate Present Value in Excel with Different Payments: 5 Easy Examples
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.
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 Future Cash Flows in Excel
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.
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.
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.
- Second, select cell D8 and drag the Fill Handle till cell D11 and we will get PV factors for all the periods.
- 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.
- 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 Lump Sum 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.
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.
- 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.
- 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 Apply Present Value of Annuity Formula 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.
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 Calculate Present Value of Uneven Cash Flows 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.
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.
Download Practice Workbook
You can download the Excel workbook from here.
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. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
Related Articles
- How to Apply Future Value of an Annuity Formula in Excel
- How to Calculate Future Value with Inflation in Excel
- How to Make a Time Value of Money Calculator in Excel
- Calculate NPV for Monthly Cash Flows with Formula in Excel
- How to Calculate Future Value of Uneven Cash Flows in Excel
- How to Calculate Future Value of Growing Annuity in Excel
<< Go Back to Time Value Of Money In Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
