Archive for June, 2011

Troubleshooting A Nested If Statement That Includes The Or function

Excel function tutorials

I have to say that I’m thrilled because we are receiving quite a few questions these days both in comments and by email. As always, we are more than happy to help and in most cases, the answer can help quite a few others so we publish the answer as a new post. We’d be very happy to receive questions from you so be sure to contact us with any problems that you have. Today, we received the following question, which initially appeared as a comment on our excel nested if statements tutorial. I’ve translated the question to make it easier to understand here:

I am just trying to get a Y or N when applying this formula:
=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”))
Below are a couple of sets of data and the results that I got when the formula was applied:

As you can see, once the data does not meet the first part of the formula, it gives you a FALSE. If it meets the first part but does not meet the second, it gives you an N. If it meets both parts, it gives you a Y. I only want the formula to produce 2 results, Y or N. Let me know if you have any further questions. Otherwise, let me know if you can help me solve this problem.

There are a few different things to discuss here. First off, let’s translate this into an easier question to understand. Let’s imagine the question is. I want to determine small or large schools that have very poor or very good results. A small school would have less than 450 students, a large one over 550, and very poor results are under 50% while a very good one is over 70%.

So what am I looking for?

Schools that have under 450 or over 550 students that have a score under 50% or over 70%

The first thing that I notice in the statement is that it is incomplete:

=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”))

This statement initially does one thing. It checks if U2 is greater or equal to 500 or smaller or equal (of course, any number will fit this condition). If that condition is met, it checks for the 2nd part, which is similar. The problem of course is that it does not give any indication for what happens if the first condition is not met. I would thus start by changing the statement to:

=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”),”N”)

This fixes the problem of getting a “False”, see the new result:

In order to add the conditions that I discussed, I would change it to:

=IF(OR(U2>550,U2<450),IF(OR(V2>70%,V2<=50%),”Y”,”N”),”N”)

By changing the set, you can see the result:

Hopefully this helps. It’s important to always structure your arguments to not miss any parts. Doing one condition at a time is a great way to do get it done. We look forward to getting more questions from you!

Paying Down The Mortgage Vs Investing

Personal finance

I know this is an eternal debate: should you pay off your debts or invest your money? Since the most important debt that you can have is your mortgage, we will take a look at what are the impacts of paying down your mortgage faster or using your extra cash flow to invest.

If you look at the investment return of US index ETFs, you will tell me that you are way better off paying down your mortgage faster than investing. I must say that it’s hard to think about investing when you see what happened in 2008. However, if you have a good asset allocation, you should be able to recover from 2008 within 3 years.

But let’s go a little bit further in your analysis. Should you pay off your mortgage or invest?

Being Debt Free

The first argument I can find is “being debt free”. While I can’t blame people who want to become debt free as soon as possible, I must ask them the following question: what will generate your income at retirement? Your debt free house? Nah! Ain’t gonna happen!

Don’t get me wrong, paying down your mortgage as fast as you can is a good thing. However, I think that one should reach a balance in order to build some assets at the same time.

This whole perception that having debt is a bad thing makes me sick. Why is it that bad? I mean, as long as you can easily make your payments (read not being over indebted!), there is nothing bad about having a mortgage. But then again, some people stress out thinking they owe money to the bank. Therefore, they want to pay off all their debts as fast as possible.

Pro “investing” Arguments

Make more money

If you have the choice of paying down your mortgage at a 4% interest rate or investing at a 6% investment rate, I guess picking the investment option is smart. This is a big debate as investment returns are not guaranteed (while the interest rate on your mortgage is!). However, if you take into consideration the power of compound interest, investing, even at 4%, is a better option than paying down your mortgage.

Another interesting argument is that investments generate income at retirement while your house will just be a source of expenses. This is why some people don’t consider their home as an asset; because it doesn’t generate money over time. So if you are looking to build a pension plan on your own (do you really count on the government to do so?), you are better off starting your investments at a young age.

Paying off your mortgage or investing? Find a balance

I think that the best option is to find a balance between paying down your mortgage as fast as possible and investing all your money. Why don’t you set a solid plan to pay off your mortgage such as making bi-weekly payments for the next 25 years? Then, you can take your extra cash flow along with any future salary raises and use them to invest money in your retirement fund.

If you get bonuses, I would personally invest them instead of paying down debts. But I am a big believer in investments ;-).

The Personal Finance Periodic Table

Personal finance

It’s time for a personal finance “chemistry” experiment.  This is “Experiments in Finance,” after all.  No matter how much we know about personal finance, it is sometimes helpful to go back to the basics and think about the core values and systems that will provide the overarching framework for personal finance and career success.  Using the elements of the actual periodic table as a blueprint (yes, we’re going slightly gimmicky today), here are some of the basic “elements” at work in having a healthy financial life, as well as some books that provide great overviews of each topic.

Element 1: (H) – Housing –  Should you rent or buy?  What is the future of the housing market?  15 year or 30 year mortgage?  How much should you borrow/how much house can you afford?  Commercial properties v. renting commercial space v. a home based office.  The list goes on and on.  To get a firm grasp of your finances, you’ll have to understand the average person’s number one expense: housing.

Book to Read: The Wall Street Journal’s Complete Homeowner’s Guidebook, by Wall Street Journal Editor David Crook.  Bonus: When you leave Experiglot, read my interview with the book’s author, David Crook.

Element 2: (He) – Health – That’s right, personal finance begins (like just about everything) with health.  Without your health, it’s much harder to do or accomplish anything.  And if you have health issues, then that can really destroy your chances at saving money.  Health issues are one of the major reasons for bankruptcies.  Having proper medical, disability, and life insurance is also important.  It’s been said that we spend the first part of our lives trading our health for money and the second part of our lives trading money for health.  It’s a trap that we all need to consider when creating our financial plans.  Our health affects everything from our quality of life to our target retirement.

Element 3. (Li) – Lifestyle – What are your goals?  What kind of life do you wish to lead and how much money will it take to get there?  What sort of lifestyle are you shooting for and how close are you to attaining it?

Book to Read: How Much is Enough?  By: Arun Abey and Andrew Ford.

Element 4: (Be) – Beliefs – What are the core values of how you will generate and save money?  Do you want to be socially conscious?  Do you want to be entrepreneurial or a W-2 income earner, or (like me) both?  How much freedom do you wish to have in your schedule?  Do you hope to be a job-hopper or a “work for one company” type?

Book to Read: The Problem with Money.  By Jane Honeck.

Element 5: (B) – Banking – What kind of investor do you want to be?  What are your investment beliefs?  Buy and hold or aggressive?  To what extent should you diversify?  How risk adverse are you and your spouse/significant other?

Element 6: (C) – Credit – How much debt are you going to allow into your life?  Is student loan debt going to pay off?  Can you get rid of your credit card debt once and for all?  Can you negotiate a lower interest rate with your credit card companies?

Book to Read: Debt Free for Life, by David Bach.

Element 7: (N) – Networking – Your job is the most important income generator in your life (for most people).  Networking is one of the most important aspects of furthering your career–in your present job and also in future positions as well.  Keep yourself in the minds of the “head hunters” and learn all you can about networking.

Book to Read: Never Eat Alone, by Keith Ferrazi

Element 8: (O) – Occupation – Going hand in hand with networking is the larger picture: your occupation.  Where will you work–both in terms of employer and industry?  What are your goals for the present and future?  How can you optimize your degree and skills?  How can you actively manage your career?

Book to Read: Do What You Are, by Barbara Barron

Element 9: (F) – Finance – Investments, ETF’s, and investing in everything from real estate to precious metals; “shorting”, dividend investing, bonds v. stocks, mutual funds, and on and on and on.  But here’s the good thing–you don’t need a MBA or a Ph.D in economics to figure these things out–just go after them one at a time, starting with the basics such as mutual funds.

Element 10: (Ne) – Need. As in determine the difference between your wants and needs.  Make a budget and stick to it.  Save and reinvest everything you can.  Make your money work for you rather than the other way around.

Book to Read: The Millionaire Next Door, by Thomas Stanley and William Danko

Conclusion

Sure, the real periodic table goes on for 180-some elements whereas we’re stopping at ten, but this should be more than enough for you to consider as you begin learning more about personal finance or go about refining your current plan.

Best of luck as you apply the “Personal Finance Period Table” to your own life.

Any questions?  Where did I stretch?  What elements would you do differently?

Excel Spreadsheet to Calculate Coupon Savings – Alternative to “If Or Else”

We’re thrilled to say that we’ve been getting an increasing amount of reader questions with their own excel challenges or problems. It’s always interesting because no matter what the subject or the exact problem is, we can usually learn something that can be applied at some point in the future. In this case, we received an email from a reader that is attempting to save how much savings she is making using simple coupons. The only twist? All coupons of \$0.50 or less are doubled by the store. Simple enough? Our reader was attempting to either use the “nested IF statements or an “else if”. She also sent me a sample file which I’ve modified slightly and will let you see at the end of this post.

As in almost any other excel problem, the main challenge is usually to “see the problem in the simplest way”. It’s much easier said than done of course. In this case, what is the situation exactly?

-For each item there may be a coupon
-That coupon should be applied but any coupon worth less than \$0.50 should be doubled in value.

Personally, I think we can easily resolve this problem with a simple “if” condition without adding any “or else” or “nested if” conditions. It’s always about finding the easiest way which not only saves us time but also makes it much easier to modify and improve the fills.

In this case, here is how I see it:

Personally, I would simply calculate the total as follows:

Total = If coupon > \$0.50, Quantity x Price – Coupon Value, Quantity x price – 2 * Coupon Value

Why? There are really only 2 cases here. A coupon worth less than \$0.50 or not.

cell E2 = IF(B2>0.5,C2*D2-B2,C2*D2-2*B2)

Get Angry and Stay Angry: A One Week Guide for Raging Your Way to Personal Finance Success

Personal finance

One week from today I want your life to be totally different. It’s a bold proposition, but I think it’s more than possible. How are you going to get there? Through pure, unadulterated rage. It’s time to go all Hulk-like on your personal finances. It’s time to climb to a window and shout at your debt: “I’m mad as hell and I’m not going to take it anymore!”And then really get into all the issues holding you back.

“I’m pissed at my bank always dinging me with transaction fees!”

“I’m sick of my credit card companies charging me usurious fees!”

“I’m really really sick of being underpaid and overworked!”

Here’s your one-week plan for a better financial present and future. (Aggravating Disclaimer: You must make your own decisions in life and this post does not guarantee results. Please consult with your own financial or other experts prior to implementing any of the changes suggested in this post).

Monday

Problem: Things have been kind of boring at the office lately. You’re happy to have a job but not so happy to be doing a ton of work for less than scale.

Solution: March into your bosses office and ask for a meeting later on. Beforehand, make note of all your accomplishments. At the meeting, go over all of your accomplishments and ask for a 3-10% raise.

Tuesday

Hopefully revved up by your success, why not take on your banks next.

Solution: Sit down and review all of the fees you are paying. Then march into the branch office and give them a peace of your mind. Tell them you’ll leave unless they drop the recurring fees. Ask them for advice on how to cut down atm or other transactional fees. See if there is any way you can qualify for a better plan or otherwise save money. Tell them to get creative or get out.

Wednesday

It’s time to take on the biggest jerks of all–those credit card “merchants” pirating away all your extra cash with 20% interest rates and annoying sales letters.

Problem: You’re paying more money towards interest than you do to heat your house.

Solution: Call up your credit card company and ask for your rates to be lowered. Then demand it. Then subtly hint that you’ll leave if they won’t give you what you want. Be persistent and see what happens.

Thursday

It’s time to get personal. We’re talking a little “family time.”

Problem: Your spouse is spending money quicker than either of you are earning it. Your kids, if you have any, are acting like entitled brats.

Solution: Bring everyone together and go over all of your finances. Be calm but honest about the problems facing your family. Talk about ways to solve the problems and put the family back on financial track. Tough love is sometimes an important element in financial success.

Friday

You’ve taken on credit card companies, your boss, and even your spouse–now it’s time for a little bit of a Friday break. All you have to deal with today is those sniveling charlatans known as the friendly “local” cable company.

Problem: You’re paying \$29.99 per month for premium channels you don’t watch. The company lured you in with great prices but have slowly increased that rate over the years.

Solution: Bring the rage. Call up your cable company and demand \$29.99 or less for basic cable, \$29.99 or less for basic telephone, and \$29.99 or less for basic internet. Threaten to leave and speak with the “retention” people. Be firm and drop the services if you have to. I’m sure you can find someone else to give you a good deal. Besides, those phony cable companies will be falling all over themselves if you actually follow through with going cable free.

Saturday

Problem: You bought your house in 2007 at a 7% interest rate. Or you’re student loan debt is way higher than a home equity loan.

Solution: On Saturday get mad at that ridiculous interest rate or those student loans and look into using a home equity loan to pay off your student loans or refinancing your mortgage to lock in the current low interest rates. These are small changes that could potentially save you thousands of dollars in interest.

Sunday

Take on yourself.

Problem: You really suck. Up until now you’ve allowed your family life, job, and even the cable companies to take advantage of you. Today you need to point the finger (and it might be a middle one) right at numero uno.

Solution: Soul search about why you’ve allowed yourself to be a witness rather than an active participant in your own life. Take one thing that isn’t bearing fruit for you and drop it. Perhaps you have three blogs and only two of them are going anywhere. Destroy the non-performing one and never look back. It’s time to get really, really pissed at yourself. And then go to bed early and wake up happy.

Because in one week you’ve taken great action steps towards creating an amazing new life for yourself.

The following was a post by Experiglot regular contributor, Chris Thomas of FreelancePF.