Keziana Financial Modelling

Global Financial Crisis 2008-2010 Movies

2010-12-11T21:51:00.000-08:00

Please contact me if you want this movie.

Movie: The Last Days of Lehman Brothers (2009, BBC Movie)

The heads of Wall Street's biggest investment banks were summoned to an evening meeting by the US Treasury Secretary... See full summary »

Director: Michael Samuels
Writer: Craig Warner
Starring:
Corey Johnson as Richard 'Dick' Fuld (Lehman Brothers CEO)
James Cromwell as Henry 'Hank' Paulson (Secretary of Treasury)
James Bolam as Ken Lewis (Bank of America CEO)
Michael Landes as Zach
Ben Daniels as John Thain (Merrill Lynch CEO)
William Hope as Bart McDade (Lehman Brothers COO)
Michael Brandon as Jamie Dimon (JP Morgan Chase CEO)
Henry Goodman as John Mack (Morgan Stanley CEO)
Alex Jennings as Tim Geithner (Fed Reserve Bank of NY President)
Peter Polycarpou as Lloyd Blankfein (Goldman Sachs CEO)

Please contact me if you want this movie.

Synopsis: 'Inside Job' is the first film to provide a comprehensive analysis of the global financial crisis of 2008, which at a cost over $20 trillion, caused... 'Inside Job' is the first film to provide a comprehensive analysis of the global financial crisis of 2008, which at a cost over $20 trillion, caused millions of people to lose their jobs and homes in the worst recession since the Great Depression, and nearly resulted in a global financial collapse...
See full summary

Distributor:Sony Pictures Classics
Directed By: Charles Ferguson
Written By: Charles Ferguson, Chad Beck, Adam Bolt
Narrator: Matt Damon

The Last Days of Lehman Brothers (2009, BBC Movie)

2010-12-01T21:20:00.000-08:00

Please contact me if you want this movie.

Movie: The Last Days of Lehman Brothers (2009, BBC Movie)
The heads of Wall Street's biggest investment banks were summoned to an evening meeting by the US Treasury Secretary... See full summary »

Director: Michael Samuels
Writer: Craig Warner
Starring:

Corey Johnson as Richard 'Dick' Fuld (Lehman Brothers CEO)
James Cromwell as Henry 'Hank' Paulson (Secretary of Treasury)
James Bolam as Ken Lewis (Bank of America CEO)
Michael Landes as Zach
Ben Daniels as John Thain (Merrill Lynch CEO)
William Hope as Bart McDade (Lehman Brothers COO)
Michael Brandon as Jamie Dimon (JP Morgan Chase CEO)
Henry Goodman as John Mack (Morgan Stanley CEO)
Alex Jennings as Tim Geithner (Fed Reserve Bank of NY President)
Peter Polycarpou as Lloyd Blankfein (Goldman Sachs CEO)

Inside Job (2010, Sony Pictures) - a comprehensive analysis of the Global Financial Crisis of 2008

2010-12-01T18:44:00.000-08:00

Please contact me if you want this movie.

Synopsis: 'Inside Job' is the first film to provide a comprehensive analysis of the global financial crisis of 2008, which at a cost over $20 trillion, caused... 'Inside Job' is the first film to provide a comprehensive analysis of the global financial crisis of 2008, which at a cost over $20 trillion, caused millions of people to lose their jobs and homes in the worst recession since the Great Depression, and nearly resulted in a global financial collapse. Through exhaustive research and extensive interviews with key financial insiders, politicians, journalists, and academics, the film traces the rise of a rogue industry which has corrupted politics, regulation, and academia. It was made on location in the United States, Iceland, England, France, Singapore, and China.
(source)
Distributor:Sony Pictures Classics
Directed By: Charles Ferguson
Written By: Charles Ferguson, Chad Beck, Adam Bolt
Narrator: Matt Damon

Wrap Accounts

2010-02-18T15:39:00.000-08:00

source: www.sorted.org.nz

A wrap account is a combined administration and investment service. It’s a system, rather than a product, that provides access to a range of funds, shares, bonds and other investments.

If you have or are considering a more sophisticated investment programme, you might want to consider a wrap account. That said, if you are happy to handle your own investments and reporting, you may find it better to arrange your savings and investments so you don’t need a wrap account and therefore save money on fees.
Wrap account features

* There may be a custodian that holds your different investments on your behalf.
* You may decide where your money is invested or you may delegate that decision to your adviser.
* A cash account is often part of the service. This receives your dividends and interest payments and new investments. Your income tax might be paid from this account.
* You will often have access to wholesale funds that you could not usually invest in as a retail investor.
* There is usually a single set of fees that covers administration, investment, advice and other services, and an asset-based administration fee (for example, 1.5% of assets) to cover administration and the adviser’s income.
* You may have Internet access to your account. Sometimes this allows you to trade investments and work out your tax liabilities online, and include discounts on fund managers’ charges.
* You can use your wrap account to get a report on the performance of your investments. Having a consolidated account like this can be useful as you’ll usually get with an annual summary, which is easier to use when it comes to working out how much tax you have to pay.

Chat with financial professionals

2009-11-30T18:48:00.000-08:00

Portfolio fee analysis

2009-11-25T15:42:00.000-08:00

Personal Early Warning Signal

2009-11-19T15:05:00.000-08:00

Insurance Needs & Wealth Protection

2009-11-19T15:04:00.000-08:00

Scenario Analysis & Wealth Projection

2009-11-19T15:02:00.000-08:00

Asset Allocation

2009-11-19T15:01:00.003-08:00

Retirement Plan

2009-11-19T15:01:00.001-08:00

Home Plan

2009-11-19T15:00:00.002-08:00

Holiday Plan

2009-11-19T15:00:00.001-08:00

Education Plan

2009-11-19T14:59:00.001-08:00

Personal Accounting and Budgeting

2009-11-19T14:57:00.000-08:00

My Curriculum Vitae

2009-11-05T17:27:00.000-08:00

S&P 500 Interactive Chart

2009-11-02T18:20:00.000-08:00

View the full SPX chart at Wikinvest

Psychospiritual Aspect in Financial Planning

2009-11-01T23:42:00.000-08:00

Psychospiritual Financial Planning Flowchart >>>
(copyright by Jeff Liando)

Some people believe that money is energy that can give them some power to do anything what they want. They earn money and then spend it for something in some way. All their expenses may include mortgage instalments or rent payments, regular basic living costs, insurance premiums, regular savings, holiday costs and life style assets (car and furniture).

However, according to Maslow, at the highest level of needs hierarchy, people would reach the stage of transcendence. The stage when people are willing to help others finding self-fulfilment and realising their potential. This may be giving money to charity or providing "seed money" or "start-up fund" in venture capital world.

The psychospiritual aspect of financial planning inevitably takes place as clients deeply rely on their financial planners' care and expect too much from them. This is fairly seen as the impact of financial planners' effort to build a strong and ongoing relationship.

We define a psychospiritual aspect as a view in searching a client's psychospiritual issues in relation to valuation of a client's ongoing peace of mind. Ongoing peace of mind is a remarkable main outcome of the financial planning relationship, therefore this can be taken as a main goal in providing this service.

The goal of maintaining a client's ongoing peace of mind can be stated in objectives on which each of them is specified, measurable and possible to result an appropriate outcome in a period of time. All of the outcomes are simultaneously used to achieve the goal.

We propose concept, method and process of setting the objectives, appraising the psychospiritual aspect based on the money consciousness concept through a method of defining the key factors of prosperity and a process of qualitative valuation.

The objectives are specified by defining four key factors, i.e. safer, happier, richer and benevolent. These are measurable on the critical values determined with a client's concerns, and possible to achieve with a planner's recommendations with respect of the financial ratios as the outcomes.

All the outcomes are used to prejudge a client's psychospiritual aspect condition whether prosperous or scarce condition in the process of qualitative valuation.

Client Demo - Investment Portfolio Report

2009-10-28T23:01:00.000-07:00

Client Demo - Financial Intelligence Report

2009-10-28T18:55:00.000-07:00

1. Personal accounting and budgeting
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.

2. Comprehensive financial planning
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.

3. Scenario analysis model
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.

4. Critical value determinator
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.

5. Early warning system model
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.

Relevant finance and investment concepts
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.

Cashflow and debt management techniques
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam congue sollicitudin ipsum, quis tempus mi egestas sit amet. Vivamus ut nisl sed augue vulputate tincidunt. Sed et neque enim, id mollis elit. Ut blandit sapien eget nibh tempor et posuere justo molestie. Praesent vitae urna est, ut tempor velit. Suspendisse potenti. Maecenas et purus ac erat posuere vehicula eu vel lorem. Donec mattis tincidunt sapien vitae sodales. Aliquam tristique placerat urna elementum porta. Cras non pulvinar lorem. In vulputate auctor tellus, ut bibendum lacus pellentesque adipiscing. Nullam euismod, dolor eget tincidunt iaculis, nisl magna porttitor augue, non rutrum turpis magna faucibus quam. Suspendisse ac porttitor mauris.
Market news update

New Issues & Picks

Investment tips

Investment products researche

Chart analysis

Asset allocation techniques

Financial Dictionary

2009-10-27T23:57:00.000-07:00

Investment TV

2009-10-27T18:28:00.000-07:00

Fixed Interest Overview

2009-10-27T18:25:00.000-07:00

Market Overview

2009-10-27T18:24:00.000-07:00

S&P 500

Dow Jones Index

S&P/ASX 200

NZX50

International Equity Overview

2009-10-27T18:23:00.000-07:00

International Equity

NZ and Australia Equity Overview

2009-10-27T18:22:00.000-07:00

New Zealand Equity

Financial Calculators

2009-10-27T18:21:00.000-07:00

Psycho-spiritual Aspect Methodology

2009-10-27T18:19:00.000-07:00

Managed Fund Selector

2009-10-27T17:01:00.000-07:00

Thank You

2009-10-15T02:34:00.000-07:00

Thank you for your donation.

Konstruksi portfolio reksa dana Indonesia

2009-09-17T18:44:00.001-07:00

Cuma suatu usulan.

Suatu portfolio investasi dapat direalisasikan cukup dengan menggabungkan beberapa reksa dana ke dlm suatu portfolio setting yang dikonstruksikan sesuai dengan risk profile investor. Logikanya adalah reksa dana itu sendiri sudah merupakan suatu portfolio terdiri dari beberapa saham dan/atau obligasi.

Kelemahan reksa dana di Indonesia adalah pengkategoriannya yang masih umum sbb: reksa dana saham, fixed income, campuran, pasar uang dan indeks. Idealnya bagi investor di Indonesia, mereka paling tidak harus mampu membedakan reksa dana mana yang berada dlm konteks Indonesia, negara2 berkembang lainnya dan negara2 maju spt Australia, US, Jepang dan US. Pasar modal tiap negara atau regional pada kenyataannya memiliki tingkat return dan risiko yang berbeda. Dengan demikian, konstruksi portfolio bisa dibangun sesuai dengan profil risiko masing2 klien.

Risk profile seorang investor dapat dikategorikan sbb: defensive, conservative, balanced, growth dan aggressive. Sementara pembagian kelas asset dapat dipisahakan menjadi income asset dan growth asset. Income asset terdiri dari cash dan fixed income (obligasi). Growth asset terdiri dari: property dan shares (saham).

Berkaitan dengan profil risiko, biasanya orang muda memiliki risk profile growth dan aggressive dan orang tua cenderung balanced, conservative dan defensive. Rangka waktu (jangka pendek atau jangka panjang), toleransi return dan tujuan keuangan juga menentukan risk profile seseorang.

Ringkasan kategori asset dalam portfolio yang disusun berdasarkan tingkat return dan risiko adalah sbb:

Income asset:
1. cash: deposito US Dollar
2. cash: deposito Rupiah
3. reksa dana fixed income negara maju: Australia, US, Eropa, Japan, Singapore, HK
4. reksa dana fixed income Asia: China, Taiwan, Korea, Malaysia
5. reksa dana fixed income Indonesia

Growth asset:
6. reksa dana properti Indonesia
7. reksa dana saham negara maju: Australia, US, Eropa, Japan, Singapore, HK
8. reksa dana saham Asia: China, Taiwan Korea, Malaysia
9. reksa dana saham Indonesia

Reksa dana 9 menunjukkan investasi yang paling tinggi tingkat risikonya, tapi paling tinggi juga tingkat returnnya. Pembagian ini mungkin akan didukung para ekonom krn memang kenyataannya demikian. Investasi property di Indonesia mungkin bisa didebatkan lebih berisiko dari pada investasi saham negara maju.

Untuk penyelidikan lebih lanjut, silakan kunjungi website MSCI-Barra untuk melihat tingkat return per negara atau regional.

Usulan konstruksi portfolio

Usulan konstruksi portfolio ini dibuat berdasarkan pengalokasian strategis (strategic asset allocation) dan masih dapat dimodifikasi dalam pengalokasian teknis (technical asset allocation) sesuai dengan kondisi pasar modal domestik dan internasional serta kecenderungan kurs Rupiah terhadap mata uang asing.

Defensive Portfolio

Income asset: 90%
1. deposito USD = 5%
2. deposito IDR = 5%
3. RD fixed income negara maju = 40%
4. RD fixed income Asia = 20%
5. RD fixed income Indonesia = 20%

Growth asset: 10%
6. RD properti Indonesia = 5%
7. RD saham negara maju = 5%
8. RD saham Asia = 0%
9. RD saham Indonesia = 0%

Conservative Portfolio

Income asset: 70%
1. deposito USD = 3%
2. deposito IDR = 2%
3. RD fixed income negara maju = 25%
4. RD fixed income Asia = 20%
5. RD fixed income Indonesia = 20%

Growth asset: 30%
6. RD properti Indonesia = 10%
7. RD saham negara maju = 10%
8. RD saham Asia = 5%
9. RD saham Indonesia = 5%

Balanced Portfolio

Income asset: 50%
1. deposito USD = 0%
2. deposito IDR = 5%
3. RD fixed income negara maju = 15%
4. RD fixed income Asia = 15%
5. RD fixed income Indonesia = 15%

Growth asset: 50%
6. RD properti Indonesia = 10%
7. RD saham negara maju = 20%
8. RD saham Asia = 10%
9. RD saham Indonesia = 10%

Growth Portfolio

Income asset: 30%
1. deposito USD = 0%
2. deposito IDR = 5%
3. RD fixed income negara maju = 5%
4. RD fixed income Asia = 5%
5. RD fixed income Indonesia = 15%

Growth asset: 70%
6. RD properti Indonesia = 15%
7. RD saham negara maju = 15%
8. RD saham Asia = 15%
9. RD saham Indonesia = 25%

Aggressive Portfolio

Income asset: 10%
1. deposito USD = 0%
2. deposito IDR = 0%
3. RD fixed income negara maju = 0%
4. RD fixed income Asia = 5%
5. RD fixed income Indonesia = 5%

Growth asset: 90%
6. RD properti Indonesia = 15%
7. RD saham negara maju = 20%
8. RD saham Asia = 25%
9. RD saham Indonesia = 30%

Ketersediaan reksa dana macam ini

Mungkin ketersediaan reksa dana macam ini masih menjadi isu utama di bisnis reksa dana Indonesia. Namun, saya yakin peningkatan pengetahuan investasi kelas menengah Indonesia akan mendorong manajer investasi yang berdomisili di Indonesia untuk menciptakan produk reksa dana yang lebih kreatif dan menunjang pembentukan model portfolio di masa mendatang.

Daftar lengkap reksa dana Indonesia dapat ditemui di website Bapepam.

Update 1

Baru mendapat konfirmasi dari John D Item, CFA bahwa:

Industri reksadana saat ini hanya boleh investasi instrumen luar negeri maksimum 15% dari total portofolio. Oleh karena itu, penggunaan reksadana lokal untuk analisa alokasi aset domestik maupun internasional saat ini belum relevan.

Kalau begitu, kreatifitas manajer investasi lokal masih terbatas dan reksa dana asing (yang dikelola manajer investasi Singapore misalnya) mungkin masih belum boleh dijual di Indonesia.

Hal ini memberi kesan bahwa portfolio investasi reksa dana di Indonesia pada dasarnya aggressive, atau paling tidak growth atau balanced. Bagaimana nasib para orang tua dengan portfolio defensive atau conservative?

Update 2

Ada pertanyaan kritis yg bagus dari Bahar, Indonesian hedge fund manager yg berdomisili di Singapur, di blog-nya. Kenapa di blog-nya? Krn saya me-link post ini ke comment-nya. Fair enough :)

Pertanyaannya:

Angka2 yang muncul di ” konstruksi portfolio RD Indonesia” berdasarkan apa bung Anymatters? Some Markowitz based optimisation?

Jawaban saya:

thanks bung iHedge. keliatannya Prof Roger Ibbotson dr Morningstar yg mengembangkan model alokasi aset itu msh berkiblat ke teori Markowitz deh.

angka2 itu saya ambil dr model portfolio-nya Morningstar NZ yg didistribusikan ke fin adviser (maaf hasil risetnya tdk bisa dishare) dan saya modifikasi sendiri sesuai dgn situasi Indonesia. dimana komponen asset class-nya saya rate berdasarkan tingkat return indeks per negara/regional di website MSCI-Barra.

proporsi asset class antara income-growth menggunakan alokasi aset strategik (blm taktikal), yaitu defensive (90-10), conservative (70-30), balanced (50-50), growth (30-70) dan aggressive (30-70).

mengenai produk2 apa saja dlm komponen asset class tsb, saya belum bisa recommend. mungkin nanti kalo pulang ke Jkt, bisa buat ide usaha :)

tapi kalo ada waktu, saya mau riset produk2 RD yg di-archive di website Bapepam. kalo ada waktu.

mungkin Morningstar Singapore sdh watch RD Indonesia dan memiliki konstruksi model portfolio RD di Indonesia. cek aja, alamatnya 80 Raffles Place, Level 35, Unit 35-06, 048624 UOB Plaza 1, Singapore, +65 6530 6402.

Pascal for Investment Database

2008-06-18T00:22:00.000-07:00

Pascal coding is so useful for Investment database with SQL server.

Why? (sorry in Bahasa Indonesia)

FPC juga datang dengan bonus yang mantap..misalnya sebuah IDE (Integrated Development Environment) yang sangat mirip dengan IDE-nya TP7. Dan disamping seluruh keuntungan tersebut, FPC tersedia dalam berbagai platform system yang berbeda-beda. Seperti Win32,Linux, BSD,Sparc , dll. Dan berbicara soal keunggulan FPC sepertinya tidak akan pernah cukup,..maka saya berikan Anda satu lagi kompiler ini juga bisa melakukan pemrograman database dan berhubungan dengan SQL-SQL server hebat seperti like MySQL dan PostgreSQL,bahkan Interbase. Untuk membuat antarmuka GUI (banyak orang masih sangat menggilai GUI) maka FPC bisa dipadukan dengan widget software seperti GTK, Tcl/Tk, dan ncurses. Jadi..mengapa Free Pascal...mengapa tidak ?!!

source: freepascal.org //razmal djamal >>>

This the simple code to calculate portfolio gain and return retrieved from ForteCRM software.

//------------------------------------------------------------------------------

procedure appendTotal(Dataset: TDataset; fTotalStart, fTotalEnd, fTotalContribution, fTotalWithdrawn,
fTotalCashIncome: float;
style, reportCurrSymbol: string; PerformanceMethod : string;
PerformanceInflate : float; PerformanceAnnualise: boolean;
iDaysInPeriod : integer);

var

fNetOutFlow : float;
fNetInFlow, fPFolioReturn : float;
fReturn, fGain, fInflateRate, fInvestInflate : float;

begin

appendSubTotal(Dataset, fTotalStart, fTotalEnd, fTotalContribution, fTotalWithdrawn, fTotalCashIncome, style, reportCurrSymbol);
Dataset.edit;

// Calculate the return.

if (fTotalStart + fTotalContribution) <> 0 then

begin

if UpperCase(PerformanceMethod) = 'SIMPLE' then

begin
fReturn := (fTotalEnd + fTotalCashIncome + fTotalWithdrawn) / (fTotalStart + fTotalContribution);
end;

end;

// Dollar Gain

fGain := (fTotalEnd + fTotalWithdrawn) - (fTotalStart + fTotalContribution);

fInvestInflate := 0;

if iDaysInPeriod > 0 then

begin
fInflateRate := (PerformanceInflate/100) + 1;
fInvestInflate := Power(fInflateRate, 365/iDaysInPeriod);
fInvestInflate := (1 - fInvestInflate) * 100;
end

If PerformanceAnnualise then

begin
fReturn := Power(fReturn, 365/iDaysInPeriod);
end;

fReturn := ((fReturn - 1) * 100) - fInvestInflate;

end;

Next coupon date - IF THEN formula

2008-04-05T12:36:00.000-07:00

This was actually a simple problem in semi-annual coupon bond pricing when I was asked to determine the next coupon/interest date after the settlement date. I just needed to use my common sense to find the date from the maturity date.

If the maturity date is 15-Jul-08 and the coupon is paid semi-annually, then the coupon dates are 15-Jan and 15-Jul (6 month difference) ignoring the year. If the settlement date is 16-Oct-07, then the next date after 16-Oct-07 between 15-Jul and 15-Jan should be 15-Jan-08. It's easy!

However, Excel and Visual Basic never consider my common sense to work just like that. There is always a formula or function need to be developed. Surprisingly, using the arithmetical date formula, I came up with the longest if then formula I've ever made. That's crazy as I couldn't find the simpler one.

Finally, I found this bloody Excel formula:

=IF(AND(B3>DATE(YEAR(B3),MONTH(F3)+6,DAY(F3)),DATE(YEAR(B3),MONTH
(F3)+6,DAY(F3))>DATE(YEAR(B3),MONTH(F3),DAY(F3))),DATE(YEAR(B3)+1,
MONTH(F3),DAY(F3)),IF(AND(B3<DATE(YEAR(B3),MONTH(F3),DAY(F3)),
DATE(YEAR(B3),MONTH(F3),DAY(F3))<DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))),
DATE(YEAR(B3),MONTH(F3),DAY(F3)),DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))))

And this is the easy way of how you look at the formula:

IF(
AND(B3>DATE(YEAR(B3),MONTH(F3)+6,DAY(F3)),
DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))>DATE(YEAR(B3),MONTH(F3),DAY(F3))),

=> if Settlement Date > first Coupon Date > second Coupon Date

DATE(YEAR(B3)+1,MONTH(F3),DAY(F3)),
=> next coupon date

IF(
AND(B3<DATE(YEAR(B3),MONTH(F3),DAY(F3)),
DATE(YEAR(B3),MONTH(F3),DAY(F3))<DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))),

=> if Settlement Date < first Coupon Date < second Coupon Date

DATE(YEAR(B3),MONTH(F3),DAY(F3)),
=> next coupon date

Else,
=> if first Coupon Date < Settlement Date < second Coupon Date

DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))))
=> next coupon date

Bear in mind that 15-Jul-07 is the first coupon date and 15-Jan-08 is the second coupon date and the settlement date 16-Oct-07 is between both dates, therefore the next coupon date is DATE(YEAR(B3),MONTH(F3)+6,DAY(F3)), which is on 15-Jan-08.

If you want to be spoiled by Excel, there is actually a formula already provided: COUPNCD(settlement,maturity,frequency,basis).
I just wanted to be a dumb person who had been trying to figure out this IF THEN formula.

CAPM return and cumulative data matter

2008-04-04T15:58:00.000-07:00

This story began when I wrote about benchmark issue in CAPM. Then, it continued with an Excel model for optimisation of shares or funds portfolio. The main problem defined in the model is to select shares and find the optimised asset allocation in a portfolio with three scenarios:

1. Assumed CAPM can't be applied and historical data is important therefore historical mean (average) return and standard deviation are used to simulate random return normal distribution and calculate Variance Covariance matrix.

2. Assumed CAPM can't be applied and cumulative data is important therefore projected mean (average) return and standard deviation are used to simulate random return normal distribution and calculate Variance Covariance matrix.

3. Assume CAPM can be applied and cumulative data is important therefore projected CAPM return and standard deviation are used to simulate random return normal distribution and calculate Variance Covariance matrix.

Starting by processing 13 NZ shares in the optimisation model with the three scenarios then applying the result of the share selection and the asset allocation into three passive portofolios, after one month holding period I found the return as follows:

Scenario 1 = -8.98%

Scenario 2 = -2.86%

Scenario 3 = +6.06%

I will update after 12 months.

The Parables of Investment

2008-03-31T23:26:00.000-07:00

From an interesting paper:
The parables, premium puzzles, and the CAPM
by Hong-Jen Lin and David C. VanderLinden
(download)
image source

Rate of return, risk free deposit, and equity premium

In Matthew 25: 14-30 (the Parable of the Talents), a master has given his three servants five talents, two talents and one talent of money, respectively. The first earns five more talents, the second earns two more talents, and the third earns nothing since he played it safe and buried all the money in the ground. The master commends the first and the second ‘‘Well done, good and faithful servants’’ (verses 21 and 25) but excoriates the third as a ‘‘wicked, lazy servant’’ (v. 26).

A key principle to this parable is risk-taking. The first two servants were willing to place at risk the endowment given to them, but the third servant was ‘‘afraid.’’ As interpreted by most commentators, the parable is an exhortation to take risks in using one’s gifts for the kingdom of God. Jesus indicates a reward for those willing to take risk, and punishment for the one who was too fearful (risk-averse).

Perhaps, we can also infer how the master evaluates performance of the investments by three servants. Why did the first and second servants receive the same praise and rewards? Both took risk and, while the absolute value of income differed, they both earned the same rate of return. In this case (but not for the Parable of the Ten Minas), the master gave them the same rewards. Proverbs 3:14 (in the Old Testament) also alludes to return: ‘‘for she (wisdom) is more profitable than silver and yields better returns than gold.’’ The concept of return appears in both the Old Testament and the New Testament.

Regarding the third servant, the master criticized, ‘‘you should have put my money on deposit with the bankers, so that I would have received it back with interest’’ (v. 27). Given the comment, we can surmise that the risk-free deposit market existed in Jesus’ era. The third servant is despised because he has earned zero return in investment, under the riskfree rate. Therefore, one can interpret the verse to mean that the master measures performance not just based on the rate of return but on the return rate in excess of the risk-free interest rate. This conforms to the formula of the Sharpe-Lintner CAPM:

ri – rf = Bi ( rm – rf )

where ri is the rate of return for asset i, rf is the risk-free deposit interest rate, and rm is the market return. Bi denotes the systematic risk. Both sides of equation are returns in excess of the risk-free deposit rate. In other words, the measurement of performance of investments is based on the rate of return in excess of the risk-free deposit rate.

A similar concept is found in the Parable of the Ten Minas (Luke 19: 11-23). However, in this parable, each servant is given one mina. The first servant invests it and returns ten minas to his master; the second invest the mina to return five minas; a third servant hides his mina in the ground because he is ‘‘afraid.’’ In the Parable of Ten Minas, the master rewarded the faithful servants by giving them authorities to rule cities; the first over ten cities, the second over five. That is, the ‘‘payoff’’ in the Parable of Ten Minas is much more than that in the Parable of Talents (as is the return), but it is proportional to the returns of each servant. Again, the criticism of the third servant’s inaction is still sharp.

The scripture seems to encourage investors to earn as much as possible. The master criticized the third servant because of his laziness. The laziness is simply caused by his fear of taking risk (v. 25). Therefore, according to the Parable of Talents, Christians are motivated to invest and accumulate wealth to glorify God. It seems that Jesus appreciates people who dare to take risk and grasp profitable opportunities. This is consistent with Solomon’s exhortation to enter into risky trade and ‘‘cast your bread upon the waters, for after many days you will find it again’’ (Ecclesiastes 11: 1). Consistent with Weber’s propositions, it may be that the financial markets have been influenced at least indirectly by these principles. That is, religious teachings can change people’s mindset and then motivate them to invest and to fully utilize the financial resources they have. As a result, financial markets are formed, which increases general wealth. The prosperity of financial markets further inspires researchers in forming financial theories such as the CAPM. A possible relationship among the biblical teachings, financial markets, and financial theories is illustrated in Figure 1.

Avariant of the CAPM is the consumption CAPM, in which investors hold wealth to allow consumption. Thus, the expected return on an asset should be related to how the asset’s returns vary with consumption. Mehra and Prescott (1985) found that the historical equity risk premium (the rate of return on stocks over and above the return on a risk-free rate) was too high for ‘‘reasonable’’ levels of risk aversion. Termed the ‘‘equity premium puzzle,’’ this finding has stirred numerous unsuccessful attempts to explain the puzzle. One possibility is that investors have diverse preferences or beliefs (rather than those of a representative individual as usually assumed). In both the Parable of the Talents and the Ten Minas, one investor either was too risk-averse to invest or had different payoff expectations such that he was unwilling to invest. Perhaps these parables offer an avenue of explanation for the equity premium puzzle.

According to Benartzi and Thaler (1995), when investors are myopic-loss-averse, the return on equity must be large enough to attract them to buy stocks. We can also observe from the Parable of Talents that the third servant is myopic-loss-averse. In other words, the third servant ignores upcoming rewards he might earn. His fear of loss is much more than the joy of gain. Therefore, the equity premium is a must to draw investors like him to invest in risky assets.

One final note on equities is in order. Because the master strongly motivates the servants to invest by authorizing them to rule over cities (i.e. have ownership of productive assets), this kind of teaching could be viewed as encouraging Christian investors to hold equities, as the light shed by Weber (1930).

In the field of corporate finance, equity holders may pass up profitable (i.e. positive net present value) investments when debt holders may get most of the benefits of the projects. That is, equity holders may tend to under invest when they realize that their gains in the new projects are limited (see Grinblatt and Titman, 2002, p. 563). In the Parable of Talents, similarly, the third servant may think that the master will get most of benefits so he is reluctant to invest. Actually, he really under invests: invests into the ground!

To sum up, conceivably these parables could help to explain the equity premium puzzle in asset pricing and the under investment problem in corporate finance. Teachings based on the parables may shape the behavior of people especially when these biblical teachings have already become a part of the system in a country.

Optimising Portolio of ING PPS Funds

2008-03-31T01:14:00.000-07:00

What is ING PPS (Private Portfolio Service) Funds?

Private Portfolio Service is a master trust administered by ING. It has been developed as both a series of unit trusts and a superannuation fund, representing a broad range of asset classes and fund managers.

Private Portfolio Service provides investors with the opportunity to diversify their portfolio, without the need to rebalance different investments across a number of fund managers and products.

Someone definitely has to do a quantitative research on these funds, right? What's the purpose? To find the best and optimised alocation of a portfolio of one's retirement plan or any investment plan.

Based on their daily/monthly historical prices, I calculated the daily/monthly log return of each PPS fund, Ln(P2) - Ln(P1) to show the continously compounded return.

I then calculated the mean and standard deviation of each fund and built the covariance matrix. Next, I calculated the portfolio mean and standard deviation.

Finally, using solver, the optimised allocation is found by assuming risk free rate 7%. Contact me to get the optimised allocation and the spreadsheet, if you're curious or just checking with your own calculation.

Please confirm if these annualised monthly means and standard deviations are close with your calculation for data ending 21 Jan 08.

PPS	mean	stdev
AggBal	7%	0.087
AsianEq	10%	0.159
AusEq	7%	0.126
Bal	5%	0.063
DivTrad	3%	0.041
EuroEq	6%	0.138
GlobOpp	-1%	0.176
InterEq	5%	0.131
InterFix	3%	0.029
Mortg	1%	0.018
NZEq	5%	0.098
NZFixed	0%	0.021
PlatInter	1%	0.101
Prop	6%	0.080

Some notes

Sorry, I didn't use CAPM return in this calculation. No market index benchmark therefore no beta and risk premium to consult. :-)

This is an optimisation of a portfolio of optimised funds as a managed fund is a portfolio which is optimised regularly by its fund managers.

Putting into practice, some clients don't believe in this simulation. This is because the simulation is based on the daily prices and they don't concern this kind of time horizon.

For getting the best point of view, in my opinion, it is best simulated from monthly prices, not daily prices.

Finance Concepts

2008-03-26T18:02:00.001-07:00

Charts, News and Quotes

2008-03-26T17:58:00.000-07:00

Yahoo! Finance
Quote for NZT

Excel and Macro Tips

2008-03-26T17:55:00.000-07:00

John Walkenbach's Excel Tips

user tips

developer tips

Allen Wyatt's ExcelTips

Daily Dose of Excel

Top 10 Indonesian Managed Funds (Reksa Dana)

2008-03-13T23:30:00.000-07:00

"Reksa Dana or Reksadana" is an Indonesian term for managed funds. The total number of managed funds in Indonesia is 475. It's amazing! And, the highest real return is around 70%!! Seeing the high return of Indonesia market, who cares about standard deviation...

Fixed Income	168
Shares	58
Mixed	111
Money market	32
Protected	103
Index	1
Listed	2
TOTAL	475

TOP Performers as of 29 March 2008 (in %)

SHARES funds	(Rp)	30 days	1 year	1 year real
Makinta Mantap	3.713,76	-16.76	66.27	62.19
Fortis Ekuitas	9.005,18	-12.42	57.97	51.83
Pratama Saham	2.118,94	-13.2	52.08	50.57
Fortis Infrastruktur Plus	1.557,37	-11.64	54.2	49.29
Optima Saham	1.578,07	-8.16	53.57	48.3
Bahana Dana Prima	8.163,82	-12.05	51.93	47.44
Schroder Dana Istimewa	2.677,91	-8.53	46.77	46.04
Danareksa Mawar	4.715,90	-11.8	47.52	45.33
Mandiri Investa Atraktif	2.757,80	-13.65	46.86	45.21
Reksa Dana Dana Ekuitas Prima	2.320,73	-11.58	53.53	43.7

MIXED funds	(Rp)	30 days	1 year	1 year real
Prospera Balance	2.788,39	-11.37	59.5	53.25
Bahana Dana Infrastruktur	4.997,78	-11.91	56.33	51.71
Fortis Pesona	11.610,89	-11.24	50.22	46.87
TRIM Syariah Berimbang	1.441,77	-9.95	43.37	43.37
Mandiri Investa Syariah Berimbang	2.136,52	-4.25	44.52	41.65
Reksadana Mega Dana Syariah	1.533,92	-6.92	42.78	39.95
Star Balanced	1.778,10	-6.06	42.03	39.9
Danareksa Anggrek Fleksibel	1.895,27	-8.04	44.53	39.62
Schroder Dana Prestasi	12.447,40	-10.69	41.64	38.49
Mega Dana Campuran	1.538,57	-8.55	40.61	37.15

Which Market do you prefer?

2008-03-13T23:29:00.000-07:00

"IHSG" is the Jakarta Market. Isn't it way too high? The mean and also the stdev.

"NZXall" is the the good one I think. It has a considerably high mean and low stdev.

US Recessions and the MSCI World Index

2008-03-13T23:28:00.000-07:00

Recent market talks are around the fears of recession in the US. Although finance is often considered as sort of an exact science, it however cannot avoid the influence of the state of anxiousness by market players and investors. That is why a behavioral finance matters and needs to be neutralised by showing the real facts in the financial markets and a good sense to answer if a US recession is really going to bring down the entire world financial market in the long term.

Periods of US recession and the definition

The state of US recession period is officially announced by the National Bureau of Economic Research (NBER). Since 1970 there have been 5 states of recession in the US, as follows :
• November 1973 to March 1975
• January 1980 to July 1980
• July 1981 to November 1982
• July 1990 to March 1991
• March 2001 to November 2001

The NBER defines that
“A recession is a significant decline in economic activity spread across the economy, lasting more than a few months, normally visible in real GDP, real income, employment, industrial production, and wholesale-retail sales. A recession begins just after the economy reaches a peak of activity and ends as the economy reaches its trough. Between trough and peak, the economy is in an expansion. Expansion is the normal state of the economy; most recessions are brief and they have been rare in recent decades.”

The informal recession definition is a decline in the Gross Domestic Product (GDP) for two or more consecutive quarters.

Relationship between recession and stock market decline

There is a confusing logic in relationship between recession and stock market decline: does a recession cause a bearish market or the other way around? The exact answer is that the recession comes first and the bearish market after. In a recession, analysts would see the value of companies is declining therefore the stock prices may fall. However, some classic strategy suggests that investment in the consumables sector may be a recession-proof investment since people still buy those products, recession or not.

If the recession comes first and the bearish market after, how can we explain the states of US recession in July 1990 to March 1991 that came 3 years after the 87 crash? Many analysts argue that the Crash was mainly caused by a programmed trading system and psychology factor that beyond the economic factor of an exact finance. The “quants” (quantitative traders) who developed a computerised trading system that automatically created a sequence of selling positions might had been to blame, or it could had been the investors who were psychology weak and then fell into selling positions.

How strong is US recession influence on global market?

Another question to answer: does US recession strongly influence the global financial market, particularly Australia and NZ markets? It may not be. Asian countries like Japan, China, South Korea and others have been the dominant trading partners for Australia and therefore NZ for the last decade competing trading flows with the US. This recent economic decoupling theory may explain how US recession may be absorbed by growth in the competing countries (Asia) as maintaining trading flows with the third trading counterparts (Australia/NZ).

Some simple examples: the US recession may not affect New Zealanders to stop buying stuff from the Warehouse stores, but Americans to visit their Walmart stores. New Zealanders are not going to disconnect their Telecom line at home because of the US recession.

The recent subprime trouble in the US economy may be likely to influence ASX and NZX in terms of market psychology rather than concluding a good excuse and common sense that Australian and NZ companies should decline in value because of it. The fundamental assessment of companies’ value seems to be flawed in the short term.

Unfortunately, the recent January big drop in Asian and European markets is however breaking the belief of the decoupling theory. Investors and market players are then more aware of the true impact of US recession on the global financial market.

Market randomness over recession period

It seems some finance and economics theories try to explain and justify the prediction of world’s share market trend in the future. However, contradictions, unpleasant facts and non-economic factors may affect one’s prediction and there is only one underlying theory that can explain all this, i.e. randomness and stochastic.

Therefore, it is important to show some historical fact to see the ex post impact of US recession to the world financial market by taking a closer look at the historical data of popular benchmark of the world financial market, the MSCI World Index. The goal is to conclude that randomness in the share markets may be likely to drive the prices up in the long term regardless recession.

The conclusion of this can be easily understood by paying attention to the following chart, which show the period of US recession and the MSCI World Index. The fact is, in the time frames of 10-15 years the US recessions seems to have been driving the ex post movement of the world share market up. Therefore, recession is not a measure of uncertainty in stock market. Or, is it just because the market randomness factor always win over the recession factor?

1977 to 1987: two recessions and 280% return

A decade before the stock market crash of 1988, and despite the two recession periods were defined in 1980 and 1982, the MSCI had been dramatically increased by 280% return.

1987 to 1997: one recession and 135% return

After all, the MSCI index still increased by 135% despite the 1991 US recession was occurred.

1997 to 2007: one recession and 92% return

Regardless the Technology Crash 2000-2002 and the “War on Terror”, and despite the 2002 US recession, the MSCI index still increased by 92%.

The best example of the MSCI Index Fund managed in NZ is AMP Investments' World Index Fund (WiNZ)

Glossary of UK Pension terms

2008-02-20T23:10:00.000-08:00

Annuity
If you have any form of personal or money purchase pension you have to buy an annuity before age 75. An annuity is an income paid to you for life by an insurance company in return for your pension pot. Once you have bought an annuity you cannot usually change your mind and switch to a different one at a later stage. Nor can you get your money back if you die the day after buying one because annuities work by a system of cross subsidy - those who die early subsidise those who live to a ripe old age. The advantage is that they will pay you an income no matter how long you live.

Approved New Zealand Superannuation Fund
Registered superannuation schemes that are subject to the New Zealand Superannuation Schemes Act 1989 and Securities Act 1978.

AVC (Additional voluntary contributions scheme)
You can supplement your pension contributions to build up an even larger retirement fund. AVC's attract tax relief on the premiums, but the final benefits are taxed as income. AVC's are better for basic rate taxpayers and Peps are better for higher rate taxpayers as a way of supplementing pension income. Employees are allowed to save up to 15% of your taxable earnings into a pension plan. When you retire the company is not allowed to pay out a cash lump sum.

Final salary or defined benefit schemes
This gives the employee a fraction of their salary on retirement for each year they have worked.

HMRC
HM Revenue & Customs www.hmrc.gov.uk

Investment trust pensions
These are pooled equity investments allowing you to pay pension contributions and enjoy the tax advantages.

ISA (Individual savings accounts)
Individual Savings Accounts (ISAs) were introduced on 6th April 1999 which replaced PEPs and TESSAs. ISAs are not an investment in their own right. They are a tax-free wrapper in which you can shelter investments. People over the age of 18 living in the UK can invest a maximum of £7,000 per year in each tax year. 16 and 17 year olds can invest up to £3,000 in a mini cash ISA.

Investment may be made in two components: equities and cash. There are strict limits on how much you can put in each component, and the limits depend in part on whether you use a 'maxi ISA' or a number of mini ISAs.
Until 5th April 2004 ISAs benefited from a 10% tax credit on UK equities. Stock and share investments which can be held in an ISA include unit trusts, open ended investment companies (OEICs), investment trusts, ordinary shares, preference shares and fixed interest corporate bonds.

PEPs in existence at 6th April 1999 may continue to be held outside an ISA with the same tax advantages. TESSAs in existence at 6th April 1999 are allowed to run their full five year term.

Income from ISA investments is tax free and you don't have to report it on your tax return. Capital gains are also exempt from CGT.

Lifetime Allowance
A standard lifetime allowance is the maximum amount of pension savings that can benefit from tax relief. This figure rises over time and the proposed amounts are as follows:
2007 - £1.6 million
2008 - £1.65 million
2009 - £1.75 million
2010 - £1.8 million

The standard lifetime allowance is based on the approximate amount of money that would be needed to purchase a pension equal to the maximum HM Revenue & Customs (HMRC) would permit under the tax regime. Funds in excess of the lifetime allowance are felt to have benefited the individual unduly from pension scheme tax advantages and therefore a tax charge is made. Funds taken in excess of this will likely be taxed at 55%.

Money-Purchase or defined benefit schemes
This is where the employee and employer have contributed a set percentage of the employee's salary into the scheme. At retirement an annuity is paid out.

PEPS (Personal Equity Plans)
It is no longer possible to start a new PEP or add to an older one. Personal Equity Plans (PEPS) are investments that are totally tax exempt, both on the capital and income side.

PIE (Portfolio Investment Entity)
The QROPS funds we recommend for your UK Pension Transfer are PIE approved investments. From October 2007 taxation of New Zealand managed funds was changed to PIE. Capital gains on New Zealand and Australian (some exceptions) equities are exempt and income is taxed at the investors marginal tax rate. From April 2008 maximum tax is 30% even for a 39% tax payer. International investments are taxed under the Fair Dividend Rate method (FDR) tax at the investors marginal tax rate on the opening value plus 5% growth.

QROPS (Qualifying Recognised Overseas Pension Scheme)
HMRC require any pension transfers to go into a QROPS registered scheme. If this is done incorrectly you will be subject to a 55% withdrawal tax.

SIPPS (Self-invested personal pensions)
SIPPS offer greater flexibility than ordinary personal and occupational pensions. You can have many types of investment in them, including British and foreign stocks, unit trusts, investment trusts, managed life funds, unit linked funds and commercial property. They have the same tax relief as ordinary personal pensions.

SERPS (State Earnings Related Pension Schemes)
Part of the National Insurance contributions goes towards your SERP which is paid out on top of your state pension when you retire.

TESSA ( Tax Exempt Special Savings Account)
These lasted for five years and you were allowed to invest up to £9,000 over the life of each account. You got all your interest tax-free and once your five years were up you could either take your money or put your money into a new TESSA account (and continue to receive tax-free interest). The last day you could open one was 5 April 1999, so there aren't any TESSAs in existence any more. But you were allowed to roll a TESSA that matured between 6 April 1999 and 5 April 2004 into what is known as a TESSA-Only ISA (or TOISA for short) so that you could continue to receive tax-free interest.

FAQ: UK Pension Transfer

2008-02-20T23:07:00.000-08:00

For an explanation of terms frequently used please refer to the Glossary page.

What criteria is required to transfer my UK Pension?
You have to have permanently emigrated to New Zealand with no intentions of returning to the U.K. You must have either have been granted permanent residency or have submitted an application.
Should I transfer my UK pension fund to New Zealand?
For many UK ex patriots your pension fund will be the second biggest "investment" after your home.
Refer to "Reasons to transfer my UK Pension".
Do I have to have a super scheme in NZ to transfer my UK pension into?
Yes. If you decide to transfer your UK pension funds, UK regulations require that they must be transferred to an approved or registered superannuation scheme in New Zealand.
Should I transfer my UK pension fund to NZ, even if I think I may return to the UK to retire?
No. Once you have made the transfer to a NZ pension you cannot transfer back to your UK pension plan as if nothing had happened, you have given up all your rights to your UK pension.
Who handles my money and how safe is it? All transfers are paid directly by your UK Pension fund into the New Zealand HMRC QROPS Approved superannuation fund. We do not use a trust account or handle your money.
How long does it take to transfer my UK Pension?
Each pension transfer is different as each individual has different circumstances. The rule of thumb is that a normal transfer will take between 3 to 4 months (the shortest 6 weeks and the longest 12 months). Generally the delay is the UK pension provider arranging transfer papers and confirming transfer figures.
What are the main benefits to transferring my UK pension to New Zealand?
- Enables you to keep track of your pension plan and gain more control of your funds without affecting their earning power. You won't need to be concerned whether the fund is merging, closing or going out of existence.
- You no longer need worry about exchange rate fluctuations affecting your pension payouts.
- You will not be paying bank fees for each transfer (may be as a high as £18 per transfer)
- You will have more information and control on the companies holding your retirement savings.
- Easier to access your money in retirement.
- If you die with a UK pension scheme your spouse can get up to 2/3 of the pension you would have received. If you both die your pension dies with you, however, If you both die leaving qualifying dependent children , your UK pension could continue for as long as you fulfill the schemes eligibility criteria. With New Zealand superannuation plans all of your remaining investment becomes part of your estate and is passed on to your children, heirs.
What are the tax issues that need to be considered?
- Under the new FDR rules introduced in New Zealand in April 2007 your UK pension is exempt tax.
- If you retain your UK pension and it pays a regular benefit this is deemed income and you will need to pay tax on it in New Zealand.
- If your funds are transferred to a NZ approved superannuation plan under current legislation you are not taxed when you withdraw funds.
Can I get my UK pension paid directly into my bank account?
No, it is a requirement of the UK legislation that the money can only be paid into an approved New Zealand superannuation plan.
Do you charge a fee for your pension transfer service?
Emphatically YES, because you need impartial advice on such a complex and important issue. If it is in your best interests to retain the status quo we want to be able to say so, but receive remuneration for the time and experience involved in assisting you to reach that decision. Should you ultimately transfer your benefits to New Zealand we will, at your request offset our charges against any initial commission received, with any excess being reinvested. Or reinvest all the initial commission and charge you separately. We do not charge a fee and take initial commission. Contact Alison Renfrew at Alison@LYFORDS.co.nz .
Why don't I just transfer the pension myself?
You can transfer the pension yourself, but the process is complex, and can be very frustrating and confusing. Do you have the necessary understanding of your actions and how they might impact on your future financial security? If you get it wrong it could cost you thousands of pounds. Our UK Pension Transfer service will save you time, money and stress.
I have already started to draw income from my UK pension can I still transfer the lump sum?
No, once your pension is being paid out as regular income you can no longer transfer what would have been a lump sum.
Will I be eligible for New Zealand Government superannuation payments?
To be eligible for New Zealand Superannuation you need to be aged 65 or over and a legal resident of New Zealand, having lived here for ten years since age 20. Five of those years have to be since you turned age 50. Contact Work and Income Support on 0800 552 002 or at www.winz.govt.nz. For current after tax rates of New Zealand Superannuation click here.
Should I transfer my UK pension to a NZ Superannuation plan before I leave the UK?
No, you need to be a permanent resident in New Zealand before your UK pension plan can be transferred. We recommend waiting until you are living and working in New Zealand before you make any sort of decision on this. At present entitlement to New Zealand superannuation is not asset tested. The NZ Government will off-set pension income you are paid by the UK Government against your NZ super entitlement. How much would I get if I qualify for New Zealand superannuation?
Can I withdraw cash from my pension fund once the transfer is complete?
Yes, but this is dependent on restrictions imposed by your UK scheme. It may be possible to take up to 60% in cash, but sometimes the transferring scheme can insist that the whole amount is 'locked in'. This is an area often abused by some New Zealand advisers and getting it wrong could result in penalties being applied. Our recommendation is that your UK pension money was saved for your long term retirement savings so keep to this plan and put it aside for your retirement savings. Besides if the UK Government gets to hear that this facility is being abused it may rescind the regulations permitting it!
UK Pension Simplification Rules 2006 - How will they affect your Pension Transfer?
On the 6th April 2006 the new "pension simplification" regulations came into effect in the UK.
Under these rules every overseas pension fund that wants to accept transfers from the UK must be approved as a "Qualifying Recognised Overseas Pension Scheme" (or QROPS). All QROPS will have to report back to HMRC any payment made to a member in respect of the amount that was transferred from the UK. This will include the date, amount and "nature of the benefit" and the current address of the member.

Note: HMRC will apply a 40% tax on the transfer value if the UK pension is transferred to a non QROPS.

The new regulations state that the earliest retirement age (the earliest age at which funds can be withdrawn) is to rise to 55 years. In addition the maximum withdrawal in the first year will be limited to 25% of the pension without incurring any tax liability. Anything above this will incur a tax liability of 40%. To be a QROP reporting of all withdrawals is required to be provided to the UK authorities. Additional contributions and/or investment growth are not subject to the UK tax penalties.

Please also refer to the "7 traps of transferring your UK Pension".

UK Pension Transfer Traps

2008-02-20T23:02:00.000-08:00

The 7 Traps to Transferring Your UK Pension Funds

There are very good reasons for transferring your UK Pension to New Zealand. But you will see after reading the section "Comparison between NZ and UK Pensions" that there are some differences.

When transferring your UK pension there are some simple Transfer Traps to avoid.

Pension Transfer Trap One

From 1 April 2006 pensions from the UK must be transferred into a Qualifying Recognised Overseas Pension Scheme (QROPS). If you transfer your pension into a non QROPS scheme you will have to pay UK withdrawal tax of up to 55%. We transfer your UK Pension into HMRC approved QROPS funds.

Pension Transfer Trap Two

If you withdraw money from a QROPS before 6 years are up you may be up for an unauthorised payment charge. This is a UK Tax charge and can be up to 55% of the amount withdrawn. QROPS approved New Zealand superannuation fund managers do not want to loose their QROPS approval status. One of the requirements to continue to be approved is that the NZ scheme must notify the UK authorities of any withdrawals.

There are some exemptions to this which you may qualify for, we can discuss this with you.

Pension Transfer Trap Three

Once you start drawing down on your pension fund in the UK it cannot be transferred to New Zealand. You need to be a permanent resident of New Zealand before you can transfer your UK pension and you must not have started to draw down.

Pension Transfer Trap Four

Delaying your decision because of the exchange rate. If you have concerns about the exchange rate then discuss this with us. We can give you a consensus of the 18 month outlook for the UK/NZ exchange rate. There are many factors impacting the exchange rate. If you are really concerned we can transfer your funds and retain them in UK pounds until you instruct us to transfer into NZ dollars.

Pension Transfer Trap Five

The Savings Trap. After transferring your UK Pension to New Zealand you may want to continue saving. If you save into the scheme you used for the transfer, and you later decide to make a withdrawal within six years, any withdrawal will be treated as withdrawing the UK portion first. You could be liable for the 55% tax charge on the amount withdrawn. Solution save into a separate scheme.

Pension Transfer Trap Six

Being too Conservative. If you are retired we recommend a "balanced" risk/return profile. This is a 50:50 mix of growth (shares, property and future funds) and income (cash, fixed interest). Even at 65 you probably have another 25 years of investing. You need to make sure your investments and retirement income is inflation hedged. If you are younger than 55 a "growth" risk/return profile may be more suitable. We will discuss this with you.

Pension Transfer Trap Seven

Not reviewing your investments. We believe its important to keep track of your investments and review asset allocations to ensure your risk/return profile stays within its limits. We will provide six monthly reports and an annual review.

Taxation Issues UK Pension Transfers

2008-02-20T22:58:00.000-08:00

Taxation Issues UK Pension Transfers to NZ

Under current tax legislation in New Zealand (NZ) all earnings from Superannuation funds are taxed at 33%. All benefits/withdrawals are tax free.

Generally there is no tax payable when a UK pension fund is transferred into a New Zealand approved superannuation fund - refer to FIF exemptions below.

If your UK pension fund is employment or self employment related and you only made contributions to it before you became a resident of New Zealand, then you will be exempt from Foreign Investment Fund (FIF) Regulations.

If you acquired an interest in a UK pension fund which was NOT employment or self employment related before you become a resident in New Zealand you will be exempt from the FIF regime for the rest of the income year in which you first become resident, and for the next three income years.

After this exemption period has expired, you are then required to declare your interest in your UK pension fund to the New Zealand Inland Revenue. Income tax will then be levied on any gains the fund makes each year.

There is a possibility that should you leave your pension fund in the UK and at retirement take the Tax Free Cash sum, this may be subject to tax in NZ, even though you have left your funds in the UK. New Zealander's are taxed on their world-wide income.

So the tax advantages to transfer are:

You simplify your tax calculations
You remove the tax and exchange rate uncertainties
You pay no tax on the proceeds from the New Zealand superannuation plan.

UK Pension Simplification Rules 2006 - How will they affect your Pension Transfer?

On the 6th April 2006 the new "pension simplification" regulations came into effect in the UK.

Under these rules every overseas pension fund that wants to accept transfers from the UK must be approved as a "Qualifying Recognised Overseas Pension Scheme" (or QROPS). All QROPS will have to report back to HMRC any payment made to a member in respect of the amount that was transferred from the UK. This will include the date, amount and "nature of the benefit" and the current address of the member.

Note: HMRC will apply a 40% tax on the transfer value if the UK pension is transferred to a non QROPS.

The new regulations state that the earliest retirement age (the earliest age at which funds can be withdrawn) is to rise to 55 years. In addition the maximum withdrawal in the first year will be limited to 25% of the pension without incurring any tax liability. Anything above this will incur a tax liability of 40%. To be a QROPS reporting of all withdrawals is required to be provided to the UK authorities. Additional contributions and/or investment growth are not subject to the UK tax penalties.

NZ Taxation Rule Changes April 2006

After 1 April 2006 new migrants and returning New Zealanders who have not been tax-resident for at least ten years will be exempted from tax for four years on foreign income such as dividends, interest, royalties and rental income.

The ten year requirement is designed to ensure that New Zealand residents do not leave the country just to become eligible for the exemption.

The changes are part of the Taxation (Depreciation, Payment Dates Alignment, FBT and Miscellaneous Provisions) Bill.

Taxation Rules

Taxation Changes NZ: April 2007

Before Immigrating to NZ

2008-02-20T22:55:00.000-08:00

Here are some suggestions if you haven't already immigrated.

Set up a Family Trust before you immigrate
In the UK for the 2007/2008 tax year, the Inheritance Tax Rate is 0% on the first £300,000 (the "nil-rate band), and 40% on the rest of the value, at death, of an individual's tax estate. The nil rate band rises annually; tax is only payable on the value of an estate above the nil rate band. In the 2007 budget report the Chancellor announced that the nil rate band is to rise to £350,000 by 2010. This is to take into account the sharp rise in house prices in the United Kingdom over the previous few years.

In New Zealand individuals are taxed on Gifts over $27,000 per year, but there are no death duties. If you set up a Family Trust after you immigrate to New Zealand it is a slow process to transfer your assets across and forgive the debt.

Contact LYFORDS and we can put you in contact with an Independent New Zealand Trustee company experienced in setting up Family Trusts and what Estate Planning considerations you should take before you immigrate to New Zealand.

For a description of how Discretionary Family Trusts could benefit you, please refer to our main web site www.lyfords.co.nz.

Schooling
Education Review Office - the Government department which reports publicly on the quality of education in all New Zealand schools and early childhood centres.

Estate Planning
In the UK an Enduring Power of Attorney (EPA) will cost around £80-700 and it will have to be registered through the courts. This is not the case in New Zealand and an EPA costs approximately $80.

You should consider who will be the Guardians of your children, Executors of your estate in New Zealand.

We can arranged for a reputable Trustee company to set up your, EPA, Wills and Family Trust before you leave the UK which would comply with UK and New Zealand laws.

Comparison between UK and NZ Pensions

2008-02-20T22:53:00.000-08:00

With a UK Pension

You can receive a lump sum when you reach your pensionable age. This is limited to 25% of the value of your pension fund, or for occupational pension schemes based upon a formula involving salary and service. This latter formula is being done away with in respect of service from April 2006, under the UK Government's Simplification Programme.
If you die with a UK pension scheme your spouse can get up to 2/3 of the pension you would have received. If you both die your pension dies with you, however, If you both die leaving qualifying dependent children , your UK pension could continue for as long as you fulfill the schemes eligibility criteria. With New Zealand superannuation plans all of your remaining investment becomes part of your estate and is passed on to your children, heirs.
Your payments from your UK pension funds will be affected by exchange rates and bank transfer charges.
New Zealand Inland Revenue assesses worldwide income as taxable income even if your investments are invested in tax havens. You may receive a tax credit for any income tax already deducted in the UK.

With a NZ Pension

At age 65 you will receive a state pension (New Zealand Superannuation) if you have lived in New Zealand for a total of 10 years since you turned 20 and a total of 5 years since you turned age 50. Any UK state pension will be offset against (deducted from) your New Zealand superannuation entitlement.
You can draw down on your investments usually from age 60 (depending on the requirements of the Trustees) and this is not regarded as assessable income for tax purposes. You can set your own income level.
Your personal superannuation savings (not the Government's New Zealand Superannuation) are part of your estate on death.

Considerations and Requirements UK Pension Transfer

2008-02-20T22:52:00.000-08:00

Have you Considered?

Does my plan contain valuable options?
Does my plan guarantee predictable benefits at retirement, regardless of the future movement of UK interest rates and investment returns?
If I take my benefits as a transfer value now, what rate of return would my Superannuation need to achieve in order to replicate the benefits I have given up?
What percentage of my overall retirement provision is tied up in my UK pensions?
What are the death benefits available from the arrangement, should I die: before normal retirement date? after normal retirement date?
What are the costs associated in making an informed decision?
Where is my pension fund invested in the UK? If I keep it there does it continue to meet with my attitude to investment risk?
What is the current strength of the insurance company it is invested with? Do they still exist?
What are the ongoing charges, if any, of my pension arrangement?
How well or badly has my chosen fund(s) performed against its peers?

Requirements

To transfer any UK Pension Benefits to New Zealand, the following requirements must be met:

Permanent departure from the UK with no intention of returning to work or retire;
Employment or self-employment in New Zealand;
New Zealand residency for tax purposes;
No part of the UK Benefit commenced paying a pension;
Payment directly from the UK scheme to an HMRC, QROPS approved New Zealand superannuation fund.

Some reasons for transferring your UK Pension to NZ

2008-02-20T22:25:00.000-08:00

We believe that the reasons for transferring your UK pension to New Zealand far out-weigh leaving it with your UK pension's administrator.

No Tax: When you transfer your UK pension to New Zealand you will be able to take the full benefit of your pension at age 60 tax free. If the funds remain in the UK, any pension income stream will be assessable as income and taxed at the marginal income tax rates.
Control: Enables you to keep track of your pension plan and gain more control of your funds without affecting their earning power. You won't need to be concerned whether the fund is merging, closing or going out of existence, issues that are not uncommon to UK pension schemes.
Flexibility: In New Zealand, there is no requirement to purchase a life pension/annuity. You will be able to draw your benefits as a retirement income (allocated pension), as a lump sum or as a combination of both.
From April 2006 simplification rules came into effect allowing transfer of UK Pensions to HMRC, QROPS approved schemes in New Zealand. At the moment the UK Government is allowing pension transfers, but how long will this continue? The Australian Government does not allow pension transfers out of Australia unless you are on a temporary work permit.
You may be able to transfer your British pension to New Zealand and access up to 40% of the value immediately. This will be dependent on the specific requirements of your UK Pension plan.
You no longer need worry about exchange rate fluctuations affecting your pension payouts.
You will not be paying bank fees for each transfer (may be as a high as £18 per transfer)
You will have more information and control on the companies holding your retirement savings.
Easier to access your money in retirement.
If you die with a UK pension scheme your spouse can get up to 2/3 of the pension you would have received. If you both die your pension dies with you, however, If you both die leaving qualifying dependent children , your UK pension could continue for as long as you fulfill the schemes eligibility criteria. With New Zealand superannuation plans all of your remaining investment becomes part of your estate and is passed on to your children, heirs.

We have experience in handling the transfer of British pensions to New Zealand, if it is appropriate. We liaise directly with your UK pension schemes. Please note that not all schemes can be transferred. The transfer will need to be into an HMRC approved QROPS registered New Zealand superannuation fund.

If the funds are transferred to a non registered fund in New Zealand a UK tax of 55% could be imposed on the funds being transferred.

If you have started to take a pension from your scheme this can not be transferred.

The UK State Pension cannot be transferred.

UK Pension Transfer to NZ

2008-02-18T05:30:00.000-08:00

UK Pension Transfer to New Zealand

It is important to seek good, experienced advice. To transfer UK Pensions to NZ Lyfords uses HMRC, QROPS approved funds. This avoids the risk of potentially being taxed up to 55% on the transfer value of your UK Pension.

Use our specialist knowledge to make an informed decision on whether to transfer your UK Pension ("superannuation") - UK Pension Transfer to NZ- to a New Zealand superannuation ("pension") fund.

In the UK, Pension plans are only lightly taxed while saving for retirement, but the income received is assessable for taxation.

In New Zealand (NZ) superannuation funds are taxed along the way and the capital at maturity is tax free and generally not locked in.

You may be able to transfer your UK Pension to New Zealand and access up to 40% of the value immediately.

Fact Finder Form

Reasons for Transferring

Pension Transfer - - enables you to keep track of your pension plan and gain more control of your funds without affecting their earning power. You won't need to be concerned whether the fund is merging, closing or going out of existence.

From April 2006 simplification rules came into effect in the UK enabling a much wider range of permitted investment.

Click here to read more .....

What You Need to Know

Does my Pension plan contain valuable options?

Does my Pension plan guarantee predictable benefits at retirement, regardless of the future movement of UK interest rates and investment returns?

Click here to read more .....

Credit Card, Mortgage and Retirement Calculators

2008-02-18T05:08:00.000-08:00

KiwiSaver Decision

2008-02-18T05:01:00.000-08:00

NZ Investment Advisers and Market Participants Regulations 2008

2008-02-11T12:33:00.000-08:00

A Noisy Market Siren

2008-01-03T03:08:00.000-08:00

The idea is to automatically activate a noisy siren from a sound file when opening the excel file as the market index value drops to, or is below, the minimum expected value. The market index value is refreshed every 30 minutes.

Eg. the index value alert is 4,300. If the market index value is below 4,300, the noisy siren will be automatically activated.

I first set a WebQuery for the market index latest price with automatic refresh every 30 minutes. Then, apply VBA macro to call a sound file.

I modified the VBA codes taken from ExcelTip.com and J-Walk.com as follows:

Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
-------------------
Sub PlayWavFile(WavFileName As String, Wait As Boolean)

If Dir(WavFileName) = "" Then Exit Sub
If Wait Then
sndPlaySound WavFileName, 0
Else
sndPlaySound WavFileName, 1
End If

End Sub
-----------------------------
Sub PlaySoundAlert()

For i = 1 To 5

PlayWavFile "c:\WINDOWS\Media\notify.wav", True

Next i

End Sub
-----------------------------
Function Alarm(V)

x = Range("alert")

If V <= x Then

Call PlaySoundAlert

Alarm = True
Exit Function
Else
Alarm = False

End If

End Function

VBA Progress Indicator

2008-01-02T20:14:00.000-08:00

I learn how to create an VBA progress indicator from j-walk blog after surfing around and finally found the simplest one.

And now I can apply it into my portfolio optimisation model.

The code:

Sub optimise()

Dim PctDone As Single

Application.ScreenUpdating = False

nosim = Range("nosim")

For i = 1 To nosim

Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient..."

Range("simulation").Copy
Range("simmeanresult").Cells(71 + i, 1).PasteSpecial Paste:=xlPasteValues

Count = Count + 1
PctDone = Count / nosim

With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With

DoEvents

Next i

Unload UserForm1

Application.StatusBar = False

SolverOk SetCell:=Range("tangency"), MaxMinVal:=1, ValueOf:=0, ByChange:=Range("proweight")
SolverAdd CellRef:=Range("proweight"), Relation:=3, FormulaText:="0"
SolverAdd CellRef:=Range("one"), Relation:=2, FormulaText:="100%"
SolverSolve UserFinish:=True

Application.ScreenUpdating = True

End Sub

Simulating Dow Jones Index in the Future

2008-01-02T19:36:00.000-08:00

Happy New Year 2008. Wishing you a prosperous year of 2008 and years after.

I was again playing with random numbers simulation and found everlasting properous years of Dow Jones Index in the future.

Bear in mind, this is just a rough simulation played to get rid of boredness in the first day of 2008.

The process is as follows:

Gather Dow Jones Index daily prices since 1-Oct-1928 and calculate the mean and standard deviation from the daily log returns.
Using NORMINV(rand(), cumulative mean, cumulative stdev), simulate the daily returns.
Using Price n * (1 + simulated daily return), estimate Price until n equals to 1-Jan-2100.

That's it. Easy. The index is randomly simulated everyday until year 2100. Number of simulations: 24,004.

Can you see the heavenly index level at the end? Such an amazing number, aye? That's why people should be optimistic with the future of finance and investment as glorifying God. Dow Jones Index at 31 Dec 07 is 13,264.82. Is it going to be 3,000,000 in 2100???

Iteration and Circular Reference

2008-01-02T16:38:00.000-08:00

If you can't live without circular references, just turn on the iteration.

cell A + cell B = cell C
while:
cell B = cell C - cell A

In Excel, cell B and cell C are the sources of circular references.
However, to make the calculation work as is, the iteration must be set on as the image below. (Tools>Options)

Then, we all can live with circular references.

Name a Cell

2008-01-02T16:35:00.000-08:00

One of the important tools in the Excel is cell naming. By naming a cell, we can relate the cell name with any formulae we want to make. Furthermore, in creating macros or visual basic codes in Excel, cell naming is very helpful and important particularly in identifying the corresponding cell.

To name a cell, just go to the box 0n the top left side. Write the desired name and press enter.

For example, we want to calculate the return of share prices. With the conventional method, we use formulae =(B2/B3)-1. However, since B2 has been named as "today" and B3 as "lastyear", then the formulae becomes =(today/lastyear)-1.

This handy method is useful in a big spreadsheet when the formulae contains particular parameters that need to be referred frequently. It's also good for easily remembering and quickly referencing the formulae.

Lotto generator

2008-01-01T17:07:00.000-08:00

This lotto generator might not explain how I was so lucky two years ago. At least, randomness may still explain how people could win a lotto.

The process of this lotto generator:

1. Running a set of Monte Carlo simulations for getting 6 numbers from 1 to 40 randomly.
2. Calculating the probability statistics from the simulations.
3. Incorporating the historical statistics since the 1st draw until the latest one.
3. Picking up the highest probability numbers.

House: Borrow, Buy, Sell and Profit

2007-12-19T01:29:00.000-08:00

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.xls

Hosted by eSnips

Portfolio Optimisation (Shares & Funds)

2007-12-19T00:44:00.000-08:00

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

2007-11-26T03:44:00.000-08:00

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

2007-11-26T03:05:00.000-08:00

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...

Hosted by eSnips

Value-at-Risk

2007-11-26T02:43:00.001-08:00

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

2007-11-26T02:40:00.000-08:00

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

2007-11-26T02:34:00.000-08:00

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

2007-11-26T02:33:00.001-08:00

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

2007-11-26T02:32:00.001-08:00

Using the closed-form bond formula posted before, this bunch of spreadsheets 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

2007-11-26T02:30:00.001-08:00

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.x...

Hosted by eSnips

Share Valuation

2007-11-26T02:08:00.000-08:00

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.xls

Hosted by eSnips

AstraWatch: week 29 Oct 07

2007-11-02T20:43:00.000-07:00

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

2007-09-19T03:51:00.000-07:00

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

2007-08-26T00:28:00.000-07:00

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

2007-07-19T22:41:00.000-07:00

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

2007-04-19T17:50:00.000-07:00

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

2007-03-01T14:32:00.000-08:00

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

2007-02-18T20:39:00.000-08:00

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%

About Me

2007-02-11T23:32:00.000-08:00

Hello, I'm Jeff Liando (Jeffry Merril Liando), a business analyst and an investment analyst holding NZ finance qualifiations of Graduate Diploma in Finance (Waikato NZ) and Master of Applied Finance (Victoria NZ).

As an Excel developer, I can assist those seeking tailored Excel spreadsheets with VBA/Macro. I can also help those seeking unbiased financial advice and optimised investment portfolio in shares, bonds, debentures and managed funds within the Australia NZ context.

more about me in FiNZ Financial Intelligence >>>

Thank you

2007-01-21T03:25:00.000-08:00

Thank you

for your time, comment

and

generosity.

Contact me

2007-01-21T03:11:00.000-08:00

If you have any inquiries, please complete the following form or email me on Jeff(at)Keziana.com. Mobile 0272448895. BlackBerry PIN 215DB450.

Powered byEMF Web Forms

Skype
Yahoo messenger

AstraWatch: Week - 19 Jan 07

2007-01-18T03:38:00.000-08:00

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%

AstraWatch: Week - 20 Nov 06

2006-11-25T04:01:00.000-08:00

PT Astra International Tbk. (ASII)

BETA= 1.447045614
WACC= 27.87%
Equity value (in IDR ,000,000,000)= 46,635.60
Value per share (in IDR) = 11,519.64

last trade
ASII= 15,400.00
JKSE= 1,717.73
INDO-17= 6.248

Kiwi Biofuel Ltd

2006-11-20T03:07:00.000-08:00

Kiwi Biofuel (KBF) Ltd is a fictional manufacturing company used in this MYOB simulation. KBF produces liquid biofuel from kiwi fruits. With $100,000 assets raised from $50,000 equity and $50 loan, KBF's production capacity is 1,000 litre per day or 300,000 litre per year. It employs 2 labours, 1 engineer and 1 office assistant.

To produce 100 litre, it needs:
5 tray kiwi fruits @$6.00 per tray
2 hours labours @$12.50 per hour
50 kWh electricity @$0.10 per kWh
Total = $60 per 100 litre

Selling price = $1.00 per litre

One year budget:

Sales = $300,000
Cost of sales = $180,000
Advertising = $10,000
Salaries = $60,000
Office = $10,000
Maintenance = $ 10,000
Other expenses = $10,000
Operating profit = $20,000
Tax = $6,600
OPAT = $13,400
ROA = 13.40%
ROE = 26.80%

It's better to build your own accounts

2006-11-19T04:47:00.000-08:00

Start with creating a new company file:

Then, follow the instructions until the accounts list menu. In this menu, it is recommended to build your own accounts list as you might need some flexible adjustments when recoding journals.

Initial Capital Investment

2006-11-18T05:10:00.000-08:00

As every business must start with an initial capital investment, KBF builds its plant and office with own equity and bank loans. KBF's equity and loan from bank may first deposit or transfer to savings before being spent for capital expenditures.

To do this, the simple way is to create journal directly:
go to command centre > accounts list

There some new accounts we want to create here:
1. KBF equity
2. KiwiBank Term Loans
3. KiwiBank Savings
For KBF equity, go to equity > press new

Fill in the form something like this:

Same process for KiwiBank Term Loans with account type: liability and KiwiBank Savings with account type: asset.

Then, just record the journals as KBF's equity and loan from bank being transfered or deposited to savings. Go to record journal entry.
Equity

Loan

From here, KBF can establish the biofuel plant and buying office equipment and vehicles. Go to purchase > enter purchases. Please use GST tax code - Standard (S) with 12.5% GST. Then pay or close all open bill with electronic clearing account.

Finally, by checking the account list (go to accounts > accounst list), it can be found the balance sheet position something like this.

How about the inventory and production? Does KBF need more funding to start production? Stay tuned.

Balance Sheet Report

2006-11-17T19:43:00.000-08:00

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:

Injecting Capital Fund by cash

2006-11-16T20:01:00.000-08:00

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:

The Market Deviates?

2006-11-12T07:14:00.000-08:00

There are some reasons why the market price deviates from the valuation of Astra International. If the valuation is correct, the market is currently trying to drive the price up until some level to conduct short selling as the future expectation is long (buy) when the price is low as of the valuation. Otherwise, the valuation is wrong.

AstraWatch: Week - 6 Nov 06

2006-11-12T06:44:00.000-08:00

PT. Astra International Tbk. (ASII)

BETA= 1.446220312
WACC= 27.63%
Equity value (in IDR ,000,000,000) = 48,100.64
Value per share (in IDR) = 11,881.53

last trade
ASII= 14,500.00
JKSE = 1,664.84
INDO-17= 6.207%

AstraWatch: Week - 30 Oct 06

2006-11-12T06:41:00.000-08:00

PT. Astra International Tbk. (ASII)

BETA= 1.447344401
WACC= 27.28%
Equity value (in IDR ,000,000,000)= 50,441.23
Value per share (in IDR)= 12,459.68

last trade
ASII= 13,950.00
JKSE= 1,612.95
INDO-17= 6.413%

AstraWatch: Week - 16 Oct 06

2006-11-06T15:31:00.000-08:00

PT. Astra International Tbk. (ASII)

BETA= 1.447796665
WACC= 27.02%
Equity value (in IDR ,000,000,000)= 52,269.56
Value per share (in IDR) = 12,911.31

last trade
ASII= 13,500.00
JKSE= 1,572.85
INDO-17= 6.47%

AstraWatch News: Week - 7 Aug 06

2006-08-08T09:02:00.000-07:00

Astra-Toyota Establish Multifinance Company
from Bisnis Indonesia (www.bisnis.com)

PT Astra International Tbk and Toyota Motor Corporation are in cooperation to establish multifinance company PT Toyota Astra Financial Services (TA Finance) with the total investment of IDR2 trillion.

The financing company is aimed to control the credit on Toyota cars credit worth IDR600 billion - IDR700 billion to finance 6000 units of car financing.

The President Director of TA Finance Buntoro Muljono said that such integration is aimed at boosting the sales of Toyota cars in Indonesia through Toyota business chain.

"Yes, it is aimed at boosting the sales of Toyota. Due to the high application, we are optimistic to meet the target of 6000 units of cars," he said during the launching of the company.

With such service, he said, the buyer could get one stop service.

Buntoro said that to establish the company, Toyota has acquired PT KDLC Bancbali Finance, from Korean company Korea Development Leasing Corp. and Bank Bali [presently Bank Permata].

The company acquired the multifinance company as the government has stopped granting the license to open multifinance company.

"We have acquired a multifinance company, KDLC Bancabali Finance, and then changed it to TA Finance."

The two shareholders have injected IDR500 billion this year, and the rest IDR1.5 trillion will be injected if the company needs additional liquidity.

=====

Toyota Astra Finance
PT. Toyota Astra Financial Services (TA Finance) is a joint venture between Toyota Financial Services Corporation (TFSC) and PT. Astra International Tbk (AI)., whose relationship has spanned for over 50 years. Memorandum of Understanding was signed in September 2005, an initiative which aims to be the most preferred and ultimately, the only financing solution for Toyota vehicle ownership.

Dynamic Company Valuation - Astra Watch

2006-08-08T05:42:00.000-07:00

This is a financial model for live-updated valuing PT. Astra International Tbk, say it as a Dynamic Company Valuation. Anyone can be the insider! This is an Excel model with macro, where some VBA codes are used to manipulate key data for updating Beta and WACC via Yahoo Finance and Bank Indonesia websites.

Introduction

Company valuation model for analysis can be developed into a broader scope of modelling techniques. This is not really due to the usage of a more sophisticated model, but quite related to the implementation of financial planning function in a company.

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.

Technical Notes

INDO17

USD 1 billion Dollar-denominated global bonds issued by the Ministy of Finance maturing in 2017 (INDO 17) rated B2 (positive) by Moody’s and B plus (positive) by Standard and Poor’s.
Daily date to be updated via Central Bank of Indonesia webpage to the blue colour range.
If the latest updated date from web query - 2 days less than today's date, no need to update.
It assumes 2 days lag from website update, however if computer is on, it can be updated automatically.
To update WACC sheet for risk-free rate assumption.
If number of days to be updated = 0, no need to update rate.

JKSE

Jakarta Stock Exchange (JSX) Composite Index consists of all stocks in the bourse. JSX opened in 1912 under the Dutch colonial government, closed during WW and re-opened in 1977.
Instead of those via Yahoo Finance, there are 18 JSX indices quoted by Bloomberg. The other popular indices: LQ-45, JSX Agri, JSX Mining, JSX Islamic, JSX Manufacture. Weekly date to be updated via Yahoo Finance webpage to the blue colour range.
If the latest updated date from web query - 7 days - 2 days less than today's date, no need to update.
It assumes 2 days lag from website update, however if computer is on, it can be updated automatically.
To update BETA sheet for index prices column in the lower bound, reversing position.
If number of weeks to be updated = 0, no need to update stock index.

ASII

PT. Astra International Tbk is a group company of the largest conglomeration in Indonesia with total asset of USD 5 billion in 2005 at USD/IDR 9,500 and sales of USD 6,4 billion.
Has been undertaken a loan restructuring program in 1999 and now owned 47% by Jardine Cycle and Carriage Ltd Singapore and 53% by public listed in JSX since 1990.
Weekly date to be updated via Yahoo Finance webpage to the blue colour range.
If the latest updated date from web query - 7 days - 2 days less than today's date, no need to update.
It assumes 2 days lag from website update, however if computer is on, it can be updated automatically.
To update BETA sheet for stock prices column in the lower bound, reversing position.
If number of weeks to be updated = 0, no need to update stock price.

Beta

The blue range "ASII" and "JKSE" are used to store price and index data updated from ASII and JKSE sheets.
The key reference numbers to find the lower bound of columns are the row numbers in the left and right hand sides. Data is in reversing position from the original data, the latest one is in the bottom.

WACC

Using the average market return from the BETA sheet, the market risk premium is 22%. This shows that the high volatility in JSX market makes the investors more love to hold stocks.
The market value of equity is calculated from the average price produced in the BETA sheet.
WACC of 28.63% is high, but normal in emerging country like Indonesia.

Value

Quarterly update of input is recommended.
The first part consists of Balance Sheets, Income Statement and Financial Ratios, which is processed to create projection from 2006 - 2010. Based on the raw data of 2004 and 2005, assumption of financial ratios is created according to the average of the two years ratios.
Then, projection is created based on the average ratios. There are two plugs: Bonds and Cash.
The second part is Bankruptcy Prediction model and its sensitivity analysis. However, this still borrows some parameters resulted in Free Cash Flow Valuation, such as equity value and stock price. The reason of doing this is because of ASII past experience in loan restructuring.
Even there has been a significant changing of ownership, the big size of the company needs to be alerted based on growth and value. Moreover, it politically matters as such a national asset.
The third part is the main part, Free Cash Flow valuation. Based on the assumption of 18% sales growth (average of 2004 and 2005), the equity value is IDR 39,329,000,000,000 or at USD/IDR 9,500 is around USD 4 billion, with stock price of IDR 9,715 (USD 1.023).

Bond

Calling the plug in balance sheet projection as ending balance, this model tries to work out the additional and repayment bonds. The additional is assumed as face value or book value of bond issued. In 2006, all 2005 ending balance is assumed to be paid in 2006.
Then, additional is calculated as ending - (-repayment) - beginning, which indicates there is a new bond issued. Then, the coupon of the new bond is calculated as repayment in 2007.
By repeating this process, it can be found a series of additional bonds to produce as a bond portfolio and the cash flows that consist of PV and coupon. Increase in Bond Price means loss.
In accounting system, the bonds PVs may go to cash and the coupons may go to interest expenses.
The yield is assumed 100 bps above the government yield and the coupon rate is 8% annually.
Portfolio duration is calculated based on weight of bonds' PV.
Sensitivity analysis consists of effect yield on bond portfolio NPV to 2010, portfolio duration and coupon effect on portfolio duration. As issuer, company chases a lower duration.

Why AstraWatch?

2006-08-07T19:34:00.000-07:00

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 can affect the analysis.

The relevant market data may include INDO17 risk-free rate, ASTRA share price and JSX Composite market index that are able to indicate changes in value. The main goal is to adjust Beta and WACC for calculating the live-updated equity value, most importantly, the equity value per share or share fair value of the Indonesia's giant automotive.

Contact:

Welcome to Keziana Financial Modelling

2005-07-07T22:04:00.000-07:00

Greetings

Keziana.com has been around the web since 2005. This website aims to provide financial modelling knowledge, assistance and service for small businesses and individuals in planning, monitoring, controlling, evaluating and analysing their financial issues. This can be a free or premium service depending on the complexity.

The free service is my "pro bono" service provided to all New Zealanders who want to know more about finance and investment and increase their level of financial literacy. Please do not hesitate to drop me an email.

The contents may include small business & personal financial models, dynamic portfolio and optimisation models, excel VBA/macro tips, managed funds prices, financial news, concepts, etc. The contents would be mainly in NZ context but a few contents may be of my choice for Indonesia context.

As an Excel developer, I can assist those seeking tailored Excel spreadsheets with VBA/Macro. I can also help those seeking unbiased financial advice and optimised investment portfolio in shares, bonds, debentures and managed funds within the Australia NZ context.

As an Access developer, I can also develop tailored Access interfaces for pipelines, new business, commissions and fund tracker and database normally used in the financial advisory industry.

Please surf my personal links to get to know me and do contact me if you need my assistance.

I wish you every success and all the best for all your matters.

Cheers
Jeffry Liando
>>> more about me >>>

Name
Email
Phone no
Subject
Message

Keziana Financial Modelling

Global Financial Crisis 2008-2010 Movies

The Last Days of Lehman Brothers (2009, BBC Movie)

Inside Job (2010, Sony Pictures)

The Last Days of Lehman Brothers (2009, BBC Movie)

Inside Job (2010, Sony Pictures) - a comprehensive analysis of the Global Financial Crisis of 2008

Wrap Accounts

Chat with financial professionals

Portfolio fee analysis

Personal Early Warning Signal

Insurance Needs & Wealth Protection

Scenario Analysis & Wealth Projection

Asset Allocation

Retirement Plan

Home Plan

Holiday Plan

Education Plan

Personal Accounting and Budgeting

My Curriculum Vitae

S&P 500 Interactive Chart

Psychospiritual Aspect in Financial Planning

Client Demo - Investment Portfolio Report

Client Demo - Financial Intelligence Report

Financial Dictionary

Investment TV

Fixed Interest Overview

Market Overview

International Equity Overview

NZ and Australia Equity Overview

Financial Calculators

Psycho-spiritual Aspect Methodology

Managed Fund Selector

Thank You

Konstruksi portfolio reksa dana Indonesia

Pascal for Investment Database

Next coupon date - IF THEN formula

CAPM return and cumulative data matter

The Parables of Investment

Optimising Portolio of ING PPS Funds

Finance Concepts

financial concepts

Charts, News and Quotes

nz finance news

global finance news

Excel and Macro Tips

Top 10 Indonesian Managed Funds (Reksa Dana)

Which Market do you prefer?

US Recessions and the MSCI World Index

Glossary of UK Pension terms

FAQ: UK Pension Transfer

UK Pension Transfer Traps

The 7 Traps to Transferring Your UK Pension Funds

Pension Transfer Trap One

Pension Transfer Trap Two

Pension Transfer Trap Three

Pension Transfer Trap Four

Pension Transfer Trap Five

Pension Transfer Trap Six

Pension Transfer Trap Seven

Taxation Issues UK Pension Transfers

Taxation Issues UK Pension Transfers to NZ

UK Pension Simplification Rules 2006 - How will they affect your Pension Transfer?

NZ Taxation Rule Changes April 2006

Taxation Rules

Before Immigrating to NZ

Comparison between UK and NZ Pensions

With a UK Pension

With a NZ Pension

Considerations and Requirements UK Pension Transfer

Have you Considered?

Requirements

Some reasons for transferring your UK Pension to NZ

UK Pension Transfer to NZ

Credit Card, Mortgage and Retirement Calculators

KiwiSaver Decision

NZ Investment Advisers and Market Participants Regulations 2008

A Noisy Market Siren

VBA Progress Indicator

Simulating Dow Jones Index in the Future

Iteration and Circular Reference