In this Excel tutorial you will learn how to perform a DCF analysis in Excel.
A discounted cash flow (DCF) analysis is a financial model used to estimate the intrinsic value of an investment or project. This method takes into account the future cash flows generated by an investment, discounted to their present value using a discount rate.
Table of Contents
Steps to perform a DCF analysis
Here are the steps to perform a DCF analysis in Microsoft Excel:
- Start by gathering all the necessary data for the analysis, such as estimated future cash flows, the discount rate, and the required rate of return for the investment.
- Open a new or existing Microsoft Excel spreadsheet and create a table to organize your data. The table should include columns for the year, the estimated cash flows, and the present value of the cash flows.
- In the present value column, use the following formula to calculate the present value of each future cash flow: =PV(discount rate, year, estimated cash flow, future value, type)
where “PV” is the present value function, “discount rate” is the discount rate for the year, “year” is the number of periods, “estimated cash flow” is the estimated cash flow for the year, “future value” is the future value, and “type” is the payment type (0 for end of period, 1 for beginning of period).
- Once you have calculated the present value of each future cash flow, you can use the SUM function to calculate the total present value of all cash flows: =SUM(present value of cash flows)
- The net present value (NPV) is the present value of the investment, taking into account the initial investment. To calculate NPV, subtract the initial investment from the total present value of the cash flows: =NPV(discount rate, present value of cash flows)
- If the NPV is positive, the investment is expected to generate more cash flows than the initial investment. If the NPV is negative, the investment is not expected to generate enough cash flows to cover the initial investment.
- To perform a sensitivity analysis, you can change the discount rate and observe the impact on the NPV. This will help you determine the minimum rate of return required for the investment to be considered a good investment.
- To perform a valuation analysis, you can compare the NPV to the current market price of the investment to determine if it is overvalued or undervalued.
