Tracking Portfolio Performance vs SP500 in Excel

May 2, 2008 - by J2R

 Most professionals recommend you only check your statements once or twice a year to balance them. Checking them too frequently might cause you to do stupid things, like sell your stocks when the market is down and buy them when the market is high. You can’t be emotional when investing, and when you check your portfolio too often, you’re bound to do stuff that/when you shouldn’t.

 Easier said than done. I know what I need to do, but I’m still obsessed about daily performance, net performance, annualized performance and even performance against the S&P500.

 Managing my portfolio in Excel would be too cumbersome without being able to automatically update stock prices. I tried Web Queries, but it wasn’t really what I wanted. So I tried Google docs. Google Spreadsheet is just amazing. I could track my overall portfolio’s performance in real time (using Googlefinance function). Stock prices are updated every few secs without any intervention. Just sit back and relax obssess. However, in addition to my daily and total net gains, I wanted to track my annualized gains. Google Docs provides the XIRR function that calculates annualized gains, but because it doesn’t have Lookup tables, the setup became quite cumbersome. Also, for complex spreadsheets with too many lookups, the spreadsheet became quite slow to use.

 Google Docs wasn’t perfect, but it was still my best solution.

 That is, until I found MSN Money Stock Quote Add In. It’s an Add In that allows you to update all your stock quotes from the internet. Unlike GoogleFinance, it doesn’t refresh prices automatically. You have to run the plugin for it to connect and refresh your stock prices and you can only do it every 5 minutes. Not a big deal. I was more than willing to give up real time stock updates for all the power of Excel.

 And here’s how you can do it: 

 Note that I’m using Excel 2007. 

First Step - Download and install the MSN Stock Quote add in

After you install it, you can access its help pages for more reference.

Here’s a summary:

MSNStockQuote(Symbol,Property,CountryCode)

Symbol   is a stock symbol (for example: “MSFT”, “AOL”, “IBM”, “AAPL”).

Property   is a piece of information regarding the stock, such as Ask Price or Close Price. This is an optional parameter. If omitted, the default is Last Price. You can either type the indicated property as text, or use a value from 1 to 25 to represent the property you want to return.

And the function reference:

Step 2 - Track your Transactions

The next step is to create a separate “table” for each stock you own so you can track your transactions. This is only necessary for more detailed performance tracking, like annualized (where you need cashflow) and return vs any index.

You need to name your tables in a specific pattern that includes the stock ticker. This is to simplify your summary table that will need to know which table to look for transactions.

In my case, I named them “Stock_” and the stock ticker symbol. For example, to track my Citigroup (yaiks) stock transactions, I created a table named “Stocks_C”.

click on the image above to expand it

It’s a pretty straight forward transactions table. The only kink I added to it was the closing SPY price for the day. For those that don’t know, SPY is used to track the S&P500 index.

For the table’s Total Row, I calculate the total number of Stocks (Qty) and the total Purchase Cost with the well known SUM function.

I also had to enter today’s date and today’s current value of my holding as part of my Cash Flow (to calculate annualized returns), so I used the TODAY() and the MSNStockQuotes functions. Don’t forget that for Cash Flow, you need to use positive and negative numbers to differentiate between deposits and withdrawls. With detailed information about each transaction, the total cost, the total number of stocks and my current position, I had everything I needed.

Step 3 - Summary - Putting it all together

Now that our Transaction tables are setup, we can finally set our Summary table up.

click on the image above to expand it

All the formulas in this table are based on the first row (the stock ticker), so to add a new stock to our Summary table, we simply have to copy one of the rows and update the ticker symbol. That is why we named the tables as “Stocks_” and the ticker symbol in the first place. We use the INDIRECT function so that we can dynamically figure out the right Transaction table to access based on the ticker symbol (Columnb B).

You’ll notice that at the top, I have SPY and its latest price. I’ll use this to calculate my hypothetical Current Value had I invested in SPY.

Tracking the performance vs the S&P 500 requires a little bit more work. Basically, the way I did this was by figuring out how many SPY (Spyder ETF that tracks the S&P index) stocks I could’ve purchased had I purchased them instead of my stocks. For that, I had to track the closing price for SPY for each transaction I had. A bit of work, but totally worth it to know whether your portfolio is actually beating or losing to the S&P500 index.

So there you go. If you’re an Excel fan, this is one great way to track your portfolio performance using Excel.

Posted in Finances, Investment 4 Comments »

Carnival of Personal Finance #146

April 1, 2008 - by J2R

The Carnival of Personal Finance #146 is up at StockTradingToGo.

Posted in Carnivals, Finances No Comments »

Getting help for a child with autism. Time is precious.

March 27, 2008 - by J2R

This CNN’s article just hit Digg’s front page. As I’ve mentioned before, my son was recently diagnosed with autism. We first brought our concerns up with his pediatrician when he was 2 years old.

His pediatrician didn’t say he was fine like the one in the article, but at the same time, he didn’t really care that much. Took him almost a month to give us a referral to a neurologist (we have HMO, so we need referrals), and after the neurologist, his pediatrician took another month to send us to what has been our greatest find: The Early Intervention program. 

It took almost 6 months after we first brought it up with his doctor for him to be officially diagnosed with autism and start getting his therapies. I really regret not going after this issue more aggressively.

Early Intervention is just that: the sooner your kid gets help, the more effective the therapies will be, and we lost precious 6 months by waiting for doctors to be available.

My suggestion to you: if you have concerns, don’t wait. Even if your pediatrician says there’s nothing wrong. Talk to teachers, talk to other professionals, take your kids to centers that have years of experience with children (eg: YMCA) and ask what they think. Time is precious during these early years.

And while you’re waiting, do your homework. Hit the internet, hit support groups, hit forums and message boards. There are tons of parents out there asking and answering questions. Keep in mind that the people in the forums are NOT professionals (or most of them), so take their advice with a grain of salt. But that doesn’t mean they don’t have tons of useful information that can point you to the right direction.

The key thing is: Time is precious.

I really wish we had known about these Early Intervention Programs before.

Posted in Family 4 Comments »

Can’t Grasp Credit Crisis? Join the Club - Another Rehash

March 26, 2008 - by J2R

The NY Times had this article a couple of weeks ago explaining the Credit Crisis in a simple and easy way to understand. I wanted to blog about at the time, but totally forgot about it till another PF blogger recently blogged about it.

So how is it that a mess concentrated in one part of the mortgage business — subprime loans — has frozen the credit markets, sent stock markets gyrating, caused the collapse of Bear Stearns, left the economy on the brink of the worst recession in a generation and forced the Federal Reserve to take its boldest action since the Depression?

Great question! And if you still can’t understand after reading dozens of articles and blog posts, don’t feel bad. I’m going to add a couple of snippets from the NY Times post and add a couple of comments I think they missed.

It really started in 1998, when large numbers of people decided that real estate, which still hadn’t recovered from the early 1990s slump, had become a bargain. At the same time, Wall Street was making it easier for buyers to get loans. It was transforming the mortgage business from a local one, centered around banks, to a global one, in which investors from almost anywhere could pool money to lend.

The new competition brought down mortgage fees and spurred some useful innovation. Why, after all, should someone who knows that she’s going to move after just a few years have no choice but to take out a 30-year fixed-rate mortgage?

We all remember those ARM (Adjustable Mortgage Rates), right? The NY Times doesn’t mention that the catalyst for the real state bubble wasn’t really the new type of mortgages, but the low interest rates. After the 2000 dot com bubble burst, the Feds, trying to save the stock market, started lowering rates (sounds familiar?). People were able to get loans with rates as low as 3.56% (1-Year ARM in 2004). Would we be in this current mess had the Feds not lowered rates as much as they did? Food for thought.

Because these loans go to people stretching to afford a house, they come with higher interest rates — even if they’re disguised by low initial rates — and thus higher returns. The mortgages were then sliced into pieces and bundled into investments, often known as collateralized debt obligations, or C.D.O.’s (a term that appeared in this newspaper only three times before 2005, but almost every week since last summer). Once bundled, different types of mortgages could be sold to different groups of investors.

Good point, but again, it’s a simplistic view. Normally, 5-ARM loans would make perfect sense. Why get a 30 year loan if I know I’ll be moving in 5 years?

But as I mentioned in my previous post, if lenders were responsible and NOT greedy, they would only loan money to people that could actually afford those loans.

And how were lenders making money with these risky loans (lending to people that can’t really afford)?

As mentioned by the NY Times, they repackaged these loans as C.D.O. These packages are then rated by agencies like Moody, Fitch and S&P. Because these rating agencies were using outdated algorithms to rate these C.D.Os, lenders were able to resell these C.D.Os with AAA ratings (the best one out there).

That caused hedge funds, international funds, investment banks and all sort of companies and people wanting to make money to buy these AAA investments with high returns.

All these investments, of course, were highly risky. Higher returns almost always come with greater risk. But people — by “people,” I’m referring here to Mr. Greenspan, Mr. Bernanke, the top executives of almost every Wall Street firm and a majority of American homeowners — decided that the usual rules didn’t apply because home prices nationwide had never fallen before. Based on that idea, prices rose ever higher — so high, says Robert Barbera of ITG, an investment firm, that they were destined to fall. It was a self-defeating prophecy.

Now we all know they were risky, but at the time, everyone were buying them because of their excellent AAA Ratings (as mentioned above). Not only that, we also had insurance companies insuring these packages. Traditionally, they used to insure only muni bonds, but greed struck them, and they decided to also insure these complex vehicles. And once these subprime mortgages started to default, everyone started realizing that these ratings meant nothing. C.D.Os and their insurers started being downgraded causing banks and financial institutions that owned them to write off billions of dollars.

It was just a huge cluster f**k that a lot of us are still trying to understand.

This toxic combination — the ubiquity of bad investments and their potential to mushroom — has shocked Wall Street into a state of deep conservatism. The soundness of any investment firm depends largely on other firms having confidence that it has real assets standing behind its bets. So firms are now hoarding cash instead of lending it, until they understand how bad the housing crash will become and how exposed to it they are. Any institution that seems to have a high-risk portfolio, regardless of whether it has enough assets to support the portfolio, faces the double whammy of investors demanding their money back and lenders shutting the door in their face. Goodbye, Bear Stearns.

The conservatism has gone so far that it’s affecting many solid would-be borrowers, which, in turn, is hurting the broader economy and aggravating Wall Streets fears. A recession could cause credit card loans and other forms of debt, some of which were also based on overexuberance, to start going bad as well.

And that’s how subprime mortgage loans were able to crash the whole global financial system, from 401k plans in America, retirement funds in japan to a Wall Street iconic symbol, like Bear Sterns.

Posted in Finances 3 Comments »

Carnival of Personal Finance #144

March 24, 2008 - by J2R

We’re in the Carnival of Personal Finance #144.

Don’t forget to check it out.

Posted in Carnivals, Finances No Comments »

The new bubble - we caused this by being impatient

March 12, 2008 - by J2R

By now, everyone should know about the Housing Bubble.

The scary thing is that there’s another bubble in the horizon, and it’s much scarier.

Paul Farrell writes that Warren Buffet (currently the richest man alive) and Bill Gross (Bond Fund King) warn us that the $516 trillion bubble is a disaster waiting to happen.

Back in 2002, Buffet ran into issues with derivatives and warned Wall Street. They didn’t listen, and this bubble exploded from $100 trillion into the current $516 trillion bubble.

Farrell puts this bubble in context:

To grasp how significant this five-fold bubble increase is, let’s put that $516 trillion in the context of some other domestic and international monetary data:

• U.S. annual gross domestic product is about $15 trillion
• U.S. money supply is also about $15 trillion
• Current proposed U.S. federal budget is $3 trillion
• U.S. government’s maximum legal debt is $9 trillion
• U.S. mutual fund companies manage about $12 trillion
• World’s GDPs for all nations is approximately $50 trillion
• Unfunded Social Security and Medicare benefits $50 trillion to $65 trillion
• Total value of the world’s real estate is estimated at about $75 trillion
• Total value of world’s stock and bond markets is more than $100 trillion
• BIS valuation of world’s derivatives back in 2002 was about $100 trillion
• BIS 2007 valuation of the world’s derivatives is now a whopping $516 trillion

This is really, really scary.

These derivatives are so complex that Federal Reserve Chairman, Ben Bernanke, had to meet with hedge fund managers to understand them. And I’m not sure he came out of those meetings fully understanding them.

Gross said that we’re virtually creating a shadow banking system. Institutions are now able to create money outside of the central bank’s system. These derivatives are simply contracts between private institutions, and that’s our shadow banking system.

I can’t put this better than Farrell:

That’s crucial, folks. Why? Because central banks require reserves like stock brokers require margins, something backing up the transaction. Derivatives don’t. They’re not “real money.” They’re paper promises closer to “Monopoly” money than real U.S. dollars.

So we have $516 trillion in Monopoly Money circulating not only in ours, but in the world’s financial institutions.

And how does all of this relate to Personal Finance?

I was reading an article at Forbes by Paul Jonson where he attributes the current mess we are to: Impatience + Greed.

Impatience led many thousands of ordinary people to seek to acquire properties of much higher value than their savings justified.

He states that normally, this wouldn’t be a problem, since normally banks and lenders would simply have turned down these borrowers. 

Unfortunately, impatience coincided with excessive greed on the part of a number of bankers. 

Financial institutions jumped in with all their greed looking for higher and higher yields. They lent money to people that couldn’t afford those loand, and then repackaged those loans and resold them to other financial institutions or used them as collateral.

It’s just sad that all this mess was triggered by impatience to save money.

Such a basic and important principle that a lot of us often forget:

- Don’t buy things you can’t afford.

Posted in Finances 3 Comments »

98 year old lady letter to her bank

March 6, 2008 - by J2R

I found this in a message board.

Alledgedly, this is a letter a 98 yo lady in the UK sent to her bank. I can’t find any source, but regardless, it is quite amusing.

Dear Sir,

I am writing to thank you for bouncing my cheque with which I endeavoured to pay my plumber last month. By my calculations, three nanoseconds must have elapsed between his presenting the cheque and the arrival in my account of the funds needed to honour it. I refer, of course, to the automatic monthly deposit of my Pension, an arrangement, which, I admit, has been in place for only thirty eight years. You are to be commended for seizing that brief window of opportunity, and also for debiting my account £30 by way of penalty for the inconvenience caused to your bank.

My thankfulness springs from the manner in which this incident has caused me to rethink my errant financial ways. I noticed that whereas I personally attend to your telephone calls and letters, when I try to contact you, I am confronted by the impersonal, overcharging, pre-recorded, faceless entity which your bank has become. From now on, I, like you, choose only to deal with a flesh-and-blood person.

My mortgage and loan payments will therefore and hereafter no longer be automatic, but will arrive at your bank by cheque, addressed personally and confidentially to an employee at your bank whom you must nominate. Be aware that it is an offence under the Postal Act for any other person to open such an envelope. Please find attached an Application Contact Status which I require your chosen employee to complete. I am sorry it runs to eight pages, but in order that I know as much about him or her as your bank knows about me, there is no alternative. Please note that all copies of his or her medical history must be countersigned by a Solicitor, and the mandatory details of his/her financial situation (income, debts, assets and liabilities) must be accompanied by documented proof.

In due course, I will issue your employee with PIN number which he/she must quote in dealings with me. I regret that it cannot be shorter than 28 digits but, again, I have modelled it on the number of button presses required of me to access my account balance on your phone bank service. As they say, imitation is the sincerest form of flattery.

Let me level the playing field even further. When you call me, press buttons as follows:

1 To make an appointment to see me.
2 To query a missing payment.
3 To transfer the call to my living room in case I am there.
4 To transfer the call to my bedroom in case I am sleeping.
5 To transfer the call to my toilet in case I am attending to nature.
6 to transfer the call to my mobile phone if I am not at home.
7 To leave a message on my computer (a password to access my computer is required. A password will be communicated to you at a later date to the Authorised Contact)
8 To return to the main menu and to listen to options 1 through to 8.
9 To make a general complaint or inquiry, the contact will then be put on hold, pending the attention of my automated answering service. While this may, on occasion, involve a lengthy wait, uplifting music will play for the duration of the call.

Regrettably, but again following your example, I must also levy an establishment fee to cover the setting up of this new arrangement.

May I wish you a happy, if ever so slightly less prosperous, New Year.

Your Humble Client

Wouldn’t we all want to treat our banks the same way they treat us? Not only banks, but pretty much any utility or financial company we have to deal with in a daily basis.

Posted in Consumer, Finances 1 Comment »

Why we use our emotions instead of reason when dealing with our money

January 26, 2008 - by J2R

I found this article extremely interesting.

Would you rather earn $50,000 a year while other people make $25,000, or would you rather earn $100,000 a year while other people get $250,000? Assume for the moment that prices of goods and services will stay the same. 

My first impulse was to pick the first choice. My reasoning was that I would rather be financially better than others than the other way around.

Unfortunately, that was the irrational decision. Given that prices of goods and services are the same, why would I rather have half my potential income, just to be financially better than others?

Turns out that the majority of people also selected the first option. Not only that, but there are researches that attribute this behaviour on our genetics. The article mentioned explains it in details, which I won’t go.

Another example I liked was the “ultimatum game”. If your partner are given $100 to split between him and yourself. Whatever division of the money he proposes you, if you accept it, you each get to keep your share. But if you reject it, neither of you get any money.

Rationally speaking, any money offered you would be free. So a $90-$10 would be better than anything, since you’re still getting out with $10.

Interestingly, research shows that proposals that offer much less than $70-$30 were rejected because they were “unfair”.

Again, the article goes in detail explaining why this is so common.

What I think is important for us to know, is that we are inclined to use our emotions rather than reason when deciding what to do with our money and that is something we need to be aware of.

That is also probably one of the main reasons for our current sub prime mess. People buying homes (they couldn’t afford) because of peer pressure or because they “loved” the house.

Never make big impulsive financial decisions. Always sleep on it. Don’t buy the house you “loved it” on spot. Don’t sign time-share contracts or any other contract on spot. Especially if you go to those large presentations that offer free tickets or free meal. They always try to use the latest sales tactics and play on your emotions rather than reason. You’ll often hear arguments like:

Don’t you think your family deserves this?
Don’t you love your family enough to get them this?
Are you willing to risk your family’s future?

Don’t do whatever your financial advisors tells you to do before checking the details and sleeping on it. Don’t buy life insurance before learning about the differences. Always, always sleep on it.

Be aware that we’re genetically wired and predisposed to waste money and you save yourself from getting in a huge financially mess.

When it comes to money, as in most other aspects of life, reason and rationality are trumped by emotions and feelings.

Posted in Finances No Comments »

Good advice?

January 16, 2008 - by J2R

When friends ask me for advices, I generally tell them to buy index funds.

However, when my sister in law asked me about investing, I gave her a different suggestion.

I had started by telling her to buy index funds, while asking her risk tolerance/aversion, expectations etc.

After she told me what she wanted:

1. Low Risk
2. Short term (1 yr)
3. High returns (4% CD wasn’t enough for her)
4. No taxes

I changed my suggestion to:

“Go to Las Vegas and put everything on black”

Posted in Finances, Lifestyle 1 Comment »

Recession and the invincibility complex

January 9, 2008 - by J2R

A while ago, I wrote about the invincibility complex and that you should be really careful with it.

I’m a skeptical by nature, so it’s easier for me to expect that good things won’t last forever.

But for those of you that are too optimistic, you should step back and take a deep breath.

Some economists are not debating anymore whether the US economy will fall into a recession. They’re debating whether it already has.

Jobs are being slashed right and left. The dollar is weak. Unemployment has risen. These are depression signs.

We like to think we’re good at our jobs. That they value our work and our commitment. That we’re untouchable, invincible. Sky is the limit. Until this depression hits us harder and harder and eventually you find yourself in a hole.

So take a minute and just suppose you’re not invincible and that you might actually lose your job.

Best to be prepared. Here are a couple of tips for you:

- Save as much as possible. Better have 6 to 8 months saved to pay your bills.

- Try to be frugal for a few months (to get a better reading on where the economy is heading). It’s better to be frugal for the long term, but if you can’t, at least try it for a few months.

- Postpone your expensive vacations or replace them with cheaper ones.

- Analyze your budget and find what you could cut to trim your expenses if you lose your job.

- And most importantly, be psychologically prepared for it. It’s much easier to handle a tough situation if it doesn’t come as a surprise.

Posted in Finances, Lifestyle 2 Comments »