Pointy Haired Dilbert - Chandoo.org

Each of us have our mechanisms to track how we spend money. We use Excel, various online sites and software like Quicken or Microsoft Money to track how we spend our bucks. The bottom line is to track where each penny / paisa / cent is going. So, how great would be it be, if by a click of mouse you can open a form that can take details of what you have spent the money on and add the details to the end of a huge list and let you analyze the expenses at leisure?

Well, you can do that using Google docs - spreadsheet forms with 4 simple steps. Doubt it? Take a look at expense tracker form that I have created here.

1. First create a new Google docs - spreadsheet.

Once you are inside the spreadsheet click on the “forms” tab as shown below.

This will open a new window where you can create a form. We will use this form to enter spending details.

2. Create a basic expenditure tracker form

We will now create a basic form with the following fields:

1. How much you spent?
2. What did you spent it on? A description for the spending
3. Category of spending A list of categories from which you can choose

The first step is to name the form:

Next we will add 3 fields as shown below, the process is very easy, just try for yourself.

Finally we will save our expenditure tracking form:

3. Now lets start entering expenses in our form

When you are done, click on the “Next, choose recipients” option. This screen will show the form:

Bookmark the form url shown in the screen and this is the url we will access whenever you want to enter new expense.

Once you enter few expenses the spreadsheet will look like this:

See how Google adds the time stamp to each expense. This is a good thing as you dont have to enter the expense date. You can use this column to see how much you are spending everyday (provided you enter information as soon as possible)

4. Analyze the tracked expenses

What is the use of tracking when you are not analyzing. You can use built-in charts in Google docs to do some analysis of the expenses. I did a bar chart to show what is possible.

Take a test drive at the expense tracker form by accessing the one here.

Photo from pfala.

Would you like to spend next 5 minutes learning how to create an excel sheet to track your mutual fund portfolio?

NOTE: I have updated the sheet to fix a formula error, download it again if you need to.

We will use 2 simple excel features to achieve this - web queries and vlookup()
[click here to learn more about web queries in excel]

1. First, lets put a tabular format for our portfolio: We can have fund name, # of units, purchase NAV (Net Asset Value, the cost of unit for your when you bought it), purchase date, total value at purchase (units * purchase NAV), current NAV (we will pull this data from internet), value as of now (units * current NAV), Profit / loss amount and profit / loss % as our table columns. Once you learn how to do this, you can add more columns depending on what / how you want to track your MF portfolio.

   When you finish creating the table, it would look something like this:
   

2. Next, we will use web-queries to load the fund-names and the corresponding latest NAVs in a separate sheet. I have queried Association of Mutual Funds India [AMFI] - Latest Mutual fund NAV page since all my investments are in India. If you are in US or some other country you can query corresponding fund house / financial info aggregator sites (like google finance) to get the data. Remember to set “Refresh data on file open” on to get fresh data whenever you open the your tracker excel sheet.

   Since AMFI returns data in a text file with ; as delimiter, I had to parse the fund names and navs out of it using a combination of search(), left() and mid(). I will not get in to the details of how its done since you may have to process your data differently depending on source.

   Finally when the processing is done, we will have a table in the second sheet with all fund names and latest navs.

3. Now, all we have to do is create lookup formulas (well just vlookup()) to get the latest NAV to our tracker table based on the entered fund name.
   • Assuming the fund name in which you invested is in cell “c1″,
   • Assuming the fund data is in table “sheet1!c1:d6000″ with “column c” containing the fund name and “column d” containing latest NAV,

   The formula for latest NAV can look like this:
   
=vlookup(c1,sheet1!c1:d6000,2,false)

   Remember to use false for last parameter since fund names may not be sorted in alphabetical order on your source web page.

   Now we will repeat this formula for all the rows in latest nav column. I have built my portfolio tracker to track 20 funds at a time. Also, you can simplify formulas using named ranges.

4. Finally we will write formulas for,

   current value = latest nav * units held
   profit/loss = current value - purchase value
   profit/loss % = “profit/loss” / purchase value

   You can add some conditional formatting to beautify the table (like turning text blue for profits and red for losses etc.)

5. Thats all, you have now created a real-time mf portfolio tracker. It would look something like this when done:
   
   You can do the same for stock portfolios, commodities etc. You just need a web source that gives you latest data and five minutes of free time

Feel free to download mutual fund portfolio tracker excel sheet I have created and play with it.

Few ideas on how you can enhance this:

• Add graphs to see visually how the funds are doing
• Build some VBA to store previous NAV values of your funds so that you can see historical dates
• Instead of doing plain % of profit / loss, compute realistic growth of your funds using date of purchase, risk free rate of return etc.

Financial freedom, or not having to work for someone is the ultimate dream for anyone wearing an access card and refreshing his/her mailbox every five minutes. Just incase you are one of those who never realized the importance of planning early for the freedom, here is a chart to help you appreciate that need.

Download the retirement savings excel and play around

Well, thats not a new mutual fund in the market, but I can understand your confusion, given that several AMCs have tiger funds. Calvin fund is the name I am giving to my investment drive. The aim is to purchase a complete collection of my favorite strips, “Calvin & Hobbes - The complete collection” which is selling at Rs. 4000 odd currently. I know, dont fume, this still is a dilbert blog, and that is a why, I am going to use my investment acumen (??) to purchase this book.

Fund name: The Calvin Fund
Objective: Systematically pump in money at regular intervals with the aim of generating investment returns of rs. 4000 or more
Timeframe: Short-term, maximum time given for the fund to generate returns in 8 months
Investment Amount: Rs. 1500 every month
Investment Avenues: Primarily in to mid-cap stocks, filtered through several screens or analyst reports and then hand-picked by me, If nothing works, the money might be parked in an mf trying to make short-term gains.
What if the fund fails: I HOPE YOU SUFFER A DEBILITATING BRAIN ANEURISM, YOU FREAK!, alternatively, I will fold-up my fund and buy the book anyways

The fund will start officially on April 2007, and aims to close by December this year. Every month first week, I will disclose the funds performance and investments. May be the NAV as well.

What do you get from this? I know many people who want to buy this complete collection, but never got around to do it. If you are one of them, you can also create your Calvin Fund and purchase the same stocks / mfs that I am buying. Feel free to comment with tips on where I can park the money so that I get to lay my hands on this tome early.

Watch this space for April First Edition and wish me all the best

PS: the Images are copyrighted from Archives and Amazon Page.

Come Ferbruary, every fund house worth its NAV comes up with a hoard of ELSS / Tax planning mutual fund offerings and promotions. These days you open any (business) magazine and you see atleast a dozen ads on how MFs can save you Rs. 33,360* or something on taxes. First up the claim that they can save so much tax itself is based on some unrealistic assumptions. But we are going to talk about something more today.

“Why do mutual funds position themselves like a herd of sheep?”

To quote Vysper Lynd from Casino Royale, “Even accountants have imagination”, but Indian fund managers (houses) seem to have lacked the imagination to get a slice of unit holders mind (pocket). How else can you explain the same “save tax, get returns” tripe literally everyone is shelling out?

As you can see if we plot various products and services on a space with Product / Service type on one axis and value on the other, MFs/Insurance products comeout as high valued commodities. They are comparable with Saloon or Public transport options on product differentiability. (mind you, i am considering only funds by top performing, well established houses like SBI, HDFC, UTI etc.)

I think MFs can do much more to differ themselves from each other. I know investing in equities is still not a hot favorite for most of the semi-urban, rural India for lack of comprehension of the investment concepts and distance from technological advances like internet and demat. Also tax planning is often not done by these people either because they earn very little or their incomes are not tracked (no TDS or paid in black)

So we can safely assume that MFs are targeting salaried / self-employed people mostly located in metro/cities with sufficient understanding of the offerings and various investment avenues.

Now, sometime back even before I could get my online trading account and PAN I wanted to start an SIP and went to several websites. Finally I called up SBIMF phone numbers in chennai and asked them how I can start an SIP for some of their funds. They sent a person to my office, but to my surprise he is not from SBI-MF, but from some broking house. He gave me application forms and finally took ECS clearance from me. That was the last I have seen or heard from them. I keep getting monthly statements, but I would certainly prefer a better way to track the investment progress and an easier way to buy the funds. Not that SBIMF doesnt have an online part for that, but its pathetic to say the least.

So there goes No. 1, the fund houses can be much more tech/mobile savvy by creating a snappy web site with easy way to do better financial planning and hassle-free investments. Hardly any mf seem to focus on this space.

No. 2 could be “no advertising policy”. For one advertising budgets have to come from fund management expenses (which are generally 1-2%) and the more number of ads you see, the more of investors money is gone useless.

I think recently Reliance MF did fiddle with the positioning and launched a mid cap fund with a Debit card attached to it so that you can redeem units once every month from any of the HDFC atms. I think the concept is innovative even though I am not sure about the modalities.

Hopefully some fund house marketers read this and launch their funds with investor friendly positioning.

Also Read: Excel Based Mutual Fund NAV Tracker | The Art of creating new MFs

Most of my classmates and friends have started purchasing houses. This coupled with the fact that I am in coveted DINK (double income no kids) group now have prompted me to do some preliminary research on buying a house. In my quest few things became obvious,

• Any house worth its tiles costs more than a million rupees.
• For a first house 2bhk is ok, 3bhk is good.
• An apartment with 2bhk in Chennai (where we would be based for a while) costs atleast 15L (that is on the lower side, it might be upwards of 20L)
• Making a quick decision on whether or not to have your own roof is not so easy.

So I have come up with a way to decide it based on pure numbers (obviously using excel). Here it is.

At the outset the choices are whether (1) to invest in house or (2) to invest the money somewhere while paying the rents.

The cash flows associated with (1) are: EMIs, One time down payment, tax benefits on interest and principal. And at the end of the period house value if I decide to sell. I have assumed that I would sell it off.

The cash flows for (2) are: rent, tax benefits on HRA, any appreciation of money (EMIs) through investments and tax paid on the same

The variables that control the net present value (npv) of these investments are,

• monthly rent
• basic & hra (to calculate the tax benefits on hra)
• growth rates in basic sal and rent YoY
• cost of the house
• loan %age & tenure
• interest rate (assumed to fixed through out)
• house value appreciation %age YoY
• maximum allowed tax free interest & principal payments
• tax rate
• returns on your investments if you invest it elsewhere
• returns on 5 yr. bank FD.

A sample set of values are shown aside.

My guess is you need about 4 seconds for each of these 15 values. Once you know them, you can generate the cashflow for both buy and rent options. As you can see, for this set the npv of rent is higher than buy.

Some observations:
1. House value need to appreciate at really high rates for it to become attractive. Something like 12% YoY. Given the current property prices, 12-15% growth may be unreasonable over a horizon of 12-20 years.

2. On the contrary even if your investments generate a modest 11% return they could be worth so much more than the house.

3. As house cost goes up so is the attractiveness of the rent option.

My conclusion:

I have decided not to buy a house although not entirely based on this. I want to give some more time to myself and that little freedom of having a bulging savings account or whatever.

But if you want to play around and find out if the house is actually working out for you can download the excel sheet I have made. Just use the sliders to change the values, have fun. And let me know how it is. Download the house investment decision maker excel sheet

Mutual fund companies often develop this compulsive urge to launch a new product, but sadly mutual funds are one of the boring financial services and all most all of them do one of the following four:

• Retain your capital and provide liquidity
• Give returns at risk free rate by investing in longterm governament bonds etc.
• Give moderate returns by mixing equity and debt prudently
• Take high risks by putting everything in equity and probably generate high returns and (or) tax benefits.

SBI MF seems to have bitten by this bug again and they launched ONE India fund, the basic proposition being the fund aspires to invest in profitable companies from four regions of India viz. South, East, North and West. To be frank, I dont think the profitable companies are distributed bby regions or anything like that. But again, its the same product in a new package to attract masses.

See the campaign images, they are interesting though…

PS: Images from SBIMF