At my day job (being a financial planner), I take care of the investment of many individuals. While most of them are concerned about their yield or about the latest moves done by the portfolio manager, almost all of them ignore or diminish the importance of asset allocation. I’ve written about etf asset allocation and how building your own asset allocation model is important. In fact, the asset allocation (e.g. diversification) is the most important factor that explains the yield you are getting.

Asset allocation by age

Unfortunately, there are no magic tricks to find the perfect asset allocation. Some say that you are better off with index ETFs while others will preach for a more balanced approach with a mix of secure bonds and equities. The perfect asset allocation is not the one which will make you rich but rather the one that will fit your investor profile. One of the key factors in determining your investing profile is your age. While it’s not the only factor to take into consideration, you can usually manage your asset allocation according to  your age:

Asset Allocation from 18 to 35

While you should not be having much money to invest during this period, this is where you should risk the most. Technically, you should not need the money you invest for retirement for a good 30 years. This is the perfect time horizon for an investor.

I would then think that an asset allocation with 90% to 100% in stocks would be ideal. You can invest through mutual funds or index ETFs as well as trying to build your own stock portfolio. Why should you select such an aggressive asset allocation? Simply because it will be the type of portfolio with the highest expected yield over time. Investing in bonds at such early age will minimize your profit expectancy for nothing. If you are inexperience, I would suggest you leave the temptation of being your own broker and you adopt a “coach potato” approach through ETF investing.

Asset Allocation from 36 to 50

This is usually the time of your life where you get a better job (therefore better salary!) but also where you get most of your expenses. Several people in their 30’s will find the perfect one and fund a family. With the family often comes 2 cars and a house… so a lot of expenses!

You probably don’t have much time to take care of your asset allocation and you might not have much money to invest (then again!). however, it is important to start investing as soon as possible and if you have not started yet while being in your 30’s, you will be running late to fund your retirement portfolio.

Since you might be a little bit concerned about your investment fluctuation since you have several financial obligations, I think that having a small portion of your portfolio into bonds should be a good thing.

Try to aim for an asset allocation including about 75% of equity and 25% of bonds. At your age, you still can afford a lot of risk and you should not be shy to take them. The 25% in your asset allocation will smooth your investment returns during important crisis but won’t slow down too much.

Asset Allocation from 51 to 65

During this period, you will be about to retire and definitely be thinking of your withdrawal strategy. However, it’s not a reason to freak out and secure your asset allocation to the maximum either!

Since you won’t be withdrawing much of your investment during at that age, you can still handle some market fluctuations. Going from a growth to a more balanced asset allocation seem logical. Therefore, a 50%/50% asset allocation approach would allow you to earn some decent investment returns while not suffering too much during market crashes.

At that age, the last thing you want is to see your investments going down by 20% or more!

Asset Allocation from 66 and older

This is for sure, you will be retired during this period of your life. If you have been investing throughout your whole life, you should be sitting on a solid nest egg. There are no reasons why you should risk in the name of higher returns.

A more secure asset allocation showing a 75% to 100% bond/cd’s portfolio is reasonable. You will simply have to manage your withdrawal schedule and avoid to have too much money concentrated into one bond. I would also leave the world of ETF’s to concentrate on CD and bond holdings.

Final thoughts on asset allocation by age

If there was only age to manage when we are talking about asset allocation, things would be pretty easy! However, this is far from being that simple. In fact, age is only a mathematical data that doesn’t take into consideration your risk tolerance. You might be young enough to support a big market drop as you will have time to play with you to gain it back, if you are about to have a heart attack when the market goes down by 5%, you won’t last until your retirement!

On the other side, I personally think that I will maintain a very high level of stocks until I retire. I don’t really mind about market fluctuation and I believe I can earn higher returns by staying invested in the market. I must also admit that I have a good portion of my portfolio which is invested in dividend which are less risky than “regular” stocks.

Good morning and welcome to another one of our excel tutorials. This time we are answering to a common question that we get which concerns using ranges in conditions. Here is the question:

If marks is 40-60 text should be “PASS” if it is more than 60 & less than 75 First Class , If more than 75 “Disti”.

It looks like a simple task but as many of you know, it is much more tricky than you would initially imagine. To make things a bit more complex, here is the range we will decide for our student grades:

0-49%: Fail
50-59%: Pass
60-75%: First Class
76%-100%: Distinction

As is always the case in excel, there are many different ways to get this done but we will try a few here, let`s start by getting a sample of students. Obviously, the goal is to make it as easy to correct 10, or 250 students!

There are many different ways to get it done but in this case we will simply be using conditions. It will look like a difficult one but you simply need to be disciplined when you write it. Doing it by steps

Step #1-If the student got under 50% = fail

=IF(B2<0.5,"Fail","Pass") Step #2-If the student got under 50% = fail, if he got under 60%, he “passed”

=IF(B2<0.5,"Fail",IF(B2<0.6,"Pass","Good")) Step #3-If the student got under 50% = fail, if he got under 76%, he got “First Class”. If none of those conditions are filled, he got a “Distinction”

=IF(B2<0.5,"Fail",IF(B2<0.6,"Pass",IF(B2<0.76,"First Class","Distinction"))) What you are doing is simply adding a condition. Step by step, you are looking at: -Did the student fail? -If he did not fail, did he get under 60%? -If he did get it 60%, did he get 76%? It looks like a difficult argument to build but if you do it one step at a time, it actually works very well. Please note that you must do these in order.

As is always the case, we’d love to get your comments or questions! Thanks in advance!

Incredulous. Dismissive. Or worse. How else can someone with a hard-earned MBA degree feel about the concept behind “The Personal MBA” experiment? For those who are not familiar with “The Personal MBA,” it is the brainchild of Josh Kaufman. Mr. Kaufman earned his MBA from the University of Cincinnati. After graduation, Josh apparently decided that MBA programs might just be a waste of time and money. Josh therefore started www.personalmba.com. There , Josh listed 99 business-related books–and inferred that anyone who read all 99 of these books would have core business knowledge similar to that of someone who actually earned his or her MBA.

Personal MBA Experiment

But first, let me introduce myself. My name is Chris Thomas and I am a new staff writer here at “Experiments in Finance.” I am the former creator/owner of Broke Professionals and now own and run FreelancePF–a freelance writing business. Now that we are properly acquainted, I must first confess something: unlike many of our audience and the owners of this site, I do not have a MBA. I do however have a J.D., and I know reading a bunch of “the right books” could never fully assimilate the experience of law school. If I had a MBA and someone who had simply read 99 books told me they had a MBA equivalent–well, I probably would not take that too well. Nor do I believe an employer would give a raise or make a hiring decision based on a”Personal MBA.” From what I have read about MBA programs, there is a major emphasis on leadership and teamwork. MBA Students generally already possess core business knowledge, and many even work full-time–so the emphasis of the program is more on doing as opposed to passively reading. No matter how many books you read, in my opinion, that will never replicate the process of actually doing. Too much of our education takes place in the theoretical already.

My Personal MBA Confession

I have another confession to make. If, dear reader, we are going to be collaborating together over the coming weeks and months, then I want us to start out our relationship honestly. I must confess that I am partaking in the “Personal MBA” challenge. Before you rush to judgment, first hear me out. I will never say that I have a MBA. I know I will never possess the business acumen of those that have completed a legitimate MBA program. At this point in my life, however, I simply have too many student loans from my law degree to enroll in a MBA program. I would love to be able to, but it’s not practical at this time. So, for someone like me, I do believe the reading list in the “Personal MBA” program provides a good introduction to basic communication, finance, marketing, and various other business-related skills.

Can the “Personal MBA” Rival a Traditional MBA Program?

The idea that the “Personal MBA” can rival the education taught in a traditional MBA program is, however, starting to gain some traction. None other than entrepreneurship guru Seth Godin appears to embrace the “Personal MBA” program. Seth Godin, in an interview at the “Personal MBA” website states: “I think instead, getting the network and the education (without the very expensive diploma) frees you up to accomplish some amazing things, to work your way through Dips that MBAs are too busy to get to.” Perhaps more troubling, Mr. Godin goes on to state: “I think what’s missing from the Personal MBA is easy transferability. In other words, it’s not trusted yet. Safety-seeking managers don’t trust themselves to hire people with a PersonalMBA. The good news is that every day, more people with a Personal MBA end up in hiring positions, or end up doing great things… so it is spreading, and it’s getting more powerful every day.”

Where’s The Opposition to the Personal MBA?

There appears to be very little formal opposition to the concept of the “Personal MBA.” Even New York Times best-selling entrepreneur writer (and big fan of quality education) Ramit Sethi (of I Will Teach You to Be Rich Fame) says on his site that he considers Mr. Kaufman a friend (and has had Mr. Kaufman as a guest-poster on I Will Teach You to Be Rich)–even though Mr. Sethi does not agree entirely with the idea of a “Personal MBA”. I am sure most people would agree that credentials don’t really matter as much as whether someone can competently perform their job. At the same time, however, I do not believe I will be listing my “Personal MBA” on my resume anytime soon. (besides, I have not yet “graduated.”) Conclusion I look forward to writing for you and reading your comments in the coming weeks and months. Let’s get the conversation started out with these two questions: 1) What are your thoughts on the idea of a “Personal MBA”? 2) What % of a MBA program is immune to a purely reading-based curriculum. I will be sure to respond to your comments– in between reading a “Personal MBA” reading-list book on negotiation that is. I’m starting to feel like a “master of the universe” already.

It’s a common problem but one that often seems difficult to manage. Last week, we discussed the use of “iserror” when doing multiple conditions and that certainly seemed to be of interest. Unfortunately, excel is not very good at managing errors. Try to do a vlookup on a value that excel cannot find and not only will you be unable to do anything with that cell but any reference to that cell will turn out as an error as well. It can become very frustrating and can make things very difficult to manage.

While it’s not always the case, I personally usually prefer when excel can keep the calculation and the spreadsheet working while possibly giving me notice of the error at the same time. A challenge? Not necessarily. Today I will discuss the use of the “iserror function” and how you can use it to keep things working while still seeing that there are possible issues.

First off, let’s take a look at a possible spreadsheet that would require the use of the iserror function

Imagine a company that has a sales team. For each of these sales people, they give a different commission rate. At the end of the month, they would want to easily know how much sales were made and how much commission should be paid out. However, using the vlookup function would generate errors each time a new sales person is added. While it’s practical to have a “notice”, it’s not always ideal to have to correct all of those before getting an estimate, especially in a big company. Take a look at the following spreadsheet

What could be done? One possibility would be adding the missing sales team members on the left and we can all agree that it will need to be done at some point. However, a short term solution could be setting a “estimate of 7% commission” for missing members. How to do it? Instead of using the straight vlookup such as:

=VLOOKUP(D2,A:B,2,FALSE)*E2

I will use an “iserror” such as:

=IF(ISERROR(VLOOKUP(D2,A:B,2,FALSE)*E2),7.5%*E2,VLOOKUP(D2,A:B,2,FALSE)*E2)

What will it look now?

Much cleaner right? And it gives a decent estimate of the commissions to be paid out. The problem of course now is to find those errors. How to do it? I would personally create a column just on the right to detect errors by doing the following:

=IF(ISERROR(VLOOKUP(D2,A:B,2,FALSE)*E2),”MISSING”,””)

You could make it even more explicit by adding conditional formatting:

And it looks much better:

Any thoughts? I’d love to get your comments on this!

We have been writing on this blog for a few months now but had not had the opportunity to discuss some excel topics. Well’, we are now ready and should be discussing a few in the next few weeks. First off, we’ll start with a question that we received last week. Please feel free to send us any excel related questions and we’ll do our best!  Here is the first one:

I am trying to find a formula to help me come up with the following results.

Below is the example. Note:Bryan, ryan, Bryce and Ryce are Internal Buyers and rest of them are External buyers.

Basically I am trying to get the following result

Result 1 = If buyer for any of those co. is Internal buyers or if the cell is blank then put an X there.

Result 2 = If buyer is External buyer(at least in one of the co.) then put X there.

The question seems complex but if you read it carefully, this is simply a case of using multiple conditions. What do I mean? For each product, we are attempting to find if at least one of the buyers is “External”. As in any other problem, there are many different ways to resolve this. I will go ahead and show you how I would resolve it.

Step #1 – Determine who the “Internal Buyers” are:

I will simply set up the name of those buyers and will then be able to reference them. So here we go:

Step #2 – For each product, determine if the buyers are external or not.

In this case, what I am looking for is confirmation that at least one buyer is external. How will I do this? Simply by using a vlookup. If the vlookup result is false (the name is not on the list) and there is one buyer, it means that the buyer is external. How am I doing it? Simply by adding a column. I could add one column per Co.A so 4 columns but in this situation, I simply need to verify if at least one of them is external. So here we go:

The formula in H2 is:

=IF(B2<>””,IF(ISERROR(VLOOKUP(B2,J:K,2,FALSE)),1,VLOOKUP(B2,J:K,2,FALSE)),0)

Which means: If Co.A has a buy (is not empty), look in the column j. If you find it, great, put 0. However if you do not find it (and have an error), please put 1 instead of the error. In this way, I can easily tell that for both Apple and Banana, the buyer from Co. A is external.

I will simply do the same thing for the 4 Co.

The result in cell H2 is:

=IF(B2<>””,IF(ISERROR(VLOOKUP(B2,J:K,2,FALSE)),1,VLOOKUP(B2,J:K,2,FALSE)),0)+IF(C2<>””,IF(ISERROR(VLOOKUP(C2,J:K,2,FALSE)),1,VLOOKUP(C2,J:K,2,FALSE)),0)+IF(D2<>””,IF(ISERROR(VLOOKUP(D2,J:K,2,FALSE)),1,VLOOKUP(D2,J:K,2,FALSE)),0)+IF(E2<>””,IF(ISERROR(VLOOKUP(E2,J:K,2,FALSE)),1,VLOOKUP(E2,J:K,2,FALSE)),0)

It seems much more complicated than it really is. And basically, from here, I can easily tell that the Apple, Banana, Grapes and Peach have at least one external buyer.

Step #3 – Use simple conditions in “Result 1” and “Result 2” columns

I will simply replace those 2 columns by formulas:

For result1, I have: =IF(H2=0,”X”,””)

For result2, I have: =IF(H2=0,””,”X”)

That’s it! Not complicated and flexible to change depending on your needs. You can download the spreadsheet used here!