How to Calculate the Present Value with Different Payments in Excel – 5 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.

Easy Examples to Calculate Present Value in Excel with Different Payments

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.

Easy Examples to Calculate Present Value in Excel with Different Payments

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.

Easy Examples to Calculate Present Value in Excel with Different Payments

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.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • Select D8 and drag the Fill Handle to D11 to see the PV factors for all the periods.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • 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.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • 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.

Easy Examples to Calculate Present Value in Excel with Different Payments

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.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • 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.

Easy Examples to Calculate Present Value in Excel with Different Payments

  • 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

Easy Examples to Calculate Present Value in Excel with Different Payments

  • 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

 

Easy Examples to Calculate Present Value in Excel with Different Payments

 

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


<< Go Back to Time Value Of Money In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo