Discounted cash flow (DCF) is a valuation method used to estimate the value of an investment based on its future cash flows. The basic principle of DCF is that the value of a future cash flow is worth less than the same amount of cash today, due to the time value of money. In other words, money today is worth more than the same amount of money in the future, because you could invest the money today and earn a return on it.
To calculate the discounted cash flow of an investment, you need to follow these steps:
↪Estimate the cash flows: Estimate the cash flows that the investment is expected to generate in the future. These can be annual cash flows, quarterly cash flows, or any other time period.
↪Determine the discount rate: Determine the discount rate, which is the rate of return that you would require to invest in the investment. This can be the rate of return of a similar investment or a benchmark rate of return.
↪Calculate the present value: Calculate the present value of each cash flow by dividing it by the discount rate and raising it to the power of the number of periods until the cash flow occurs.
↪Sum the present values: Sum the present values of all the cash flows to get the total present value of the investment.
Here's an example of how to calculate the discounted cash flow of an investment:
Suppose you are considering an investment that is expected to generate cash flows of $10,000 per year for the next five years. You have determined that a discount rate of 8% is appropriate for this investment.
To calculate the present value of the cash flows, you would use the formula: PV = CF / (1 + r)^n, where PV is the present value, CF is the cash flow, r is the discount rate, and n is the number of periods until the cash flow occurs.
For the first year cash flow of $10,000, the present value would be calculated as follows:
PV = $10,000 / (1 + 0.08)^1 = $9,259.26
For the second year cash flow of $10,000, the present value would be calculated as follows:
PV = $10,000 / (1 + 0.08)^2 = $8,547.01
You would repeat this calculation for each year of the investment, and then sum the present values of all the cash flows to get the total present value of the investment. In this example, the total present value of the investment would be:
PV = $9,259.26 + $8,547.01 + $7,905.69 + $7,326.58 + $6,802.72 = $39,841.26
Therefore, the discounted cash flow of the investment is $39,841.26. If the investment is available for purchase at a price lower than this, it may be a good investment opportunity. If the purchase price is higher than the discounted cash flow, it may not be a good investment opportunity.
Can you make that calculation in Excel?
Yes, you can easily calculate the discounted cash flow of an investment in Excel by using the PV (Present Value) function. Here's how you can do it:
↪Open a new Excel spreadsheet and enter the following headings in row 1: "Year", "Cash Flow", "Discount Rate", "Present Value".
↪Enter the year of each cash flow in column A. In this example, we will use years 1 through 5.
↪Enter the expected cash flow for each year in column B. In this example, we will use $10,000 for each year.
↪Enter the discount rate of 8% in cell B2.
↪Use the PV function to calculate the present value of each cash flow. In cell D2, enter the following formula: "=PV(B$2, A2, 0, B$1)". This formula calculates the present value of the cash flow in row 2 using the discount rate in cell B2 and the year in cell A2. Note that the "0" represents the payment that occurs at the end of the period (since we are using an annual cash flow, the payment occurs at the end of each year).
↪Copy the formula in cell D2 to the rest of the rows in column D. You can do this by clicking on the lower right corner of cell D2 and dragging it down to the last row of your data.
↪Finally, sum the present values of all the cash flows in column D to get the total present value of the investment. In this example, you would use the formula "=SUM(D2:D6)" in cell D7.
Your final spreadsheet should look something like this:
Therefore, the discounted cash flow of the investment is $39,841.26.
Comments