Next coupon date - IF THEN formula

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(B3DATE(YEAR(B3),MONTH(F3),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(B3DATE(YEAR(B3),MONTH(F3),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

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

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

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

Charts, News and Quotes

Excel and Macro Tips

John Walkenbach's Excel Tips

user tips

developer tips

Allen Wyatt's ExcelTips

Daily Dose of Excel

Top 10 Indonesian Managed Funds (Reksa Dana)

"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?

"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

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

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

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

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

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

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

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

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

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

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

KiwiSaver Decision

NZ Investment Advisers and Market Participants Regulations 2008

A Noisy Market Siren

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

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, as follows:

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

notes

financial concepts

nz finance news

global finance news