Mock Trading: 2 Equity Allocation

Second part of the mock trading involves allocation of the equity. The details below is what we did in choosing the stocks and its weights.

Equity allocation from naojan

Fund Size Description and Client Requirements - This contains the necessary requirements for the fund. This is necessary as policy statement goes.

Philippine Stock Market Outlook - As we recall, we found out that Consumer Staples, Utilities, Financials, Real Estate and Tourism are the sectors that are favorable to invest in. Moreover, analyzing the PSEi risk return statistics using monthly rolling annual log returns, each sector has something to give as the stock market today is very attractive. Applying the optimizer, by minimizing the variance subject to the constraints by the fund and client requirements and expected return greater than 15%, we found out that it is advisable to invest in Holding firms, Mining and oil and Services.

Stock Selection Screening - Furthermore, we analyzed each stock per sector that is found in the PSE. The bullets below are the things that we did in our analysis.

• Fundamentals - we analyzed each of the stocks by using different ratios based on whether the stock is Cheap or Not, its Liquidity, Leverage, Growth and if it pays Dividend or not. Also, specific ratios are used depending on what sector we are analyzing. ie. for banks, we used P/B as for holding firms we used P/B and P/E.
• Technicals - we just looked at the periods from 2007 onwards whether the stocks  is a bull or in the bear market
• Risk Return Statistics - here we looked at the volatility, mean return and the sharpe ratio like what we did above in the Phil Stock Market outlook. 
• Other Analyst Consensus - we also considered other analyst consensus using Reuters.
• Optimization per Sector - same as Phil stock market outlook, we use the optimizer in each stocks per sector to minimize risk subject to the return greater that 15% and fund and client requirements. It determines the possible weights for each stocks and the stocks that we can pick for the investment. 

After all the analyzing, we found out that some of the weights considered in the optimization did not coincide to the other analyses. As such, we override the values of the optimization as to give in to the consideration mostly on the fundamentals. With so much contemplation, we choose stocks in the PSEi and added more stocks that we think are attractive. We did this so that we really have a very diversified portfolio. Also, we based our weights on the Stock Screening Process and the Portfolio manager had the final say. Final picks and weights are in Slide 14 to 16. 

In choosing those stocks, we are glad to report that after doing the correlation matrix, most of them are uncorrelated and we had a portfolio return of 24.66, which is above the said requirements. 

MockTrading: 1 Macroeconomy

Happy April Fools Day!!! :D

So far, I've been busy with other stuff that I didn't have the time to write. This is some of what my group and I have been doing for the past two weeks, tsp related. :)

Written below is the complete write-up of what we have researched for the macroeconomy for the mock trading.

Macroeconomy Philippines from naojan

Global Outlook 

•  Advance Economies and EuroZone growth rate is consistently below world growth rate.
• USA growth rate is above world growth rate for 2000 – 2008, but drastically went down on 2009 due to the crisis and recovered following the world growth rate in the next few years.
• Developing Asia’s growth rate is consistently the highest.
• Comparing GDP growth of per country group to Philippines shows that the Philippines has been performing better in years.
• Growth rate of the per counter group is expected to rise for about 1 to 2 percent for 2013 -2017.
• Barring major geopolitical shocks, the world economy in 2013 should mark the beginning of sustainably faster growth with declining trade imbalances.
• Expansionary monetary policies will continue to drive the cyclical upturn in the United States and in the euro area, despite significant headwinds from fiscal consolidation.
• Economic activity in Latin America will strengthen. With an estimated current account deficit of nearly $80 billion, this area is making a net contribution to world economic growth.
• East Asia will remain the fastest growing segment - and the largest surplus unit - of the world economy. Unfortunately, a good part of that growth will be on the back of its trading partners. East Asia sells $270 billion more than it buys from the rest of the world. Its huge excess savings will continue to be a source of finance to deficit countries.
• Philippines is expected to outperform most other nations in Asia and enjoy another year of strong economic growth. Philippine economy is forecast to expand 6.2 % in 2013 on strong domestic consumption, improved global financial conditions, and recovery in exports.

Philippine Economy

• The Philippine economy was one of the global star performers in 2012 with growth accelerating over 6%, well above the 3.9% in 2011 and above the average over the last 10 years (5%). In 2013, the Philippine economy is expected to grow by around 6% on the back of sound policy developments and strong underlying fundamentals.
• Philippines is also as “the diamond of the region” by an RBS economist in November has been proudly replayed by media and policy-makers alike.

Economic Indicators

• Gross Domestic Product
• Gross Domestic Product (GDP) grew by 6.8 percent in 4Q2012, paving for the full-year GDP estimate to post a broad-based growth of 6.6 percent. 
• Increase was fuelled by robust performance of th e Services sector as well as the substantial improvements of Manufacturing and Construction.
• GDP is expected to grow above 5% in the coming years, mainly coming from buoyant public and private spending.
• Inflation
• Year-on-year headline inflation for the whole year of 2012 averaged 3.2 percent well within the Government's target range of 3-5 percent for the year.
• Inflation is expected to remain within the BSP’s target of 3% - 5% in the next few years. 
• Unemployment
• Unemployment rate improved in 2011 at an average of 7% compared to 7.4% in 2010.  Growth in job creation outpace growth in labor force. Meanwhile, the unemployment rate is expected to hover around 4%.
• External Balance
• Current account continued to register a surplus at US$1.8 billion, equivalent to 2.9 percent of GDP but 15% lower than the surplus of US$2.1 billion in 2010. 
• Surplus in the current account was sustained by net receipts in current transfers, services and income, which offset the widening trade-in-goods deficit. 
• Current account is expected to remain in surplus on the back of strong inflows from OFWs and receipts from the growing BPO industry
• Fiscal Balance
• Fiscal deficit in 2012 amounted to P424.8B equivalent to 2.3% of GDP below the 2.6% target.
• Phils. making progress in tackling fiscal challenges – inadequate tax collection and chronic under spending.
• Government has continued to demonstrate prudence in its fiscal management, as characterized by low budget deficits (less than 3% of GDP)
• Interest Rates
• Policy interest rates, overnight repurchase (repo) rate and reverse repo rate, were kept on hold, at 5.5% and 3.5%, respectively, since October 2012.
• Yields on government papers fell to record lows due to investor strong demand on the back of the country’s solid fundamentals
• The market is expecting a 25bps rate hike in 2013. Nevertheless, interest rates are expected to remain at or slightly above current levels.
• Exchange Rates
• Peso emerged as Asia Pacific’s 2nd best performing currency against the dollar in 2012 due to continued influx of foreign funds
• Measures to temper capital inflows include ban on foreign funds in SDA and cap on non-deliverable forwards (NDF)
• The peso is expected to appreciate further on strong inflows from OFWs, the BPO sector, foreign direct investment and portfolio investment flows (hot money).

Growth Drivers

• Summary of Key Drivers
• Conditions are ripe for spending (both from the public and private sectors)
• Inflation in check, interest rates to remain low, liquidity abound
• Sustained consumer spending and increased investment spending from the private sector
• Government spending on infrastructure and socio-economic programs
• Conditions are ripe for spending
• Interest rates expected to remain low as there is a continued downward trend of the 10-yr. T-bond yield 
• Liquidity is abound
• Domestic liquidity (M3) grew by 10.6% yoy in Dec. ‘12 to reach P5.2 trillion
• Growth in money supply (M2) driven by expansion in net domestic assets
• Improved NPL ratio resulting in more funds available for lending making the banking sector is sound
• Election Related Spending
• In 2013, expenditure is likely to be propped up by election-related spending for the May mid-term poll. Continued slow progress on flagship public-private partnership scheme of the president will dampen spending growth.
• Consumer Related Spending
• Robust consumer spending on the back of strong inflows from OFWs and receipts from the growing BPO industry
• OFW remittances up 7.2% to new record $ 20.12 B in 2011 amid political turmoil in some parts of MENA and slowdown in global economic growth
• Remittances remained resilient due to sustained foreign demand for skilled Filipino manpower and financial service innovations of banks
• OFW remittances are likely to still expand by 4-5% as the demand for Filipino workers abroad remains vibrant. Be¬sides, OFWs tend to remit more dollars to compensate for a peso appreciation in order to meet the peso bud¬gets of their families in Philippines. Q1 2013, however, will likely exhibit a slowdown from the previous quarter, as the OFWs tend to save up for months. 
• With strong OFW remittances and portfolio capital in¬flows, the natural tendency will be for the peso to ap¬preciate particularly in Q1. However, this will be tem¬pered by BSP’s actions—further buildup of international reserves, more macroprudential measures, a further 25 bps cut in policy rates, among others—and by a larger trade deficit, which will emerge from stronger capital goods imports and larger imports of crude oil as a result of faster economic growth. 
• IT-BPO services serve as catalysts for growth, the industry played a major role in fueling recent economic development in the Philippines
• IT-BPO revenue reached $11B in 2011 24% higher than 2010.
• BPAP 5-yr. plan to grow at an average of 20% a year – above the projected global annual growth rate of 10-15%
• Credit Rating Upgrade
• Upgrades in indicators of competitiveness and sovereign ratings reflect an improved investment environment.
• S&P raised its outlook on the Phils.’ credit rating to “positive” from “stable”. 
• Current administration possesses a level of legitimacy, support and stability that reduces political uncertainty and allows for improved legislative efficiency. This environment allowed the government to focus its efforts on improving its revenues, building infrastructure and reducing poverty. 
• Credit rating may be raised from “BB+” to investment-grade by end-2014. The case for investment grade is supported by a number of factors, including a resilient economy, a current account surplus, stable fiscal policy, and the narrowing of the budget deficit. 
• Philippine Stocks to outperform its peers

Risk Factors

• Fiscal deficit is expected to narrow gradually over the next few years.
• Fiscal consolidation would continue especially with the implementation of the "sin tax" in January. 
• Policy rates will remain unchanged at 3.50% given robust growth outlook and a manageable inflation. Possible policy rate hike by 25 bps in Q4. 
• Further SDA rate cuts can’t be ruled out, which would give central bank more freedom to manage FX volatility.
• Inflation may accelerate, particularly in 4Q2013, due to higher food and energy inflation, consumer spending, and base effects but will remain manageable and unlikely to breach the inflation target.   

Preferred Sectors for Stock Pics

• Consumer Staples
• Utilities
• Financial
• Real Estate
• Tourism

Summary/Other notes

• With the year ending at a high note, and prospects both at home and abroad looking a bit better, we think that 2013 would show an acceleration in growth by as much as 8%, assuming the peso does not appreciate to below P40/$. 
• As the most recent job figures tend to be ambivalent, and the election preparations heat up starting December, we expect a further acceleration of growth in H1 of 2013. 
• Inflation is likely to continue at 3% or lower for most 2013, with food supply remaining abundant, while crude oil prices held down by rising U.S. oil and gas output from shale, and from Canadian tar sands. Due to base year effects, Q1 may see inflation average at 3%, but the trend would be downward in subsequent quarters. We expect full-year inflation to average 2.8%. 
• The fiscal sector will continue to provide higher spend¬ing for infrastructures and other capital outlays, albeit at a slower pace, between 15% and 20% for the year. However, this growth will still be above 20% in H1 considering that the Department of Public Works and Highways (DPWH) would have unspent funds of some P40 B that will be used up in H1 2013. Besides, the fiscal sector’s consolidation will continue as a result of the passage of the Sin Tax reform law and more robust tax collections of the BIR arising from faster growth and better tax ad-ministration. 
• Monetary policy will be neutral to easy, as the low inflation allows the BSP to further cut the interest differential between peso instruments and foreign debt papers. A smaller differential would reduce foreign portfolio inflows, and lessen losses of the BSP (which is huge for every peso of appreciation, due to its large amount of foreign exchange reserves). 
• Exports are likely to gain by 10%, not only because the U.S. and China are more recently showing signs of gains, but also because of the inward flow of manufacturing facilities into the country by Japanese and Korean firms. Besides, electronics exports seem to have bottomed out, and a resurgence would be more apparent in Q1 2013. 
• OFW remittances are likely to still expand by 4-5% as the demand for Filipino workers abroad remains vibrant. Besides, OFWs tend to remit more dollars to compensate for a peso appreciation in order to meet the peso budgets of their families in Philippines. Q1 2013, however, will likely exhibit a slowdown from the previous quarter, as the OFWs tend to save up for months. 
• With strong OFW remittances and portfolio capital in¬flows, the natural tendency will be for the peso to appreciate particularly in Q1. However, this will be tempered by BSP’s actions further buildup of international reserves, more macroprudential measures, a further 25 bps cut in policy rates, among others and by a larger trade deficit, which will emerge from stronger capital goods imports and larger imports of crude oil as a result of faster economic growth. 

References

• http://www.cnbc.com/id/100347707/Why_2013_Will_See_Faster_Stronger_Global_Growth 
• http://philippinebritish.com/outlook/philippines-economy-2013-outlook 
• http://www.adb.org/countries/philippines/economy
• http://www.moodys.com/research/Moodys-assigns-positive-outlook-on-Philippines-sovereign-credit-rating--PR_247006 
• www.bloomberg.com
• http://www.imf.org/external/pubs/ft/weo/2012/02/weodata/index.aspx 
• http://www.bsp.gov.ph/publications/media.asp?id=3070 

VBA: Getting Data From Bloomberg

This is what we did last time:

• Open bloomberg site. This is where we are going to get out data from.
• On Excel, Go to Data -> Get External Data -> From Web
• A New Web Query box will appear. It is like a browser so we input in the url of bloomberg site we want to copy
• Make sure that the Show Icons button is checked. It is beside the Refresh button, then click Import. A text format of the site will be imported in an Excel Sheet.

On GetData.xlsm, Sheet1.5, This is the data from sir. The exercise involves creating a macro that shows the recent price now, importing from bloomberg. And we want the refreshed data to be put in column B. Sheet1 would be useful because that is where we are going to find our data

Sheet2 is where I put my answers. Sheet3 contain all excess codes. It can be written as one Sub, but the challenge of this exercise is to create multiple sub or functions, instead of just creating loops.

How the code works

When the refresh button is pressed, the code gets data from bloomberg and imported it into a new sheet I called ws. It also assumes that the index name is on the first row and its values are in the second row when imported. Output is on Sheet2 and the ws worksheet will be deleted.

• Sub Calling(ws) - imports data from bloomberg
• Sub Refresh() - works like a Sub test(), it create a new worksheet, calls Calling(ws) that imports data from bloomberg, find the data needed and put it on the second column.
• Function GetData(inFind, ws)- looks for data in ws and get the appropriate value next to it

New to the Code

• I actually learned how to import data from bloomberg! Sub Calling contains such code
• Doesnt show alert box - Application.DisplayAlerts = False
• Delete the worksheet ws - ws.Delete
• Show alerts box -  Application.DisplayAlerts = True
• To stop updating the screen - Application.ScreenUpdating = False
• Gets the index of the last row - Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
• Gets the index of the last column - Lastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

The Code

Sub Calling(ws)

'

' Getting data from bloomberg,

    

    With ws.QueryTables.Add(Connection:= _

        "URL;http://www.bloomberg.com/markets/stocks/world-indexes/americas", _

        Destination:=ws.Range("$A$1"))

        .Name = "americas"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .WebSelectionType = xlEntirePage

        .WebFormatting = xlWebFormattingNone

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

    

End Sub

Sub Refresh()

    

    Application.ScreenUpdating = False

    

    Dim ws As Worksheet

    Set ws = Sheets.Add

    

    Call Calling(ws)

    

    Sheets("Sheet2").Range("B1") = Evaluate("Now()")

    

    For x = 3 To Sheets("Sheet2").Range("A1").End(xlDown).Row

        inFind = Sheets("Sheet2").Range("A" & x).Value

        Sheets("Sheet2").Range("B" & x).Value = GetData(inFind, ws)

    Next x

    

    Sheets("Sheet2").Activate

    

    Application.DisplayAlerts = False

    ws.Delete

    Application.DisplayAlerts = True

    

End Sub

Function GetData(inFind, ws)

    'This assumes that the first row has the Index Names and it second row contains the values.

    Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Lastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

      

    For i = 1 To Lastrow

        IndexName = ws.Range("A" & i)

        If IndexName = inFind Then

            GetData = ws.Range("B" & i)

        End If

    Next

End Function

Userforms and Algorithm for Indiv Project

In class, we are to make an outline. As to how is our project supposed to work. Since I've done the logistics of the code for CRR tree and BS, I will continue on how am I supposed to ask data from the user and how am I supposed to show my output. The following slides are supposed to be my guideline for that. This is supposed to be a scratch userform

Algorithm from naojan

The picture below also show my initial input-output plan, and the proposed plan. I included this for the intention of not becoming lost along the way.

The initial plan:

The proposed plan

VBA: OLS and SSE

This is a part of our class exercise. Also can be seen in the OLS and SSE excel file. The problem involved:

•  Getting the results of the regression and find the regression estimate per row and compare with the actual, 
• Computing for the error and the squared error
• Displaying the results of regression and the sum of errors in a new worksheet
• Creating a form for that,
• And running the regression program dynamically

I reused and revised the code in the post Autoregression in functions, in particular the  Function OLS() to regress. In addition, I made up a form getting the data dynamically. Although test for errors were not included.

Functions and Subs

• xM(x1) - Function that creates the x matrix
• CommandButton1_Click() -serves as the test button or the Ok button. or also known as my input - output button because it determines what is your input and creates the output.
• CommandButton2_Click() - the cancel button
• OLS(y, x) - the revised OLS function. Its output are the betas
• SSE(y, x) - solves for the SSE of the regressed y,x

Learning

I spent almost half of the class figuring out why the error mmult property cant run keeps popping up. and after a few hours I found out where I got it wrong. The logic of the code is correct but the parameters of the inputs are wrong. So lesson of the day is: Kung tama ang logic, at mali naman sa pinag gamitan. Mali pa rin yun. It works in real life.

The Form

Simple right :)

The Code

Private Sub CommandButton1_Click()
    y = Range(RefEdit1.Text)
    x1 = Range(RefEdit2.Text)
    
    'creating x matrix
    x = xM(x1)
    
    'regress
    param = OLS(y, x)
    
    'output Sheet
    Sheets.Add
    Range("A1") = "Solution Matrix"
    Range("A" & 2) = "Constant"
    For i = 2 To UBound(param)
        Range("A" & 1 + i) = "x" & i - 1
    Next i
    Range("B2").Select
    ActiveCell.Range(Cells(1, 1), Cells(UBound(param), 1)) = param
    Range("D1") = "Sum of Squared Errors"
    Range("D2") = SSE(y, x)
    
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Function xM(x1)
    'adding ones to for coefficients in x matrix
    x = Range(Cells(1, 1), Cells(UBound(x1), UBound(x1, 2) + 1))
    For j = 1 To UBound(x, 2)
        For i = 1 To UBound(x)
            If j = 1 Then
                x(i, j) = 1
            Else
                x(i, j) = x1(i, j - 1)
            End If
        Next i
    Next j
    
    xM = x
End Function
    
Private Function OLS(y, x)
 'regression, getting betas and coefficient
 'given the matrix y and x
    
    xtrans = Application.WorksheetFunction.Transpose(x)
    xtx = Application.WorksheetFunction.MMult(xtrans, x)
    xtxinv = Application.WorksheetFunction.MInverse(xtx)
    xtxinvxt = Application.WorksheetFunction.MMult(xtxinv, xtrans)
    bsol = Application.WorksheetFunction.MMult(xtxinvxt, y)
    OLS = bsol
    
End Function

Private Function SSE(y, x)
    'sum of squared errors
    
    param = OLS(y, x)
    
    model = Application.WorksheetFunction.MMult(x, param)
    
    SumErrors = 0
    For i = 1 To UBound(x)
        SumErrors = SumErrors + (y(i, 1) - model(i, 1)) ^ 2
    Next i
    
    SSE = SumErrors
End Function

Interest Rate Models

The class groups discussed two of term structure models: The Vacisek Model and the CIR term model.  These models are used to model interest rates.

What about the models:

• equilibrium type of model
• uses the Mean Reversion 
• offer benefits since they are numerically quite simple and easy to solve with computers
• very helpful in finding the important factors. 
• interesting and widely tested empirically since they offer closed form solutions of their conditional and steady state density functions.
• possible to get negative interest rates

Given the following:

• $\Delta t = T-t$ 
• $a$ as the speed of mean reversion
• $\mu$ as the long run average rate
• $r$ as the current state
• $\sigma$ as the volatility

We can get the models:

CIR Model:
\[ P(t,T) = A(T-t)e^{-B(T-t)r}\]
where: \[ A(T-t) = \left [   \frac{2 \gamma e^{(a+ \gamma)(T-t)/2}} {(\gamma +a)(e^{\gamma (T-t)}-1} \right ]^{2ab/ \sigma^2} \]
and
\[ B(T-t) = \frac {2(e^{\gamma (T-t)}-1)}{(\gamma+ a)(e^{\gamma (T-t)}-1)+2 \gamma}\]


Vacisek Model:
\[ P(t,T) = A(T-t)e^{-B(T-t)r}\]

where: \[ A(T-t) = \text{exp} \left [ \frac{ ( B(T-t) - T+t ) (a^2b-\sigma^2/2)}{a^2} -\frac{\sigma^2 B(T-t)^2}{4a}\right ] \]
and
\[ B(T-t) = \frac {1-e^{-a(T-t)}}{a}\]

Comparing these two models, they have the same process only that the parameters $ A(T-t)$ and  $B(T-t)$ are different.


Sources: 

• Hull book
• Groups ppt. They simplified the process.

Monte Carlo: Antithetic and QuasiRandom

Please see: "Antithetic and QuasiRandom.xls"

As I have came to understand, the Antithetic and QuasiRandom is used to reduce the variances.

Antithetic technique:

• Each time random variable r is drawn, its complement 1-r calculated and used to drive a parallel run of the simulation. This tends to lead to negatively correlated output values; hence lower variance
• Example: this is from Monte Carlo - Antithetic and Quasi-Random.xls and it's quite direct to the point.
• Table: The rand generates the random variable, and the randns generate the distribution. S1 solves $S_T$ of the simulation. S2 solves for antithesis of $S_T$ of the simulation. Then we get the payoffs for each S1 and S2. Then get the average payoff. Then we do it many many times. 
• MC Value: Get the average of the avg pay and discount it to get the present value of the option. 

QuasiRandom technique:

• We use Low Discrepancy Sequences to determine the random variables
• Faure Sequence
• Halton Sequence
• Sobol Sequence
• Inverse normal distribution
• Box-Muller transform
• Example: This is from Monte Carlo - Antithetic and Quasi-Random.xls 
• Table: it uses Faure Sequence to determine the random variable, then Box-Miller transformation is used to determine the inverse normal distribution. Then we get $S_T$ for the share price and get the payoff.
• MC Value: Get the average of the payoff and discount it to get the present value of the option. 

As can be seen in the graph below, Monte Carlo simulation, both Quasi and Antithetic Variate, converges to Black Scholes formula.

Source: Monte Carlo - Antithetic and Quasi-Random.xls 

Monte Carlo Option Pricing

Our group reported on Monte Carlo Option Pricing:

Monte carlo option pricing final v3 from naojan

Notes:

Monte Carlo

• important is how to generate the random distribution.
• Do not rely on this as a black box solution..
• We can look at the historical data.
• We can do this as a project – straight forward models from hull or wilmott to generate the price setting model..to value our payoff.

Slide 3 - The Entire slides focused on Option Pricing. But for Portfolio Statistics we have 3 important concepts:

• Find an algorithm for how the most basic investments evolve randomly. 
• Equities: 
• often the lognormal random walk
• can be represented on a spreadsheet or in code as how a stock price changes from one period to the next by adding on a random return. 
• Fixed-income
• BGM model in modeling how interest rates of various maturities evolve
• Credit 
• A model that models the random bankruptcy of a company. 
• Can represent any interrelationships between investments  which can achieved through correlations.
• Understand the derivatives theory for after performing simulations of the basic investments, there is a need to have models for more complicated contracts that depend on them such as options/derivatives/contingent claims. 
• May be able to use the results in the simulation of thousands future scenarios to examine portfolio statistics 
• Ie. how classical Value at Risk can be estimated

Slide 4

Risk-neutrality assumption – We make the assumption that investors are risk neutral, i.e., investors do not increase the expected return they require from an investment to compensate for increased risk.

Cox and Ross (1976) have shown that the assumption of risk neutrality can be used to obtain solutions of option valuation problems.’ This implies that the expected return on the underlying asset is the risk-free rate and that the discount rate used for the expected payoff on an option  is the risk-free rate.

Slide 6 - Stages from Watsham book and the original Boyle paper

Slide 7 - 14 - Option pricing_monte carlo example.xls

Slide 15 -Example from Wilmott: 

Its difference from watsham is that the watsham example creates random variables with empirical data's probability distribution. Wilmotts random variables are with uniform probability distribution.

***Note: have to understand the "Antithetic and QuasiRandom.xls"

VBA: CRR European, BS, and American Options

How the code works:

This is what I have done so far. It contains the basic codes for BS Merton, CRR European and American Options. It also includes the greeks, delta, gamma and theta. I coded it the way I understood options. In a visual way. Though it's not yet tested for other numbers.

Sub Test() just contains the input and output of what is happening on the code since I'm just halfway done. The "user" part and the "output" part isn't completed yet. It is just presentable.

GenEurAm() contains the code in solving the option value and generating the values involved in the tree for Amerucan and European options.

Greeks() computes for the greeks and has an output table of the greeks.

GenBS() is the Black-scholes Merton way for computing european options

DrawTree() generates a designed tree complete with asset price and option prices. Though it is not as efficient as I would like. I computed for 100 steps and it took 9 secs to generate the tree alone.

DrawTree2() generates a tree in a more efficient manner. I have a choice whether what tree to generate. Either the price tree or the option value tree.

The functions are just a way to make the code flexible and more user-friendly. Though I don't know what to do with them right now at my codes..maybe later it will be handy.

Will do the user part next. and then the output sheets.

New to the code:

• Learned: Declaring variables outside the sub or function
• ActiveCell.Offset(0, 0).Value 
• Range(starter).Offset(0, 0).Value
• On properties of the range
• With ... End With 
• .Interior.ColorIndex = 34
• .Borders.Weight = xlThin
• .HorizontalAlignment = xlCenter
• .Value = OptionValue(lp, j)

Code:

Public OptionValue() As Double

Public Price() As Double
Public bsOV As Double

Public kind As String
Public z As Integer

Public So As Double
Public K As Double
Public q As Double
Public r As Double
Public T As Double
Public n As Integer

Sub Test()
    
    So = 100
    K = 95
    q = 0
    r = 0.08
    vol = 0.3
    T = 0.5
    n = 5
    'kind = "a" 'american = a, european = e
    z = -1 'for call z= -1, if for put z= -1
    
    starter = "C11"
    
    Range(starter).Offset(0, 0).Value = "European:BS"
    Range(starter).Offset(0, 1).Value = GenBS(z, So, K, q, r, vol, T, n)
    
    Call GenEurAm("e", z, So, K, q, r, vol, T, n)
    Range(starter).Offset(1, 0).Value = "European:CRR"
    Range(starter).Offset(1, 1).Value = OptionValue(0, 0)
    
    Range(starter).Offset(3, 0).Value = "Asset Prices:"
    Range(starter).Offset(4, 1).Select
    Call DrawTree2(n, Price)
    Range(starter).Offset(n + 5, 0) = "European Option Prices:"
    Range(starter).Offset(n + 6, 1).Select
    Call DrawTree2(n, OptionValue)
    
    Call GenEurAm("a", z, So, K, q, r, vol, T, n)
    Range(starter).Offset(1, 3).Value = "American:CRR"
    Range(starter).Offset(1, 4).Value = OptionValue(0, 0)
    Range(starter).Offset(2 * n + 7, 0) = "American Option Prices:"
    Range(starter).Offset(2 * n + 8, 1).Select
    Call DrawTree2(n, OptionValue)
    
    Range(starter).Offset(3 * n + 9, 0).Value = "Greeks:"
    Range(starter).Offset(3 * n + 10, 1).Select
    Call Greeks
    
End Sub

Private Sub GenEurAm(kind, z, So, K, q, r, vol, T, n)
    
    'computation of the formulas
    u = Exp(vol * Sqr(T / n))
    d = 1 / u
    b = Exp((r - q) * T / n)
    p = (b - d) / (u - d)
    df = Exp(r * T / n)
    
    'Price tree
    ReDim Price(0 To n, 0 To n)
    
    'American and European Option tree
    ReDim OptionValue(0 To n, 0 To n)
    
    For j = n To 0 Step -1
        For lp = 0 To j
            
            'Price tree
            Price(lp, j) = So * u ^ lp * d ^ (j - lp)
            whenX = z * (Price(lp, j) - K)
            
            'option tree
            'at the end nodes
            If j = n Then
                OptionValue(lp, j) = Application.WorksheetFunction.Max(whenX, 0)
            'at other nodes
            Else
                whenNotX = (p * OptionValue(lp + 1, j + 1) + (1 - p) * OptionValue(lp, j + 1)) / df
                'for american option
                If kind = "a" Then
                    OptionValue(lp, j) = Application.WorksheetFunction.Max(whenX, whenNotX)
                'for european option
                ElseIf kind = "e" Then
                    OptionValue(lp, j) = whenNotX
                End If
            End If
        Next lp
    Next j

End Sub

Private Sub Greeks()
    'after calculating the prices and the option values
    'brute force
    
    Dim del(0 To 2) As Double
    Dim gam As Double
    Dim the As Double
        
    del(0) = (OptionValue(0, 1) - OptionValue(1, 1)) / (Price(0, 1) - Price(1, 1))
    
    del(1) = (OptionValue(0, 2) - OptionValue(1, 2)) / (Price(0, 2) - Price(1, 2))
    del(2) = (OptionValue(1, 2) - OptionValue(2, 2)) / (Price(1, 2) - Price(2, 2))
    gamma = (del(1) - del(2)) / (Price(0, 1) - Price(1, 1))
    
    theta = (OptionValue(1, 2) - OptionValue(0, 0)) / (2 * T / n)
    
    ActiveCell.Offset(0, 0).Value = "Delta"
    ActiveCell.Offset(0, 1).Value = del(0)
    ActiveCell.Offset(1, 0).Value = "Gamma"
    ActiveCell.Offset(1, 1).Value = gamma
    ActiveCell.Offset(2, 0).Value = "Theta"
    ActiveCell.Offset(2, 1).Value = theta
    
End Sub

Private Function GenBS(z, So, K, q, r, vol, T, n)
    'Black Scholes Merton
    
    'defining the parts
    d1 = Application.WorksheetFunction.Ln(So / K) + (r - q + vol ^ 2 / 2) * T / (vol * Sqr(T))
    d2 = Application.WorksheetFunction.Ln(So / K) + (r - q - vol ^ 2 / 2) * T / (vol * Sqr(T))
    
    If z = 1 Then
        nd1 = Application.WorksheetFunction.NormSDist(d1)
        nd2 = Application.WorksheetFunction.NormSDist(d2)
    ElseIf z = -1 Then
        nd1 = Application.WorksheetFunction.NormSDist(-d1)
        nd2 = Application.WorksheetFunction.NormSDist(-d2)
    End If
    
    'getting black scholes
    GenBS = z * (So * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2)
    
End Function

Private Sub DrawTree(n, starter)
    'with design, not efficient
    
    r = 2 * (2 * n + 1)
    For j = 0 To n
        nrow = r / 2 - 2 * j
        For lp = j To 0 Step -1
            With Range(starter).Offset(nrow, j)
                .Interior.ColorIndex = 42
                .Borders.Weight = xlThin
                .HorizontalAlignment = xlCenter
                .Value = Price(lp, j)
            End With
            With Range(starter).Offset(nrow + 1, j)
                .Interior.ColorIndex = 34
                .Borders.Weight = xlThin
                .HorizontalAlignment = xlCenter
                .Value = OptionValue(lp, j)
            End With
            nrow = nrow + 4
        Next lp
    Next j
End Sub

Private Sub DrawTree2(n, ranger)
    'after calculating for the prices and the optionvalue
    'simple lng
    
    For col = n To 0 Step -1
        For nrow = n - col To n
            ActiveCell.Offset(nrow, col).Value = ranger(n - nrow, col)
        Next nrow
    Next col
    
End Sub

Public Function EuroCall(So, K, q, r, vol, T, n)
    Call GenEurAm("e", "1", So, K, q, r, vol, T, n)
    EuroCall = OptionValue(0, 0)
End Function

Public Function EuroPut(So, K, q, r, vol, T, n)
    Call GenEurAm("e", "-1", So, K, q, r, vol, T, n)
    EuroPut = OptionValue(0, 0)
End Function

Public Function AmCall(So, K, q, r, vol, T, n)
    Call GenEurAm("a", "1", So, K, q, r, vol, T, n)
    AmCall = OptionValue(0, 0)
End Function

Public Function AmPut(So, K, q, r, vol, T, n)
    Call GenEurAm("a", "-1", So, K, q, r, vol, T, n)
    AmPut = OptionValue(0, 0)
End Function

Public Function BSCall(So, K, q, r, vol, T, n)
    BSCall = GenBS("1", So, K, q, r, vol, T, n)
End Function

Public Function BSPut(So, K, q, r, vol, T, n)
    BSPut = GenBS("-1", So, K, q, r, vol, T, n)
End Function

Sources:


http://www.cpearson.com/excel/classes.aspx

http://dmcritchie.mvps.org/excel/colors.htm

http://www.global-derivatives.com/index.php/component/content/52?task=view

Black, F. & Scholes, M. "The Pricing of Options & Corporate Liabilities" The Journal of Political Economy (May '73)

Hull, J. "Options, Futures & Other Derivatives" 5th Edition 2002 - Chapter 12

Merton, R. "Theory of Rational Option Pricing" Bell Journal of Economics & Management (June '73)


Mathlab Example, European or American options
          - http://www.goddardconsulting.ca/matlab-binomial-crr.html

American Call Option, Greeks, Python, getting data from google finance, etc., Concepts and formulas included
          - http://www.quantandfinancial.com/2012/11/cox-ross-rubinstein-option-pricing-model.html

Option Pricing Formulas book, Complete with greeks and American Options p279-288
          - http://r2-d2.bu.edu/AT__The_Complete_Guide_to_Option_Pricing_Formulas__2nd_ed_.pdf

Option Calculator
         - http://www.volatilitytrading.net/cox_ross_rubenstein_calculator.htm

VBA: Bond Valuation II

In our class discussion, the prof taught me a different way of coding using the record macro and worksheets.

• Made me think it will be helpful in creating excel sheet forms and output data.
• Discovered ActiveCell.Offset(r,c)

Here's the code:


Sub Macro3()

For i = 1 To 108

Application.ScreenUpdating = False

Sheets("DATA Process").Select
Range("A1").Select

'dito ka lng nagloop and it works for all
ActiveCell.Offset(i, 0).Range("A1:E1").Select
Selection.Copy

Sheets("Scratch").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Range("F15").Select
Selection.Copy

Sheets("DATA Process").Select
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next i

End Sub

CRR: Option Pricing

This post dwells on CRR tree with the application of Option Pricing.

I admit the first method is the simpler one but only used for European Option Pricing. However if I wanted to generalize European and American Option Pricing, it is better explained in the second method.

Inputs:

• Share Price (S) in dollars
• Exercise Price (K) in dollars
• Dividend yield (q) in percent (continuously paying)
• Risk-free rate (r ) in percent and p.a.
• Volatility ( σ ) in percent
• Time of maturity (T) in years
• Number of periods/ steps (n)  

Output: European Call and Put Option

Method 1: Direct Approach (similar to Haug book and  Jackson and Staunton book)

First we get the necessary $u$, $d$ and $p$ by using the following formulas:

• $u = e^{\sigma \Delta{t}}$
• $d = e^{-\sigma \Delta{t}}$  or simply $d = \frac{1}{u}$
• $p = \frac{ e^{(r-q)\Delta{t}} - d }{u-d} $

where $\Delta{t} = \frac{T}{n}$.

European Options are only exercised at the end of the period. That means we are just looking at the end nodes of the tree. In this case, we solve for the probability at each of the end nodes, multiply it by each payoff, sum it up and then get its present value. The formula for call and put option are as follows:

\[ c= e^{-rT} \sum_{i=0}^{n}  {_n}C_{i}  p^i  (1-p)^{n-i} \text{max}[Su^i d^{n-i} - K, 0] \]
\[ p= e^{-rT} \sum_{i=0}^{n}  {_n}C_{i}  p^i  (1-p)^{n-i} \text{max}[K-Su^i d^{n-i}, 0] \]
where

• $e^{-rT}$ is the discounting factor
• ${_n}C_{i}  p^i  (1-p)^{n-i}$ is the probabilities at each final node $n$ at step $i$
• $Su^i d^{n-i}$ is the prices at each final node $n$ at step $i$
• $\text{max}[Su^i d^{n-i} - K, 0]$ is the payoff for call option.
• $\text{max}[K-Su^i d^{n-i}, 0]$ is the payoff for put option.

Notice that the only difference for put and call options are their payoffs.

Example: same as in Haug approach sheet in CRRforProj.xlsm 

Given the following values, we want to compute for the European Call and Put Option.

Solution:

Method 2: Using Trees (also similar to Haug book, Jackson and Staunton book and the presentation found in earlier post.)


First we get the necessary $u$, $d$ and $p$ by using the following formulas:

• $u = e^{\sigma \Delta{t}}$
• $d = e^{-\sigma \Delta{t}}$  or simply $d = \frac{1}{u}$
• $p = \frac{ e^{(r-q)\Delta{t}} - d }{u-d} $

where $\Delta{t} = \frac{T}{n}$.

Then we create the tree for price. The formula is  $Su^i d^{n-i}$ at each node $j$ at step $i$.

Then we create the tree for the option price. The end nodes, we compute for the payoff. It is the same whether it is European or American options.

• $\text{max}[Su^i d^{n-i} - K, 0]$ is the payoff for call option.
• $\text{max}[K-Su^i d^{n-i}, 0]$ is the payoff for put option.

At the remaining nodes, European and American Option Prices differ even if the process is the same. It is because European option only exercises the end of the period while American options can be exercised earlier. So the formula for the remaining nodes $j$ at step {i} are as follows:

• European Call and Put: $O_{i,j}= e^{-r \Delta{t}} (p O_{i+1,j+1}+(1-p)O_{i,j+1} ) $
• American Call: $OV_{i,j} = \text{max}[Su^i d^{n-i} - K, e^{-r \Delta{t}} (pO_{i+1,j+1}+(1-p)O_{i,j+1} )]$
• American Put: $OV_{i,j} = \text{max}[K-Su^i d^{n-i},e^{-r \Delta{t}} (pO_{i+1,j+1}+(1-p)O_{i,j+1} )]$

Example:

for European Option Example: please see earlier post.

For American Option Example: same as in American Tree sheet in CRRforProj.xlsm 

Given the following values, we want to get the American Put Option Price.

Solution:

Source: 

• http://r2-d2.bu.edu/AT__The_Complete_Guide_to_Option_Pricing_Formulas__2nd_ed_.pdf
• Advance Modelling in Finance using Excel and VBA by Jackson and Staunton

Black Scholes: Option Pricing

I thank God for blogging. It forces me to put my thoughts into words, helping me to think clearly.

Inputs:

• Share Price (S) in dollars
• Exercise Price (K) in dollars
• Dividend yield (q) in percent (continuously paying)
• Risk-free rate (r ) in percent and p.a.
• Volatility ( vol ) in percent
• Time of maturity (T) in years or Option Life (T-t) (it doesnt matter

Output:

• European Call and Put Option

Method: Black-Scholes Merton

• With or without dividends, we can use the same formula.
• d1 = Ln(So / K) + (r - q + vol ^ 2 / 2) * T / (vol * Sqr(T)
• d2 = Ln(So / K) + (r - q - vol ^ 2 / 2) * T / (vol * Sqr(T))
• z  * (So * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2)
• if z = 1, we have a call option 
• if z = -1, we have a put option
• nd1 and nd2 are standard normal distribution of d1 and d2 respectively.

Sources:

• http://www.global-derivatives.com/index.php/component/content/52?task=view
• Black, F. & Scholes, M. "The Pricing of Options & Corporate Liabilities" The Journal of Political Economy (May '73)
• Hull, J. "Options, Futures & Other Derivatives" 5th Edition 2002 - Chapter 12

VBA: Bond Valuation as Function

Given:  Data from automation macro Data.xls

On that excel sheet, we are given: term, credit rating, coupon rate and par value. Compute for the PV Cash flow/Market Value of the bond of the using the spot yield curve with the credit rating add on.

How the code works:

The Market Value is solved using the user defined function MV(term, creditrate, coupon, pvalue) with dynamic spot yield and credit rating, by letting the user define such.

1. We define the discounting factor depending on the requirements by using Lookup() of Excel.

2. We then use the discount factor in getting the cash flows. We sum up the PV of every year for the results. 

The AutoCompute() Sub automatically computes for the MV given number n of records.

New to the code:

• Defining Dimensions
• Application.WorksheetFunction.Lookup(i, spotYC)
• r = Range("A2").End(xlDown).Row - 1

Other learnings:

• one dimensional array: http://www.vbtutor.net/VBA/vba_chp21.htm
• Dim rate(10) as double

Code:

Function MV(term As Integer, CreditRate As String, Coupon As Double, pv As Double)
    
    Dim sumMV As Double
    Dim drate As Double
    Dim s As String
    Dim a As String
    
    s = Range("o6") 'range of spotyield
    spotYC = Range(s)
    a = Range("o7") 'range of credit rating addon
    CreditReq = Range(a)
    
    'determining individual credit rate Add on
    
    addon = Application.WorksheetFunction.Lookup(CreditRate, CreditReq)
    
    'determining cashflows
    
    sumMV = 0 'summing cashflows along each term
    For i = 1 To term
        srate = Application.WorksheetFunction.Lookup(i, spotYC) 'determine the spot yield rate per year
        drate = srate + addon
        sumMV = (Coupon * pv) / (1 + drate) ^ i + sumMV 'for cashflows with coup*pv
    Next i
    sumMV = sumMV + pv / (1 + drate) ^ term 'for the last cashflow


    If term = 0 Then
        sumMV = 0
    End If

    'throwing back to excel
    MV = sumMV

End Function


Sub AutoCompute()
    
    Dim term As Integer, CreditRate As String, Coupon As Double, pv As Double
    
    'first row serves as Titles so start with
    r = Range("A2").End(xlDown).Row - 1
      
    For i = 1 To r
        term = Range("b" & 1 + i)
        CreditRate = Range("c" & 1 + i)
        Coupon = Range("d" & 1 + i)
        pv = Range("e" & 1 + i)
        Range("f" & 1 + i) = MV(term, CreditRate, Coupon, pv)
    Next i
    
End Sub