How to Calculate Present Value in Excel with Different Payments: 5 Easy Examples
Example 1 – Calculate the Present Value for a Single Payment
The sample dataset (B4:C8) showcases the annual interest rate, No. of years and the future value of a single payment.
To calculate the Present Value of the payment for a single payment, use the PV function..
Steps:
- Select C8 to keep the present value.
- To calculate the present value enter the formula:
=PV(C5, C6, C7)- Press Enter to see the Present Value of the single payment.
Read More: How to Calculate Present Value of Future Cash Flows in Excel
Example 2 – Count the Present Value for a Periodic Payment
In the sample dataset (B4:C9) you can see the investment is $200 per month for 5 years at a 5% annual interest rate.
Steps:
- Select C9 to keep the present value.
- To calculate the future value, enter the formula:
=PV(C5/C8, C6*C8, C7)- Press Enter to see the Present Value.
Example 3 – Present Value Calculation with Regular Cash Flow
In the dataset (B4:E12) you can can see 4 periods, a required return, and regular cash flows of $200.
Steps:
- Select D8 and enter the following formula:
=1/(1+$C$4)^B8- Press Enter to see the Present Value (PV) factor for period 1.
- Select D8 and drag the Fill Handle to D11 to see the PV factors for all the periods.
- To calculate the present value for an individual period, select E8 and enter the following formula:
=C8*D8- Press Enter to see the present value for period 1.
- Select E8 and drag the fill handle to E11 to see the present values for all the periods.
- To get the total present value, use the SUM function. Enter the formula in E12:
=SUM(E8:E11)- Press Enter to see the total present value.
Read More: How to Calculate Present Value of Lump Sum in Excel
Example 4 – Present Value Calculation with Irregular Cash Flow
The dataset (B4:D12) showcases 4 periods, a required return, and irregular cash flows.
Steps:
- Select D8.
- Enter the formula in C8:
=PV($C$4, B8,,-C8)- Press Enter to see the present value of the corresponding cash flow.
- To get all the present values of the cash flows, drag the Fill Handle to the final cash flow.
- All present values of the individual cash flows will be displayed.
- To get the total present value, use the SUM function. Enter the formula in D12:
=SUM(D8:D11)- Press Enter to see the total present value.
Read More: How to Apply Present Value of Annuity Formula in Excel
Example 5. Creating a Present Value Calculator
Assign cells for all arguments.
Define the arguments:
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
Steps:
- Select C11 and enter the formula:
=PV(C5/C10, C6*C10, C7, C8, C10)- Press Enter to see the present value.
Read More: How to Calculate Present Value of Uneven Cash Flows in Excel
How to Calculate the Future Value with Different Payments in Excel
To calculate the future value of a single payment.
Steps:
- Select C8 to keep the future value.
- Enter the formula:
=FV(C5, C6, C7)- Press Enter to see the Future Value of the single payment.
Read More: How to Calculate Future Value in Excel with Different Payments
Download Practice Workbook
Download the Excel workbook here.
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!
