• This service has been inactive since 2010 as my new role in TM1 consulting.
  • Please disregard any commercial offers as this website has been archived and transformed into a blog rather than a commercial website.
  • Please contact me for more info in BI and Financial Modelling consultancy.

House: Borrow, Buy, Sell and Profit


House price may increase in one or two years and the total cost including the paid instalments, left loan balance and prepayment penalty may be lower than the house price (after agent fee) if sold.

Then, we can profit and start finding another house to buy and wait for another year or two. The key is maintaining a good relationship with the loan provider.

HomePlan
HomePlan.xls
Hosted by eSnips

Portfolio Optimisation (Shares & Funds)



It's quite a complex portfolio optimisation model which aims to find the optimised fund allocation in a portfolio of Australian and NZ managed funds with future return simulation using MonteCarlo method.

The historical prices are provided by Morningstar NZ and the copyright of this model is belong to Lyfords Ltd which I work with.

The optimisation is done by maximising the tangency portfolio (theta) so that it touches the edge of the efficient portfolio from the constant. There are also additional constraints such as particular conditions for any particular fund allocation. (eg. Thoroughbred Cash Fund = 5% and PPS Int Fixed Interest less than 10%)

If it applies into managed funds instead of shares, this may be an optimisation of a portfolio that may have been optimised (by fund managers) as a managed fund is in fact an optimised portfolio.

The process as follows:

1. Gathering data input: historical monthly fund prices. Calculate the historical monthly mean and stdevation of each asset.

2. Conduct a MonteCarlo simulation for monthly returns through 3 years horizon (36 months) using normal distributed random numbers using the historical montly mean and stdev at random probability.

3. Simulate the whole 3 years random movement. If simulated 10 times, so there are 36 monthly simulations times 10 three-yearly simulations (360). Record the projected mean and stdev.

4. Find the simulated portfolio return and risk using the projected mean and stdev.

5. Find the optimised portoflio expected return and risk.

6. Solve the optimised asset allocation using solver VBA. Apply additional constraints to reoptimise.

7. Calculate the value-at-risk.

Option to use mean or CAPM return is to choose whether CAPM is believed to be effective or not in this optimisation. If mean return is used then beta and risk premium don't matter.

Covariance matrix is also to be projected based on the simulation result.

Option to use historical or projected standard deviation is to choose whether portfolio risk is believed to be accumulated in the simulation result.

To run the Solver VBA.

1. Tools > Add-ins to have the Solver add-in first (in a file called Solver.xla)

2. Then create a reference to the 'Solver.xla' in Visual Basic Editor.
Go to Tools > Macro > Visual Basic Editor, choose Tools > References and check the box for Solver.

If it is missing, just click browse and manually find the "Solver.xla" file wherever it is located in your computer.

[Sorry, it's a premium model]

Live-updated Beta Calculation




Assume CAPM is applicable and works empirically without any doubt in the theory, we can then calculate a stock Beta dynamically over time as it represents the updated historical stock prices from the latest price. Hence, the stock Beta changes over time.

The process and the VBA coding are similar with the Live-updated Yield posted before.

This spreadsheet consists of 3 worksheets that dynamically calculates the beta of Astra stock returns (ASII.JK) against the Jakarta Composite index returns (^JKSE) as being updated over time by clicking the "Update" buttons. Thus, we can get the latest Beta of Astra stock.

The first worksheet is JKSE sheet that contains the recent updated market prices and the second one is ASII sheet for the recent updated stock prices. In these two sheets, there are two "Update" buttons that allow us to update the recent prices from web query and to record the recent prices into the Beta sheet (third worksheet).

The stock Beta is then calculated by simply finding the slope between the periodical stock (x axis) and market (y axis) returns.

[Sorry, it's a premium spreadsheet]

Comprehensive Financial Planning

An Excel model to judge your prosperity and set your financial goal and objectives.

[Sorry, it's a premium spreadsheet]

The comprehensive spreadsheet is premium, however, you can download this file: University and Pension Plans.

Uni & Pension Plan
Uni & Pension Plan...
Hosted by eSnips

Value-at-Risk

Suppose you hold a portfolio like this.

Astra Int shares (ASII) with market value of IDR1,000,000,000.00
Indonesia Govt bonds (Indo-17) with market value of IDR1,000,000,000.00

Total market value of your portfolio is IDR2,000,000,000.00

You may wonder how much your portfolio value would be tomorrow in the worst-case scenario.

How do we get that value? Such value is known as Value-at-Risk (VaR).

The procedure:
1. Collect historical daily data for Astra share prices and calculate the daily return.
2. Collect historical daily date for Indonesia govt bonds yields, generate index and calculate the daily return.
3. Generate covariance matrix for share return and bond return.
4. Calculate portfolio variance and standard deviation. In Excel, it can be done using MMULT(MMULT(TRANSPOSE(value),covariancematrix),value)
5. Calculate VaR at 99% confidence level as 2.326 multiply by standard deviation.
6. Tomorrow's worst value = Today's value - VaR

From the example, I found tomorrow's worst case value is IDR1,937,346,708.11

[Sorry, it's a premium spreadsheet]

Bond Price, Duration and Convexity



In Feb 06 I submitted some short of article in Wikipedia about closed form formulas for bond duration and convexity. Below is the clone and hopefully it can be useful for those who may use the formulas for bond analysis modelling in Excel spreadsheets.

As far as I know, these have never been published on the web like this as I experienced some problem in finding them when doing some bond pricing project.

Closed-form formula:
A single arithmetic formula obtained to simplify an infinite sum in a general formula. The general formula of bond duration and bond convexity cannot be said closed-form as there is an infinite sum over the different time periods. Using a closed-form formula, a bond’s duration or convexity can be calculated at any point in its life time.

Bond duration closed-form formula (Richard Klotz):


C = coupon payment per period (half-year)
P = present value (price)
i = discount rate per period (half-year)
a = fraction of a period remaining until next coupon payment
m = number of coupon dates until maturity

Bond convexity closed-form formula (Blake and Orszag):


D = coupon payment per period
P = present value (price)
B = face value
i = discount rate per period (half-year)
a = fraction of a period remaining until next coupon payment
m = number of coupon dates until maturity

[Sorry, it's a premium spreadsheet]

Live-updated Yield

The purpose of this model is to provide a live-updated spreadsheet by automatically capturing the current data provided by a website via Web Query and then automatically recording the data into a master data sheet through a Bootstrapping method.

So, it's just two clicks away on our spreadsheet to update a time series graph to the current rate, automatically. It's similar when we are running a live-update for our antivirus software.



The Worksheet

[Sorry, it's a premium spreadsheet]


The Sheets


There are three sheets: INDO17, Data and Graph. "INDO17" is a sheet where the web query should go and where the two updating clicks are available. "Data" is a sheet where the bootstrapping process is running. "Graph" is only for visualisation.


Cell Naming


Cell naming is an important method as explained before in the previous post. There are several cells to be named in this worksheet and used for visual basic coding: INDO, INDOrow, INDOwebquery, nodays and updatelatestINDO.

To find which range is referred by a cell name, just go to the box on the top left, scroll the box and click the name. A range or cell will be shown.


  • INDO: a range as the destination for the updated rate from 2-Mar-06 to 31-Dec-10 in "Data" sheet (B5:B1263).
  • INDOrow: a cell showing a number of rows from 2-Mar-06 to the latest date has been updated in "Data" sheet (D3).
  • INDOwebquery: a range as the source of data captured via web query in "INDO17" sheet (A2:D21).
  • nodays: a cell showing a number of days need to be updated since the last updated date in "INDO17" sheet (M14).
  • updatelatestINDO: a cell showing the next date needs to be updated in "Data" sheet (G4).

Visual Basic Coding


Web Query Update
This code is for the first button "Update INDO-17 Yield from Web Query". The webpage source used for web query is the INDO-17 yield data provided in the Central Bank of Indonesia website.

The code:

Sub dailyINDO()

Application.OnTime Now + TimeValue("12:00:00"), "updateINDO"

End Sub

Sub updateINDO()

Sheets("INDO17").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
dailyINDO

End Sub


Recording Data through Bootstrapping
This code is for the second button "Record Yield into Data Sheet". The code is quite complicated. However, if we can follow the logic and as referring to the range names, this is a piece of cake for us.

The code:

Sub recordINDO()

For j = 1 To Range("nodays")
For i = 1 To Range("nodays")

If Sheets("Data").Range("updatelatestINDO") = Range("INDOwebquery").Cells(i, 1) Then
Range("INDOwebquery").Cells(i, 4).Copy

r = Sheets("Data").Range("INDOrow") + 1

Sheets("Data").Range("INDO").Cells(r).PasteSpecial Paste:=xlPasteValues
Calculate
End If

Next i
Next j

Sheets("Data").Select

End Sub


The meaning of the above code is:

For the loops as many as the number of days needs to be updated, Excel will copy each yield in the web query sheet and paste into the data sheet on each corresponding date. Where the corresponding date is found by referring the number of rows from the first date to the latest updated date plus 1 day after.

Piece of cake, aye?

Option for updating screen
You may insert this code to disable the visualisation of screen updating. Meaning, you can't see the price line moving forward as time moves.

Application.ScreenUpdating = False


The "INDOrow"


It seems the key problem here is to find the number of "INDOrow", a cell showing a number of rows from 2-Mar-06 to the latest date has been updated in "Data" sheet (D3).

The way is using a MATCH function to calculate the number of rows from 2-Mar-06 to the latest date as this formulae:
=MATCH("latest date",C5:C498,0)

Where the "latest date" is found by tagging a note in the column C as the latest date using IF formulae: =IF(AND(the next yield=0, the current yield >0),"latest date","").


The "updatelatestINDO
"

This is a cell showing the next date needs to be updated in "Data" sheet (G4) that can be found easily using an INDEX function:

=INDEX($A$5:$A$498,INDOrow+1)


Recommendation and Notes

The title is for updating government bond yield, but the application can be also for updating other time series data such as foreign exchange rates or share prices.

I also open for any suggestions particularly for any more simple VBA codes.

Please let 2 days lag from Bank Indonesia website for current rate. It doesn't mean they are lazy to update, they just have a lot of other things to do.

Dynamic Company Valuation



Since the key parameters in valuation model are mostly influenced by market behaviour, then it is important to update some relevant market data in regular basis to see how they affect the analysis. The relevant market data may include risk-free rate, stock price and market index that are able to indicate changes in value.

This model introduces manipulation of data by automatically changing some market data via the tool of external data import in Excel and some VBA simple codes for refreshing the Web Query. The main goal is to adjust calculation in Beta and WACC in the framework of company valuation and also for Option and Bond analysis.

In projecting financial statement, the historical data of the last two year operation is used to find the assumptions in the form of financial ratios that are normally corresponded to Sales. Then, the average of the last two year ratios can be used as the basic assumptions. Sales Growth ratio can be used as a look-up parameter in sensitivity analysis.

Sensitivity models are used by utilising two important tools in Excel, i.e., Data Tables and Graphs. Both tools are set to show visual changes in value after altering the growth ratio to some levels. This model also introduces sensitivity analysis of changes in financial ratios for the goal of predicting the possibility of bankruptcy. Traditional financial ratio techniques used are for Altman Bankruptcy model and Chesser Loan Surveillance model to be visually compared with the equity value and stock price resulted from the Free Cash Flow valuation.

The key analysis in this model is the Free Cash Flow valuation that has been as the important technique explained in the course and from the text. The method follows the that has been explained in the class using the No-Negative Cash and Debt as the Plugs. Some modification is tried to be applied by using Bonds as the plug, where the Bank Loans is calculated based on the bank loans to bonds ratio. The sum of both is the Long-term Debt.

Using bonds as the plug, the plan is to set of a Bond Portfolio for covering future financing in the projection. Then, the analysis refers to Net Present Value and Portfolio Duration that may anticipate in the future. As a bond issuer, the company should expect the lower value and duration.

[Sorry, it's a premium spreadsheet]

Bond Portfolio Analysis




Using the closed-form bond formula posted before, this bunch of spreadsheet is produced to value a bond at any point in its life time and do some analyses, as follows:
  • Portfolio valuation, duration and convexity
  • Derivatives valuation: FRA, Swap, Futures
  • Risk governance: Value-at-Risk, Stress Testing and Scenario Analysis
  • Hedging effectiveness
  • Liquidity analysis

Remark: this portfolio is a liability/debt portfolio, NOT an asset/investment portfolio.

[Sorry, it's a premium spreadsheet]

WACC Calculation



This model is to estimate the weighted average cost of capital (WACC) of Mainfreight Limited (MFT), an NZX listed company.

Bear in mind, the debt and equity values should be estimated as the MARKET VALUES.

Some may just estimate the BETA, but in this model I calculated it based on the historical share prices. I used NZX ALL index as the market proxy and 5 year NZ Government Bond as the risk-free rate with risk premium assumed 7%.

WACC Calculation
WACC Calculation.x...
Hosted by eSnips

Share Valuation

This model is the share valuation of PT Astra International Tbk using Dividend Discount Model with assumed growth of 40%.

This should be the simplest way to value a share. By calculating the cost of equity as the discount factor, the projection of dividend cash flow is discounted to get the estimated value.

Seems similar with growth annuity valuation?

ShareValue
ShareValue.xls
Hosted by eSnips

Value-at-Risk

Suppose you hold a portfolio like this.

Astra Int shares (ASII) with market value of IDR1,000,000,000.00
Indonesia Govt bonds (Indo-17) with market value of IDR1,000,000,000.00

Total market value of your portfolio is IDR2,000,000,000.00

You may wonder how much your portfolio value would be tomorrow in the worst-case scenario.

How do we get that value? Such value is known as Value-at-Risk (VaR).

The procedure:
1. Collect historical daily data for Astra share prices and calculate the daily return.
2. Collect historical daily date for Indonesia govt bonds yields, generate index and calculate the daily return.
3. Generate covariance matrix for share return and bond return.
4. Calculate portfolio variance and standard deviation. In Excel, it can be done using MMULT(MMULT(TRANSPOSE(value),covariancematrix),value)
5. Calculate VaR at 99% confidence level as 2.326 multiply by standard deviation.
6. Tomorrow's worst value = Today's value - VaR

From the example, I found tomorrow's worst case value is IDR1,937,346,708.11

Download the Excel model here >>>

AstraWatch: week 29 Oct 07

PT Astra International Tbk (ASII)

Live-updated Value
Growth 16.00%
BETA 1.298713447
WACC 21.35%
Equity value (in IDR ,000,000,000) 42,757.86
Value per share (in IDR) 10,561.79

last trade
ASII 22,500.00
JKSE 2,638.21
INDO-17 6.086

Astra share value using Dividend Discount Model

Share valuation of PT Astra International Tbk using Dividend Discount Model.

Last dividend: 290
Dividend growth: 40.00% assumed
Discount rate: 42.13% cost of equity

Estimated share price = Last dividend (1 + dividend growth) / (discount rate - dividend growth)

Estimated share price = 19,047.38

AstraWatch: week 24 Aug 07

PT Astra International Tbk (ASII)

Live-updated Value
  • Growth 16.00%
  • BETA 1.303462794
  • WACC 19.76%
  • Equity value (in IDR ,000,000,000) 68,188.12
  • Value per share (in IDR) 16,843.41

last trade
  • ASII 17,300.00
  • JKSE 2,143.11
  • INDO-17 6.887

AstraWatch: week 17 Jul 07

PT Astra International Tbk (ASII)

Assumed Growth 24%.
Still undervalue. Strong buy.

Live-updated Value

Growth 24.00%
BETA 1.374012174
WACC 25.33%
Equity value (in IDR ,000,000,000) 93,234.68
Value per share (in IDR) 23,030.26

last trade

ASII 18,600.00
JKSE 2,333.68
INDO-17 6.329

AstraWatch: week 20 Apr 07

PT Astra International Tbk (ASII)

Live-updated Value

Growth 15.00%
BETA 1.39372807
WACC 19.53%
Equity value (in IDR ,000,000,000) 56,714.69
Value per share (in IDR) 14,009.32

last trade
ASII 14,300.00
JKSE 1,918.35
INDO-17 6.036

Astra net income decreased, so growth assumption changed

From the 2006 Financial Statement, PT. Astra International net income decreased from Rp5,457 bil to Rp3,712 bil. This is contributed by the decrease in sales growth in 2006 by -10.08%, while the growth in 2005 is 37.41%. The average growth between 2005 and 2006 is 13.67%.

It is good because Astra becomes more rational in determining growth in business. The 37.41% growth in 2005 can be considered as too much and theoritically in FCF valuation, high growth can drop the value referring the sensitivity analysis.

Therefore, Astra valuation in this blog needs to change the growth assumption to 15%.

Live-updated Value
Growth= 15.00%
BETA= 1.411115248
WACC= 19.56%
Equity value (in IDR ,000,000,000) = 56,282.57
Value per share (in IDR) = 13,902.58

last trade

ASII= 14,950.00
JKSE = 1,794.36
INDO-17= 6.073%

Astra Watch: Week 12 Feb 07

PT Astra International Tbk. (ASII)

Growth: 19.00%
BETA: 1.411115248
WACC: 27.22%
Equity value (in IDR ,000,000,000): 50,862.09
Value per share (in IDR) : 12,563.64

last trade
ASII: 14,950.00
JKSE: 1,794.36
INDO-17: 6.073%

AstraWatch: Week - 19 Jan 07

PT Astra International Tbk. (ASII)

Growth= 19.00%
BETA= 1.439090216
WACC= 27.35%
Equity value (in IDR ,000,000,000)= 49,937.87
Value per share (in IDR) = 12,335.35

last trade
ASII= 15,600.00
JKSE = 1,730.47
INDO-17= 6.026%

Injecting Capital Fund by cash

From the previous example, it is shown that the initial capital injection was made by simply recording journal of term loan and equity transfers to bank saving, to be then spent for purchasing fixed assets. The bank balance then is:
Electronic Clearing Account ($ 75,000)
KiwiBank Saving Account $ 100,000
Bank balance $ 25,000

The owner is now thinking to add some more fund to the bank account by cash. However, he wants to consider that his fund injection is to be recorded as a banking receipt to the company.

To record this:
go to Banking > Receive Money


Then fill in the form like this:


Some notes to this form:
1. Deposit to account: KiwiBank Saving
2. The payor is Jeffry Liando as personal
3. Account is for KBF equity as to be injected as capital

The transaction journal is similar with the previous direct method (recording journal):


The updated balance sheet as of Jan 2007:

Balance Sheet Report

To see the balance sheet report as of Dec 2006:
go to Report (bottom menu) > Accounts
choose Standard Balance Sheet > click Customise
and choose the period of December


And the report will be like this after clicking Display: