How to Calculate Future Value of Uneven Cash Flows in Excel

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to know how to calculate Future Value of uneven cash flows in Excel? We can calculate the Future Value of both even and uneven cash flows in Excel. Here, you will find 3 ways to calculate Future Value of uneven cash flows in Excel.


What Is Future Value of Uneven Cash Flows?

Future Value is the total amount of the Present Value and Total Interest. The future value can be calculated for both even and uneven cash flow. To calculate the future value of uneven cash flows first future value for each cash flow is calculated. Then, all values are added to get the total future value.

The formula for calculating the future value of uneven cash flow is given below.

FV = CF0 * (1+r)^ n + CF1 * (1+r)^ n-1+ CF2 * (1+r)^ n-2 + ……. +CFn

Here, CF represents the Cash Flow, r represents Interest Rate and n indicates the Total Number of Payment Periods.


How to Calculate Future Value of Uneven Cash Flows in Excel: 3 Ways

Here, we will show 3 different ways to calculate the Future Value of uneven cash flows in Excel by using different functions and manually calculating by using the formula.


1. Using FV Function to Calculate Future Value of Uneven Cash Flows Excel

In the first method, we will show you how to calculate the future value by using the FV function. The FV function is used to find out the future value of investment in different conditions. It returns a negative value which indicates an outflow of cash.

Here, we have a dataset containing the Time Period (Year), Cash Flow, and Rate of an investment. Now, we will use this dataset to calculate the future value using the FV function.

Using FV Function to Calculate Future Value of Uneven Cash Flows Excel

Follow the steps given below to do it on your own.

Steps:

  • Firstly, select Cell D5.
  • Then insert the following formula.
=FV($C$12,$B$10-B5,,C5)

Here, in the FV function, we selected Cell C12 as the rate and fixed this Cell using $ sign. Then, selected the subtraction of Cell B10 and Cell B5 as the nper and Cell C5 as pv.

  • After that, press ENTER to get the value of the Future Value.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Using FV Function to Calculate Future Value of Uneven Cash Flows Excel

  • Finally, you will get all the values to the future value for each time period using the FV function.

  • Next, select Cell C13.
  • Then insert the following formula.
=SUM(D5:D10)

Using FV Function to Calculate Future Value of Uneven Cash Flows Excel

Here, we used the SUM function to add the values of Cell range D5:D10.

  • Now, you will get the value of the Total Future Value which is in negative form.

  • Then, to convert the value into a positive one select Cell C13.
  • After that, insert the following formula.
=-SUM(D5:D10)

Using FV Function to Calculate Future Value of Uneven Cash Flows Excel

Here, we used a (-) sign in front of the SUM function to convert the total future value into a positive one.

  • Finally, you will get your desired total future value in Excel.

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


2. Use of NPV and FV Functions to Calculate Future Value of Uneven Cash Flows

Now, we will show you how to calculate future value of uneven cash flows using the NPV and FV functions in Excel. The NPV function returns the net present value of an investment using a given rate.

Here, we have a dataset containing the Time Period (Year), Cash Flow, Rate, and Total No of Payment Period of an investment. Now, we will use this dataset to calculate the Future Value using the FV and NPV functions.

Use of NPV and FV Functions to Calculate Future Value of Uneven Cash Flows

Go through the steps given below to do it on your own.

Steps:

  • In the beginning, select Cell C14.
  • Then, insert the following formula.
=NPV(C12,C5:C10)

Here, in the NPV function, we selected Cell C12 as the rate and Cell range C5:C10 as values.

  • After that, press ENTER to get the value of the Future Value.

Use of NPV and FV Functions to Calculate Future Value of Uneven Cash Flows

  • Now, select Cell C15.
  • Next, insert the following formula.
=FV(C12,C13,,C14)

Now, in the FV function, we selected Cell C12 as the rate, Cell C13 as the nper and Cell C14 as pv.

  • After that, press ENTER to get the value of the Future Value.

Use of NPV and FV Functions to Calculate Future Value of Uneven Cash Flows

  • Then, to convert the value into a positive one select Cell C15.
  • Next, insert the following formula.
=-FV(C12,C13,,C14)

Here, we used a (-) sign in front of the FV function to convert the Future Value into a positive one.

  • Finally, you will get your desired future value in Excel.

Use of NPV and FV Functions to Calculate Future Value of Uneven Cash Flows

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


3. Manually Calculating Future Value of Uneven Cash Flows in Excel

In the final method, we will Manually Calculate the Future Value in Excel. Follow the steps given below to do it on your own.

Steps:

  • Firstly, select Cell D5.
  • Then, insert the following formula.
=C5*(1+$C$12)^($B$10-B5)

Manually Calculating Future Value of Uneven Cash Flows in Excel

Here, we used the conventional formula to calculate the Future Value given above. In the formula, Cell C5 is the Cash Flow, Cell C12 is the Interest Rate and the subtraction of Cell B10 and Cell B5 represents the Compounding Period.

  • After that, press ENTER to get the value of the future value.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

  • Finally, you will get all the values to the future value for each time period.

Manually Calculating Future Value of Uneven Cash Flows in Excel

  • Next, select Cell C13.
  • Then insert the following formula.
=SUM(D5:D10)

In the formula, we used the SUM function to add the values of Cell range D5:D10.

  • Finally, you will get your desired Total Future Value of uneven cash flows in Excel by manually calculating.

Manually Calculating Future Value of Uneven Cash Flows in Excel

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


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Download Practice Workbook


Conclusion

So, in this article, you will find 3 ways to calculate future value of uneven cash flows in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.


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.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo