Building a DCF Valuation Model for Polycab India Ltd. in Excel – A Detailed Guide


📘 Building a DCF Valuation Model for Polycab India Ltd. in Excel – A Detailed Guide

🎯 Objective

To estimate the intrinsic value of Polycab India Ltd. using the Discounted Cash Flow (DCF) methodology, a cornerstone of equity valuation under the CFA curriculum (Level I-III).

We will construct the model in Microsoft Excel, using publicly available data from Screener.in, Annual Reports, and NSE.

🏗️ DCF Valuation Framework

DCF valuation is based on the principle that a stock’s fair value is the present value of its future expected free cash flows (FCFs) discounted at an appropriate rate of return (cost of capital).

Intrinsic Value (Equity) = PV of Forecasted FCFs + PV of Terminal Value – Net Debt (if using FCFF)

In this case, we will use Free Cash Flow to Firm (FCFF) and value equity per share by dividing by outstanding shares.

🧮 Step-by-Step Model Construction for Polycab India Ltd

📝 Step 1: Gather and Validate Key Financial Data

Input

Value

Source

Free Cash Flow (FY23)

₹1,063 Cr

Screener.in → Cash Flow Statement

Revenue (FY23)

₹14,108 Cr

Screener → Profit & Loss

EBITDA Margin (Avg 3Y)

~12.5%

Screener / AR

CAGR Revenue (5Y)

~14.5%

Screener → Charts/5Y history

Net Debt

₹(2,200 Cr) (cash)

Screener.in / Balance Sheet

Shares Outstanding

15.01 Cr

Screener.in (Right Panel)

Tax Rate

~25%

Company effective tax rate

📈 Step 2: Set Assumptions and Forecast Parameters

Assumption

Value

Rationale

Forecast Period

5 Years

Standard horizon for DCF

FCF Growth Rate (Years 1-5)

15% CAGR

Inline with historical trends + India infra growth tailwinds

Terminal Growth Rate (g)

5%

Slightly above inflation, below GDP

Discount Rate (WACC)

12%

Based on implied cost of capital (conservative)

📊 Step 3: Forecast Future Free Cash Flows (FCFF)

📘 Formula:

FCFt=FCFt−1×(1+g)FCF_{t} = FCF_{t-1} \times (1 + g)

🧾 Excel Table:

Year

Formula in Excel

FCF (₹ Cr)

FY24

=1063*(1+15%)

1,222

FY25

=1222*(1+15%)

1,405

FY26

=1405*(1+15%)

1,616

FY27

=1616*(1+15%)

1,859

FY28

=1859*(1+15%)

2,138

📉 Step 4: Discount Each FCF to Present Value

📘 Formula:

PVFCF=FCFt(1+r)tPV_{FCF} = \frac{FCF_t}{(1 + r)^t}

Year

FCF (₹ Cr)

Discount Factor (12%)

PV of FCF (₹ Cr)

1

1,222

0.8929

1,091

2

1,405

0.7972

1,119

3

1,616

0.7118

1,150

4

1,859

0.6355

1,183

5

2,138

0.5674

1,214

Total

₹5,757 Cr

🔁 Step 5: Compute Terminal Value (TV)

📘 Formula (Gordon Growth Model):

TV=FCF5×(1+g)(r−g)=2138×1.050.12−0.05TV = \frac{FCF_5 \times (1 + g)}{(r - g)} = \frac{2138 \times 1.05}{0.12 - 0.05} TV=22450.07=₹32,071CrTV = \frac{2245}{0.07} = ₹32,071 Cr

🎯 Discount to Present Value:

PVTV=TV(1+r)5=32071(1.12)5=₹18,180CrPV_{TV} = \frac{TV}{(1 + r)^5} = \frac{32071}{(1.12)^5} = ₹18,180 Cr

🧾 Step 6: Enterprise Value (EV) and Equity Value

Enterprise Value=PVFCFs+PVTV=5,757+18,180=₹23,937Cr\text{Enterprise Value} = PV_{FCFs} + PV_{TV} = 5,757 + 18,180 = ₹23,937 Cr

Since Polycab has net cash (negative net debt):

Equity Value=EV+NetCash=23,937+2,200=₹26,137Cr\text{Equity Value} = EV + Net Cash = 23,937 + 2,200 = ₹26,137 Cr

💰 Step 7: Intrinsic Value per Share

Value per Share=EquityValueSharesOutstanding=26,13715.01≈₹1,741\text{Value per Share} = \frac{Equity Value}{Shares Outstanding} = \frac{26,137}{15.01} ≈ ₹1,741

📉 Step 8: Market Price Comparison

  • Current Market Price (May 2025): ₹1,400 (approx.)

  • DCF Value per Share: ₹1,741

  • Margin of Safety:

(1741−1400)1400=24.5%\frac{(1741 - 1400)}{1400} = 24.5\%

⚠️ Limitations and Considerations

Area Risk / Subjectivity
Growth Rate Assumed based on past CAGR + outlook
WACC Estimate No exact CAPM used, simplified to 12%
Terminal Growth Highly sensitive; consider sensitivity analysis
FCF Cyclicality Polycab operates in infra/construction-linked sector

📊 Conclusion

Based on our conservative DCF model, Polycab India's intrinsic value per share is estimated at ₹1,741, implying a ~25% upside potential from current levels. While DCF is highly sensitive to assumptions, it serves as a robust foundational method for valuation.

🔗 References & Tools

Disclaimer: The above is a sample calculation provided for illustrative purposes only. It does not constitute investment advice or a recommendation. Please consult with your investment advisor or a qualified financial professional before making any investment decisions.


Location: India

Comments

Post a Comment

Popular posts from this blog

Investment Research: Polycab India Ltd. "Wired for Growth: Powering India's Electrification Journey with Market Leadership and FMEG Expansion

  Unlevered Discounted Cash Flow (DCF) Valuation for Polycab India Ltd Executive Summary This unlevered DCF model values Polycab India Ltd, India's largest manufacturer of wires and cables. The company has established itself as a market leader with a strong presence across multiple segments including wires, cables, fast-moving electrical goods (FMEG), and EPC projects. This analysis projects Polycab's free cash flows over a 10-year forecast period, discounted at an appropriate rate to determine enterprise value. Company Overview Polycab India Ltd is a market leader in the Indian wire and cable industry with a diversified product portfolio. The company has been expanding its FMEG business and has a strong distribution network across the country. Polycab has demonstrated consistent growth over the years, driven by infrastructure development, increasing electrification, and government initiatives like "Make in India" and "Digital India." Key Assumptions G...
Read more

Rakesh Jhunjhunwala’s Stock-Picking Methodology: A Guide for Retail Investors

🐂 Rakesh Jhunjhunwala’s Stock-Picking Methodology: A Guide for Retail Investors Rakesh Jhunjhunwala, often called the "Warren Buffett of India," was one of the most successful investors in Indian stock market history. Starting with just ₹5,000 in 1985, he built a portfolio worth over ₹30,000 crores by the time of his passing in 2022. This article breaks down his stock-picking approach in a step-by-step manner — so you, as a retail investor, can learn to think like him. 1. 🎯 Invest in What You Understand (Circle of Competence) Jhunjhunwala only invested in businesses he could understand well . If he couldn’t explain how a company makes money in simple terms , he wouldn’t invest. ✅ Lesson : Don’t chase hype. Invest in companies and industries you understand — like FMCG, retail, banks, or consumer products. 2. 🧮 Focus on Fundamentals (Not Just Stock Price) He was a fundamental investor — meaning he studied the company's financials , management, growth pote...
Read more