| Excel Spreasheets for Finance!!! | |
|
I
have gathered spreadsheets on equity valuation, derivatives, and portfolio analysis
from around the net, and have placed them all
onto one page for convenience (I know how much a of a pain it is to
have to search for them). All of the spreadsheets here are allowed to be
freely distributed according to their authors, so pass
this page along to your co-workers and friends. If you are looking for
MONTE CARLO spreadsheets, add-ins, or VB code click here
|
|
Note: This is due to the server that my site is currently on. I hope to correct this soon!
| Equity Valuation/Analysis | |
| Perform a sensitivity analysis on a possible takeover through stock acquisition. | buyout.xls |
| Ratio analysis of statements | ratio.xls |
| Optimize capital structure | opt.xls |
| Stock valuation | stock.xls |
| Tax Write-Off | tax.xls |
| Valuation by comparable ratios and similar transactions | comp.xls |
| Valuation using cash flows | cf.xls |
| Five year financial projection model | fpm.XLS |
| Determining implied forward rates | ifr.XLS |
| Leverage analysis for companies | lev.xls |
| Levered and unlevered betas | beta.XLS |
| This program allows you to estimate an "Optimal" Capital structure for a company using the cost of capital approach. | capstru.xls |
| This is a variant that allows you to estimate an "Optimal" capital structure for a company whose operating income might vary with its debt rating - for instance, financial service firms. | capstruo.xls |
| This program compares the dividends paid to what a firm could have paid, by estimating the free cash flow to equity (the cash flow left over after net debt payments, net capital expenditures and working capital investments. | dividends.xls |
| This program computes the value of equity in a firm using a two-stage dividend discount and FCFE model. (For more extensive choices on valuation, look at the programs under the valuation section below.) | dcfval.xls |
| This program analyzes the value of equity and the firm in a leveraged buyout. | lboval.xls |
| This program estimates the value of synergy in a merger. | synergy.xls |
| This file describes the programs in this section and provides some insights into their usage. | readme1s.xls |
| This file provides a rough guide to which discounted cash flow model may be best suited to your firm. | model.xls |
| This file summarizes the three approaches that can be used to estimate the net capital expenditures for a firm, when it reaches stable growth. | cpxest.xls |
| This file converts operating lease expenses into financing expenses and restates operating income and debt outstanding. | oplease.xls |
| This file converts R& D expenses from operating to capital expenses, estimates a value for the research asset and restates operating income. | RandD.xls |
| This spreadsheet calculates the implied risk premium in a market. This can be used in discounted cashflow valuation to do market neutral valuation. | implprem.xls |
| This model tries to do it all, with all of the associated risks and rewards. I hate having to work with a dozen spreadsheets to value a firm, and I have tried to put them all into one spreadsheet - a ratings estimator, an earnings normalizer, an R&D converter, an operating lease converter, a bottom-up beta estimator and industry averages. Try it out and make your own additions. | fcffginzu.xls |
| Stable growth, dividend discount model; best suited for firms growing at the same rate as the economy and paying residual cash as dividends. | ddmst.xls |
| Two-stage DDM; best suited for firms paying residual cash in dividends while having moderate growth. | ddm2st.xls |
| Three-stage DDM; best suited for firms paying residual cash in dividends, while having high growth. | ddm3st.xls |
| Stable growth, FCFE discount model; best suited for firms in stable leverage and growing at the same rate as the economy. | fcfest.xls |
| Two-stage FCFE discount model; best suited for firms with stable leverage and having moderate growth. | fcfe2st.xls |
| Three-stage FCFE discount model; best suited for firms with stable leverage and having high growth. | fcfe3st.xls |
| Stable growth FCFF discount model; best suited for firms growing at the same rate as the economy. | fcffst.xls |
| Two-stage FCFF discount model; best suited for firms with shifting leverage and growing at a moderate rate. | fcff2st.xls |
| Three-stage FCFF discount model; best suited for firms with shifting leverage and high growth. | fcff3st.xls |
| Three-stage FCFF valuation model, also presented in terms of projected EVA. | fcffeva.xls |
| A generalised FCFF model, where the operating margins are allowed to change each year; best suited for firms in trouble or transition or high-growth firms with negative earnings today. | fcffgen.xls |
| This is a model that uses a two-stage dividend discount model to estimate the appropriate equity multiples for your firm. It will give you identical answers (in terms of value) as the 2-stage DDM model. | eqmult.xls |
| This model uses a 2-stage FCFF model to estimate the appropriate firm value multiples for your firm. It will give you identical answers (in terms of value) as the 2-stage FCFF model. | firmmult.xls |
| This is a dividend-adjusted model for valuing short-term options. It considers the present value of expected dividends during the option life. | optst.xls |
| Tnis is a dividend-adjusted model for valuing long term options. It considers the expected dividend yield on the underlying asset. | optlt.xls |
| This is a model for valuing options that result in dilution of the underlying stock. Consequently, it is useful in valuing warrants and management options. | warrant.xls |
| This model estimates the value of financial flexibility, i.e, the maintenance of excess debt capacity or back-up financing. | flexval.xls |
| Derivatives | |
| Generates Black-Scholes Call and Put Prices and Hedge Parameters | blsh.xls |
| A 50 period Binomial Tree Model | Binomial2.xls |
| A simplified Binomial Tree | Binomial.xls |
| Implements the Black-Derman-Toy Model for Interest Rate Based Options | bdt.xls |
| Identify arbitrage using put call parity | pcp.xls |
| A model that uses option pricing to value the equity in a firm; best suited for highly levered firms in trouble. | equity.xls |
| A model that uses option pricing to value a natural resource company; useful for valuing oil or mining companies. | natres.xls |
| A model that uses option pricing to value a product patent or option; useful for valuing the patents that a company might hold. | project.xls |
| This model estimates the value of the option to abandon a project or investment. | abandon.xls |
| This model estimates the value of the option to expand in an investment project. Modified, it can also be used to assess the value of strategic options. | expand.xls |
| This model estimates the value of the option to delay an investment project. | delay.xls |
| Portfolio Analysis | |
| An Excel spreadsheet that demonstrates how to use Excel to calculate the covariance of two portfolios from the asset correlation matrix, asset standard deviations, and the two sets of portfolio weights. | portcovex.xls |
| Risk adjusted returns for a portfolio | rar.xls |
| Standard deviation for a portfolio | std.xls |
| This program allows you to do a basic capital budgeting analysis for a project, and compute NPV, IRR and ROI. | capbudg.xls |
| This sheet allows you to use past returns on a stock and a market index to analyse its price performance (Jensen's Alpha), its sensitivity to market movements (Beta) and the proportion of its risk that can be attributed to the market. | risk.xls |
| This sheet allows you to enter the current beta, tax rate and the debt equity ratio for your stock, and obtain a table of betas at different debt ratios. | levbeta.xls |
| This sheet allows you to estimate a rating and a cost of debt for your company from the firm's interext coverage ratio. | ratings.xls |
| Complete bond valuation spreadsheet. | BondValu.XLS |
| An Excel spreadsheet that calculates the MV and P portfolios on the minimum-variance frontier, and the MVE portfolio of risky-assets. eff3.xls is for three assets only | eff3.xls |
| An Excel spreadsheet that calculates the MV and P portfolios on the minimum-variance frontier, and the MVE portfolio of risky-assets. eff4.xls is for four assets only | eff4.xls |
| An Excel spreadsheet that calculates the MV and P portfolios on the minimum-variance frontier, and the MVE portfolio of risky-assets. eff6.xls is for six assets only. | eff6.xls |