Creating a Financial Model in Excel Including Portoflio Theory and More Than 2 Risky Assets for an Efficient Frontier.

Creating a financial model in Excel including Portoflio theory and more than 2 risky assets for an Efficient Frontier.

15527824

Cassandra Seah

Financial Model

A financial model represents the financial operations or financial statements of a company in terms of its business parameters and forecasts future financial performance. They are also used to provide valuations of individual assets that might not be actively traded in a secondary market.

(http://financial-dictionary.thefreedictionary.com/Financial+model)

Modern Portfolio Theory (MPT) is an investing method where the investor attempts to take minimal level of market risk to capture maximum-level returns for a given portfolio of investments. However, although widely used within the financial industry, there are recent criticisms and variations of style to consider before applying MPT to your own investment portfolio.

(http://mutualfunds.about.com/od/managingyourportfolio/a/Modern-Portfolio-Theory.htm)

Portfolio Returns

In order to calculate the portfolio return, we use the following formula:

rp represents the portfolio returns

Wj represents the portfolio value weight of the asset i

ri represents the return

The values used are historical or past hence called the ex-post context.

Other times, the values are expected or forecasted, which would change the formula slightly as well as it being called the ex-ante context.

As the formula is slightly different, E(rp) is the expected portfolio and E(ri) represents the expected return of asset i. (Bodie, Kane and Marcus 2011)

In order to find the individual values of the asset returns from historical data, we use discrete returns and average them.

Discrete return=New pricePrevious price-1

Using excel with the weight of the asset and the return known we can use the SUMPRODUCT formula to calculate the portfolio return.