How to Calculate Present Value of Future Cash Flows in Excel

Get FREE Advanced Excel Exercises with Solutions!

At present, it is very important to know the calculation of present value of future cash Flows. If you want to invest somewhere, or you want to save money in every step you should know the present value of future cash flows. So, in this article, I will show you how to calculate the present value of future cash flows.


What Is Present Value of Future Cash Flows?

Present Value (PV) of Future Cash Flows is a process of discounting the cash that you expect to get in the future with a value of the current time. The present value may be greater than future cash flows or vice versa. Furthermore, the present value is less than the future cash flow for inflation.


How to Calculate Present Value of Future Cash Flows: 4 Methods

To explain the 4 methods on how to calculate the present value of future cash flows in Excel, I will use the following dataset. Which has a Discount Rate, Time Period, and Future Cash Flow.

 How to Calculate Present Value of Future Cash Flows


1. Use of PV Function to Calculate Present Value of Future Cash Flows

Here, you can use the PV function to calculate the present value of future cash flows. The steps are given below.

Steps:

  • Firstly, you need to select a different cell D6 where you want to calculate the present value.
  • Secondly, use the corresponding formula in the D6 cell.
=PV($C$4,B6,0,C6)

Formula Breakdown

Here, the PV function will return the present value of an investment.

  • C4 denotes rate as the annual interest rate. The Dollar ($) sign denotes that the value of the C4 cell is fixed.
  • B6 denotes NPER as the total period of time.
  • As there is no Payment so PMT will be 0.
  • C6 denotes FV as the Future Cash Flow.

  • Now, press ENTER to get the Present Value.

 How to Calculate Present Value of Future Cash Flows

Here, the Minus sign denotes that you need to keep this amount at any monetary institute.

  • After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D7:D10.

At this time, you will see the following result.

 How to Calculate Present Value of Future Cash Flows

  •  Now, you have to select the data range. Here, I have selected D5:D10.
  • Then, from the Home tab >> you should go to the Number feature.
  • After that, in the Number feature >> Click on the Drop-Down Arrow >> At this time, you need to choose currency.

  • Finally, from the Number feature >> you need to Click two times on decrease decimal to decrease the decimal.

 How to Calculate Present Value of Future Cash Flows

  • Now, you need to press the CTRL+1 keys to open the Format Cells dialog box directly.

Also, you can use the Context Menu Bar or the Custom Ribbon to go to the Format Cells command.

  •  First, select the data range >> right-click on the data >> choose the Format Cells option.

In the case of using Custom Ribbon,

  • Select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.

After that, a dialog box named Format Cells will appear.

  • Now, from that dialog box, you have to make sure that you are on the Number command.
  • Then, you need to go to the Currency option.
  • At this time, choose the second option from the Negative numbers option.
  • Finally, press OK.

Furthermore, to get the total Present value, I will use the SUM function.

  • Firstly, you need to select a different cell D11 where you want to calculate the total Present Value.
  • Secondly, use the corresponding formula in the D11 cell.
=SUM(D6:D10)
  • Finally, press ENTER.

 How to Calculate Present Value of Future Cash Flows

Here, the SUM function will return the summation of the data range D6:D10. At last, you will get the following present value.

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


2. Applying NPV Function for Calculating Present Value

You can apply the NPV function to calculate the present value of future cash flows. The steps are given below.

Steps:

  • Firstly, you need to select a different cell C11 where you want to calculate the present value.
  • Secondly, use the corresponding formula in the C11 cell.
=NPV(C4,C6:C10)

 How to Calculate Present Value of Future Cash Flows

Formula Breakdown

Here, the NPV function will return the net present value of an investment.

  • C4 denotes the discount rate. Which is 5%.
  • C6:C10 denotes the series of FV as the Future Cash Flows.

  • Now, press ENTER to get the Net Present Value.

Read More: Calculate NPV for Monthly Cash Flows with Formula in Excel


3. Employing Generic Formula to Calculate Present Value of Future Cash Flows

You can employ the generic formula to calculate the Present Value of Future Cash Flows.

Steps:

  • Firstly, you need to select a different cell D6 where you want to calculate the Present Value.
  • Secondly, use the corresponding formula in the D6 cell.
=C6/((1+$C$4)^B6)

 How to Calculate Present Value of Future Cash Flows

Formula Breakdown

  • Here, C4 denotes the rate as the annual discount rate. The Dollar ($) sign denotes that the value of the C4 cell is fixed.
  • Firstly, I have added 1 with the discount rate.
    • Output: 1.05.
  • Secondly, the Power (^) sign raises it to a fixed power which is the value of B6 cell. Also, that is the time period.
    • Output: 1.05.
  • Finally, I divided the C6 cell value by 1.05.
    • Output: $3,809.52.

  • Now, press ENTER to get the present value.

  • After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D7:D10.

Furthermore, to get the total present value, I will use the SUM function.

  • Firstly, you need to select a different cell D11 where you want to calculate the total present value.
  • Secondly, use the corresponding formula in the D11 cell.
=SUM(D6:D10)
  • Finally, press ENTER.

 How to Calculate Present Value of Future Cash Flows

Here, the SUM function will return the summation of the data range D6:D10. Lastly, you will get the following Present Value.

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


4. Using Generic Formula for Compound Interest

You can employ the generic formula to calculate the present value of future cash flows in the case of Compound Interest. Let’s have the following data set.

The steps are given below.

Steps:

  • Firstly, you need to select a different cell C9 where you want to calculate the present value.
  • Secondly, use the corresponding formula in the C9 cell.
=C4/((1+C5/C7)^(C6*C7))

How to Calculate Present Value of Future Cash Flows

Formula Breakdown

  • Here, C5 denotes rate as the annual discount rate.
  • Firstly, I have divided the cell value of C5 by Compounding year or C7 cell value.
    • Output: 0.016666667.
  • Secondly, I have added 1 with the result.
    • Output: 1.016666667.
  • Thirdly, I have multiplied C6 with C7.
    • Output: 15.
  • Fourthly, the Power (^) sign raises it to a fixed power.
    • Output: 1.281382444.
  • Finally, I divided the C4 cell value by the output.
    • Output:$13,423.00 .

  • Now, press ENTER to get the present value.

Read More: How to Calculate Present Value of Lump Sum in Excel


💬 Things to Remember

Try to avoid generic formulas as there are built-in functions to calculate the present value of future cash flows.


Practice Section

Now, you can practice the explained method by yourself.

How to Calculate Present Value of Future Cash Flows


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 4 methods to calculate present value of future cash flows. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo