:: Subscribe to our newsletter
 
Articles / Directory
06/17/2013
Latest version of TraderCode(v5.6) includes new Technical Analysis indicators, Point-and-Figure Charting and Strategy Backtesting.



06/17/2013
InvestmentCode, a comprehensive suite of
Financial calculators and models for Excel is now available.



09/01/2009
Launch of
Free Investment and Financial Calculator for Excel.



09/08/2007
Launch of
TinyGraphs - open source add-in for creating sparklines and tiny charts in Excel.



1/02/2009
Launch of NeuralCode(v1.0) for
Neural Networks Trading.



Free Company Share Price Valuation (Free Cash Flow to Firm) Spreadsheet

Company Share Price Valuation using Free Cash Flow to Firm

This spreadsheet values a company's share price by using the Free Cash Flow to Firm of a 10 years projection horizon. The Free Cash Flow to Firm is defined as the sum of the cash flows to all claim holders in the firm, including equity holders and lenders.



Valuation Summary

The Free Cash Flow to Firm (FCFF) is calculated as follows:

FCFF = EBIT * (1-Tax rate) + Depreciation - Capital expenditure - Change in Working Capital

The terminal value of the firm beyond the projection horizon is also estimated and added to the cash flow. The final cash flow discounted with the cost of capital provides a value of the operating assets. By adding the value of the non operating assets and deducting the value of debt, the equity value is derived.

Assumptions

The following are inputs to be entered into the spreadsheet as assumptions. The values will be used by other parts of the spreadsheet.


  • Discount Rate - Weighted Average Cost of Capital. This is used to discount the cash flow to firm.
  • Tax Rate - Company's Tax Rate
  • Growth rate of Cash Flow after projection horizon - A fix growth rate after the projection horizon. This growth rate is used in the estimation of the Terminal Value of the company.
  • Value of Non Operating Assets - The Discounted value of the Free Cash Flow to Firm yields the value of the operating assets. The equity value can be derived by adding the value of the Non Operating Assets and deducting the value of debt from the value of the operating assets.
  • Value of Debt - The Discounted value of the Free Cash Flow to Firm yields the value of the operating assets. The equity value can be derived by adding the value of the Non Operating Assets and deducting the value of debt from the value of the operating assets.
  • Number of Common Shares - The equity value will be divided by the number of common shares to determine the price per share.

Projected Income

The Earnings before Interest & Taxes (EBIT) is calculated as follows:

Earnings before Interest & Taxes(EBIT) = Net Sales - Total Variable Costs - Total Fixed Costs - Depreciation

Net income is calculated as follows:

Net income - EBIT - Taxes


Net Working Capital and Investment (Capital Spending)

The Net Working Capital and Investment (Capital Spending) are taken into account in these two sections.

The Net Working Capital at Year 0 can be entered directly into the spreadsheet. From Year 1 onwards, it is calculated as as function over Net Sales as follows:

Net Working Capital = Net Working Capital over Sales * Net Sales

Net Working Capital cash flow is calculated as follows:

Net Working Capital cash flow = -(Current Year Net Working Capital - Previous Year Net Working Capital) + NWC Recovery at end


Aftertax salvage value is calculated as follows:

Aftertax salvage value = Salvage value * (1 - Tax Rate)

Net Capital Spending is calculated as follows:

Net Capital Spending = Initial Investment + Aftertax salvage value

Projected Free Cash Flows

This section uses the value from the Net Working Capital, Investment (Capital Spending), EBIT and Depreciation to calculate the Free Cash Flow to Firm.

Free Cash Flow to Firm (FCFF)

FCFF = EBIT * (1-Tax rate) + Depreciation - Capital expenditure - Change in Working Capital


Equity Valuation

Terminal Value

The Terminal Value of the Firm is estimated to be as follows:

Terminal Value = Final Year Cash Flow * (1+Growth rate of Cash Flow after projection horizon) / (WACC Discount Rate - Growth rate of Cash Flow after projection horizon)

Operating Value

Operating Value is calculated as follows:

Value of Operating Assets = Net Present Value of Cash Flows + Discounted Terminal Value

Equity Value

Equity Value is calculated as follows:

Equity Value = Value of Operating Assets + Value of Non Operating Assets - Value of Debt


Download Free Company Share Price Valuation spreadsheet - v1.0

System Requirements

Microsoft® Windows XP®, Microsoft® Windows Vista®, Windows 7 or Windows 8
Windows Server 2003, 2008, 2012
512 MB RAM
5 MB of Hard Disk space
Excel 2002, 2003, XP, 2007, 2010, 2013

License

By downloading this software from our web site, you agree to the terms of our license agreement.

Download

FreeCompanySharePriceValuation.zip (Zip Format - 97 KB)

Get the Professional version

Benefits
  • Unlocked
  • Allows removal of copyright message in the template
  • Allows commercial use within the company
  • Allows full customization of the model
Commercial License PDF Specifications

Bonus Price

USD49.00 - Purchase








 

Copyright (c) 2007-2014, ConnectCode Pte Ltd. All Rights Reserved.


Trademark Acknowledgements

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. Avery is the trademark of Avery Dennison Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners