Jupiter.plymouth.edu
Sketches in Finance IIINTRODUCTION1Chapter 1 NATURE of FINANCE21.1 Academic Discipline11.2 Profession1 Investment Analyst21.3 Other Disciplines21.4 Ethics and Finance2Chapter 2 CORPORATE FINANCE 32.1 Objective of the Firm32.2 Financial Analysis32.3 Ratio Analysis32.4 Functions42.5 Capital Structure42.6 Capital Budgeting42.7 Net Present Value and Discounted Cash Flow62.8 Internal Rate of Return (Modified)72.9 Dividend Policy82.10 Cash Flow Management92.11 Ancillary Responsibilities9 Chapter 3 PORTFOLIO MANAGEMENT103.1 Modern Portfolio Theory103.2 PPS through Time103.3 Returns through Time103.4 Standard Deviation of Returns103.5 Second Security113.6 Volatility of Combined Securities113.7 Hyperbolas113.8 Efficient Frontier113.9 Tobin’s Line113.10 Number of Firms12Chapter 4 STRATEGIES134.1 Speculation134.2 Hedging134.3 Arbitrage134.4 Active Management134.5 Passive Management134.6 Efficient Mkt Hypothesis144.7 Capital Asset Pricing Model144.8 Behavioral Finance154.9 Fundamental Analysis154.10 Technical Analysis154.11 Black Swans & Fat Tails154.12 Ponzi Schemes16Chapter 5 VALUATION175.1 Valuation Defined175.2 Gordon’s Model175.3 Variations175.4 Caveats175.5 Model Weaknesses185.6 Valuation of Bonds185.7 Risk on Bonds185.8 Continuous Compounding195.9 Loans195.10 Amortization Table195.11 Expected Value19Chapter 6 DERIVATIVES206.1 Mutual Funds206.2 Index Funds206.3 S&P 500 Index Funds 206.4 DJIA Index Funds206.5 Options216.6 Black-Scholes Option Pricing Model 216.7 Straddles &Strangles226.8 Hedge Funds236.9 Credit Default Swaps236.10 Collateralized Debt Obligations23Chapter 7 FINANCIAL MODELS247.1 Alpha247.2 Beta247.3 Brownian Motion247.4 DJIA247.5 LIBOR247.6 MATLAB247.7 Monte Carlo Simulation247.8 Pi25Chapter 8 INTERNATIONAL268.1 Foreign Currency Exchange268.2 Arbitrage268.3 Two Way Currency Arbitrage278.4 Three Way Currency Arbitrage278.5 Forward Contracts288.6 Futures288.7 Currency Options29Section 9 APPENDIX299.1 Time Value of Money299.2 Correlation Coefficients309.3 Regression Analysis319.4 Z-Scores319.5 Normalization31Section 10 EXERCISES32 10.1 Ratio Analysis3210.2 NPV and IRR*3310.3 Creating a portfolio3410.4 Creating a hyperbola3510.5 CAPM and beta3610.6 Valuation of Stocks, Bonds, and Loans 3710.7 BSOPM3810.8 Number of Firms3910.9 Cross Rates, Arbitrage and Forward Contracts4010.10 Final Exam41 INTRODUCTION: These notes were compiled from class lectures in various financial courses. They are not intended to break new ground, to promote any particular investment philosophy, to be a complete reference, or to be the source of high entertainment. They are intended to replace the traditional textbook by touching the peaks of topics considered essential in an introductory finance course but with an economy of verbiage and economy of cost to the student.Chapter 1 NATURE of FINANCE: The nature of the broad discipline of finance may be regarded from slightly different perspectives: That is, one might regard finance 1) as an academic study, or 2) as a professional activity with a particular set of skills. The nature of finance is also revealed by noting 3) the relationship between finance and other academic disciplines, and finally, by noting 4) the relationship between finance and ethics.1.1 Academic Discipline: As a field of academic study, the primary focus of finance is often on the firm and the financial management of the firm because such a large proportion of the total wealth in a capitalistic economy is created within the context of the firm (i.e. the enterprise, the corporation, the business, the private sector, etc.). For this reason, introductory finance courses are variously known as financial management, corporate finance or managerial finance. However, a secondary academic perspective is external to the firm and focuses on individuals and institutions (acting as investors) that participate in the capitalization of the firm through the purchase of equity securities (stocks) or debt instruments (bonds). As an academic study this second perspective is referred to as investing or portfolio management. There is yet a third perspective in the academic discipline of finance that regards the activities engaged in by financial professionals who design new contracts (financial engineering), institutions that lend and borrow capital (investment banking), and others who take speculative positions based on the performance of other instruments (institutional finance). And in addition, more specialized courses, for example in international finance, financial modeling, and financial accounting, cover various niches of finance in greater detail. 1.2 Profession: As a professional activity, the practitioner needs to understand the nature of corporations, the laws (regulations, standards, protocols) governing financial activities, the mathematics of the financial instruments, the accounting standards adopted by the firm, and the economic theories that are inherent in the actual management of the firm and the management of funds that may be held external to the firm.The particular skill set required to successfully participate in the financial profession include many of those universally found in other professions, for example the ability to work with others, the ability to communicate clearly in written and spoken discourse, and the ability to develop and practice productive work habits. However, some skill sets are more essential to finance than to some other professions, for example - the quantitative skills of mathematics and quantitative modeling and the skilled use of information technology. Below is a job description for an investment analyst at a large financial institution. It reflects the skills sought in the real world:Investment Analyst (IA) [an entry-level investment/financial analyst]Role: The Investment Analyst is responsible for participating in research and modeling work on various traditional and alternative asset classes for institutional, private bank, domestic and international retail, and high net worth businesses. The IA performs evaluation of products for inclusion in asset allocation models and develops and maintains proprietary optimization and simulation models and participates in asset allocation related projects with clients. The IA provides general support of private bank and consumer bank with respect to risk management, portfolio analytics, attribution analysis, and other general investment topics. The IA participates in marketing efforts and client meetings.Education and Experience: MBA in finance or similarly quantitative field, and/or CFA.? Five to ten years experience in portfolio management or quantitative analysis is required. Specific work in asset allocation is a plus. Quantitative background and programming skills (e.g. VBA and MATLAB) are required.Additional qualifications:Financial expertise: Practical understanding of financial principles and portfolio management concepts; familiarity with, risk and valuation models. Statistical understanding: Statistical methods must be understood well enough to judge their efficacy and drawbacks in specific circumstances; must be comfortable dealing with numerical and statistical concepts. Computer understanding:? Comfortable with statistical analysis and analytic programming; familiarity with commercial analytic packages (e.g., VBA, MATLAB).1.3 Other Disciplines: Finance has clear connections with other disciplines (e.g. law, marketing, information technology), but often the distinction between finance and economics, and between finance and accounting gets fuzzy. Finance has sometimes been considered a sub-type, a "grubby off-spring", of economics, owing to finance's pre-occupation with actually making money for the practitioner. Economics, on the other hand, tends to be more of an intellectual pursuit, seeking understanding of the cause and effect relationships among variables. The relationship between finance and accounting is similarly fuzzy, except that accounting is the more mundane number-crunching activity that compiles the necessary statements for the higher-order financial types. [The allusion to any actual superiority of one discipline over another is purely artistic license used to illustrate a point.] Psychology has more recently joined forces with finance as both disciplines strive to understand the markets which are an aggregate of individuals making financial choices. Within the last decade, the discipline of behavioral finance has emerged as a combination of these two disciplines. And finally, mathematics, the pure science, provides the tools for financial calculations.1.4 Ethics and Finance: In other cultures and in other times, the charging of interest was immoral and illegal. Lending money to a neighbor was considered acceptable, but charging interest on the loan was considered harvesting money from a source into which no labor was provided. Ergo, interest must have come from the Devil. Times and culture have changed to where “charging (reasonable) interest” is universally accepted as fair play, yet there are still ample opportunities to take unethical advantage of our imperfect economic system. Harry Markowitz has some thoughtful comments in "Markets and Morality" related to ethical issues in capital markets. Chapter 2 CORPORATE FINANCE 2.1 Objective of the Firm: The discussion of finance within the corporation is best opened with the existential questions of “why does the corporation exist?” Or, put another way, “what is the objective of the firm?” A widely accepted financial theory states that the firm exists for the benefit of the owners of the firm. The owners, or investors, put up the capital, initiated the firm’s incorporation, and are assumed to be motivated by the capitalistic desire to maximize their personal wealth through the maximization of the value of the firm as reflected in the stock price. As a consequence, owners hire agents to manage the firm in a manner that will attract new investors and create a demand for equity in the firm. Owners benefit as the demand for the stock goes up, as the price per share goes up, and the market value of their investment goes up. 2.2 Financial Analysis: Financial analysis of the firm is practiced by outside investors [outside of the firm], owners of the firm, and the management of the firm. The term "financial analysis" has different shades of meaning, but in general, the analysis regards the financial health and value of the firm from various perspectives. For example liquidity, leverage and operational efficiency are all measures of financial health, and measuring those properties may reveal financial strengths and/or weaknesses. Financial analysis may also refer to the process of stock valuation whereby the analyst comes up with estimated price per share that the stock "should" be selling at. Benjamin Graham built a reputation on techniques to analyze the inherent value of firms. Other techniques refer to "fundamental" analysis, that regards fundamental factors such as market share, annual growth, technological advances, and ratio analysis to establish a "fair market value". "Technical Analysis", in contrast to fundamental analysis, regards quantitative and statistical patterns and trends in (primarily) the firm's stock price to forecast future stock prices. 2.3 Ratio Analysis: Ratio analysis is the classic approach to conducting corporate financial analysis. The technique has been used for over 100 years and many different ratios are commonly used. In the descriptions below, note that when “one value (A) is compared to another value (B)”, the calculation of the ratio is A divided by B. This calculation may also be framed as “A relative to B”. Some typical ratios include the following:The Current Ratio shows the liquidity of the firm by comparing the current assets to the current liabilities. Note: the word ”current” in this context refers to the accounting definition. That is, current assets are assets that could reasonably be expected to be “convertible into cash within a year”. Current assets are usually explicitly designated as such on the balance sheet. Current liabilities refer to payments that are due within the year. A healthy firm would want a current ratio greater than 1.00, and a ratio of 2 or 3 would certainly add a margin of comfort.The Quick Ratio, or “Acid Test”, is a more stringent measure of liquidity, in that it subtracts inventory from the current assets before doing the same comparison as with the Current Ratio (above). Inventory is not considered to be as liquid as other current assets.Days Sales Outstanding (DSO) is a measure of the firm's ability to manage one portion of its assets - accounts receivable. Sometimes called "average collection period", it is a measure of how quickly receivables can be converted into cash. Fewer days is better than more days, and an average of a month and a half is normal for many industries. It is calculated by dividing sales into receivables to get the proportion of sales still outstanding and then multiplying the results by the number of days in a year.Inventory Turns is another measure of the firm's asset management prowess. In this case, the skill in managing inventory is measured – having the right stuff on the shelf when needed, not having too much in stock, and moving stuff quickly. Inventory Turns represents the number of times the firm has "filled and emptied" its shelves in a year. More turns is better than fewer turns. There is a wide range of what is a normal number of turns, depending on the nature of the business. Some firms use average inventory in the calculation, others use end-of-year inventory. Some analysts divide inventory into sales, and others divide inventory into cost of goods sold. This course uses COGS/EoY inventory. The Debt Ratio measures the proportion of debt (Total Liabilities) relative to the total capitalization (Total Assets) of the firm. Somewhat counter-intuitively, more debt is not necessarily a bad condition FOR A FIRM (debt is still something to be avoided for the individual). A firm that avoids borrowing, at the expense of foregoing profitable projects, may be losing opportunities that its competitors end up taking advantage of. Debt ratios will usually fall between 0 and 1.00. Over 1.00 is definitely a bad sign. But between 0 and .80, it's difficult to say whether a change is better or worse without further information.Times Interest Earned (TIE) indicates a firm's ability to cover its interest payments due on its bonds with its profits from operations. Said another way, TIE is the number of times the firm could pay its interest obligations with its earnings before interest and taxes (EBIT). The calculation is EBIT relative to Interest. Less than 1.00 would be a bad sign for sure, and multiples like 2, 3 and higher are healthy signs.Profit Margin, Earnings relative to Revenues, is the percentage of the top line (sales=revenues) that flows down to the bottom line (EAC=net profit=net income) after taking out all the expenses. A firm that keeps two cents for every dollar of sales is typical, although there are certainly more profitable firms, too.Return on Assets (ROA) Pretty self-explanatory. ROA measures the firm's bottom line profits as percentage of the total assets of the firm. Sometimes called "basic earning power", it suggests that a firm should be able to earn a (relatively) fixed percentage of every dollar invested in the firm. A 12% ROA is not unusual.Price Earnings (P/E) Ratio is unlike the ratios above in that it is not generated solely from the income statement and balance sheets – it also requires the price per share (pps) from current market data. This ratio regards the price investors are willing to pay for a dollars worth of earnings. The earnings are usually historical, trailing twelve months (ttm), although the P/E ratio often compares current pps to estimated (future) earnings. The P/E ratio is often abbreviated to PE ratio and is sometimes called the firm's "multiple". The reciprocal of the PE is called the "earnings yield", the percentage of net earnings generated each year for a given price per share, and is perhaps a more intuitive way of understanding the relationship between pps and earnings available to common stockholders (EAC). Remember, EAC belongs to the owners of the firm – the shareholders. As of the closing bell on 17 July 2009, the average PE for the DJIA was 13. As PE is a measure of "pricey-ness" of a stock, 13 is cheap, over 20 is getting pricey.Market to Book is similar to the PE ratio in that it relies on current pps data. In this ratio, "market" refers to the value that investors have put on the firm, the "market capitalization" or MKT CAP (as seen on Yahoo). Market cap is the total current market value of all outstanding stock, or pps times number-of-shares. Pretend you wanted to buy IBM, as in, buying the whole company. How much would it cost? That's market cap. In mkt/book, "book" is how the accountants value the company and is found on the balance sheet as "shareholders equity". Mkt/book is another measure of pricey-ness, and typical ratios run 2.0 to 4.0.2.4 Functions: As the primary objective of the firm is to maximize stockholders wealth as reflected in the stock price, then the primary function of financial management is also to address that objective. Management accomplishes this through convincing investors that the "quality of future earnings" is strong – that is, that future earnings are both "likely" (have a high probability of coming to pass) and robust (exhibiting healthy growth through time). If management is effectively convincing, then a demand for a share of ownership in the firm will apply the upward pressure on the price, and management will have succeeded in fulfilling their objective - for the time being. The function of a financial manager is to support the primary objective through simultaneous actions in four major areas: 1) Capital Structure, 2) Capital budgeting, 3) Dividend Policy, and 4) Cash Flow Management. 2.5 Capital Structure: Financial managers are responsible for the "capitalization of the firm", or identifying and executing sources of funding. The two major sources are stocks (selling equity) and bonds (issuing debt), with a third, and not inconsequential, source being the internally generated capitalization from earnings. This area is also referred to as "the capital structure" decision, in which the financial manager tries to anticipate the optimum mix of debt and equity (proportion of bonds to stock) to achieve the primary objective of maximizing stock price. While there are many models addressing the capital structure decision, the study by Miller [see also: Varian] raises doubts as to those models' efficacy. In finance, capital structure refers to the way a corporation finances its assets through some combination of equity, debt, or hybrid securities. A firm's capital structure is, then, the composition or 'structure' of its liabilities. For example, a firm that sells $20 billion in equity and issues (borrows) $80 billion in debt is said to be 20% equity-financed and 80% debt-financed. The firm's ratio of debt to total financing, 80% in this example, is sometimes referred to as the firm's leverage. In reality, capital structure may be highly complex and include multiple sources. The Modigliani-Miller theorem, forms the basis for modern thinking on capital structure, though it is generally viewed as a purely theoretical result since it assumes away many important factors in the capital structure decision. The theorem states that, in a perfect market, how a firm is financed is irrelevant to its value. This result provides the base with which to examine real world reasons why capital structure is relevant, that is, a company's value is affected by the capital structure it employs. These other real world reasons include bankruptcy costs, agency costs, taxes, information asymmetry, to name some. This analysis can then be extended to look at whether there is in fact an optimal capital structure: the one which maximizes the value of the firm. [Adapted from: Wikipedia "capital structure"]2.6 Capital Budgeting: The next major decision is in the use of the capitalization that was raised as a result of the capital structure decision. The "uses" of capital in the corporate environment is called "capital budgeting", and the objective of this area is, again, the maximization of stock price. In cruder terms, the firm just raised a lot of money. Now, what should they to do with it? They should invest it into projects that have the greatest positive impact on the stock price. The terminology of capital budgeting refers to "capital projects", or those activities that require some up-front investment in hopes of reaping longer-term rewards. Capital projects may take the form of buying another company, building a new manufacturing plant, developing a new product, purchasing equipment, entering a new market – there is a wide range of investments considered “capital projects”. One could say that “attaining a college degree” is a capital project. Invest time and money now in hopes of greater returns in the future. Also, recall the accounting distinction between capital spending and expense spending: capitalized items become assets on the balance sheet and are depreciated over time and expense items are shown as costs for the current time period on the income statement. The current US tax code favors capital spending by allowing accelerated depreciation so as to realize tax savings sooner than later. The capital budgeting process involves 1) identifying potential projects, 2) estimating the incremental cash flows related to the respective projects, 3) identifying and quantifying risk associated with each project, 4) application of various financial models to quantify the projects value to the firm, prioritize the project relative to other potential projects, and to make the final decision as to whether to pursue the project. 2.7 Net Present Value and Discounted Cash Flow: The net present value (NPV) method and the discounted cash flow (DCF) method are two names for the same process. It is a technique used to take an estimated cash flow and to discount it to yield a net present value. a. Cash Flows (CF) are the marginal changes in cash to the firm that are anticipated as a result of the adoption of a project. These cash flows would reflect estimated changes in revenue and direct expenses and the magnitude of these changes are generally estimated by engineers and cost accountants. Financial analysts assume the responsibility of calculating the net present value of the cash flows. b. Discount Rates: To discount a cash flow (CF), one must first determine the discount rate. (Not to be confused with the Fed's “Discount Rate” which is an entirely different concept.) For academic purposes, the discount rate is often given as a constant, although sometimes it is a variable increasing through time. In the following models, we use "k" as the variable for the discount rate. For corporate purpose, there are various theoretical approaches to determine the rate's intrinsic value. For example, one approach suggests using the weighted average cost of capital of the firm and then adding a risk premium for the particular project to yield a discount rate. c. Ascending Discount Rates: In the real world the determination of discount rates may be surprisingly subjective. And in capital budgeting spreadsheets, the discount rate used to discount the cash flows may be a variable rate rather than a fixed rate for the entire period of the project. Typical of ascending rates is a constant increase through time (ascending) to reflect the increasing uncertainty about the cash flows actually materializing as forecasted. The assumption for the project may be something like:"The discount rate for this project, for this year (year 0), is 14%. However, with the increase in uncertainty about years 1 through 5, we're adding ?% each year to the discount rate."year012345discount rate0.140.14250.1450.14750.150.1525d. Discount Factors: Once the discount rates have been determined, the discount factors can be calculated using: discount factor = (1+k)-n , where "k" is the discount rate and "n" is the number of years hence. You'll recognize this from PV=FV(1+k)-n, where "FV" is the future value, or the future cash flow.The spreadsheet formula format for the discount factor is: =(1+k)^(-n), where actual cell locations are substituted for k and n. e. Present Values: Using the PV formula above, each year's cash flow is multiplied by its respective discount factor to get its present value. This step is applied to every year including "year 0".f. Net Present Values: The present values for all the years (including year 0) are added together to get the "net present value(NPV)", or in other words, the "discounted cash flow (DCF)".The NPV is the current value of all the project’s cash flows, current and future. A firm's total net value increases by the NPV of a project when the firm commits to that project. This “increase” may seem counter-intuitive: a firm writes a check for $100K, committing itself to a new project, and suddenly the value of the firm goes up by $120K? Sure. The value of the project is $120K, and the firm is going to engage in the project.This notion of increasing the value of the firm can be carried a little further. If the value of the firm goes up, then so, too, goes the stock price. They are, after all, directly proportional. The value of firm divided by number of shares equals stock price. Hence, the theoretical change in stock price for taking on a project will be the NPV of the project divided by the number of shares. The NPV relates to the rate of return of the project in the following way: If the discount rate (think "cost of money") is less than the return of the project, then the project will "make money". More technically, if the discount rate is less than the internal rate of return, then the NPV will be positive. And, conversely, if the discount rate is greater than the internal rate of return, then the NPV will be negative. Note also, that the discount rate is externally determined (e.g. by credit markets, and risk factors), whereas the project's rate of return is internally determined by the project's cash flows and discount rate. Finally, if the discount rate and the projects rate of return are equal, then the project has no positive value and isn't worth doing. This will be reflected in an NPV=0 and is intuitively illustrated with the scenario of borrowing money at 20% and putting the money in a 20% project.Twisting the preceding paragraph around a bit, we could say that the internal rate of return (IRR) on a project is that rate that when applied to the cash flow yields a net present value of zero. And that is the definition of IRR. 2.8 Modified Internal Rate of Return: The IRR model referred to in the paragraph above has a short-coming. The IRR model assumes that cash flows generated by one project can be re-invested into a second project that will yield the same rate as the original cash-generating project – and this may not be a valid assumption. Thus, a modified internal rate of return (IRR*) model has been concocted to address this shortcoming. The IRR* model assumes that the positive cash flows generated by a project are reinvested at the firm's discount rate to the end of the last year of the cash flow. This assumption differs from the reinvestment assumption of the ordinary IRR model, which assumes implicitly that reinvestment of the cash flows are made at the derived IRR. In IRR*, the sum of the reinvested cash flows are an estimate of the future terminal value (TV) of the project (not counting the original investment). The terminal value is subsequently reevaluated to reflect its net present value, using a rate (the IRR*) that will discount the TV to an amount exactly equal to the original investment. By definition, the hypothetical discount rate which yields a net present value (NPV) of zero is the project's internal rate of return. 2.9 Dividend Policy Decision: One of the major corporate financial decision areas is the dividend decision, or “how much of after tax earnings should be paid out to stockholders in the form of dividends”. [See also 1.8 and 1.11]. The criteria for determining dividends should be, in theory, "what policy will maximize the stock price". And while there are some legal constraints regarding the magnitude of a dividend payout, the decision lies wholly with the board of directors. a. Accounting: Quarterly Earnings Available to Common (EAC) is split into Common Dividends and Retained Earnings. That is, funds not paid out as dividends are folded back into the firm as Change in Retained Earnings (ΔRE). The ratio of dividends to EAC is the “payout ratio”. The ratio of ΔRE to EAC is the “retention rate. The dividends may represent a significant contribution to an investor’s total overall return from owning the stock, and Retained Earnings can represent a significant source of funding for the growth of the firm (sometimes called “internally generated capitalization”). b. Modigliani & Miller (MM): MM addressed the dividend decision from the perspective similar to their approach to the capital structure decision – that is, from a strictly academic/theoretical viewpoint. Their assumptions regarding transaction costs, taxes, and other market “frictions” are equally rarefied. Their conclusion is that the magnitude of dividends is irrelevant to the determination of stock price, or more bluntly, that dividend policy doesn’t matter. They note that stock price is more a function of return on assets (ROA), or the “basic earning power” of the firm. They do acknowledge that changes in dividend policy may send ambiguous messages to stockholders about the future of the firm, which may, in turn, create changes in demand for the stock. They also acknowledge that changes in dividend policy may alter the market for the stock – a phenomenon elaborated upon in their “clientele theory”.c. Gordon & Lintner: Gordon and Lintner base their theory of the significance of dividends on Gordon’s discounted dividend model, P0=D1/(ke-g), which implies that dividends are the sole determinant of stock price. Mathematically, changes in stock prices would be directly proportional to changes in dividends. Their theoretical justification for this opinion is liquidity preference, that is, investors prefer having dividends paid to them now, rather than seeing funds folded back into the firm in hopes of higher future rewards. MM responded by calling this argument a "bird in the hand theory" after the proverb “a bird in the hand is worth two in the bush”.d. Empirical Test: Harkavy conducted a traditional event study in which certain events are compared to actual changes in stock price in order to observe any possible causal relationship between two variables. In his empirical test he compared changes in payout ratios, the independent variable, to changes in stocks price, the dependent variable, and found a slight positive correlation between the two ratios. Both MM and Gordon & Lintner claimed that Hakavey's study lent credibility to their respective competing theories – MM asserting that the correlation was de minimus and Gordon & Lintner claiming that the positive correlation in the data was consistent with their position.e. Theory of Residuals: Walters suggested a normative model that posited that firms should use Earnings Available to Common shareholders (EAC) to fund projects whose Internal Rate of Return (IRR) are expected to be greater than the stockholders required rate of return. The theory is that a firm’s EAC should be reinvested at the highest available IRR, regardless of whether that IRR is available internally or with the investors. Funds that are not expended on these high IRR projects should be paid out to stockholders in the form of dividends. f. Realworld models: Theoretical models suggest what should be done. Realworld models describe what firms actually do. Here are a sample of various dividend policies.i. Constant dollars: The firm pays out same dollar amount every quarter.ii. Constant dollars with a “kicker”: Although quarterly dividends generally stay constant, occasionally a firm will add an unanticipated extra, a bonus or kicker.iii. Constant increases: Some firms take pride in increasing the dollar amount of their dividends every quarter.iv. Constant payout ratio: Other firms attempt to hold to a steady payout ratio (div/EAC) each quarter.v. Custom Policies: An example of a custom policy is the one time dividend policy of the Norton Company, a closely held, but technically a publically traded firm. During a time of double digit inflation (late 1970’s), their board of directors, many of whom were descendants of the founding family, decided to match dividend increases to general price increases as measured by the Consumer Price Index (CPI). In this way, shareholders could see the effective buying power of their dividends stay constant in spite of the rising cost of living.2.10 Cash Flow Management: The first three functions (raising the funding, investing the capital, distributing the harvest) all require a careful oversight of the flow of actual cash. It is this "cash flow management" function that is cited as the fourth major function of the financial manager. Cash flow management requires planning and oversight with diligent accounting skills. 2.11 Ancillary Responsibilities: The traditional functions of the financial manager of the firm include the four major areas described above. In addition, the financial manager often has oversight responsibility for the non-financial areas of accounting and information technology. Accountants are the first to gather the monetary transactions of the firm and to put those transactions into some sort of order for the raw material for the financial departments. Therefore the financial manager has vested interest in the integrity of the work of the accountants. And information technology made its corporate debut in the accounting department (for the processing of those same transactions) and likewise fell under the management umbrella of the financial manager. But as for the clearly financial functions, the four functions above are the most often mentioned.Chapter 3 PORTFOLIO MANAGEMENTThe practice of finance is not limited to the financial management of the firm. Considerable wealth is held by unincorporated entities such as individuals, trusts, non-profit organizations, municipalities and a wide range of other institutions. And financial institutions, while often technically incorporated, manage their financial assets with portfolio management models more than with "corporate" revenue-to-profit models. The wealth held in portfolios, or collections of the financial instruments, and the management of these portfolios requires financial professionals with portfolio management skills. 3.1 Modern Portfolio Theory (MPT): The discipline of portfolio management had a major incarnation with the introduction of Modern Portfolio Theory (MPT). See also the first section of the Varian article on Markowitz. Prior to MPT, portfolio management focused on active stock picking – that is, speculating and selecting those stocks with the highest expected returns. This is an intuitive and still popular focus of many investors. However, with MPT, Markowitz quantified the notion of minimizing risk through diversification, and lowering the probability of losing principal. This is not to say that "maximizing returns" is wrong-headed or undesirable, but it suggests that "minimizing risk" is a worthwhile and simultaneous endeavor. His model identifies a set of portfolios that have the lowest risk for a given return - or said another way, a set of portfolios with the highest return for a given level of risk. This set is the “efficient frontier”. 46228003529330003.2 PPS through Time: The development of the MPT model can be illustrated with a series of charts. This first chart shows daily stock prices, or price per share (pps), through time. In this example the time span is one year and shows pps at the close of each trading day. The blue dash line is a projection of the original value at the start of the measured year. The short red vertical line represents the change (Δ) in pps over the course of the year. 46228004924425003.3 Returns through Time: The second chart shows returns (K) for each of those same trading days using the classic “K = Δ/orig” model, or “returns equal the change in price divided by the original price”. “K” is a percentage, and is expressed either as a percent (i.e. 25%) or as a decimal (i.e. .25). Although dividends are ignored in this return calculation example, total stock returns are more correctly calculated with dividends, taxes and transaction costs factored in. 45262806459855003.4 Standard Deviation of Returns: The third chart illustrates the calculation of the variability, (the "bounciness" of the data, or volatility) of the returns. The statistic that reflects this volatility is the standard deviation (σ) of the returns, and is, by definition, the risk of the stock. There is a natural attraction to think of risk as the volatility of the price, and indeed, a bouncy price will cause bouncy returns, however, the volatility of the returns is the accepted measure. Note that data in this third chart is the same as the second chart, but a line has been added representing the mean and a normal distribution of returns based on the mean and standard deviation. 4483735144145003.5 Second Security: MPT suggests that the standard deviation of the returns, the risk, can be mitigated by the introduction of a second security whose return pattern is significantly different than the original security. In the adjacent chart the original security (from the previous charts) is shown as "A" in blue, and the second security is shown as "B" in red. Their respective distributions, based on their respective means and standard deviations, are shown on the right side of the chart. The two distributions might be similar, but probably won't be identical. 45974001581785003.6 Volatility of Combined Securities: As the two securities are combined into a single portfolio, the resulting composite return (the return of the portfolio) is shown in blue in this chart. A correlation coefficient is used to compare return patterns, with a low correlation indicating greater differences in patterns, and results in a portfolio with less volatility (less risk) than either of the original securities. [This is a good chart to click on to better see the low risk portfolio].49498253039745003.7 Hyperbolas: Chart 3.7 illustrates this same phenomenon from a different perspective. Again, consider the same securities, A and B. A has some historical risk/return profile, and let's assume that B has a somewhat higher risk and higher return (consistent with the CAPM, as shown here). A portfolio with 100% of the dollar invested in A and 0% in B, would have a risk/return profile ( σ vs. K%) at point A on the chart. As B is incrementally added to the portfolio, the return of the portfolio will rise and the risk will be reduced, the risk/return profile starts moving toward the upper left in the chart, beginning to trace a hyperbola. Around the apex of the hyperbola, there is a 50/50 dollar mix of A and B in the portfolio, and at this mix, the portfolio of A &B has lower risk than either A or B. Magic. The returns of the portfolio will always be a weighted average of the returns of the components. 50025304873625003.8 Efficient Frontier: A set of portfolios composed of six securities will trace five hyperbolas (A to B, B to C, etc.), but will also define an area representing every possible weight-combination of A,B,C,D,E,F. The upper left boundary of this set of portfolios is a line that represents those portfolios with the lowest risk for a given return (or the highest return for a given level of risk.) This boundary is the "efficient frontier".47009056223000003.9 Tobin’s Line: Tobin posited that a line emanating from the risk-free rate (Krf)and laying on the efficient frontier will have a point of contact at the risk/return point of market portfolio (e.g. an S&P 500 index fund). The line between the risk-free rate and the market portfolio represents a set of portfolios comprised of varying weights of risk-free assets and market funds. The line extending beyond the market represents a set of portfolios, all fully invested in the market funds, but with ever increasing amounts of borrowing at the risk-free rate.4791075309880003.10 Number of Firms: The last chart shows that as more firms are added to a portfolio, the risk of the portfolio tends to decline to the level of the market's risk (systematic risk). The first point on the left on the red line represents a portfolio with one security. The risk of the portfolio is thus the same as the risk of that single security. The next point shows how the addition of a second security to the portfolio reduces the risk of that portfolio. With the addition of each additional security, the portfolio tends to diminish until it reaches the market risk. Note that the market risk is the same as the risk of a portfolio with every publically traded security included.Chapter 4 STRATEGIESThis topic explores different financial strategies that are used by firms, individuals, and institutions. Items in this list are not necessarily mutually exclusive (an investor might use a combination of several strategies), but they are representative of currently popular approaches. Excluded from the list are some well-known and commonly practiced approaches that are rational methods for saving and investing (e.g. 401Ks, dollar-cost-averaging, buy-and-hold, buy low-sell high) but whose underlying assumptions are too obvious to warrant further academic discussion in this context.4.1 Speculation: This strategy assumes that the investor has some special insight into what might happen in the future. As the investor believes that the price of a security will go up, he/she will "buy low" and wait for the security to go up and "sell high". This special insight may be due to the recognition of an historical pattern that other investors either do not perceive or perhaps other investors believe the pattern does not apply to this particular environment. The notion of "alpha", excess returns above market returns, are tied to this strategy. Speculators believe they have a good chance of generating alpha.4.2 Hedging: This strategy requires Investing in multiple instruments that are likely to perform differently from each other, such that if one investment under-performs, the other is likely to over-perform. A passive investor whose entire portfolio is comprised of market index funds has essentially hedged all the firm-specific risk and is guaranteed, by definition, not to generate any alpha, but to achieve the market return. Hedge funds derive their name from this strategy, as they originally invested in instruments that were negatively correlated with existing portfolios, however, through the years, many hedge funds have tended to follow more speculative strategies 4.3 Arbitrage: This strategy requires highly sophisticated computer systems to identify relatively small dis-equilibriums in a market and trading with very large sums of cash. For these two reasons, arbitrage is engaged in by institutions rather than individuals. A simple example of an arbitrage would entail noticing a small price difference in the price of gold in two markets, buying at the low price in one market and immediately selling in the other market at the higher price. Large differentials in prices tend not to occur, and small differentials tend to disappear quickly. But imbalances do happen in the normal course of changing prices and it is in the process of arbitrage that prices tend to seek equilibrium. 4.4 Active Management: [a term used in the context "active versus passive"] An active investment management strategy implies an underlying belief that through rigorous analysis, quality information and rational decision making, an investor can, on average, beat the market. A disparaging phrase for this approach is "chasing alpha". Stock brokers and professional portfolio managers tend to fall into the category of "active" managers, as they sell their clients on the concept that their active management adds value to the portfolio and justifies their management fees. Traditional portfolio management fees are about 1% of the portfolio's value per year. Carried to extremes, an overly active manager who continually re-balances a client's portfolio, presumably for the additional commissions related to the trades, might be accused of "churning", not for higher returns, but for excessive fees. 4.5 Passive Management: [in contrast to "active" above] A passive investment strategy implies an underlying belief in the efficiency of markets and the futility of chasing alpha. This strategy manifests itself in the investment in market index funds, such that the returns of the investor will invariably approximate the market, except for the minimal management expense fees (typically .25% or less) charged by index funds. Professional portfolio managers may use this strategy with clients who have little interest in the details of the allocation of assets. The value added in passive management is in the knowing which index funds to invest in, how to mechanically implement the investments, and in being able to justify the reduced activity in the portfolio. 4.6 Efficient Market Hypothesis (EMH): Eugene Fama of the University of Chicago is credited for having brought EMH into focus. His premise is that financial markets (and equity markets in particular) are efficient in the sense that prices in the markets respond instantaneously to new information, and as a consequence, prices are always reflective of the fair market value. These assumptions imply that an investor cannot consistently beat the market - with the following caveat: Given the great number of investors in the world, there exists the very real probability that a few investors will consistently beat the market simply through blind dumb luck. And the fact that these lucky investors may also be intelligent, honest, hard working, and convinced (as well as being convincing) of their special prowess does not negate the randomness of their golden touch. The Mann interview [unavailable at the time of this writing] with Eugene Fama elaborates on this theory. 4.7 Capital Asset Pricing Model (CAPM): Relate to EMH (above) is the Capital Asset Pricing Model (CAPM) that was developed by William Sharpe. He argued that in an efficient market the return on an asset (a security, a stock) should be a function of the risk of that security, where risk is measured by beta. Here are the underlying mechanics of CAPM:When historic return data for a particular security (Ke) are regressed against historic return data for the market (Kmkt), the resulting regression line illustrates graphically how much of the changes in the returns of the security are "caused by" the changes in the returns of the market.? Remember, in its general form, regression analysis compares a dependent variable (y) to an independent variable, (x) and yields a "regression line" of the form y = mx + b.? In this particular application related to financial returns, the dependent variables are the returns on the security (Ke) and the independent variables are the returns of the market (Kmkt), where the market is usually defined as the S&P 500 .? Thus, when the returns of an individual security are regressed against the returns of the market, the resulting slope of the regression line (the line of best fit, or the line of least squares) is defined as "beta" (or the volatile ty of a security's return relative to the volatility of the returns of the market).? Obviously, when the returns of the market are regressed against the returns of the market, the slope of the line will be 1.000.? Therefore, by definition, the beta of the market is 1.00. The CAPM model estimates the return on a security as being the return on a "risk free security", plus a "risk premium". Traditionally, a US Treasury instrument, perhaps a 3-month bill or a 10-year note, was used to represent a risk free security. But contemporary derivative models are tending to use the London Inter-Bank Offer Rate (LIBOR) as a more global risk-free instrument. In CAPM the "risk premium" is estimated to be β(Kmkt-Krf), or beta times the difference between the expected return of the market and the risk free rate.? Thus the CAPM model:?? Ke=Krf+β(Kmkt-Krf). When the data from the model is graphed to a quadrant in which the horizontal axis is "risk", or "beta", and the vertical axis is "returns", the result is a straight line, the so called "Security Market Line" or (SML). [Take care not to confuse this graph with the graph described further above that generates the values for beta]. This line, the SML, goes through two points, or two "risk/return profiles" – one point being that of the risk free instrument (beta=0, Krf), the second being that of the market (beta=1.00, Kmkt). The SML represents the straight line relationship between risk and return, and suggests that all securities should fall on this line, thus showing a higher expected return for securities with higher risk. Sample problem:? Use CAPM to find the expected returns of a security, given that a 3-month Treasury bill yields .93 %, that the expected returns on the S&P500 are 17 %? and the beta of the security is 1.50.? Draw the related Security Market Line (SML).Solution:? The CAPM set up would be Ke=.0093 + 1.5(.17-.0093) for an expected yield of 25.035%. ??The graph should have points (X,Y)=(βeta, K) at (0, .93%) for the Risk Free instrument (the US T-Bill); (1.0,17%) for the market; and (1.5,25%) for the security in question.4.8 Behavioral Finance: This branch of finance is not so much a strategy itself, but is the study of financial strategies from a psychological perspective. Given that most financial transactions are the result of a human being making a decision, then understanding the process of human decision making becomes an essential component of understanding financial markets. Amos Tversky and Daniel Kahneman are credited with extensive studies of decision making, and Richard Thaler has juxtaposed their work against the efficient market hypothesis to expose some significant conflicts between these two widely accepted theories. Perhaps the significance of behavioral finance to students is that this field reveals how little we really know about the hows and whys of human financial decision making, and this may help mitigate the intimidation of the vast amount of technical knowledge that appears to have accumulated over the years. The Kolbert article lends substance to this discussion and the Hilsenrath article gives a summary of the Fama/Thaler dispute. 4.9 Fundamental Analysis: In the taxonomy of financial analysis, there is often a distinction made between fundament analysis and technical analysis. These two approaches are perhaps not so much strategies, per se, as they are techniques or methods used in the pursuit of a speculation strategy. There exist certain empirical dimensions of a firm that are called the "fundamentals" of the firm, similar in concept to a broader view of the "fundamentals" of the economy. The fundamentals of the firm include, for example, market share, revenue growth, earnings, and leverage. And fundamental analysis is the process of regarding the trends (historical long term and short term) of these variables, plus their relative and absolute levels, and ultimately makes a subjective judgment about the health of the firm based on the fundamentals. 4.10 Technical Analysis: A technical analysis takes a different approach by regarding stock price data and the patterns imbedded therein and further using these patterns to forecast price trends. An implicit assumption is that past patterns will tend to repeat in the future, so the critical step is to discover and recognize the patterns. And if an analyst can find a new, previously undiscovered pattern, then acting on that new pattern will give the analyst (now, the investor) a competitive edge over the rest of the market. Related to technical analysis are "event studies" in which the analyst tries to identify a previously undiscovered relationship between some variable, or variables, and stock prices. An example that has become a historical cliché is the relationship between sun spots and stock prices, in which there was for many years a tight correlation between the two data sets and the resulting predictive model enjoyed surprising success. That there was no rational causal relationship between the two variables did not negate the strong statistical significance of this finding. 4.11 Black Swans and Fat Tails: For hundreds of years "black swans" have been a symbol of unusual, unanticipated and statistically unlikely events. In recent years, Nassim Taleb has popularized the symbol in the context of finance, using as examples the collapse of Long Term Capital Management (LTCM) and 9/11 and the effects of these events on financial markets. The impact of black swans on financial strategies is that non-believers will tend to bet on probable outcomes, where probabilities are estimated from past occurrences. Believers, on the other side of the bet, will assume that the tails of the probability distributions, while historically "thin", contain unpredictable and calamitous outcomes. Hence, the tails are fat. 4.12 Ponzi Schemes: Named for Charles Ponzi of Boston, this illegal strategy involves soliciting assets from investors on the grounds that the "investment manager" has created a unique combination of trades that can yield extraordinary returns for the investor. The hoax attains credibility as the manager pays extraordinary returns to the original investors using assets from new investors and may be further reinforced by issuing fictitious statements that reflect those extraordinary returns. The fraud may continue for years, continually harvesting new money, avoiding regulatory scrutiny, until enough investors try to withdraw their non-existent funds. When the pyramid scheme collapses, it falls quickly. But given the frailties of human nature, new Ponzi schemes will probably continue to emerge. Chapter 5 VALUATIONThe process of valuation in finance is consistent with the more general meaning of valuation – that is, assigning a value to, assessing, or appraising some asset. And, not surprisingly, the assets commonly valued in finance are stocks and bonds. 5.1 Valuation Defined: Consider that a share of stock (or a bond, or a CD) is simply a contract (a written legal agreement) between two parties that entitles one party to certain rights in consideration of an agreed upon purchase price. The valuation issue is: how much is that contract (the stock/the bond) worth? What is its value? How much should the buyer pay for the contract? The answer is: The buyer should pay the current value of the future cash flows related to that contract. The term “contract” might better be replaced with "financial instruments", or a "share of stock", or a "bond". And "purchase price" may be replaced by "price per share" (pps) or the Value of a Bond (Vb). And rather than "current value", we’ll use "present value", to be consistent with the common names of the models used in this valuation process.5.2 Gordon’s Model: Myron Gordon offered a model for the theoretical valuation of stock based on the same premise as that shown above. That is, the value of a share of stock (P) is the present value of the future cash flows associated with that particular security. Gordon suggested that the only real cash flow associated with a share of stock is the dividends. Further, he assumed that dividends can be expected to grow at a constant growth rate (g), often tied to expected growth in earnings, and that these cash flows should be discounted at the required rate of return of the equity investor (Ke). Using a little Calculus, Gordon found that P= D1/(Ke-g) where D1 is next year’s dividends, and Ke and g are as defined above. 5.3 Variations: There are four variations of Gordon's model - two for common stocks and two for preferred stocks.? Preferred stocks differ from Common (in this context) in that preferred dividends are fixed – they stay constant from quarter to quarter.? And the two variations (for each of the two types of stocks) differ only in the variable that is being solved for - in one case, the unknown variable is P (the theoretical price per share), and in the other K, either Ke or Kpr, the expected return to the stockholder.1. Common:? P = D1/(Ke-g)2.?????? "???????? Ke = (D1/P) + g3. Preferred:? P = D/Kpr4.???? "?????????? Kpr = D/P?? 5.4 Caveats:? 1. All variables are on an annualized basis. 2. With Common, D1 is generally not a given.? D1 is estimated by taking the actual dividends over the past 4 quarters (Do), and increasing them by the estimated growth rate.? That is, D1 = Do (1 + g).? 3. With Preferred, because dividends are fixed, there is no growth (g) in dividends, so that term is missing from the models.? 4. These models are valid in their mathematics, but they are forward looking models as opposed to "historical", or backward looking models. Consequently some of the variables are only best guess estimates, and the resulting prices or returns cannot be guaranteed.? Some analysts factor in the notion that dividends follow earnings, and that earnings growth determines dividend growth.? Other analysts recognize that earnings are not real cash flows, and that real cash flows are more relevant to value than earnings.? Consequently, they modify this model by regarding expected cash flows rather than expected dividends. 5.5 Model Weaknesses: Several inherent problems with stock valuation models in general can be illustrated using Gordon's model as a straw man. First, some input data is historical, or empirical, in nature. The data itself is true enough, but there are no guarantees that the data will hold true in the future. Second, some input data is speculative, and looking into the future is a foggy view at best. And third, even if the historical data holds true for the future AND the speculative data is luckily "dead-on", the resulting perfect answer of what the intrinsic value of the stock should be, as often as not, is not likely to be the same as the actual current market price. This leads to an investor's valuation dilemma.The dilemma is that regardless of the integrity of a valuation, there is little assurance that the market will tend towards that valuation. For example, if an analyst determines that a particular stock is worth $60, and the spot price (current market price) is $50, the rational investor would buy the stock (at $50) and wait for the rest of the market to wise up and drive the stock to $60. But the nature of the market is that stocks do not consistently trend toward their valuations. 5.6 Valuation of Bonds: The following illustrates how the value of a bond is determined. The sample bond, issued by Sample, Inc. promises to pay the bondholder a fixed 5 1/4 % (annual) coupon rate, that is, 5.25% each year of the face value (denomination). By convention, the actual payments are paid every six months in amounts equal to half of the amount due annually. The denomination of corporate bonds is typically $1000. And for this example, let's assume that the bond matures (that is, the firm returns the principal to the bondholder) in 8 years. [Note: This valuation was done in 2012 when there was 8 years to maturity. Through time, the date of maturity does not change, but the years to maturity changes every year.] This bond would be listed as:SMPL 5.25% 2020 (or some variation of company_name , coupon_rate, date_of_maturity) These three properties of the bond are fixed – they do not change over the life of the bond. If this sounds like an I.O.U. that's because it is an I.O.U.. Further, assume that current market rate for comparable (same risk category) bonds is only 4%. [See also: “Risk on Bonds” (below)] How much should this investor pay for the Simple, Inc. bond? What is its value? The "future cash flow" will be 1) the interest payments of $26.25 every 6-months for 8years, plus 2) the face value of the bond, $1000, at maturity. The firm, Sample, Inc. will eventually pay $26.25x16=$420 in coupon payments, plus $1000 back to the investor, for a total of $1420. But the $1420 isn't all paid today. The present value of that future cash flow can be calculated using "time value of money" concepts. Use the "Present Value of an Annuity" model [see also:……], where the 6-month discount rate is .04/2=.02, the number of 6-month compounding periods is 16, and the annuity payment amount is $26.25. The answer is $1084.86.5.7 Risk on Bonds: The risk on a bond is the probability of the firm not being able to pay the full amount of the interest payments due to the bondholder, or not being able to return the face value of the bond when due at the date of maturity. This risk is measured by Moody's, Standard & Poor's and Fitch Ratings who use the "probability of default" as the primary criteria for grading the bond. Their rating scale is similar to an academic scale of A,B,C, & D, where A is good, and D is not. Specifically,Moody's S&PAaaAAABest quality, low risk, low returnAaAA High quality, but some long term riskAAStill "investment grade"BaaBBBLong term uncertainty, medium risk, medium returnsBaBBSpeculative attributesBBNot considered investment gradeCaaCCCIn poor standing, high risk of default, high returnsCaCCHighly speculative, high probability of defaultCCLowest rated class--DIn default5.8 Continuous Compounding: The classic FV=PV(1+k)n model illustrates how more frequent compounding yields greater future values than less frequent compounding over the same period with the same rate. For example: Given PV=$1000, annual rate = 5%, for 2 years yields $1102.50 when compounded annually (twice over the period), but yields $1105.16 when compounded daily. In this example, the frequency of compounding went from 2 to 730 over the two years. Imagine the frequency going to a million, or to a gazillion, or to infinity. That's continuous compounding. In the model FV=PV(1+k)n , k would be equal to the annual rate divided by infinity, and n would equal two times infinity. One needs a little Calculus to handle infinity in an equation, but it can be done, and the resulting model looks like FV=PV erT, where r=annual rate, T = time, and e is the natural log. To execute this in a spreadsheet, try FV=PV*(EXP(r*T)). You should get an extra penny for all your work, $1105.17.This model is commonly used in valuation of derivatives, for example BSOPM. 5.9 Loans: Here are some of the mechanics behind the calculation of payments on loans, and the construction of amortization tables.Typical “vanilla” loans (so called, recently, to differentiate traditional loans from exotic adjustable rate, up-front points, etc.) have payments equal to the sum of the interest due on the outstanding balance plus a payment that contributes to paying off the principal. The actual formula is: PMTS= (PVa x k) / [1-(1+k)^-n] where PVa=present value of the annuity, or the amount that the bank is willing to give to you if you sign a contract promising to pay them a fixed amount (the annuity) every month (the timing of the payments doesn’t have to be monthly, but that is the traditional timing). K= the rate for the period of compounding, usually the annual (quoted) rate divided by 12 months of the year. And n= the number of periods of compounding.5.10 Amortization Table: The amortization (the slow expiration) of the loan can be expressed in a table in which every period (or month) the interest and principal reduction is calculated on the outstanding loan balance using the following logic:Beginning Balance = for the 1st period, is the original amount of the loan. In subsequent periods, the beginning balance is the ending balance from the previous period, i.e. the outstanding balance. Interest $ = (annual rate/12 months of the year) X outstanding balance of the loan.Payments (PMTS)= (see above)Reduction in Principal: The interest$ are subtracted from the fixed monthly payments to yield the "reduction in the balance of the loan" (Red'n Bal). Ending Balance: When the Red'n Bal is subtracted from the "Beginning Balance" the result is the ending balance for that period. The ending balance of one period is the beginning balance of the next period. 5.11 Expected Value: Beyond present value/future value concepts, the notion of expected value is often seen in the valuation process. Expected value is the product of the anticipated cash flow multiplied by the probability the cash flow actually materializing. For example, if I roll a die (that's singular for dice) and will pay you $100 if I roll "6", then the value of that game to you equals $100 x .166666…. = $16.67 6. DERIVATIVES Derivatives are a general class of financial contracts that have grown to huge economic significance as measured in dollars of positions held and transactions conducted. They are currently highlighted in the financial news as being at least partially responsible for the larger economic meltdown. Yet, for all their detractors, there are many who defend the existence of derivatives as being essential for the flow of cash in a free market system. Regardless of whether derivatives are good or evil, some derivatives will likely survive the character assassination and thus they are entitled to a close regard.6.1 Mutual Funds: Mutual funds are incorporated entities created by a parent company. Two dominant examples of parent companies are Vanguard and Fidelity. As the parent company creates a new mutual fund, the contributors (investors) in the fund become the stockholders. With capitalization from the investors, the fund manger re-invests in financial instruments with characteristics that were decided upon a priori into a portfolio that is owned mutually [hence the name] by the stockholders/investors. As a hypothetical example, if Fidelity decided to start a "Green Energy Fund", they would incorporate the fund, sell shares in the fund, and buy shares of companies serving the environmentally-friendly energy market. As the share prices of the companies fluctuated, so too would the value of the portfolio fluctuate. The net asset value (NAV) of the portfolio and the value of the mutual fund company (not the parent, but the individual fund) are the same, so the share price of the mutual fund changes proportionally also. Hence the term "derivative" – the value of a share of the mutual fund is derived from the underlying assets, the green companies.6.2 Index Funds: Index funds are a sub-group of mutual funds. Their unique characteristic is that their portfolios are constructed to mirror the performance of a particular market index, for example the Dow Jones Industrial Average (DJIA) or the Standard & Poor's 500 index. Index funds are popular vehicles for passive investors because capital put into index funds are likely to have the same yield as the entire market – with the following exception: fund managers need to be compensated. Typical management expense fees for index funds average about .25% of invested assets per year, so net yields to the fund investor will tend to be lower than the market yield by that fractional percent. 6.3 S&P 500 Index Funds: The S&P 500 Index is constructed by S&P (the firm) selecting the top 500 firms according to level of market capitalization. And because each firms influence on the index is weighted by that firm's market cap, then a portfolio (an S&P 500 index fund) that endeavors to match the performance of the index must hold positions in each firm according to the firm's weighting in the index. As a firm's stock price fluctuates continually, the firm's weighting in the index will also fluctuate. But, conveniently, the weighting in the index fund will also fluctuate by the same proportions, as they are based on the same dynamic market value.6.4 DJIA Index Funds: The DJIA is constructed differently than the S&P500. In the DJIA, the firms' influences on the index are weighted according to price per share. Those firms with the higher stock prices have the higher influence. Thus, a DJIA Index fund would be constructed with the same number of shares for each firm in order to match the performance of the index.6.5 Options: Options are derivative financial instruments whose values are a function of the price of the underlying asset. The more common underlying assets are stocks, but options may be written on other financial asset classes such as commodities and currencies. Options give the purchaser of the contract the right, but not the obligation, to buy (or sell – depending on the type of option) shares from (or “to”) the seller (the writer) of the contract at a predetermined price, the strike price. Two variations of options are calls and puts. Calls are options to buy shares at the strike price. Puts are options to sell shares at the strike price. An investor may take either side of an option trade – that is, they may buy or sell (write) a call, and they may buy or sell a put. A single option contract is for 100 shares.An option has an expiration date, as a specification of the contract, that is either 1) the one future date at which the option must be exercised or allowed to expire (a so called "European" option), versus 2) the last date of a period during which the option may be exercised (the "American" option). Buying and writing options is a “zero sum game”. That is, the buyer’s profit (or loss) will be the same as the writer’s loss (or profit). The buyer pays the writer for the options up front and may or may not recapture that cost through the exercise of the options. The writer receives the price of the option up front and may or may not be forced to buy or sell shares at a loss. These transactions are all done through the options broker at an exchange and buyers and sellers will not generally be aware of the identity of the counterparty. Because of the dynamic nature of the market price of the stock, the spot price, will usually be different than the strike price, the option will either have value (said to be “in the money”) or will be worthless. 1) a) That is, for a call, if the spot is greater than the strike, the investor can exercise her option, buy at the strike and turn around immediately and sell at the higher spot, and take home the difference. b) If the spot is less than the strike, the call is worthless and will not be exercised.2)a) For a put, if the spot is less than the strike, the investor can buy at the lower spot, and then exercise her option, i.e. sell at the higher strike.b) If the spot is greater than the strike, the put is worthless and will not be exercised.Having options “in the money” does not necessarily ensure that the holder of the option will actually realize a net gain from the entire transaction. In order to break even, the spot needs to be far enough “in the money” to cover the original cost of the option. Then, if and when the spot passes the breakeven price, the investor can realize a gain.The valuation of options is generally determined by the Black-Scholes Option Pricing Model (BSOPM) as seen in the next section.6.6 Black Scholes Option Pricing Model: The Black-Scholes Option Pricing Model (BSOPM) has been referred to as a milestone in the development of financial theory on the grounds that it significantly raised the level of sophistication in the quantitative valuation of complex financial instruments. While options have been around for hundreds of years, their market value was mostly determined by the forces of supply and demand prior to BSOPM. Now, options are valued based on empirical data, which tends to give them a truer value (perhaps closer to an inherent value). The result of BSOPM is the price (or value) of a European call, or an American call on a non-dividend-paying stock. The BSOPM is: C = S N(d1) - K e-rT N(d2)The input variables are:S = Current price ("spot" price) of the underlying stock. As this pps changes through time, so too will the price of the option.K = The "strike" price of the call. The strike is fixed (does not change) throughout the life of the option. It is the price that the buyer of the option will pay for the stock if the call is exercised.r = The "risk free rate". As with many derivative models, the current LIBOR (for a time period equivalent to the remaining life of the option) is used as the risk free rate.T = The amount of time remaining to the expiration date. T is in years, so an option with 3-months remaining would have T = .25.σ = The standard deviation of the underlying asset's annualized returns based on historical data. This variable is necessarily based on historical data, and as such, is an imperfect estimator of future volatility – a weak link in the model.The intermediate variables are: d1 = the z-score, or number of standard deviations away from the mean of the distribution of stock prices. This is a measure of the spot price relative to the mean stock price.d1= [ ln (S/K) + (r+σ2 /2)T] / [ σ x T 1/2] Note: The “ln(S/K)” above is read “the natural log of S divided by K. And “ln” is a function key on many calculators. In Excel the function is “=LN( )”.d2 = the z-score of the strike price relative to the mean stock price. And d2= d1-[ σ x T 1/2] N(d1) = the cumulative standard normal distribution for d1, or the area under the Normal curve given d1 (where d1 is a z-score), or the probability related the spot price.N(d2) = the cumulative standard normal distribution for d2.The output variable is:C = The price of the call, is = S N(d1) - K e-rT N(d2)6.7 Straddles and Strangles: Investors will often buy calls and puts on the same stock with the same expiration data and the same strike – this combination is a straddle. With a straddle, either the call or the put will be “in the money” (unless the spot is exactly the same as the strike, which would be quite a coincidence). And in order to realize a net gain, the spot must exceed a breakeven stock price that covers the original cost of both the calls and the puts, even though only one of the two will be exercised.The strangle, a variation of the straddle, is a position with calls and puts on the same stock, with the same expiration date, but with a spread between the strike of the call and the strike of the put. (The strike of the call will be higher than the strike of the put.) Strangles are cheaper to buy than straddles because they have a lower probability of being in the money and the calls and puts will be cheaper. A Strangle example: An investor creates a strangle on stock with a strike price of $32.00 on the calls, with 10 call contracts (@100 shares per contract) and 10 put contracts with strike of $28.00, selling at $2.00 and $1.60 per share for the calls and puts respectively. On the final day of the option, the spot price is $33.33.a) Which of the options does she exercise (if either)? Answer: CALLS [When the spot exceeds the strike, the calls will be exercised. The investor buys at the lower strike and sells at the higher spot. Actually, the broker will do all that for her.] b) How much did the original strangle cost her? $3600[$2.00 per share for the calls, times 1000 shares (10 contracts at 100 shares per contract)Plus $1.60 per share for the puts, times 1000 shares (10 contracts at 100 shares per contract)]c) How much did she make (or lose) on the entire transaction? ($2270) [She buys at the strike, $32.00 /share, sells at the spot, $33.33 /share, makes $1.33 /share, on 1000 shares (10 contracts at 100 shares per contract), for a gross profit of $1330. But she paid $3600 for the strangle, yielding a net loss of $2270 [=-3600+1330]. d) How much did the writer of the strangle make or lose? $2270[Options are a zero sum game. Assume the writer doesn’t own the stock. He needs to buy it at the spot, sell it at the strike to the investor, losing $1330. But he still gets to keep the original price of the calls and puts that he sold for $3600. So his net gain is $2270 [=3600-1330]e) What were the breakeven stock prices for the entire transaction? High $35.60 Low $24.40[The spot needs to exceed the call’s strike by $3.60 [$2.00 =$1.60] in order to cover the cost of the calls and the puts and to breakeven. That is, $32.00 + $3.60 = $35.60. Or, the spot needs to be below the put’s strike by $3.60 in order to cover the calls and puts. That is, $28.00 - $3.60 = $24.40.] 6.8 Hedge Funds: Hedge funds are "derivatives" in the sense that the value of a share of a hedge fund is derived from the value of the underlying assets held by the fund , similar to the value of a share of a mutual fund. Hedge funds differ from mutual funds on several dimensions, for instance 1) minimum initial investment in a hedge fund tends to be higher than in a mutual fund by a factor of perhaps a 100. 2) hedge funds are sold privately versus public offering of mutual funds 3) consequently, hedge funds are subject to less scrutiny and less regulation 4) management expense fees for hedge funds tend to be much higher than mutual funds. 5) Hedge funds provide an environment for financial engineering, or the construction and design of new derivatives. (See also: the article "Hedge Clipping" by Cassidy)6.9 Credit Default Swaps (CDS): A swap is contractual arrangement between two parties whereby a trade is conducted, no cash is exchanged, and the items traded are similar entities, for example, and in this case, credit default contracts. No cash is exchanged initially because both parties are swapping cash flows of equal net present value. A credit default contract is similar to an insurance policy against the defaulting on a loan obligation, such that if the loan goes into default, the insurer would then be obligated to cover the loan payments. In a credit default swap, the two counterparties exchange their risk (and coverage obligation) of their respective third party defaults.6.10 Collateralized Debt Obligations (CDO): Large collections of loans, most of which are typically backed by (collateralized) real estate are called CDOs. After the loans are aggregated, shares of the whole are sold much like shares of stock. The ability to measure the probability of default on an individual loan is challenging enough, let alone measuring the probability of default on a bundle of hundreds, perhaps thousands of loans. Many CDOs received excellent (but totally baseless) default risk ratings from reputable rating agencies, and paid higher yields than instruments of comparable ratings and were popular with large investors. When the individual loans start defaulting, the values of these CDOs become impossible to measure. See Patterson "Math Wizards".Chapter 7. FINANCIAL MODELSHere is a sampling, in alphabetical order, of some other financial models that appear frequently in the academic literature, in the real world and in this course. 7.1 Alpha: Alpha is the excess return over a benchmark return. For US stocks in general, the benchmark return is the S&P500, or similarly, an S&P500 index fund. The concept is used to measure performance, often of a fund manager, such that a manager who consistently "generates" alpha is "beating the market".7.2 Beta: Beta is a measure of risk of a stock relative to the risk of the market. Technically, it is the slope of the regression line y=mx + b derived from the least-squares method where the dependent variable (y) is the return of the stock being measured, versus the independent variable (x), the return of the market. When market returns are regressed against market returns, the slope will equal 1.000 by definition, in other words, the beta of the market is 1.000. Stocks with greater volatility than the market have betas greater than one [ > 1.00], and stocks with volatility less than the market have betas less than one. 7.3 Brownian Motion: Brownian motion was originally observed and defined as a physical phenomenon, specifically, the motion of pollen dust particles in suspension in water. The motion was later associated with bond price movements by Bachelier in 1900, and mathematically defined by Weiner and Einstein. Brownian motion is now regarded as the classic model of random movement in stock prices.7.4 Dow Jones Industrial Average (DJIA): The DJIA is the standard measure of US stock prices, not so much for its accuracy in reflecting changes in market value, but more for its longevity and exposure in the popular media. It is somewhat narrow in scope, using price information on a stable set of 30 large cap firms. A major flaw in its logical integrity is in its price weighted property – the stocks with the higher nominal price-per-share have greater impact on the index than those of more modest pps. 7.5 LIBOR: The "London Inter Bank Offer Rate"(LIBOR) is a composite index of rates charged by large banks to other banks. The rate is calculated daily from data collected from sixteen international banks and is quoted for periods as short as overnight to as long as several years. Practitioners of financial modeling often use the LIBOR as a surrogate for “risk free rate” in their models. 7.6 MATLAB: The name "MATLAB" is short for "matrix laboratory", where a matrix is a table of rows and columns and the basis of a branch of mathematics – matrix algebra. MATLAB is a proprietary software package owned by Mathworks, Inc. and licensed to PSU for educational use. Within MATLAB there are financial modules that can plot efficient frontiers of portfolios and perform many of the calculations related to the valuation of derivatives.7.7 Monte Carlo Simulation: "Monte Carlo" refers to the city in Monaco with the grand casino, and Monte Carlo simulation models use random variables, not unlike the randomness on the roulette wheels at the casino. While there are several variations of the model, they all have the property of being able to integrate probabilities into the outcomes, thus distinguishing them from deterministic models. They are used in financial forecasting because the outcomes expressed as a distribution of results (with related probabilities) can be more useful than outcomes expressed without their related probabilities. 7.8 Pi (?): One of the most recognized mathematical relationships, pi's connection to finance in its use with the Normal distribution model. Specifically, it is used in computing the area under the curve, or the cumulative distribution function (cdf). While practitioners of finance rarely have the need to actually calculate pi's value, they will see that it is an essential piece in some financial models, for example BSOPM. Students should be comfortable pi's definition and memorize pi to at least a few places past the decimal point. See also Pi.Chapter 8 INTERNATIONAL FINANCE A variety of new financial issues arise when one broadens their perspective from concentrating on the internal domestic scene to regarding the financial interaction among the various other countries of the world. While many of the traditional financial models still hold true (e.g. portfolio management, time value of money, and corporate finance), these models are made increasing complex with the introduction of differences in currency exchange rates, national interest rates and foreign accounting conventions. With the increasing complexity there is also an expansion of opportunity for new variations of financial activity with foreign investment instruments. Here are some of the more significant financial instruments related to international finance.8.1 Foreign Currency Exchange: Every country has an official currency for transactions. Each country's currency's value is stated in terms of every other country's currency. The relationship between the currencies is established by government policy and/or by world markets. For example, an exchange rate between one country's currency and another may be "fixed" by policy, whereby the value relationship doesn't change between it and some other currency (e.g. the Chinese yuan pegged to the US dollar). Or currencies may "float", whereby the value relationship is constantly changing as determined by supply and demand in the currency markets. At any point in time all exchange rates are more or less in equilibrium, and this can be expressed in a "cross rate table". A student grappling with exchange rates might do well to start with the simple $/€ format. This fraction-like looking expression means "the number of US dollars required to buy one euro". It does NOT mean dollars divided by euros. Further, the exchange rate of $/€ = 1.35 means $1.35 = €1.00. It is true that, similar to a fraction, that the reciprocal of the exchange rate format equals the reciprocal of the value, that is, €/$ = .74074.Another format for expressing exchange rates is the cross rate table. These tables used to be printed daily in The Wall Street Journal although the numbers are in a constant state of flux. Understanding how to read and how to construct a cross rate table is a useful exercise in understanding foreign exchange. Cross rates are calculated using the following logic: If $/€ =1.35 and $/C$ = .80, then €/C$ = ($/C$)/($/€) or €/C$ = .80/1.35 = .592593 It should be noted that some countries also have "unofficial" currencies that are used in addition to their official currency. A common example can be found in developing countries in which transactions are conducted with both the official national currency and also with the US dollar. Transactions with unofficial currencies are often illegal (but not always) in the countries where the practice is common.8.2 Arbitrage: Currency arbitrage is conducted by professional traders who take advantage of the small dis-equilibrium that occurs among currencies as a result of the floating valuations between the many different currencies. The profits generated by arbitrage trading are small as a percent of the amounts traded, but the amounts traded are huge, transaction costs are small and thus actual dollar profits can be significant. The process of arbitraging currencies has the effect of restoring equilibrium to the currency markets. Arbitrage opportunities are recognized by computers and trades are executed within fractions of a second. There is fierce ongoing competition for faster and faster trading systems among trading houses, because being second best means losing the trade. 8.3 Two Way Currency Arbitrage: The two way arbitrage is conducted by professional traders who use super-fast computer systems to scan bank quotes looking for even the slightest imbalance in exchange rates. When banks quote their exchange rates, they list both the bid and ask prices. See also the example below. That is,€/C$ @ .71076-78 means that Deutsche Bank is offering to buy Canadian dollars at € .71076 per C$ and is willing to sell Canadian dollars at €.71078 per C$. The two-way arbitrage opportunity is created when a second bank, in this case, Royal Bank of Canada, quotes bid/ask prices outside the range, either above or below, the first bank, as in the example below. Over-lapping ranges do not create an arbitrage opportunity.In the example, a trader could buy Canadian dollars from Royal Bank of Canada at €.71074 and sell to Deutsche Bank at €.71076. The profits are small (.00002/.71074), working out to the equivalent of $28.14 for every $1 million traded, or .002814%. However, if a bank trades in quantities of several million dollars per trade, and trades several times per day, the profits are enough to buy lunch. Note also that the first trade will signal both banks that their pricing structures are out of balance, and they will react quickly to closing the gap and ending the arbitrage opportunity. On the other hand, trades are not guaranteed by law or by circumstances to be immediately transparent to all parties, and thus arbitrage opportunities continue to emerge. Example: At 13:45 hrs GMT on 9 November 2010 Deutsche Bank quotes €/C$ @ .71076-78 and Royal Bank of Canada quotes €/C$ @.71072-74a) Is there an arbitrage opportunity? Y/N? _yes___b) If so, one would buy from _RBoC___ and sell to _Deutsche Bank_? c) Calculate the return (i.e. the profit as a %) _.002814__%8.4 Three Way Currency Arbitrage: This is similar to the two way arbitrage, in that it is conducted by professional traders who use computer systems to watch multiple exchange rates simultaneously. When the opportunity arises, the system conducts the trade quickly. In the following example it suggests that a human is doing the trading, whereas the reality is that systems are actually executing the trades.The mechanics: The trader starts with some amount of currency A. Trader uses A to buy currency B, then, uses B to buy currency C. And finally, uses C to buy currency A. Or, A to B, B to C, C to A. If the currencies were in perfect equilibrium, then the trader would end up with the same amount of currency A as when he/she started. However, when the currencies are in disequilibrium, there is a potential to make a profit through the three-way trade. There is also the opportunity to lose money after the three trades. If A to B , B to C, and C to A generates a profit, then it is certain that trading in the other direction (A to C, C to B, B to A) will generate a loss. One should not assume that even an experienced trader can identify disequilibrium in currency rates by merely glancing at the various data. So, as an academic exercise, if A to B, B to C, and C to A generates a loss, then a student should recalculate in the reverse order (A to C, C to B, B to A). In the real world, the system does these calculations.Example: Assume that $/Ps=.092 in Mexico City, and Ps/€ = 16.06 in Zürich and €/$=.675 in NYC. Starting with $1M, make the three simultaneous trades that would yield an arbitrage profit. If the result of your planned trades would generates a loss, then don’t execute those trades! Instead reverse the sequence of your trades so as to generate a profit. Trades:a) city__NYC_____ sell_$1,000,000_ buy_Ps 10,869,565.22b) city__Zurich___ sell_ Ps 10,869,565.22 buy_€ 676,809.7894c) city_Mexico City sell__€ 676,809.7894 _ buy_$ 1,002,681.169Note how the arbitrage profit, as a percent of original investment, equals .2681169 %8.5 Forward Contracts: Forward currency contracts are a popular investment vehicle used for hedging against future changes in exchange rates. An investor with a known obligation to provide funds in a foreign currency at a future date (for example, to pay for imported goods scheduled to be delivered in several weeks) may be motivated to buy forward contracts to lock in the cost of obligation. Forwards are sold for only a few currencies and are sold at a premium or discount relative the current spot price. The currencies that have forward contracts available are the Canadian dollar (C$), Japanese yen (?), Swiss franc (SF), and the UK pound (?). Within each of these currencies, there are 1 month, 3 month, and 6 month forwards available. That is, the currency delivery date is one month (or 3 or 6 months) from the date of purchase. For a given currency, for example the Swiss franc, the forwards may be selling at premium or discount to the recent market or spot price. The forward prices are set by supply and demand and change based on currency traders' aggregate assumptions regarding future exchange rates between, in this case, the US dollar and the Swiss franc. A clipping from the 8 July 2009 issue of The Wall Street Journal from their table called "Currencies July 7, 2009 U.S. –dollar foreign-exchange rates in late New York trading" shows in part the following:Switzerland franc.91771-mos forward.91803-mos forward.91886-mos forward.9203The first quote is the spot price of the SF (from the day before) and is in US$ , or number of US$ to buy one Swiss franc, ($/SF). The 6-mos forward is selling at a premium over the spot, and this is generally expressed as a percentage. (.9203 - .9177) / .9177 = 0.00283317 or "the 6-mos forward is selling at a .28% premium". Forwards selling at a premium reflect the widely held assumption that the value of the currency (the SF in this case) is more likely to rise than to fall in relation to the US dollar over the next few months.8.6 Futures: Futures are similar to forward contracts in that they represent advance sales of currencies at an agreed upon price. However, only a few specific currencies are traded, they are only traded in a few physical markets (e.g. IMM and CME), and they are only sold in the following fixed denominations:GBP 62,500 Great Britian pound EUR 125,000 euroCAD 100,000 Canadian dollar MXP 500,000 Mexican pesoJPY 12,5000,000 Japanese yenCHF 125,000 Swiss franc8.7 Currency Options : Options on currency are mechanically similar to options on stocks, indices, and commodities. There are calls and puts, strike prices and expiration dates as with a stock option. Straddle and strangle strategies can be created as with stock options and individual investors as well as institutions engage in this popular market for speculating and hedging. Section 9 APPENDIX:This section is a catch-all for some statistics, models, and tools used often in finance and referred to specifically in the text above.9.1 Time Value of Money: There are four basic models for calculating time value of money (FV, PV, FVa, PVa). The variables used in the models are:FV = the future value of a lump sum invested for a period of time PV = the present value of a known discounted future valueFVa = the future value of an annuityPva = the present value of an annuityk = the rate for the period of compoundingn = the number of periods of compoundingPMTS = the payments related to annuityAnnuity = a condition in which equal payments are paid (or received) every period (daily, weekly, monthly, etc.) for a set length of time.Future value: The simplest model addresses the future value of a lump sum deposited at a given interest rate for a given amount of time, with interest compounded periodically. Example: $1000 (the present value) is invested at 5% per annum for 3 years with monthly compounding. How much will that $1000 grow to (or “what will be the future value”) in three years? As an algebraic equation: FV=PV(1+k)n As entered into a calculator: FV=PV * (1+(k))^nSubstituting: FV=1000(1+(.05/12))^36 =1161.47Note that the annual rate is 5% (or .05 as a decimal), and so .05/12 would be the monthly rate. So k=(.05/12)=.00416666…..repeating. As a practical matter, when doing the calculations by hand it is better to enter “(.05/12)” than to enter “.00416667” because the former will have less rounding error than the latter.Also note that “n” is the number of periods of compounding, every month for three years, 3*12=36. “n” will always be a multiple and will not be subject to rounding error.Present value: Using the first model (above), and solving for PV, yields the model for calculating the present value of a known future value that has been discounted by a fixed rate.Example: I’ll need $10,000 in 5 years. How much must I deposit today at 3%, compounded weekly, in order to accumulate the required amount? As an algebraic equation: PV=FV(1+k)-nAs entered into a calculator: PV=FV*(1+(k))^(-n)Substituting: PV=10000(1+(.03/52))^(-260)= 8607.45Note: k and n are handled the same way as in the first example and are handled the same way in the following models.Future Value of an annuity: The model for calculating the future value of regular payments for a given length of time.Example: A payroll withholding account is set up to withhold $200 every week into an account that guarantees a 4% return. How much will be in the account after 40 years?As an algebraic equation: FVa=PMTS [(1+k)n-1] / k As entered into a calculator: FVa=PMTS*((((1+(k))^n))-1)/(k)Substituting: FVa=200*((((1+(.04/52))^2080))-1)/(.04/52)= 1,026,996.59Present value of an Annuity: The model for calculating the present value of regular payments for a given length of time.Example: A lucky person wins a lottery prize advertised as being worth a half million dollars. The fine print says the winner will receive $25,000 per year for 20 years or a “lump sum cash equivalent”. The lottery uses a 7% discount rate to calculate the cash equivalent – how much would that be?As an algebraic equation: PVa=PMTS [1-(1+k)-n] / k As entered into a calculator: PVa=PMTS*((1-((1+(k))^(-n))))/(k)Substituting: PVa=25000*((1-((1+.07)^(-20))))/(.07)= 264,850.369.2 Correlation Coefficients: A "coefficient" is a fancy word for an "index", or a "measurement", and "correlation" might be thought of as "co-relation" – or the relation between two streams (or series) of data. In finance, correlation coefficients are used to measure how closely (or how differently) two data sets follow each other. A common example is the measurement of the returns, through time, of two equities; for instance, how closely do the daily returns of Ford match the daily returns of GM?The variable "r" is used for the correlation coefficient and the range of possible values goes from r = 1.00 to r = -1.00, where 1.00 indicates "perfect positive correlation", -1.00 shows "perfect negative correlation", and r = 0.00 indicates that there is no positive or negative correlation at all. Perfect positive correlation occurs when the percent change in one series is matched by the same exact percent change in the second series. Perfect negative correlation is when the changes in the two series are the same in magnitude, but opposite in sign.Using the Ford/GM example above, assume that the two data series consisted of daily returns for 250 consecutive trading days (about one calendar year). Further, assume that on most days Ford and GM's stock prices move in the same direction as the market (sometimes up, sometimes down), but that their respective "percent change from the previous day" are usually slightly different from one another. And further assume that on some days the percentage changes are in the opposite direction from each other. The correlation coefficient of Ford and GM's daily returns is likely to be "mildly positive", for example r = .342 In MS Excel, the correlation coefficient function is "CORREL". Assume Ford's daily returns are in column A, and GM's returns are in B. The correlation coefficient expression would be: =CORREL(A1:A250,B1:B250) 9.3 Regression Analysis: Regression analysis also regards two series of data as does the calculation of correlation coefficients, however, regression analysis treats the two series differently by designating one data series a dependent variable, and the other an independent variable. The regression analysis seeks to measure how much of the change in the dependent variable can be explained by changes in the independent variable. In finance, the daily returns of the market are often considered the independent variable and the individual security (e.g. Ford) considered the dependent variable. Hence, the question becomes "how much of the daily change in Ford's stock price can be explained by the daily change in the market"9.4 Z-Scores: In the Normal distribution, the number of standard deviations away from the mean is called the z-score. The z-score is used to find the percent of the total area under the curve, which in turn, represents the probability of occurrence of some condition (or event) – in this case, the probability of a stock being over (or under or between) some finite price. The area is more technically called the cumulative standard normal distribution. To convert z-score to area/probability, one may use a z-score table, the Excel function "=NORMSDIST(…)", or on a TI-83 there is a fairly easy routine to generate the area/probability (2nd function /DIST. Select #2 on menu. NORMCDF(lower limit, upperlimit) [where lower limit = -1E99 and upper limit=0] and E="EE" on the TI-83 keypad.9.5 Normalization: In portfolio management modeling, the "quantity" of the diverse assets (the different stocks) is often measured in terms of dollars of market value (or other currency) or in the proportion of dollars relative to the whole (sometimes the proportions are referred to as the "weights"). And frequently it becomes necessary to translate those dollars, or sometimes the weights, into "Number of Shares". The process of calculating the number of shares required to equate to a given dollar amount is the process of normalization. While it is perhaps more common and more intuitive to go from number of shares to dollar amount (by multiplying no of shares times pps), nonetheless, it is not uncommon to have to "go backwards" from dollars to number of shares. For example, assume that a portfolio manager wanted to equally weight two stocks in her portfolio, i.e. "to have a 50/50 dollar mix of StockA and StockB. Let's further assume that the portfolio's total value is $20K, which implies $10K of StockA and $10K of StockB. And finally, assume that the price per share [sometimes the most recent price, sometimes an average pps for StockA is $32.75 and the pps for StockB is $56.25. How many shares of each does she need to make $10K for each?The Mechanics: Divide $10,000 by $32.75. Answer: 305.343511450382 [At this point the financial analyst might want to make some decision about "appropriate level of precision". Is this an academic exercise? Is this amusing theoretical research? Is this somebody's real money? Let's go 3 places past the decimal – it looks precise and it really doesn't matter.] = 305.344 shares. And not to forget StockB: $10,000/$56.25=177.778 shares.The Results: The normalized number of shares for a 50/50 mix of StockA and StockB, when trading at $32.75 and $56.25 respectively, equals 305.344 and 177.778. To calculate the no. of shares for a 75/25 mix of A and B, cut the 305.344 in half and raise the 177.778 by half [152.672 shares of A and 266.667 shares of B?]. That's "normalization of shares".10 EXERCISESHere are the exercises used in this course. The “sample” files referred to are available publically at: and variations thereof. The general approach is for students to download the sample, copy it to a local file, and then use the format of the sample as a template for their own work. The work usually demands writing over various cells with formulas in the Excel format, with heavy use of cell references instead of using hard-coded values in the formulas. Student can check the efficacy of their formulas by comparing the results of their formulas with the results shown in the original sample. Often, the instructions demand that certain inputs be changed (and saved). These new inputs will create new outputs. The re-written files are then submitted to a course management system (currently Moodle2) for evaluation by the instructor. 10.1 EXERCISE 1 Ratio AnalysisThe objective of this exercise is to perform a ratio analysis of the firm. See also 2.3 Ratio Analysis above for detailed calculations. Use the Moodle2 website for links to the websites listed below. 1) Open . Save a copy of this file to your local drive with new filename of your choice. 2) Regard IBM's income statement and balance sheet, copied from Yahoo, for the year ending 31Dec08 and prior years at the following sites: data from the 2008 IBM statements have been retyped into the tabs “income” and “balance” at the bottom of sample1.xls. The tab “ratios” shows the results of doing a ratio analysis on these data.3) Assume that you are a financial analyst updating this ratio analysis. Start by getting the latest data on IBM. Try “”>”Get Quotes” for “IBM”>Income Statements & Balance Sheet. Update tabs “income” and “balance” in your file.4) Also regard IBM's current market data from Yahoo's finance page. There’s an old sample at: but yours should be even more current. Take the PE ratio and pps directly from Yahoo and enter them into your homework file. Overwrite the date that you downloaded this data into the appropriate cells (see the old dates?). For the "Market to Book" ratio, take "Mkt Cap" from Yahoo and divide it by shareholders equity from the balance sheet.5) As you update the various ratios, use Excel formulas with cell references, for example use “=B7/F5” rather than typing in the actual number. This allows the reviewer of your work to see where you got your answers – especially helpful if your answers aren’t exactly correct. Indeed, for the calculated cells, you MUST use formulas (and not actual values) as input in order to receive full credit for your work. The other advantage to using cell references in your formulas instead of actual values is that your spreadsheet will be more functional – if you change the inputs, the ratios will update automatically.6) Notice column E in the ratio tab. These are brief comments regarding the change in the ratios and whether they are going in a favorable direction. To say that a value is getting bigger or smaller is not saying whether the firm is getting better or worse in the particular area that the ratio measures. Change the comments to reflect the changes from 2011 to 2012. Your comments will be the real “analysis”.7) Put your name in cell A1 of the first tab, and ensure that A1 is selected when you make the final save of your file. This way, when I open it, the selected cell will show your name and I won’t get it confused with all the other similar files that I’ll be opening.8) Submit your updated spreadsheet to me via Moodle2. When you submit, see if you can find a block in which to leave a note for the instructor – and please leave a comment. The comment may be as trite as “have a nice day” or more substantive e.g. “I hated this exercise”, but in aggregate, the comments give me vital feedback on the value of the exercises and assures me that the exercises are being submitted by real people and not some robot in an online mill. 10.2 EXERCISE 2 Net Present Value (NPV) and Modified Internal Rate of Return (IRR*)In this exercise you create a spreadsheet that calculates the NPV and IRR* for capital projects. In the sample spreadsheet the net present value (NPV), estimated change in price per share (est Δpps) and modified internal rate of return (IRR*) for a project are calculated. The formulas for the cells in sample2.xls have been intentionally suppressed. The point of the exercise is for you to write your own formulas in your own spreadsheet so that new input data may be inserted and new NPV & IRR*s can be calculated. 1) Start by downloading sample2.xls. The link is available on the Moodle2 website.2) Save the file locally to a new worksheet that you will then modify. 3) For every cell with black numbers, enter a formula overwriting the original data. Do NOT use the "NV" or "MIRR" functions available in Excel – show that you understand those models and can re-create them with basic +,-,*,/,^ operators. [It's OK to use the "=sum( )" operator – I assume you could re-create that function .] Row 5: Use the same cash flows as in the sample spreadsheet,Row 6: Use the original discount rate for year 0, but increase it each year by the step amount shown in E20. (This is an illustration of the ascending discount rate model referred to in Sketches in Finance II ).Row 7: Calculate discount factors based on (1+k)-n Hint: In Excel =(1+B6)^(-B4)Row 8: Calculate present value based on PV=FV(1+k)-n Hint: FV=future cash flowRow 9: NPV=sum of the PVsRow 11: Calculate the change using (NPV /No of shares). Remember: NPV is in thousands of dollars.Row 12: Reinvestment rate equals original discount rate. Use a cell reference, not a value.Row 13: Reinvestment factors based on (1+k)n where k is the original discount rate and n is the number of years of reinvestment from time of receipt to the final year 5.Row 14: Calculate the future values (in year 5) for the reinvested cash flows by multiplying the cash flows in row 5 by the reinvestment factors in row 13.Row 15: Add up the future values to get the “terminal value (TV)”.Row 16: Original investmentRow 17: Divide TV by Original Investment. Raise the results to the 1/5th. Subtract 1.Now, check to see that your new formulas yield the exact same results as in the original sample2 worksheet. If not, then fix your formulas.4) Make the following changes to your input data:* Change the original "discount rate” in B6, to 15% * Change “steps” in E20 to 1/4 % increase each year, assuming “ascending discount rates”. * Change “No of shares” to a half million.* Change reinvestment rate in C12 to 15% for all 5yrs. 5) Save these changes (and the new NPV, change in pps & IRR*) to your file.6) Submit your file to Moodle2.10.3 EXERCISE 3 Creating a portfolio (port.xls):In this exercise, you are given actual stock price data from an actual portfolio. You are to use the raw pps data (tier1) to generate market values (tier2) and daily returns (tier3). The three tiers together make a portfolio spreadsheet that we’ll call port.xls. The daily returns data will be used in subsequent exercises to illustrate portfolio management concepts. A sample structure of port.xls can be seen in sample3. Your port.xls will be much larger than the sample. Note: Prices have been adjusted for stock splits. Dividends are ignored. Market data (last column) is the S&P500 Index (old symbol shown in sample3 is ^SPX. Current symbol is ^GSPC). 1) Open the Excel spreadsheet file called "portmaster.xls". 2) Save a copy to your local drive. This first block of data (approximately 250 trading days times 30 firms) is called “tier1”. The data comes from finance., Historical Prices. Hint: As you build your formulas, you might try testing them in “sample3” to see if they work (i.e. see if you get the same results as in the sample)3) Build a second block of data (tier2, market values) directly and immediately below tier1. Start by copying all the dates from tier1 into tier2 [if this hasn’t already been done for you]. Next, calculate the market value (pps x number of shares) for every day for every firm. Only do this for the firms (not for the total portfolio or for the market index). Your formula should look roughly like “=C$3*C4” where C3 is the “no of shares” and “C4 is the “pps”. The dollar sign ($) in C$3 will hold the row constant at row 3 as you copy the formula down and across for every day and every firm.4) Next, calculate the market value of the portfolio (col. B) = the sum of all the stocks' market values. Note: The S&P500 data [^GSPC] should be copied from tier1 into tier2. This market data is being shown for comparison and is not part of the portfolio.5) Build a third block of data (tier3, daily returns) directly and immediately below tier2. Again, make sure that you have the dates in Column A. Calculate the daily returns for every stock for every day (except for the first day). Use the basic model of (new-old)/old. [What happens if you use new-old/old?]. You cannot calculate a return for Day1 because you don't have the data for Day0. The first daily return is on should be in Day2 and the formula should be =(day2 daily return minus day1 daily return) divided by (day1 daily return). But in your formula, use cell designations. Write the formula once, then copy it to all days and all firms. Also, calculate these daily returns for the portfolio and for the S&P500 index (this needn't be an extra step – you can calculate daily returns for the portfolio, all the firms, and the market all in one copy- formula operation. 6) Calculate Standard Deviation using the Excel function =STDEVP on the daily returns for each stock, for the portfolio, and for the S&P 500. Place these data in a row under tier3 (see the sample3 for an example).7) Calculate total annual returns by using “(lastday marketvalue – firstday marketvalue)/firstday marketvalue” to calculate total annual returns for each stock, for the portfolio, and for the S&P 500. Place these data immediately under the standard deviation data. In the sample3 the total returns are only for six days. In your port.xls, the total returns are for a year. 8) Send your port.xls through Moodle2 “port.xls” assignment link, the same way you've been sending previous assignments. Remember- accompanying comments are appreciated.10.4 EXERCISE 4 Creating a hyperbola: One of the salient features of Modern Portfolio Theory (MPT) is the phenomenon of putting two stocks together such that the resulting portfolio has a lower standard deviation (lower risk) than either of the component stocks. This is the magic of diversification and this exercise illustrates the phenomenon graphically.Note: For Spring 2013, stock A= AMZN & stock B=HBHC. 1) Overview: Given the pps data of two firms from your port.xls use Excel to create the hyperbola that would result from plotting the risk/return profile of the set of portfolios comprised of the two stocks as the dollar mix goes from 100% of the dollars in stock A and 0% in stock B, to 0% in Stock A and 100% in stock B. 2) Generate data for five of these portfolios. The data will be risk and return, where risk is the standard deviation of the daily returns of the portfolio and returns is the total annual (or 12 month) return. 3) The dollar mix of the five portfolios that you plot will be:Portfolio 1: 100% stock A and 0% stock B Portfolio 2: 75% stock A and 25% stock BPortfolio 3: 50% stock A and 50% stock BPortfolio 4: 25% stock A and 75% stock BPortfolio 5: 0% stock A and 100% stock B4) You will create 5 separate portfolios in a new sheet (or “tab”) [Rename the tab “hyperbola”] in your port.xls with the two firms mentioned above (stockA and stockB). Each portfolio will have the three tiers (pps, mkt value, daily returns) and each portfolio will have three columns (the total portfolio, stockA , stockB). There will be a total of 15 new columns. 5) Populate tier1 of stock A and stock B with the original pps data found in sheet1. Do this five times, that is, once for each of the five portfolios. 6) Calculate the “normalized number of shares” for stockA and stockB using =10000/ average pps . Enter “number of share” data for both stocks in each of the five portfolios such that portfolio1 has 100% of the normalized number of shares for stockA and 0% of the normalized number of shares for stockB. Portfolio2 has a 75% and 25% mix, and so on for the other three portfolios. 7) Using the same logic and formulas as in the main sheet, calculate the Market Values (Tier2) for all 5 portfolios in the hyperbola tab.8) Using the same logic and formulas as in the main sheet, calculate the Daily Returns (Tier3) for all 5 portfolios.9) Calculate the Standard Deviation (of the population using =STDEVP ) of the Daily Returns of each of the 5 portfolios. Put these in a row below tier 3.10) Calculate the Total Return (top to bottom of Tier2) for each of the 5 portfolios using (last day-first day)/first day. Put these in a row below the STDEVP.11) Chart the stdevp and totalK of the 5 portfolios. Use “scatter with data points connected by smooth lines”.12) Optional: Modify the two axis so as to exaggerate the curve. Chart Tools>Layout >Axis13) Save your file with the hyperbola displayed so that when I open your file, the hyperbola will be displayed and I won’t have to go looking for it. Submit to Moodle2.10.5 EXERCISE 5 CAPM and beta:The goal of this exercise is to show CAPM graphically. We start by calculating risk and return data (a.k.a. "risk/return profile") for all the firms in the portfolio (as seen in port.xls) as well as risk/return profile for the entire portfolio and for the market. Having calculated the data, the next step is to draw a Security Market Line (SML) that graphically represents the actual data (the empirical line). The last step is to draw a theoretical line going from the risk/return point of a risk-free asset to the risk/return point of the market. All the work is to done in your pre-existing portfolio file, perhaps called port.xls.1) Calculate the betas of all the firms in the portfolio (plus the portfolio and the market). Save them into a row below Tier3 on Sheet1 in port.xls. Use the Excel function =SLOPE [Remember that the beta is the SLOPE of the regression line that is the result of regressing Ke against Kmkt] to make the calculations, as in =SLOPE(range of daily returns for the dependent variable, range of daily returns for the independent variable). Your first Excel cell format might look something like: =SLOPE(B500:B750,$AC500:$AC750)…although actual rows and columns might be different for your spreadsheet. See also: ) Calculate the total annual returns of each security (plus the portfolio and the market). This return calculation will be (last day mkt$-first day mkt$)/first day mkt$. 3) Create a scatter plot of risk/return profiles (of all the firms plus the portfolio plus the market) on a graph with risk on the horizontal axis, returns on the vertical axis. 4) Superimpose a line running from the current risk-free rate (use LIBOR=2.5%) to the risk/return point of the S&P500. Use Total Annual Returns of the market as the "expected market return". This line is the theoretical “security market line” (SML). In my version of Excel, to “superimpose” this line, I “insert” a “shape”, where the “shape” is a line. 5) Superimpose a second line, a "trend line", or a regression line that best describes the actual points. This is the empirical SML. In my version of Excel I “insert” a (trend) “line”.6) My sample5 has a pretty crude graph. The lines aren’t necessarily placed properly, either. I hope you can do better. For example: label the chart “CAPM”, label the two axes “return” & “beta”, and maybe even label your two lines “theoretical” and “empirical”. The accuracy of your graph is important. The cosmetics are less important, but you should still have some pride in your work. 7) Formatting the deliverable for presentation and submission: Move the cursor/selected cell to somewhere near the graph and do a final save so that when I open your exer5.xls I’ll see the graph and the relevant data. I don’t want to have to search around for your graph - not that I’m lazy (which may or may not be true) but I want to be sure that I can find it. Submit exer5 via Moodle2. 10.6 EXERCISE 6 Valuation of Stocks, Bonds, and Loans This exercise requires the building of a single spreadsheet with multiple modules. Each module is a little valuation calculator for an assortment of financial instruments. See sample6.xls for an example of the format for your exer6.xls. Your task is to build formulas in the output cells so that when the inputs are changed, the outputs will change accordingly. Use the inputs and outputs in sample6.xls to check your formulas. Then replace the input with the new data (shown below in “New Inputs”) to get new outputs. Save the new results before submitting to Moodle.1) The first module has two parts. The first part (on the left) calculates the value (pps) of a share of stock using Gordon's model. The givens are: expected growth rate (g), last year's dividends (D0), and required rate of return (k). The second part (on the right) uses the same model, except it solves for required rate of return, given the current price per share.2) The second module calculates the value of bond (Vb). Inputs are: rates for comparable securities, interest payments on the bond, and years to maturity. For the 2nd part of this module (on the right), use "trial and error" in "mkt rates" [cell F16] to find that rate that calculates the actual closing price. Hint: Go to "Office Button/Excel Options/Advanced/Editing Options/ uncheck the "After pressing Enter, move selection". Then "OK". This keeps your cursor on F16 while you hunt for the effective market rate.Or for a more sophisticated approach, use the Excel routine that does the trial and error for you. Try the “Data” tab, “What-If-Analysis”, “Goal Seek”, where “Set cell”=G19, “To value”=1200, “by changing”=F16.3) The third module has two parts dealing with continuous compounding. The first part calculates the future value (FV) of an asset given a present value (PV), a rate (k), and the length of time (T). The second part calculates the present value (PV) given FV, k & T.4) The fourth module calculates monthly payments on a typical loan, given the amount of the loan, the annual rate, and the time of the loan. Lastly, build a complete [for every period of the loan] Amortization Table, using the monthly payments (calculated above). You can read more about the construction of amortization tables in the valuation section of NOTEBOOK12.5) Enter these new inputs:a) g=.06 Do=3.05 k=.12 solve for pps g=.06 Do=3.05 pps=40.00 solve for kb) coupon=.0688 mat_yrs=10 mkt rates=.0575 solve for value Coupon=.075 mat_yrs=15 close= $1200 solve for mkt ratesc) pv= 2500 rate= .06 T=2.5 solve for FV Rate=.075 T=1.25 Fv=37,000 solve for PVd) PVa=23,750 no of yrs=5 Annual rate=.07 solve for PMTS and generate table6) Save, submit to Moodle2, add a note.10.7 EXERCISE 7 Black - Scholes Option Pricing Model (BSOPM):This exercise requires you to build a spreadsheet that will take the input variables S,K,r,T, and σ, to calculate C, the price of the call. See also: Sample7.xls. Note that there are two calls in the sample. The one on the left used the same calculations as the one on the right. You can modify the one on the right without corrupting the original calculations on the left. Here's what you should do: 1) Open sample7.xls and save it to a local file.2) Create formulas in the yellow highlighted cells. The results of your formulas in your spreadsheet should generate the exact same data as in my sample7.xls3) Enter this NEW input data into the right-hand “dynamic” calculatorS= $75.75, r= .0222 K= $74.00 T=.5 σ= .254) Save your results and submit your exer7.xls via Moodle.Here are some supplemental comments:a) The value of the option, C, is equal to…,b) the current stock price(S) of the underlying stock, multiplied by…c) a cumulative probability, N(d1), where the z-score= d1, d) Subtract from the product calculated in "b x c shown above" the following:e) the "discounted strike price" of the option, multiplied by…f) the cumulative probability (Nd2), where the z-score=d2.And here are some more hints applicable for when you are in Excel:h) To calculate “e-rT “, use the Excel function "=exp(-r*T)", where r and T are replaced with cell locations of the risk free rate and number of years. Or i) use a rough equivalent of "e", where e=2.71828182845905, and raise it to the negative r*T.j) To calculate ln(S/K), use the Excel function "=ln(…)" where S and K are replaced with cell locations. Note: “ln” is lowercase LN, NOT uppercase “I” [pronounced “eye”] n. k) To calculate N(d1) [read: "the N of D sub 1"] use the Excel function "=NORMSTDIST(z-score)", where z-score is replaced with the cell location of D1. D2 and N(D2) are handled similarly to D1 and N(D2).10.8 EXERCISE 8 Number of Firms (NooF):The Question is. . . How many (randomly selected) firms does one need in his/her portfolio in order to be diversified enough to have the same risk as the market? See also “3.10 Number of Firms” in an earlier chapter of this book. The Assumptions: a) Individual firms are riskier than the market as a whole; b) As more firms are added to the portfolio, the overall volatility of the portfolio will decline.c) At some point (i.e. at some number of firms having been added to the portfolio), the volatility of the portfolio will be [virtually] the same as the market. Creating the Chart:Create a chart in Excel showing the declining standard deviations of a portfolio (use your port.xls) as additional firms are added to the portfolio. The vertical axis is “std dev”, the horizontal axis is the “number of firms” in the portfolio (ranging from 1 to “no of firms in portfolio”). Also, show the standard deviation of the market as an extended horizontal line.Recommended methodology: 1) Copy the first three tiers onto a new "sheet" (but still in "port.xls"). Rename the sheet “noof”.2) Calculate standard deviations of the daily returns of the portfolio, every firm, and the market [you may have already done this]. This STDEVP row should probably be immediately under tier3.3) Sort the firms (but NOT the portfolio or the market), from left to right, highest to lowest, by stdevp of the firms. Include firm labels, no of shares, daily prices (tier1), market values (tier2) and daily returns (tier3) in your sort. [Note: This step is cheating. Do you see why?] 4) Go back to top of Tier1. Insert a new row above "No of shares". In this new row, calculate for all firms a NEW "no. of shares" to equal a normalized no. of shares for $10K. =$10000/average(share price)5) Set all the ACTUAL no. of shares to zero ["initializing the data"]. The market value (Tier #2) should show all zeros.6) Copy (and “paste special”/” values”) the first firm's “no of shares” to the initialized row. Split your screen [drag down that tiny little bar above the scroll bar]. Go to the bottom where you have the portfolio's stdevp.7) Cut 'n paste special/values the stdevp of the port (into the new blank next row down) in the col for the first firm.8) Go back to top. Cut 'n paste special/values the 2nd firms normalized no of shares to the initialized no of shares row.9) Go back to bottom and cut 'n paste special/values the new stdevp of the port to the col for 2nd firm. Continue for all 30 odd firms. The stdevps of the portfolios are the data to plot on your graph.10) Add the stdevp for the market as a red horizontal line.11) Put your name in cell A1. Save the file. Submit via Moodle2.10.9 EXERCISE 9 Cross Rates, Arbitrage and Forward Contracts:The objective of this exercise is to work with foreign exchange rates. 1) Download Sample9 and copy it to a local file and use the file as a template for the following:2) Cross Rates: Here are the traditional cross rate questions. The calculations are simple, but it's easy to get mixed up. If the euro(€) is selling for $1.5488, and the yen(?) is selling for $.009538, what are the cross rates between... a) the € and the ?? (€/?) = ______ b) the ? and the €? (?/€) = _____c) the US$ and the ?? ($/?) = ______3) Building a cross rate table: Use the following exchange rates to build a cross rate table in the same format as sample9.xls. Ensure that your cells use formulas for their source of data, not simply pasted values.$/€ = 1.35$/? = .007$/? = 1.87$/C$ = .87$/Ps = .08Hint: Type the values (above) into row 11 in sample9.xls. B12 thru B16 are reciprocals of C11 thru G11. Fill in the rest of the cells by carefully extending the patterns. 4) Two Way Arbitrage: Build the formula into the appropriate cell that will calculate the profit, using the example in “8.3 Two way currency arbitrage” in which Deutsche Bank quotes €/C$ @ .71076-78 and Royal Bank of Canada quotes €/C$ @.71072-74.a) Is there an arbitrage opportunity? Y/N? __b) If so, one would buy from ____ and sell to __? c) Calculate the return (i.e. the profit as a %) ___%5) Arbitrage (3 way): Build the formulas into the appropriate cells that will calculate the "units" for the various currencies bought and sold. Use the same inputs as in the example in “8.4 Three way currency arbitrage”. That is: Assume that $/Ps=.092 in Mexico City, and Ps/€ = 16.06 in Zürich and €/$=.675 in NYC. Start with $1M and show the trades (a,b,c) that would yield an arbitrage profit or loss. Note: in the "solution" on your spreadsheet, show both currency and unit amount. Carry each currency to 4 places past the decimal. 6) Forward premiums and discounts: Calculate the premiums (or the discounts) associated with each of the Swiss Franc forwards listed in The WSJ. Use formulas in the boxed cells. This is the same example as in the section “Forward contracts” in Sketches in Finance II where…Switzerland franc.91771-mos forward.91803-mos forward.91886-mos forward.92036. 7) Save your work and submit it to Moodle2.10.10 EXERCISE 10 Final Exam (sample only – some parameters may change):To START: Open sample10. Save as exer10.xls. Note that there are multiple sheet tabs at the bottom of the worksheet. Clicking on these tabs takes you to subsidiary worksheets where you will do most of your work. The output cells in the subsidiary worksheets are automatically read onto the MAIN sheet.1) Ratio Analysis: Open finance.. Find most recent financials (income statement and balance sheet dated 29 Sept 2012), and current market data (11 Nov 2012 or later) for Apple, Inc. (AAPL). Save copies of those web pages as local files (your files). In your file "exer10.xls", click on the "RATIO" tab at the bottom of the sheet. Fill in cells B3 thru B10 with data from Yahoo. Create formulas in cells B13 thru B18 to yield the appropriate output. Use space at right of inputs and outputs to do any intermediate steps. Outputs that you calculate in the RATIO sheet will automatically be read into the MAIN sheet. Hint: Refer to exer1.doc and sample1.xls for review of ratio concepts.2) Capital Budgeting: Given a cash flow and a discount rate, calculate NPV and IRR*. Use the "CAP BUD" worksheet to do the calculations for NPV and IRR*. Use the same discount rate for all years. That is, DO NOT increase the discount rate as with the “ascending discount rate” model. Use cells B4 and B5 (in CAP BUD tab) for your answers. And similar to step 1, cells in the MAIN tab have already been set to equal B4 and B5 (in CAP BUD) 3) Portfolio Management: Tab PORT includes some price per share data. For this exercise we will only use a few days worth of data. Calculate the market values for the three firms (pps X no of shares) to fill in tier 2. Calculate the market value of the portfolio by adding the market values of the three firms. Calculate the daily returns of the portfolio, the three firms, and the market (^GSPC=S&P500) using the logic (day2-day1)/day1. Calculate the standard deviation (=STDEVP) of the daily returns for the portfolio, for the firms and for the market. Calculate the TOTAL return (from 30Jun06 to 10Jul06) for the portfolio, the firms, and the market.4) Hyperbola: Continue working in the PORT tab to create a hyperbola using Clorox (CLX) and Nestlés (NSRGY.PK) stock. Their data can be found in columns M through P and rows 3 through 762. The data is laid out in the three tiers, similar to the previous problem 3 (above). Note: Tiers 2 & 3 were pasted “values only”. You’ll need to replace the values with formulas in order to see updated values in the “portfolio” column. First, calculate the "normalized no of shares" (NNOS?) for CLX and NSRGY.PK that would yield $10,000 worth of stock for each firm (=10000/Avg pps). There is a scratch box (G thru L in PORT tab) for you to work in. Then replace O3 & P3 with NNOS for CLX and ZERO shares for NSRGY. Calculate the “STDEVP” and “total K” for the Portfolio and put the numbers in I6 & I7. Next, change the no of shares of NSRGY to its NNOS and put the new “std devp” and “total K” in J6 & J7. Last, zero out the number of shares of CLX and put the resulting std dev and total K in K6 &K7. [Note, the 50/50 portfolio will be twice as large as the 100/0 and the 0/100 portfolio, but that’s OK - the std dev and total K data will be the same as if we had held the total dollar value constant for all portfolios.] Insert" a "scatter" plot (X-axis =std dev, Y-axis =total K, with smooth lines) representing the hyperbola plotted from a set of portfolios going from 100% CLX to 100% NSRGY.PK (with a 50/50 mix about half way between.) Save the chart somewhere in tab PORT. Also, try to copy the chart into the MAIN tab in the upper right corner within rows 1-12 and columns G through N. Do not change the formatting of the MAIN sheet in exer10.xls – that will mess up my viewing of your other data.5) Betas: Continue working in PORT tab, but return to the left side to calculate the betas for the portfolio, the three firms (YHOO, GE, MMM) and the market. A space for the answers is on the lower left and is linked to the MAIN tab. There is no chart required in this step.6) Valuations: Open the VALUATION tab and make the calculations for 1) expected stock price; 2) Bond valuation; 3) Continuous Compounding; 4) Payments on a loan; and 5) the first few periods of a loan amortization schedule (do only three periods for this exam). Use the "inputs" as the "givens" for all your calculations. 7) Black-Scholes Option Pricing Model: Open the BSOPM tab. Use the inputs as shown. Create (or copy from previous exercises) the formulas to yield the intermediate variables and the final price of the call option. Your answers from tab BSOPM should copy into the MAIN tab automatically.8) International Currency: 1) Open the tab CURRENCY to find the beginning of a cross rate table based on once-current data. Complete the table. Hint: The first row will be the reciprocal of the first column. 2) Calculate the forward premiums (or discounts) related to the UK pound on this out-of-date data.9) Processing your final exam: Save a copy of your exer10.xls to one of your local folders. Send a copy of exer10.xls via Moodle 2“assignments” as you have done in the previous exercises. Include a “note” with your exercise commenting on the final exam only. $$ End of text $$ ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.