How to Calculate the Discounted Cash Flow in Excel – 3 Easy Steps

What Is the Discounted Cash Flow?

What your future cash flow is worth in terms of today’s value is known as Discounted Cash Flow.


Formula to Calculate The Discounted Cash Flow

The total amount of the cash flow for every period of time divided by one increment of the discount rate raised to the power of the period count is the Discounted Cash Flow (DCF) formula.

DCF=CFt/(1+r)^t

CFt = Cash flow in period t (time)
r = Discount rate
t = Period of time (1,2,3,……,n)


Step 1– Calculatiing the Present Value

Steps:

  • Consider a cash flow for every year. Here, $50,000.

How to Calculate Discounted Cash Flow in Excel

  • Consider the Discount Rate. It symbolizes the interest rate to calculate the future cash flow based on the current situation. Here, 10% as the discount rate.
  • Enter the following formula to calculate the present value after 1 year.
=C6/(1+$G$5)^B6

C6 = Cash Flow after the first year
G5 = Discount Rate
B6 = Period of Time

  • Press ENTER to see the output.

How to Calculate Discounted Cash Flow in Excel

  • Drag down the Fill Handle to AutoFill the rest of the cells.

Read More: How to Calculate Annual Cash Flow in Excel


Step 2 – Calculating the Discounted Cash Flow in Excel

Steps:

  • Estimate the total value of the Present Value. Enter the following formula:
=SUM(D6:D25)

How to Calculate Discounted Cash Flow in Excel

  • Press ENTER to see the result.

The total present value of the payment is $425,678.19. The total cash flow is $1,000,000.

Read More: How to Calculate Incremental Cash Flow in Excel


Step 3 – Validation of the Discounted Cash Flow Calculation in Excel

Steps:

  • Calculate the profit of the investments in a column. Here, a profit of 10% was assumed based on the invested amount and listed in the Return column.
  • Use the following formula to calculate the return:
=F6*$I$5

F6 = End Value of the previous year
I5 = Return Rate

How to Calculate Discounted Cash Flow in Excel

  • Press ENTER to see the return value.

  • Calculate the End Value using the following formula:
=F6+C7+E7

F6 = End Value of the previous year
C7 = Cash Flow of the current year
E7 = Return value of that year

How to Calculate Discounted Cash Flow in Excel

  • Press ENTER to see the output.

  • AutoFill the Return and End Value columns.

How to Calculate Discounted Cash Flow in Excel

The projected End Value after 20 years is calculated.

  • Consider the total present value as the Lump Sum value for the beginning of the investment.
  • Enter the following formula to get the Lump Sum value for the following year:
=G5*(1+$J$5)

G5 = Lump Sum Value
J5 = Discount rate

  • Press ENTER to see the Lump Sum value.

How to Calculate Discounted Cash Flow in Excel

  • AutoFill the rest of the cells.

The End Value and the Lump Sum value is equal at the end of the time period. The Discounted Cash Flow calculation is accurate.

Read More: How to Forecast Cash Flow in Excel


Download Practice Workbook


Related Articles


<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

2 Comments
  1. Reply
    Vijiaselvam Nayagam Nov 27, 2023 at 3:10 PM

    thank you
    being a non finance person, this is really helpful for me to derive DCF for a project

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo