The core economic tenet that people invest in future anticipated gains rather than now consumption is adhered to by the discounted cash flow model. The present investmentâ€™s worth ought to be equal to or less than the present value of the anticipated future cash flows, according to logic. The investment is profitable only after that. In this article, I am going to explain how to calculate discounted cash flow in Excel. I hope it will be helpful for the related people with it.

**Table of Contents**Expand

## What Is Discounted Cash Flow?

What your** future cash flow** is worth in terms of todayâ€™s value is known as **Discounted Cash Flow**. The **PV** (present value) of a future cash flow is another name for this. **Discounted Cash Flow **helps the investors to assess how much money goes into investment as well as when that money is spent, how much money the investment generates, and when the investor can access the proceeds from the investment.

Keep it in mind that itâ€™s a predicted value, the estimated and actual value may differ so for a model where the actual value is the prime requirement may not be suitable with this.

## Formula to Calculate 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`

Here,

**CFt** = Cash flow in period t (time)

**r** = Discount rate

**t** = Period of time (1,2,3,â€¦â€¦,n)

We can easily calculate the **Discounted Cash Flow **and validate it, just follow the steps:

__Step- 01__: Calculation of Present Value

__Step- 01__

**Discounted Cash Flow **is actually a valuation method that is used to forecast future gainings based on the present investment. For this, we need to calculate the **Present Value **first.

** Steps**:

- Consider a cash flow for every year. In my case, I have considered a $50,000 cash flow every year. You can consider different amounts in different years too.

- Take into account a
**Discount Rate**. The**Discount Rate**symbolizes the interest rate to calculate the future cash flow based on the current situation. Here, I took 10% as the discount rate. - Input the following formula to calculate the present value after 1 year.

`=C6/(1+$G$5)^B6`

Here,

**C6 **= Cash Flow after first year

**G5 **= Discount Rate

**B6** = Period of Time

- Press
**ENTER**to have the output.

- Use
**Fill Handle**to**AutoFill**the rest cells.

**Read More:** How to Calculate Annual Cash Flow in Excel

__Step- 02__: Calculating Discounted Cash Flow in Excel

__Step- 02__

**Discounted Cash Flow **is calculated in this section using the **Present Value** calculated in the above section.

** Steps**:

- Estimate the total value of the
**Present Values**. Input the following formula for that purpose:

`=SUM(D6:D25)`

Here, **the SUM function **adds the present value over the 20 years of cash flow.

- Press
**ENTER**to have the result.

The total present value of the payments is $425,678.19. The total cash flow is $1,000,000. We are losing a lot of value due to the time value of money at a 10% rate every year.

**Read More:** How to Calculate Incremental Cash Flow in Excel

__Step- 03__: Validation of Discounted Cash Flow Calculation in Excel

__Step- 03__

To validate the **Discounted Cash Flow**, We are gonna compare the **End Value **of our investments over the years with the total **Lump Sum **value.

** Steps**:

- Calculate the profit of the investments in a column. Here, I have assumed a profit 0f 10% based on the invested amount and listed it in the
**ReturnÂ**column. - Use the following formula to calculate the return:

`=F6*$I$5`

Here,

**F6 **= End Value of the previous year

**I5 **= Return Rate

- Press
**ENTER**button to have the return value.

- Followingly, calculate the
**End Value**with the addition of**Return**using the following formula:

`=F6+C7+E7`

Here,

**F6 **= End Value of the previous year

**C7 **= Cash Flow of the current year

**E7 **= Return value of that year

- Hit the
**ENTER**button to have the output.

- Now,
**AutoFill**the**Return**and**End ValueÂ**columns.

Thus, we can have the projected **End Value** after 20 years.

- Now, consider the total present value as the
**Lump Sum**value for the beginning of the investment. - Input the following formula to have the
**Lump Sum**value for the following year:

`=G5*(1+$J$5)`

Here,

**G5 **= Lump Sum Value

**J5 **= Discount rate

- Press
**ENTER**to have the**Lump Sum**value of the year.

- Finally,
**AutoFill**the rests.

At the end, we can see that the **End Value **and the **Lump Sum **value is equal at the end of the whole time period. So, we can say with full confidence that our **Discounted Cash Flow **calculation is accurate.

**Read More: **How to Forecast Cash Flow in Excel

**Download Practice Workbook**

## Conclusion

Thatâ€™s all for this article. In the end, I like to add that I have tried to explain step-by-step procedures of how to calculate discounted cash flow in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little.

**Related Articles**

- How to Calculate Free Cash Flow in Excel
- How to Calculate Cumulative Cash Flow in Excel
- How to Draw a Cash Flow Diagram in Excel
- How to Track Cash Flow in Excel
- How to Create a Personal Cash Flow Statement in Excel
- How to Calculate Operating Cash Flow Using Formula in Excel
- How to Calculate Payback Period in Excel
- How to Calculate Payback Period with Uneven Cash Flows
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Operating Cash Flow in Excel
- How to Calculate Net Cash Flow in ExcelÂ

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

thank you

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

Hello

Vijiaselvam Nayagam,You are most welcome.

Regards

ExcelDemy