Investment Banking University is the world's only university dedicated to higher learning in investment banking.
Don’t miss anything. Follow Us.
CALL +1 920 264 3054

Finance with Excel

Finance with Excel

Express your decisions using Excel. Excel is the premier business computational tool


Implement financial analysis using the tool for financial analysis, Excel


Valuation process


Heart of finance is time value of money and discounting


Excel Concepts Needed for Finance

Write down variables (defining the parameters of the decision)

Absolute or relative values copying (=A1) (=$A$1) and formulas

Functions (=fx(    ))

Data tables (“sensitivity tables”)


Express Decisions with Excel

Implement financial analysis with Excel




Using a Financial Model for Decision Making: The Investment Decision

Ability to get financing from financial institutions depends on ability to make a financial model for the new or existing business


The financial model projects future earnings from the organization


Predict the future performance of a firm.


Accounting statements report what happened to the firm in the past. A financial model predicts what the firm’s accounting statements will look like in the future. Start by taking the initial accounting statements and inputting them into Excel


Difference between accounting and financial model is in the current assets and current liabilities. In financial model we are concerned only with operating assets and operating liabilities. We exclude financing related


Financial model has three components:


Model parameters (value drivers)

Financing decision assumptions (i.e. Mix between debt and equity, what does firm do with excess cash? Repay debt, payments to shareholders, or as cash balance)

Pro forma financial statements


Cash in the financial model is a plug. The plug is so that the balance sheet balances.


Cash = total liabilities and equity – current assets – net fixed assets


The plug is the balance sheet item that guarantees the equality of the future projected total assets and future projected total liabilities and equity. Every financial model has a plug and the plug is almost always cash, debt, or stock.


Financial Model and Valuation Process:


Assumptions (value drivers)

Existing accounting statements (IS and BS)

Projected financial statements

Free cash flow calculation (FCFs)

Terminal value calculation

Valuation calculation

Sensitivity table for major value drivers to see range of valuation


Once the financial model is complete (i.e. accounting statements have been projected), we can use the model to:


Value the firm by projecting free cash flows (FCFs)

Determine ability of firm to pay it’s debts (i.e. credit analysis)


Using a Financial Model for Decision Making: The Financing Decision

All companies must decide how to finance their activities


Proportion of debt and equity


The discount rate should be appropriate to the riskiness (i.e. variability or beta) of the cash flows being discounted.


Discount rate is also called interest rate, cost of capital, opportunity cost.


Compute annualized IRR


The cost of capital of an investment is related to the risk of the cash flows of the investment. The relationship of individual asset returns to the risk is called the security market line (SML). You can use SML to get the discount rate for individual investments. The SML is used for private companies.


The cost of capital of an organization is related to the risk of the combined riskiness of the investments in the portfolio. The relationship of portfolio returns to the risk is called the capital asset pricing model (CAPM). You use CAPM to get the discount rate (i.e. cost of capital). When the investment is a public security, you use CAPM since the buyer of the security will have a portfolio to diversify away risk.


Portfolio risk is associated with statistics.


Wealth Maximizing Decisions

Investment decision – What is it worth? NPV of strategic alternative

Financing decision – What does it cost? IRR of financing alternative


Cash is King

Wealth maximization has to do with maximizing cash. Cash in the context or organizations is known as cash flow.


Return is a word for cash flows


Cash Flow Definition (FCF)

Profit after taxes

+ Depreciation (noncash expense)

+ Change in net working capital (- increase in current assets and + increase in current liabilities)

Capital expenditures (CAPEX)

+ After-tax interest payments

= Free Cash Flow (FCF)

Role of the Finance Professional

The role of the financial professional is to quantify the cash flows and risk of strategic alternatives available to the individual or organization.


Investment bankers compute the IRR and NPV of strategic alternatives.


Capital Markets

The capital markets is made up of cash flows and discounts


Capital Markets and Information

Information is valuable in determining investment and financing decisions in the capital markets. Overall, markets are weak form efficient meaning that their valuations reflect previous stock price performance (i.e. stock price data) and are sometimes semistrong meaning that valuations incorporate all public information. Capital markets are not strong form efficient meaning that valuations do not reflect private information.


Multiple Investment and Financing Decisions: Portfolio

When there is multiple investment and financing decisions, we have something called a portfolio. The discount rate can be decreased by diversifying with a portfolio. When the discount rate is decreased, the valuation of the portfolio increases as cash flows have maintained more value.


A corporation/organization is simply a portfolio of sources and uses


Modeling a Strategic Alternative

Put all variables (“value drivers”) at the top of the spreadsheet

Never use a number where a formula will also work

Blue for hard codes

Black for links and outputs



Finance: Exchanging Value Through Time

Assets have a time dimension


Future value function =FV( )

Value in the future of a sum of money compounded into the future


Present value =PV( )

Value today of future payments discounted to present


Net present value (NPV) =-First payment + NPV( )

Incremental wealth increase earned by a strategic alternative. NPV tells you economic value of an investment today. Always use NPV in the investment decision.


Internal rate of return (IRR) =IRR( )

Compound rate of return earned by a strategic alternative

VIII. Rate of Return vs. Cost of Capital

What is the asset’s IRR?


Compare to the cost of capital (Effective annual interest rate – which is the annualized IRR used to compare financing alternatives aka Compound Annual Growth Rate (CAGR))



Cost of Capital

Calculate IRR of financing alternatives to determine cost of capital


Need to get IRR in annual terms to facilitate comparison. May have to start with monthly IRR then annualize


Annualized IRR = (1 + Monthly IRR)^n-1


Finding a Value in a Financial Model

When we want to find a value by setting a particular value to another cell, we use:


Goal seek – Alt, A, G



Financing Alternatives: Loan Amortization

=PMT( )

To calculate the debt payment per period


=IPMT( )

To calculate the interest portion of the payment of debt


=PPMT( )

To calculate the principal portion of the payment

VIII. Financing Alternatives: Direct Comparison

IRR of differential cash flows tells you the cost of the option


IRR tells you the cost of the financing alternative


CAGR is Effective Annual Interest Rate (EAIR) to allow for comparison


Analyzing the Strategic Alternative: Sensitivity Table

Data Table is Alt, A, W, T


Tells you how output changes with incremental changes in the inputs (i.e. variables)


The Financing Alternative: Nominal vs. Real Cost

In determining the true cost of a financing alternative, it is important to use the real rate of interest which incorporates inflation. The real rate of interest is determined by using the real cash flows.


Inflation acts as a discount rate


Strategic Alternatives Analysis

For each strategic alternative, compute the NPV and IRR, then have decision rules for investing including:


Minimum NPV

Hurdle rate (IRR)


You are using NPV and IRR to make investment decisions but you need the discount rate. The discount rate is associated with the financing decision


Cash Flows and Risk

Are cash flows riskless (i.e. treasury bills) or are they risky (i.e. market portfolio)


Cost of Capital and Opportunity Cost

The returns of similar investments should be used as the cost of capital


The Discount Rate

An organization’s discount rate is the cost of equity and cost of debt. The cost of the total capital structure is known as the Weighted Average Cost of Capital (WACC):


WACC = rE* (E/(E+D)) + rD (1-Tc)*(D/(E+D))


Value of Equity

The value of equity is the present value of all future dividends

Sources & Uses

Uses                          Sources

Free Cash Flows          WACC

CAPM to get cost of equity


Accounting Statements: Statement of Cash Flows

The purpose of the statement of cash flow is to explain the increase in the cash accounts on the balance sheet as a function of the firm’s operating, investing, and financing activities.


Valuation Methods: Total Enterprise Value (TEV) vs. DCF

Market valuation:


Total Enterprise Value (TEV) = MVE + MVD + Preferred – Cash


  1. DCF Method (intrinsic value) = PV(FCFs) @ WACC + liquid assets


Accounting Value vs. Finance Value

Accounting value of firm is backward looking and thus incorrect to use in valuation. Finance value is forward looking and consistent with the fact that the owner of an organization or security has claims on the future cash flows of the business.



Free cash flow (FCF) calculations is DCF


Portfolio Analysis and the Capital Asset Pricing Model (CAPM)

Discount rate is a measure of risk associated with:





We get the discount rate by analyzing the distribution of an investment’s returns. We get the standard deviation which is a measure of variance in returns. Standard deviation is a component to finding the discount rate:




What does the frequency distribution look like?


Determine risk measure known as beta and plug this into CAPM to get the discount rate of equity. Derive the cost of debt and then calculate WACC to get the discount rate of the firm.


Ex Ante vs. Ex Post Returns

Ex Ante is the expected return


Ex Post is the actual return

VIII. Statistics for Portfolios

=Average( )

To get mean return


=Varp( )

To get variance of returns


=Stdevp( )

To get standard deviation of returns


=Covar( )

To get covariance between two sets of returns


=Correl( )

To get correlation between two sets of returns


Trendline (regression) – click on points of XY graph and right click to Add Trendline with linear regression and display equation and R-squared on chart


Portfolio Returns and The Efficient Frontier

Statistics are used to determine acceptable and unacceptable portfolios


Diversification lowers standard deviation of the portfolio


Are the returns correlated? If no, then add security to the portfolio (i.e. diversify)


The efficient frontier is the set of all portfolios that are on the upward-sloping part of the graph starting with the minimum variance portfolio (i.e. the market portfolio). Choose the portfolio that is on the efficient frontier.


The Efficient Frontier and the Optimal Portfolio

The best investment portfolio is made up of the risk free asset and a risky asset representing the market (i.e. the market portfolio)


Determine the market portfolio (the portfolio with the highest attainable sharpe ratio)


Market portfolio is the best combination of risky assets available to the investor


Security Market Line & CAPM

The security market line says that the expected return of an asset is a function of the asset’s beta (i.e. sensitivity to the market).


Only relevant risk is systematic risk since the investors will all be diversified


Security Market Line & Investment Performance

The security market line says that the expected return of an asset is a function of the asset’s beta (i.e. sensitivity to the market).


Only relevant risk is systematic risk since the investors will all be diversified


Security Market Line & Investment Performance

The security market line says that the expected return of an asset is a function of the asset’s beta (i.e. sensitivity to the market).


Only relevant risk is systematic risk since the investors will all be diversified

VIII. Security Market Line & Investment Performance Continued

Investment performance:


Risk adjusted performance; excess returns?



Risk Adjusted Performance

Market portfolio proxy is S&P 500


Beta is measure of riskiness of security


Alpha measures excess return


Market portfolio proxy is S&P 500


Beta is measure of riskiness of security


Alpha measures excess return


It is about investment performance versus the risk involved in the investment


CAPM & Investment Performance

Use CAPM to get the discount rate of equity and compare to cost of financing alternatives


Is there risk adjusted overperformance or underperformance?


Is performance commensurate with risk?


Excess Return

Excess return is the investment’s spread over the one year treasury (i.e. risk free rate)


Use regression equation to determine if underperformance (negative alpha) or overperformance (positive alpha)


When regressing asset’s returns against the market portfolio, alpha measures excess returns over the market portfolio


Beta & R^2

High beta is an aggressive stock


Low beta is a defensive stock


R^2 is percentage of variability that is market related risk when returns are regressed on the market portfolio


Diversification increases R^2 of the portfolio and decreases nonsystematic risk


Alpha and Efficient Markets

In efficient markets, there is no alpha and investments earn their risk-adjusted return


CAPM and the Cost of Capital

CAPM = rf + Beta [ E(rm) – rf]


In CAPM, use Beta of asset to calculate cost of equity


WACC is the discount rate based upon the capital structure of the investment


Valuing Securities in Efficient Markets

Market efficiency and the role of information in determining asset prices


Publicly available information should be reflected in market price

Post a Comment

User registration

Reset password