Discounted Cashflow Modeling
Everyone who has completed CT1 or CM1 has come across the concept of the Discounted Cashflows or NPV. Let's Create a DCF model using EXCEL.
Let's understand what is Discounted Cashflow modelling and when it is used by the companies.
The EXCEL MODEL IS ATTACHED AT THE END OF THE PAGE.
Discounted cash flow modeling is a valuation method used to estimate the value of an investment based on its future cash flows. It figures the value of an investment today, based on the projections of how much money it will generate in the future. DCF helps both the investors and business owners who are looking to make changes to their business.
The purpose of DCF modeling is to estimate the money an investor would receive from an investment adjusted for the time value of money. Investors can use the concept of the present value of money to determine whether the future cash flows of an investment or project are equal to or greater than the value of the initial investment.
For companies taking over another company can use DCF to estimate the takeover price.
The main limitation of DCF is that it requires making many assumptions. In reality, future cashflows would depend on a variety of factors such as market demand, the status of the economy, unforeseen obstacles. Estimating cash flows too low will make appear the investment costly and could result in missed opportunities. Estimating the risk discount rate for the model is also an assumption and would have to be estimated correctly for the model to be worthwhile.
Having discussed the theoretical part for DCF we now move to build the model.
Related: Excel VBA basics part 1-2-3
The model is built simply by assuming the cash flows a company is going to receive from 2020 onwards. We have taken into account the TERMINAL VALUE. We have assumed that after 5 years, revenue will be stabilized to some % margin. We have calculated the TERMINAL VALUE by applying the formula of annuity in perpetuity.
While modeling in excel you will come across many new formulas that are being used for calculation.
We calculate the profit arising from the operations of the company and discount the profits back to the valuation date.
less: Operating expenses
= EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization)
less: D&A (Depreciation, and Amortization)
= EBIT (Earnings Before Interest and Taxes
less: TAX and interest
= NOPAT (Net Operating Profit After Tax)
add: D&A (We add back D&A because it is a non-cash expense)
= Free Cashflow
To this free cashflows we apply the discounting factor and calculate the Discounted Cashflows for each year.
Written By: Tirth Shah
About the author: Tirth Shah has cleared the CT series of Actuarial Science from IFOA. He completed his graduation in BCOM from Ahmedabad University in 2019.