This page contains financial and investment models for educational purposes.  It will be constantly updated, so please come back to see what is new.  In order to use the links, you will need to have MS Excel installed on your computer.  Simply press on the red link for any model, and it will download to your computer.

• 1. In this very basic model we look at variation; how variation can decrease portfolio variation, and some basic properties of Gaussian probability distributions.  See chapter 5 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004 for more details on these concepts.  Click on the link, below to pull up the Excel sheets. 

Rudiments of Variation.xls

• 2. In this workbook we look at some basics of bond valuation, including the concept of bond duration.  The worksheets are set up so that the user can vary the basic data and view the changing results in number and graphical form.  See chapters 3, 5, 9, and 10 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004 for more details on these and associated concepts.  (Press the link, below, to pull up workbook).

Bond Basics.xls

• 3.  In this spreadsheet, we show how return varies with risk, in the form of standard deviation, for perfectly correlated, perfectly anti-correlated, and zero-correlated assets in a two-asset portfolio.  See chapters 5 and 10 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004 for more information on formal portfolio theory for multiple assets.

Two-Asset Portfolio Curves.xls

• 4. In this spreadsheet model, we demonstrate the analysis and construction of the Security Market Line (SCL).  The SCL is found by looking at the returns of a stock versus the returns of the market, in this case, the NYSE Index.  A regression analysis is performed to find a line fit, which is the SCL.  The intercept is the alpha of the stock, while the slope of the line is the so-called beta of the stock.  Then, we also perform the calculations for excess returns of the stock versus the market, where the excess is the return, above the riskless rate, which is taken to be the yield on the one year constant-maturity Treasury bill rate.  This corresponds to beta for the Capital Asset Pricing Model (CAPM).  We have performed the calculations for monthly returns for IBM and MMM, annualized by multiplying by a factor of 12.  We have included extra data for IBM and MMM; more data for the NYSE Index can be found at the NYSE website: www.nyse.com. More information about these and similar methods of stock return analysis can be found in chapter 5 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004.  Click on the link, below to pull up the Excel sheets.  (Press on link, below)

IBM & MMM Alpha & Beta.xls

• 5.  In this spreadsheet, we look at a world in which there are only two stocks, ABC and XYZ, in order to demonstrate some basic securities models.  We calculate the so-called efficient frontier for portfolios of the two stocks.  From the efficient frontier, we calculate the optimal portfolio and the Capital Allocation Line (CAL) along which lie the most efficient completed portfolios of risky and riskless investments.  We analyze the changes in portfolio composition for various prices of the two stocks, and work up an implied demand schedule for the stocks.  We also consider how a market index investor, who composes a portfolio that is weighted in the same manner as the stocks are capitalized in the market.  We look at a market index of the two stocks.  In addition, we look at the differences of another investor, who has a different required return.  We look at demand schedules of the various investors, and we consider how large investment funds might act to supply the other based on the assumption that the funds have been put together at lower prices.   More information about these and similar methods of portfolio analysis can be found in chapters 5 and 10 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004. (Press on link, below.)

Demand for 2 Stocks.xls

• 6. In this model we use monthly returns of stocks and the market, in the guise of the NYSE Index, to calculate security characteristic lines (SCL'S).  From those calculations we form the security market line (SML).  In truth, we have used only the capital gains and have not included dividends, which, technically, we should have, but our aim is a simple demonstration of the methods, involved.  Although the model is simplified in its assumptions, it serves to exemplify how these methods can be put to practical use. More information about these and similar methods of stock return analysis can be found in chapters3 and 5 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004.  (Press on link, below)

SML.xls

• 7. In this spreadsheet model we examine the Black Scholes options model.  The spreadsheet is interactive.  You can put in numbers for stock, option exercise price, the riskless rate, and volatility, and the BS value is computed.  In addition, the program automatically generates a list of option prices versus stock price and charts of the BS value versus intrinsic value.  More information about these and similar methods of stock return analysis can be found in chapters3 and 5 of Investing in the Real World: Practice and Theory by C. L. Mattoli  © 2004.  (Press on link, below)

Basic Options.xls

• 8. I find that many of my students, beginning or intermediate, have trouble with the idea of an installment loan as the present value of payments, present value annuity (PVA).  Then, when they actually do understand that, the idea of loan amortization, again, eludes them.  In this spreadsheet example, we look at an installment loan amortization table that explains what the idea of PVA actually is in terms of the payments: interest and principal.

Installment Loan Amortization Table Example.xls