Aadisht was keenly interested in the spreadsheet I referred to in the post below. It so happens that I am inordinately proud of my creation. So, as a service to society, I have uploaded it to Google and shared it with the world.  But:

1. You need to be logged in to google to see it
2. The data is scrubbed. All values are dummies, except the names of the funds in the first sheet – those are a subset of the ones I actually own. But everything else, including folio numbers, prices and number of units is fake.
3. It is writable by anyone.  I had to make it writable for you guys to see the formulas. But please don’t make any changes.
4. On the last sheet are supposed to be two pivot tables. As I have explained, when I upload a .xls file, Google converts pivot tables into simple tables. But the pivot is self-explanatory and  you can recreate it.
5. You can download as .xls for your use. But a stupid bug will be apparent to you if you compare the last sheets. It is trivial to fix it.
6. The spreadsheet is probably unusable unless you understand the thinking behind it. 

The last lacuna, I shall now rectify with the following treatise on how I do my investing.

I have described my spreadsheet as “insanely complex”. The description is somewhat unfair. The bulk of the process is merely complex. The insanely complex aspect can be safely skipped – it adds little value.

Ingredients

In addition to the spreadsheet, you need the following:

1. Some time to understand the spreadsheet.
2. Some time every month, preferably on the first weekend of the month after you’ve got your salary.
3. Your actual cashflow for the past month. (Required only for the insanely complex part)
4. Your expected cashflow for this month. (Recommended)
5. A shortlist of well-managed funds and an easy way to invest in and redeem them online.
6. Some idea of your expenses that you need to budget for over the next few years.
7. An intuitive understanding of portfolio theory
8. A healthy scepticism towards the idea of timing markets or making speculative profits.

Step 1: The first step is to update the sheet titled Transactions. If you are like most people, you probably have some investments already. To get them all into the system is a one time activity (if you are lucky and exceptionally well-organized). Once you have most of them in, this becomes a montly exercise. Every month, you take the previous month’s spreadsheet, save it with a new name and do some magic with it that will tell you how much to invest and what to redeem. These investments and redemptions should be added to this sheet in the next month’s cycle.

Most fields are self-explantory, but some are not:

Purchase date: Self-explanatory.

Folio Number: Self-explanatory, optional.

Fund Name: Self-explanatory. You will notice that “Cash” and “Fixed Deposit” are also “funds” for me. There are no “transactions” for cash. I just update the value in the same row every month.

Source: Over the years, I have bought funds from multiple sources. I use this to filter and organzie stuff.

Purchase price: The price at which you made the purchase. Duh. But let’s say that you have a fund you have made multiple purchases of. You don’t want to enter every individual past transaction into the system. Just enter today’s price into the system and be done with it.  This field is not very important anyway.

Units: The number of units you are buying. Use a negative value for redemptions.

Purchase Value: Obvious. If you have some non-mutual fund type investments (like FDs) or if you have just applied and you don’t yet know how much you have been allotted, just enter the value here instead of the previous two fields.

Last Month’s Price: In your monthly dance, once you have saved last month’s spreadsheet with a new name, you need to copy last month’s current prices (see below) and paste it to this field. If you have made any purchases or redemptions in the last month, this field will contain the purchase or sale price.

Current Price: Just enter the current prices into the system. Skip where it is not applicable.

Last Month’s value: Obvious. Once again, for cash or FD type investments, you can skip the last two fields and directly enter this.

Amount: Current Amount – obvious again.

P&L: Amount-Last Month’s value.

Class: This is the asset class into which your investment falls. I use 6: Equity Index, Equity Diversified, Equity Sectoral, Long term Debt, Short term Debt and Cash. All except the last refer to mutual funds. If you invest in stocks, that will probably be another category.

When you are creating this sheet for the first time, you don’t need to enter last month’s  values. (Or just enter the same values as the current month.) 

Once you have entered all the values, have a look to your extreme right of the sheet.  Field O2 tells you how much money you have. (N2 tells you how much you had last month and P2 tells you what profit or loss you made.) The value O2 is extremely important and will be used at multiple places. This concludes Step 1.

Step 2: Next, go to the “Planned Allocations” sheet.  Column A contains a list of “Accounts” into which you will allocate your money. These accounts aren’t “real” accounts, just conceptual categories. These are things you are saving for, and depending on their time horizon and importance, you will allocate different percentages to them.  

For expenses that are likely to occur in less than a year (like a vacation) I allocate the maximum amount to short term debt. For expenses that are more than a year away and under 3 years away, I allocate a larger proportion to long term debt. For expenses that are 3 years away or more, allocate more to equity. Within Equity, index funds are the least volatile and sectoral funds are the most volatile.  If I were a true believer in the efficient markets hypothesis, I wouldn’t have invested in diversified or sectoral funds at all. But it turns out that in India, because most retail investors are idiots, a well-managed diversified fund can  beat the market, so I invest in a few. As for diversified funds, I allocate very little to them, mostly for really long term stuff.

But this is not the only rule. It also depends on how much risk you can take with that investment.  Let’s say that I am planning to buy a car next September. I put a bulk of the allocation for it to long term debt, but why shouldn’t I put some into an index fund? If the stock market booms, I have a larger amount available for down payment, and if it crashes, it won’t kill me to postpone my car purchase by couple of months.

I have thought a bit about expenses that will not occur in a single lump. e.g. Childcare. It is not a single expense, but many spread over periods of time. In those cases, I take a guess about how much I will need when, and allocate accordingly. When an individual component of that expense becomes clear, (say, school admission…) I will break out that part separately and plan for it. (I haven’t done this effectively yet.)

I have an account called “Long term” where all the money I don’t allocate elsewhere goes. This is my.. umm… retirement fund. More correctly, everything I don’t allocate elsewhere goes here.

There are two special accounts – “Cash” and “Safety”.  The former is for this month’s needs and the latter is my just in case fund. 100% of the Safety account goes into short term debt funds, which I can redeem at a day’s notice.

These percentage allocations do not stay constant, but gradually change month by month. As the expense gets closer, I allocate less to equity and more to long term debt, and then less to long term debt and more to short term debt.

Till now, I have not allocated any actual money to these accounts. This I do in the next step.

Step 3: Next we move to the “Planned Allocation” sheet. The columns and rows look similar to the previous sheet. Not surprisingly, because the column and row headers actually come from there. See the value in J2?  That, you will notice, comes from the first sheet.  Now, look at the “Allocation” column.  This is where I allocate money to the various accounts. To the “Cash” account, I allocate, as a rule of thumb, my expected cash flow for the next month +50K. I keep 1 lac in my safety account. To all the other accounts, I look at the overall fund situation and allocate money.  I try to increase the allocation as the target date for the expenditure comes close.

You will notice that columns B through G get automatically populated based on the percentage allocations. That brings us to the final step of the complex process.

Step 4: Let’s skip to the “rebalancing” sheet. (We will come to the P and L sheet in the “insanely complex” process.)  I don’t have to actually do anything here. I just have to behold the fruits of my labour. There are two pivots here. Let us concentrate on the first one. The “Actual Investment” pivot gets data from the first sheet – the transactions are pivoted by asset class. The table shows how much has actually been invested in which asset class.

The third column, Planned Investment (which, by the way, is not part of the pivot table. It has been created by hand.) tells me how much I should invest in that asset class. This comes from the “Planned Allocation” sheet.  Column D tells me what the mismatch is. If it is negative, I should sell those funds. If it is positive, I should buy. Simple!

Except that here, some amount of common sense is called for.  I don’t really need to balance everything to the last paisa. Equity and long term debt funds usually carry exit loads. I don’t want to sell any equity fund unless I have held it for 3 years. In fact, I don’t want to sell equity till I turn 45.

So at this point, I take a step back. I go back to my percentage allocations and see what the trend is. Note that as long as I am getting my salary, I will always be moving funds out of the “cash” column and into the “long term” account, which will be overweight on equity. So there is no need to precipitously sell equity. Likewise, if there is an FD maturing next month anyway, there is no need to be selling long term debt funds right now. In general,  I never sell any fund if I have to pay an exit load.

So, based on the numbers and common sense, I decide on some funds to sell and some funds to buy. I execute them, and these transactions go into my first sheet next month – and the eternal cycle continues.

This was the complex process.  Where does the need for the insanely complex process come from?

The Insanely Complex Step 1a: There are two problems with the above process.

First,  I noticed that when stock prices were rising during a bull run, my system was telling me to buy more  equity. This does not make sense. According to portfolio theory (and common sense), when prices go up, you should sell to keep the proportion of equity in your portfolio constant.  On investigation, I realised that this was happening due to a small kink in my formulas. In my “Planned Allocation” sheet, the money that does not get allocated elsewhere goes into the long term account. So when there is a bull-run, the value of the portfolio goes up. If I don’t increase my allocation to other accounts, the allocation to long term increases. But long term debt has the highest weight to equity, which in turn means that my spreadsheet told  me to put more money into equity.

Second, I needed a way to find out how much profit my accounts were making. If I have allocated some money to my car fund and the stock market goes up, I need to know just how much  more I can afford for the down payment. 

Both problems have a common solution, which consists of adding an insanely complex step after step 1.  Once you have your investments updated, you need to find out what profit you have made. For this, visit the last sheet, where you have the Pivots. Cast a glance at the second pivot, the P&L pivot.  This tells you the break up of profits by asset class.  The column next to that tells you the profit percentage for that asset class.

Armed with this information, we visit the sheet that we have neglected so far, the P and L sheet.  This has the table structure that we are familiar with by now – only, this time what we have is not percentages or the allocation, but the Profit/Loss that would have been made for that asset class allocation. For example, if my car account has 10K bucks allocated to equity-index which rose 2% last month, my notional profit on this is 200 rupees. Add up the profits across allocations and I know how much profit or loss I made on the car account.

Except that this is not the actual profit. These are the profits on the planned allocations. When I add them up, I get the profit that I would have made if I had allocated my funds exactly as planned. But because I tolerate some deviation, there will be a variance, which should be corrected for.

Wait, there is one more thing.  I am looking at this sheet after I have concluded step 1. In step 1, I have updated my current cash position. This value has increased by the net cash inflow for this month (my salary – cash outflows). I need to correct for this too. This, in a nutshell, is what you see happening in cell M2. This is the amount of correction I should apply. I apply the correction in proportion to the account’s value. This is what you see happening in columns H through K.  Column K tells me what the new Account value should be. I may or may not apply these new values in the planned allocation sheet. Obviously, my cash and safety will not be affected by this. My long term fund will continue to be whatever is left as balance. Even for the other accounts, I may choose to increase or decrease the allocation when I get to step 3 – which is what I meant when I said that this step is too complex and adds too little value. (Oh, and you might want to save the spreadsheet with a new name after this step. As soon as you go to step 2 and update your percentages or to step 3 and updated your allocations, everything in this sheet will become incorrect!)

Thus concludes my narration of the story of this most wondrous spreadsheet.  It has been said in the shastras that both those who hear this story and those who recount this to others will take one step forward in achieving all four purushaarthas. They will stay on the path of righteousness. Their store of artha will increase. They will get laid, and if they understand the true spirit behind this, they will achieve moksha.  Don’t you want to achieve moksha?