2 minus 1 is different from 1 minus 0

The numerical difference between 1 and 2 is the same as that between 0 and 1, but they are not the same. I cite some examples:


1. When sharing food, and there's only one piece left, nobody touches the food. Every time you take away a piece, you deprive others of having another piece. This deprivation is greatest when you take away the last piece, compared to any other pieces. Suddenly from having food in the plate, there's none left now. The more pieces there are, the less the deprivation from taking away one piece. Hence, the speed of taking food from a plate full of it is very fast initially but decays rapidly to near zero when it approaches the last piece. Especially between strangers.


2. Going from 0 pull up to 1 pull up is tremendously difficult. Going from 1 to 2 is easier than going from 0 to 1. I think 90% of the effort is spent training to go from 0 to 1 than any other increment. So the real question is this: Can you do one? If you can, you can do a lot more!


3. If the price of an item decreases from $2 to $1, it's quite different from dropping from $1 to $0, even thought the drop is still $1. What's $0? It's free! Free is so powerful that it makes people want something even though they don't need it. Conversely, an item that is free initially, once you start charging money for it, you'll howls of protest. If you've offered free water and start charging for it now, you better have some PR skills out there.

This has some implication in doing certain things differently.


When you're selling things to others, you might want them to buy something small from you first. This could be the opener to other things. Once they get over the hurdle of buying anything from you, effectively crossing the great gulf from 0 to 1, you can convince them to buy more things from you. This is a trick that is used in bargain shops. One or two items might be very cheap compared to the rest of the market, but when you're there and you bought it, you've just crossed the distance between 0 and 1. It makes it more likely for you to buy any other things since you're already there. Change a non-interested person to an interested person, from an interested person to a small buyer, from a small buyer to a large buyer.


When negotiating, make the other party concede to something small first, effectively breaking their resistance to bigger concessions. Once they crossed the gulf from 0 to 1, it'll be easier to ask them to concede more.


When building confidence, start by doing small things that makes you feel confident. You might want to start a weight loss program and didn't have the motivation to start. Buy yourself  a new shoe, go down and just walk first. Once you get over that big jump from 0 to 1, progressively increase it, making sure you build the confidence to clear small hurdles before climbing bigger ones.


Mathematics is funny when applied to real life with all our emotions invested in our actions. 2 minus 1 is really different from one minus zero.

Read More

What's 14 subtracted from 432?

Sorry, I just had to get this out of my head. I learnt about binary system in JC but I was very bad at it. However, that new number system is so vastly different from the current decimal one that I'm obsessed and fascinated by it at the same time. It had always been on my mind and recently more so. Did I tell you that being moody makes me ultra creative in unexpected ways? It can be in the form of art, poem, music or whatever other forms it takes. It just happens to take the form of a elaborate sibei eng new number system. 

----------------------------------------------------------------

We’re all fixated with the decimal system. We have 10 digits on our hands, and that makes it easier to count, so I guess that’s the reason why our number system is based on 10 digits from 0 to 9. But let’s say we are all aliens with 4 digits, how would our ‘normal’ viewpoint of numbers change? Let’s start from first principles.

If we have 4 digits in our hands, the only numbers that we can work on is 0,1,2,3 and 4 – essentially a 5 digit system.  There’s no digits after 4, so there's no 5, 6, 7, 8 or 9.

Looking at our current decimal system, how do we get the next number after 9? We add one more placing to the left of our single digit, and reset the number 9 back to 0. So after 9, we can 10. Back to our 5 digit system, what’s the number after 4? We add one more digit to the left of our number and reset our number back to 0. So, after 4, we get 10. Let’s run through the whole number sequence:

For decimal:

0,1,2,3,4,5,6,7,8,9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21…

For our 5 digit system:

0, 1, 2, 3, 4, 10, 11, 12, 13, 14, 20, 21, 22, 23, 24, 30…

So what happens after we reach 40+? 40, 41, 42, 43, 44. What’s the next number?

By right, we should add one to the left of the digit we’re currently working on, and reset the digit back to 0. But since both digits are 4, we have to reset the numbers and add another digit to the left again. We end up with 43, 44, 100, 101, 102, 103, 104, 110, 111 and so on and on.

That’s for addition.

In principal, subtraction is the same as addition, except you’re going backwards. How do we do 432 – 14 in our new 5 digit number system? The best way to do this is to convert back to decimal system. Every 10 in our new system is equivalent to a 5 in decimal system, so 20 is 10 in decimal, 30 is 20 in decimal, and 40 is 30 in decimal and so on and on

Every 100 in our new system is actually 25 in decimal, so 200 is 50 in decimal and 300 is 75 and so on.

In other words, the hundreds placing of our new system is really how many multiples of 25; the tens placing is how many multiples of 5 and the ones placing is just ones. In our new system, if we have a number xyz, then the conversion of this number to decimal is just 25x + 5y + z

Therefore 432 is equivalent to 4x25 + 3*5 + 2 = 117 and 14 is 1*5 + 4 = 9. Since 117 – 9 = 108 in decimal system, we need to convert back to our new 5 digit system.

108 is not entirely divisible by 25, so the best we can do is 4 x 25, and we’ll have a remainder of 8. Next, we try dividing 8 by 5, the best we can do is 1 x 5 and we’ll have a remainder of 3. So, 108 = 4x25 + 1x5 + 3. In other words, 108 in decimal is 413 in our new system.


432 - 14 = 413

Can we derive a new method of doing subtraction by hand? Sure.

This is essentially the problem.  Looking at the ‘ones’ placing, 4 cannot be subtracted from 2, so we need to take away the ‘tens’ placing (i.e. 3), knock off 1 and add 5 (not 10 because we’re doing a 5 digit number system) to the ‘ones’ placing. Like this:

Looking back at the ‘ones’ placing, 5+2-4 = 3. And 2-1 = 1, so we have:

Essentially the same answer if we were to convert the number 432 and 14 back to decimal, carry out the subtraction, then convert back to 5 digit system.


This means that our operation of numbers is very robust in the sense that besides being able to work on the current decimal system, even by restricting the digits available at hand (from 10 to 5), it can still work. I haven't tried multiplication, which is really another form of addition, and also division, which is really another form of subtraction. From there on, it can develop in powers (another kind of multiplication) and what other operation out there.


Is this important? Nope. But it's certainly fun for me. I'm trying to answer this age old question. Why is 1 + 1 = 2? The answer is that it need not be 2. It really depends on which number system you're using. It can really be 10 if you're using binary for example.

--------------------------------------------------------

Hey, while showering, I just came up with a formula to convert any base system to our standard decimal one! If the base is y and the numbers are abcd, then to convert it, you just have to do this:

a(y^3)+b(y^2)+c(y^1)+d(y^0)

For example, if we want to convert the binary number (base 2) 10110 to decimal, we just do this:

1(2^4)+0(2^3)+1(2^2)+1(2^1)+0(2^0) = 22.

Or if you want to convert 432 (base 5) to decimal, you just do this: 4(5^2) + 3(5^1) + 2(5^0) = 117!

EUREKA!

Read More

I played 800 games of 100 coin toss

You have to read this article before this to make sense of what I'm saying here.


After putting up the probability distribution and showing that after playing 100 toss of coin, where each head gives you $2 and each tail takes away $1 from you, will not always give you a positive returns, I was still unsatisfied.


I went ahead and did a monte carlo simulation of the game on excel. Using RAND() function, I will get a random value between 0 and 1. I programmed it such that if I have a number bigger or equal to 0.5, it'll be heads and I'll assign a value of 1 to it. If not, it'll be tails and I'll assign a value of 0 to it.

IF( cell >= 0.5, 1, 0) 

After tallying the number of heads and tails, I proceed to count the gain/loss from this 100 toss game. I did 800 simulation of such 100 coin toss, effectively throwing 80,000 coins in my computer program.

HUAT AH!

Here's the results of all 800 such games of 100 coin tosses. The numbers in each cell represents the gain/loss from playing the game:

I highlighted 2 cells in yellow because this is what I get after playing 800 such games - I've two games where I have negative returns! The probability is higher than the initial theoretical calculation. The theoretical probability of ending up having losses after 100 coin tosses is 0.043686%. Based on this monte carlo simulation, I have 2/800 or 0.25% of ending up not being profitable.


I calculated the average score of all these 800 games of 100 coin tosses, and it turns out to be $49.43875, which is very close to the expected value calculated of $50. Just for interest sake, the mode (the sum that occurred most often) of all 800 such games is $53 and the median is $50.


There! The statement that after 100 coin tosses, you always end up being profitable is not true. Combining the new found skills in excel simulation with real life examples is really really interesting LOL

Read More

THAT statement is probably improbable

I saw this article from the fifth person while having mee goreng for lunch. The article talked about how you can increase your wins in investing. All in all, a good piece of article. Sound investing advice. The only complain that I have is that it's not sound mathematically. I'm being anal about this, of course, and it's all because of one word. But let's hear my arguments.


You play a game of tossing a fair coin. If it shows heads, you get $2. If it shows tails, you lose $1.


So what's the expected gains/loss from playing such game? It's 2 x 0.5 - 1 x 0.5 = $0.50. So the article mentioned that if you play one such game, you might not get $0.50, but after playing a large number of games, notably 100 such tosses, the law of averages will kick in and you will earn $50. The article went on to say that "You will always end up profitable after 100 tosses".


Eh...something isn't quite right leh. My mind is attuned to seeing the word 'always'. I get doubts whenever I see that word. Always means 100%. So you mean I will always be profitable after 100 tosses? I've strong issues with that statement that you will always end up profitable after 100 tosses, because it doesn't sound right. Let's test it out.


100 tosses of a coin with a 50% chance of getting heads and 50% chance of getting tails is just a binomial distribution with number of trials as 100 and probability of success, here defined as getting a head, as 0.5. So I let X be the number of heads you get from throwing a fair coin 100 times.


I used an excel spreadsheet to calculate the probability distribution of X. This is a random variable that can only take values between 0 and 100, inclusive. Also, if I have 2 heads, i.e. x = 2, I will lose $94 ($2 x 2 - $1 x 98 = - $94). Below shows a screenshot of the spreadsheet that I did:

This goes on and on until we first see the losses, represented by the strings of negative values, turn positive. That occurs when we have 34 heads and 66 tails, getting us a grand total of $2.

This means that if I get 34 heads and above (i.e. x = 34 to 100), I'll end up having gains after 100 tosses. The probability of getting gains is therefore to sum up all the probabilities from x = 34 all the way to x = 100. That gives me 99.95631%. Quite high, and quite certain, but it's far from 100%.


Conversely, if I get anything from 0 to 33 heads, inclusive, I'll end up having losses after 100 tosses. The probability of losing is thus 0.043686%. Again, it's not certain that we will definitely win. There's a possibility of losing, just that it's highly improbable.


And of course, the expectation of these 100 tosses is still $50 ($0.50 per game x 100 games). I'll be rather careful to say that just because the expectation is $50, you'll always end up being positive after 100 such games. The expectation is just an average of 100 games and we all know what's the perils of using averages. If the average water level in an unmapped ocean is 1 m, does it mean that suddenly, you won't drop into a deep trench of 10 m deep?


Since it's binomial and the distribution is symmetrical, the mode of the distribution, which is the most likely gain/loss you get after 100 toss, will be $50 (corresponding with x = 50), with an attached probability of 7.95892%.


The best that I can say about using expectancy is this: If it's positive, the odds are in your favour. If it's negative, the odds are against you. Probability doesn't mean much to me, having seen my father-in-law winning lotteries almost every month. Yea, so what if the probability is small, as long as there's a chance, no matter how improbable it is, somebody has to win it LOL

Read More

Realize (the value of time)

This is a fantastic poem called Realize, author unknown. I first saw it hanging as a poster in my then girlfriend's room. This poem captured both the essence of time in the viewpoint of different people in their unique circumstances. Take this as the sequel of my previous post "Time slipping through my hands like sand".

Realize
-----------

To realize the value of one year, ask a student who has failed a final exam.

To realize the value of one month, ask a mother who has given birth to a premature baby.

To realize the value of one week, ask an editor of a weekly newspaper.

To realize the value of one hour, ask the lovers who are waiting to meet.

To realize the value of one minute; ask a person who has missed the train, bus or plane.

To realize the value of one second, ask a person who has survived an accident.

To realize the value of one millisecond, ask the person who has won a silver medal in the Olympics.

Time waits for no one. Treasure every moment you have. You will treasure it even more when you can share it with someone special.

Read More

Time slipping through my hands like sand

It's nearing the end of Feb. Soon it'll be March and it'll be another quarter of 2015 gone. Just like that.


It's crazy when you sit back and observe the flow of time. It's slowest when you have nothing to do and you keep looking at your watch, urging with all your mental strength for the minutes to tick by faster. It's fastest when you're the busiest, secretly wishing that time will slow down for you to do that little bit more. Since I felt that time passes without me noticing, I must have been busy.


Work-wise, I don't recall having been this busy in this usually quiet first few months of the year. If I didn't control, I would have been head to toes in work by Feb, which is highly unusual. Am I complaining? Yes and no. Yes because I felt that I had not fully rested. I've been running into this year since last year. No, because my salary increases with more work hours. I'm busier hence I earn more. In terms of my saving goals, I've a few more months to earn before the traditionally drier seasons, so it's highly likely that my savings goals will be reached, and likely sooner.

My ideal kind of retreat

That should have been a happy occasion, worthy of celebration. But I stand here, slightly ambivalent about the achievement. What's happening to me? No longer a money whore? No longer pursuing financial freedom dreams? Not that, it's just that these days, I'm more aware of the cost of reaching any goals. And I'm slightly weary of it. At the back of my mind, this question keeps lingering on my mind - "so what?". So what if you achieved the savings target? It'll be like that the next year and the next year and the next year until a bigger target is achieved. And then what?


Is there a stop to all these? I think there is, but it's far far away. And in the meantime, I better do something to prevent myself from burning out. It's a long journey ahead, and I'm determined to finish happy. Perhaps this long CNY break, which prevents me from working, is making me slightly depressed. I know how ludicrous I sound, but my work is a savior to me in multiple ways. For one, it tempers my mood and usually during my off peak season, I almost always feel slightly moody.


In terms of health, I managed to do 3 clean chin ups. Still working on it, and doing it every other day with my wife at the pull up bar. She's getting stronger too, but still can't clear that important hurdle from zero to 1. Will be switching to palms facing outwards soon (technically a pull up) and fully expecting a drop in the number of counts.


I also managed to finish 10 books and I'm into my 11th book now. 1 book a week means I should have done 9 books, so I'm slightly ahead, but not too far. I'm reading slower because I've to prepare for some new modules to teach, and that takes up some time from me. I'm actually quite happy to stick to my reading schedule. Usually I will read more during my off peak seasons, but I'm trying to see if I can stick to a more regular kind of reading schedule, which means I can probably read more than 52 books a year. That would be amazing! Imagine the incremental knowledge that I will have by reading just more books that others. I'm not a scholar by appointment, but I think I'm one by habit.


I started this article feeling down but finished it feeling a little more upbeat. I think it's great counting our blessings!

Read More

Thought experiment on being FF

What will I do after I have a million dollars? A million dollar is figurative, it might not really be one million dollars. Could be 2 million. The point is, what will I do if I have the financial means not to ever work again?


Quite resolutely and with much certainty, I'll continue doing what I'm doing. I don't hate my job; in fact I like it very very much. There's a lot of meaning and it brings me a lot of satisfaction and joy beyond the obvious financial gains. What I'll do is to reduce the intensity and the duration of my work hours. I think I'll be a little more selective in my students and take in the ones that can benefit the most from me. I would increase the number of students that I help for free or at a very low rate. Perhaps I'll go back and join SINDA and CDAC to help out students.


What else would I do? I'll probably give a lot more money to my parents. More importantly, I'll spend more time with them. Not everything can be solved with money, but those that can be, I should be able to solve it easily without affecting me too much. If I have 1 litre of water, sparing 500 ml of water might be substantial. If I've 10 litre, 500 ml of water doesn't seem like much anymore. My solution to such money woes is to grow big enough to make the problem seem insignificant.


What else can I do? Get a nice mini cooper for my dear wife. It need not even be new (for me, I doubt if I'll ever buy a brand new car), but at least it must be worth it. She likes one and probably find that a very useful tool and nice thing to have and to use in her everyday life. The good thing about having the means is that you can settle all the needs and indulge in a little more wants. Can I afford one now? Sure, it'll be a pre-owned car with 3 year left and it'll cost me 60 to 70k. Is it worth it? Definitely not, hence I'm reluctant to do it despite her numerous attempts to convince me.


At this point, I'm a little flabbergasted. I've a hard time thinking what I'll do with the money. Is it because I'm contented? Or is it because I have limited imagination? It's hard to say. Maybe after not having grapes since young, I started seeing those grapes as sour and eventually no longer want those grapes at all now.


Is there anything that I really want?  I could do with a new desktop, but the problem is that I like old things. The older they are, the more memories of me using the things and hence the more precious it is to me. Says the person who had only 1 mechanical pencil that I used for about 18 years. New things? Cool, but I might not like it. How about more experiences? I could do that. Perhaps more trips overseas? The furthest I've ever been is to Taiwan. Maybe Aussie, but that's because of army, so maybe it's not counted. I suspect I don't really like going overseas. An ideal relaxed day for me is a book in hand, at home, without a care for time and commitments. This will be punctuated by my wife asking me to go out for a short tea breaks at nearby cafe and just sharing a cake or two and watching people and talking about everything and yet nothing at the same time. This can happen anywhere, and especially near home and need not be overseas.


What if I'm financially free but my loved ones are not longer there to share it with me? I'm quite sure I'll bury myself in work. Probably I'll work harder than I ever had, so that I can find more meaning in future days where the sun no longer shines and no cakes and teas ever taste the same again. I'll probably not go to those cafes that we had been together - it'll likely be too painful for me. Oh, very likely I might live overseas and seek a different life altogether.


So typing this post and reflecting, maybe it doesn't really matter if I reach financial freedom at all. Do I need that million dollars to start doing all this? Not really. It'll just be more comfortable and probably it'll feel psychologically more secure. Maybe I can just say screw it and get what my wife desires - a mini cooper. Does 60 or 70k really matter? At most it'll slow me down by 1 year or 2 years, but the important thing is that I get to share something important at the right time with the right person. I'm saying this not just about the car, but everything else in general.


I realised deeply that money isn't very important for me but I'm concerned about it enough to track my expenses daily, invest in the stock market, have money goals and saving goals. Why? Is it because I want to reach financial freedom? I think maybe it's not. You don't see me talking much about financial freedom in my blog and I'm certainly not obsessed about it. To me, the mindset is that if I get it, I get it. If not, life goes on. I'm quite certain I'll be a millionaire though. It's just a matter of when. The trajectory in my spending and earning patterns certainly points me in that direction. And then what?


So let's me totally honest. What I really really want is just security. If I don't address that, even if I have 1 billion dollars in my bank, I'll still feel the same. Can I give more money to my parents now? Sure. Not as much as I could have given if I have a million dollars now, but I can certainly give more.  Can I afford to take up less students and instead do more volunteer teaching? Sure. My earnings might take a hit but that's still okay. I have to think that earning another 1k might not be worth as much as the satisfaction in mentoring a person when I volunteer my time. Can I buy a car that my wife wants? Sure, not a brand new car, but a pre-owned one that we can still use for another 5 yrs perhaps? I'll set me back by a few years, but so what? Can I buy the desktop upgrade that will cost me perhaps 2-3k? Sure, I can, but it's certainly not the priority given my penchant for old things.


That clarifies a lot of things. The difference between me being officially financially free and now is just simply the level of security that I'll have. I should spend more effort relaxing and getting comfortable with spending rather than racking my mind to earn more money.


Self-enlightenment.

Read More

Why I read blogs

My daily morning routine for the past few years had been quite constant. The first thing I do is usually to go to TheFinance.sg to read what other people are blogging about in an area of interest known as 'Personal finance' and 'Finance'. TheFinance.sg is actually a blog aggregator, which means that I just have to access one site to see a range of different sites that contributes to it, saving me a lot of time searching for similar themes and topics.

Do I read the newspaper? I did subscribe to it, though it wasn't my idea at all. I would have preferred not to subscribe. I think newspaper as a source of news is not at all a good idea. News comes in randomly, and the value of a news article decays rapidly with time. To be printed on a national newspaper, you have to get a reporter on the spot, churn out a report, verify the factual information, send it to the editor to approve and finally publish it physically as a newspaper before distributing for public consumption. By then, the short half life of news articles would have rendered it old news already. Which is why social media like facebook and especially twitter, is a better source of breaking news, though often unverified.

So what's the use of traditional printed material like newspaper and news magazine?

It's for their opinions and editorial pieces, where people analyse and give their viewpoints on how the news relate to us. These half digested news piece saves us the trouble of thinking through each and every news article, and probably generate new thought sources and reflection. These opinions of facts, necessarily means they are subjective, but that's the thing I like about them. They don't pretend to give general good advice for everyone. It's the same news but it's seen through the lens of someone with his own usual prejudices and baggage. 

Blogs often do give subjective viewpoints. We might all be talking about a particular news, but because we all are different, we'll focus on different parts of the news that are important to us. The sum of parts of many subjective interpretations makes it more objective than any individual subjective parts. If you read widely, especially from different camps, you'll end up having both sides of the argument, and then you can proceed to make your own judgement about the situation.

That is how we should treat all sources of information.

The other main reason of why I like to read blogs is because it's an immersion of this subculture that makes it harder for me NOT to succeed. This subculture of a financial warrior, striving hard to save more and earn more and invest more with the aim of being financially free is weird and when we talk about this subject matter to the everyday common people, we might be seen as... unsociable. Imagine telling someone that our food budget is reached, so is it possible to eat something cheaper today? It's an outright turnoff.

But having gone out with a couple of financial bloggers, we all know what we want. We'll totally understand if someone's budget is nearing a certain amount. We can totally sit down and talk about deep issues on investment and personal finance matters freely. This is just an extension of our online persona into real life. Nothing weird there. In fact, it feels like we're just bantering and commenting on each other's blog as usual, albeit using our body language instead of mere emoticons.

To be immersed in a conducive environment where it's normal to save more than 50% of your income and not drape yourself in fine clothes and fine watches is like a baby bird watching eagles fly. This is just everyday normal life, nothing extraordinary and nothing that we can't do ourselves. 

I can't tell you how enlightening and free this can set your mind. Everyone who wants to have a shot at financial freedom ought to be immersed in such an environment.

Read More

Planting a bamboo is insane

Can you imagine sitting still for years, doing the same thing again and again while waiting for the results, if any, to show? People might have called this the definition of insanity - doing the same thing again and again yet hoping for a different results. But there's exactly how a Chinese bamboo tree takes to grow.


Most tree will grow steadily, so you can definitely see progress for the effort of taking care of them and watering them. The Chinese bamboo is different. For the first year, you see nothing. So you water them and take care of them. For the second year, you see nothing. It's alright, you continue to water them. For the third year, you also see nothing. Now, you're starting to feel anxious. Did I plant them correctly? Are they dormant and waiting to burst out of the soil or dead and buried in it? You can't exactly peek inside to observe! For the fourth year, as usual, you see nothing indicating that they are still living. Then, in the fifth year, just when you're about to give up hope, an incredible thing happens. The bamboo shoots break ground and grow to a height of 27 metres in one year!


Some people like to compare investing with fishing. I don't. Fishing sounds so...predatory. I prefer to compare it to gardening. The first few days of planting a seed is the hardest to swallow. You plant it, and everyday you'll go and water and see if there's any progress. Sometimes, after waiting for a long period, you might or might not see results. I ever waited months, watering every day to know all my tomato seeds is dead from the start. It's the best test of patience and discipline. Despite how it sounds, doing the same thing again and again without seeing any progress (might even be a regress) is not easy at all.


Sitting still is not easy.

To overcome this, they used to say we should be motivated. I think to stay motivated like that is insane. Motivation is a positive feedback cycle of seeing positive results from your efforts. If you don't see anything, it's hard to stay constantly positive. I think having a system is better. You just keep doing the same thing day in day out. If you do it, consider that as a job well done! This way, you segregate the results from your efforts and makes it possible to sustain for a much longer period of time. And when you finally see progress, the positive feedback cycle for motivation will continue to feed the system.


That being said, always be ready to give up a foolish venture. What? Doesn't that negate everything I've said?! It takes intelligence to acquire knowledge, but it takes wisdom to know exactly when to apply what knowledge. So what am I saying? Nothing and everything at the same time.

Read More

How to do a 2 variable data table

I was going to teach a student business analytics, which is something I've never really done before. So far so good, it's half statistics, which I already knew but more application type of questions (instead of theoretical ones) somewhat related to making sound business decisions. The other half is about simulation and modelling using mainly excel. I've always wanted to learn the deeper end of excel but never really got the motivation to see down and try out all the fancy stuff. It's not just about learning it, but also applying it to my daily life and see how I can hack out some more productivity out of the things that I do. That's more important for me.


I'm at best a novice user of excel. The formula part of the excel is not a problem for me. It's the more intricate data crunching part that eludes me. So if you're already an excel expert, don't laugh at my feeble attempt.


I happened upon this question where they set up a 2 variable data table to see how it varies the final output. Fancy stuff, didn't even know I could do that! So here's me sharing how to do 2-variable data table, step by step.


So here's my problem: I want to get a sum of money in 30 yrs time. I want to find out how much I can get by using this formula:

Future value (FV) = Present value (PV) x (1 + Rate/100)^time

There are two input variables in here, namely PV and R, and one input constant, which is time. My output data will be FV. Time is fixed at 30 yrs, and the two input variables are rate (of investment returns, at %/pa) and PV (which is the amount that I'm going to invest right now). I want to see how the two variables will affect the output, which is the future value FV, given this particular set of constraints.


I limit my PV to go between $5k and $100k inclusive, and my rate between 0.5% and 8% inclusive.


Step 1: Key in the data in excel.

The cell B7 is using the formula: = C4*POWER((1+C3/100),C2). It doesn't matter what rate or PV you used initially. I used 0.5 and $5000 at first, but it doesn't really matter.


Step 2: Key in the rows and columns.

The rows I let it be the rate, ranging from 0.5 to 8 with incremental steps of 0.5%. The columns I let it be the PV starting from 5k to 100k, with incremental steps of 5k. Do note that the rows and columns must be to the right and bottom of the cell B7.

Step 3: Highlight cell B7:R27, as shown below

Step 4: Go to Data / What-if-analysis / Data Table and click on it.

Select row input cell as C3 and column input cell as C4. Basically you're telling them that you want the row of cells (C7:R7) to the right of B7 as the Rate (C3) and the columns of cells below B7 (B8:B27) as the PV (C4).


Step 5: Done!

You can now analyse the data based on the different inputs of rate and PV. For example, if you want to reach 200k at the end of 30 yrs, you can do a few options:

1. Put in 20k and invest at 8%
2. Put in 35k and invest at 6%
3. Put in 80k and invest at 3% and many other options


This is seriously going to be helpful for me!

Read More