Archive for February, 2012

How to Use The Excel Sumproduct Function

Personal finance

One of the great benefits of using excel is that it just seems like for almost anything that you are trying to do, there are several different ways to get them done. They might have small differences but in general they do come out to the same thing. When given the choice, it’s always better to use the “simpler”, lighter versions. Today, I’d like to look at the use of the “Sumproduct” excel function, which makes it easier to do two a combination of two things. First off, let’s take a look at a non-optimized spreadsheet that we would commonly use to calculate the value of a portfolio:

So how would you calculate the value of the portfolio? What most people would do is calculate the value of each position and add those up, see an example:

Certainly, that gets the job done as we do have the correct total of $54,785. However, if you add hundreds of lines, or even if you wanted to do it more easily, you could certainly do that. Instead of finding the total for each line and then summing up all of those, you could simply use the SumProduct function. Here is what it would look like in this case:

=SUMPRODUCT(C6:C11,D6:D11)

And the result:

As you can see, it gives me a much quicker and more flexible solution. I could easily add lines, modify numbers or do other modifications and then simply refer to that one formula to get the updated number. What does SumProduct do? Exactly what you would expect.

For each line included, it will multiply the numbers of each row (I included 2 rows here but you could add more) and sum all of those together. It’s simple yet brilliant.

As is always the case, you can download our spreadsheet here.

An Experiment in Saving Money

Personal finance

Today I wanted to look at the idea of trying to make saving money fun. Most articles on saving money revolve around lame frugality tips and other tips on how you can save a few dollars by cutting your own hair (and likely looking like a clown). I wanted to look at an interesting strategy for saving money.

What’s a unique way to start saving money right now?

We all have our own unique methods when it comes to saving money. Some of us host house parties to save money on fancy dining and expensive night clubs. Others will ride the bus to work or find a job close to home. I recently heard of a really cool experiment on saving money. A good friend of mine does this with a group of family friends.

This experiment in saving money involves using a group of five people to save money together. What my friend does is he saves money with four of his closest family friends. They all roughly have the same pay day. They also roughly make the same amount (off by a few hundred dollars). Every pay day everyone puts in $500 and the money goes to one person.

How does this experiment work?

Every pay day the individuals involved in this plan take out $500 and give the money to one person. The person that gets this money is determined on a simple schedule. Every pay day a different person gets the money until everyone involved has had the money. This might sound sort of silly, but let me explain it in further detail…

What are the benefits of group savings?

I’m going to outline the benefits of this plan that my buddy shared with me.

  1. Keeps you accountable. You need to have the $500 every pay day. You’ll be letting your four friends down. They’ll want to know why you don’t have the money or what you did with your income. You’re forced to save the $500. When your turn to get the money comes, you can do anything that you want with it. You can save it. You can pay your bills in advance. You can budget for your major expenses.
  2. It’s social. You and your closest friends are forced to meet up and discuss your financial plans. This group loves to help each other out and keep tabs on everyone’s progress. The social aspect also makes it much more fun and interactive. It beats trying to save money on your own when you have a bad habit of spending your pay checks. Some of us just need that little boost of motivation to start saving money. In this case you get motivation and a system that works.
  3. It forces you to live lean. For the few pay days that you’re putting the money into the pot ($500 in this case, which is 40% of income) you have to figure out how you’re going to survive on less money. Essentially, you’re paying yourself first and getting your friends involved. You’re put in a position where you need to figure out how you can live on less.

As you can tell there are many benefits to trying this out. What are you waiting for? It’s an experiment that’s worth trying with your closest friends.

Getting Back in the Arena – A True Story of a Blogging Meltdown

Blogging

A Blogging Flameout/Meltdown 

Perhaps some people who read this blog know my story: that I used to have my own personal finance blog called Broke Professionals.

I had started the blog in part as an escape from a job that I disliked.  I have always had the passion, if perhaps not the skill, for entrepreneurialism.  I thought the blog would be a great way to confront my wife and my six figure student loan debt and indicate the mistakes we had made to get where we were, and what we were trying to do to dig out.

I’ve always been creative and have a great love of writing, so I started the blog to have an outlet.  I never knew you could make money blogging—I just thought it was an expense, but one I was happy to pay.

I eventually started and ran, over the course of just about six months, what I believe was a good, solid personal finance blog.  It was honest, surprisingly well-read for a new blog, and I even scored a couple of big guest posts and also interviews of people for my blog—such as a Wall Street Journal Finance Editor.

About this time, my Wife and I bought our house.  This was of course, yet another financial mistake.  Now we had a mortgage to go along with our student loan debt.  I started to see if I could make some money from my side-project.  Eventually, I flamed out entirely, and sold the blog when money got real tight.   All our money was tied up in the house and paying ongoing bills.

At that time, I was happy to sell and decided I would focus on a freelance writing side-job.  I didn’t know how to make money with blogging, but knew people were willing to pay me for my writing.  I took on a lot of clients, and began to feel overwhelmed.  I have since cut it back significantly.  I have also since quit the job I disliked to start my own business—which of course pays me less than before but at least I am happy.

So why am I telling you this story?  If you’re a blogger or considering blogging, I think it’s important that you realize that running a good blog takes a lot of time.  It can start to be overwhelming.  Also, if you blog, don’t do it because you hope to get famous or make money.  Most people don’t make much if any money from their blogs.  Finally, you’ve got to have a financial cushion.  I loved my blog and felt terrible having to sell it when I did—this led to a complete blogging meltdown from which I still feel embarrassed to this day.

I used to write a post every night for my blog.  I was sort of addicted to the idea of getting more readers, more links, etc.  It didn’t start like that and I’m not sure why that’s what it became.  A mix of desperation, immaturity, and my own inherent personality defects, I suppose. I often wonder what might have come of my blog had I gone about things the proper way, and if I had maintained a better mindset throughout the process.

Getting Back in the Arena

I’ve been thinking recently about how much I miss having my own blog, and how much I miss the friends I made blogging.  It’s not the same being a “lurker” on their sites.

So I’m going to continue freelance writing, and also started my own personal finance blog again.  This time, for all of the right reasons.

How about you bloggers reading this post?   Have you ever experienced the “dark side” of blogging?  I look forward to reading your comments.

Tricks To Use Excel Functions With Dates

Excel function tutorials

If you have tried using dates in excel, you will know exactly what type of problem that I’ve encountered too many times. A large portion of the emails that we receive with excel related questions actually are related to using dates. Microsoft Excel is robus in many ways but I would say that manipulating dates is not one of the strenghts. Why? I’m not sure. It could be because there so many different formats depending on where you are and your computer settings. I’m not sure. But what I do know is that it creates many headaches. There are no bulletproof solutions but I will show you how I deal with dates in excel.

An Example

First off, as is always the case, I will try to start by using an example. In this case, we received a file from a reader that is trying to group, for a large excel file, the dates into one of 4 groups:

-Over 10 years old
-5 to 10
-3 to 5
-0 to 3

So basically, I have some dates and need to set them in those ranges, here are the instructions:

If Cell A2 (Last Receipt Date) > 12/2/2009 but less than 12/31/2011 return 0-3 years
If Cell A2 (Last Receipt Date) > 12/2/2006 but less than 12/2/2009 return 3-5 years
If Cell A2 (Last Receipt Date) > 12/2/2001 but less than 12/2/2006 return 5-10 tears
If Cell A2 (Last Receipt Date) < 12/2/2001 return over 10 years Basically, I will transform the statements into the following: =If date is older than 12/2/2001, +10 years, if date is older than 12/2/2006, if date is older than 12/2/2009, if date is older than 12/31/2011 I could try finding out the range but that is not necessary in this case. What I started by doing is writing the dates down as you can see here:

Then, next to it, I copied the same numbers but then changed the format from date to “numbers” or “general”, by clicking here:

If when you change the format, you do not get numbers like this, that means that excel does not correctly identify the dates as “dates”.

Once that is done, I used the following formular in B2

=IF(A2<$E$14,"+10",IF(A2<=$E$13,"5-10",IF(A2<=$E$12,"3-5",IF(A2<=$E$11,"0-3",""))))

As always, you can download the excel file here

Why I Decided to Rent My Condo Out

Housing

In early-2010, I finally closed my condo and was given the keys to my very first property. I was excited to be a property owner in my early-20s. I was even more excited about the prospect of renting out the place while I built my equity. Well, I ended up living in the condo for a year and a half. It was the best time of my life. I have many memories from that time.

Eventually the time came to decide what I wanted to do next. I had to answer a few questions about my condo and my life…

Should I become location independent and travel the world?

Should I find a job and settle into my condo?

Should I not do anything?

I wasn’t sure of what to do. I did know that I wanted to blog full-time. I also knew that I needed to cut back on my expenses if I wanted to go all in with blogging. What was I to do? I started to think about the idea of renting out my condo. Below are the pros and cons of my decision to rent out my place.

The reasons against renting out my condo were simple:

  1. Move back with parents. Anyone that has moved out knows how great it is to be on your own. Moving back with your parents is well pretty lame to be honest. I was pretty worried about this.
  2. Less freedom. I won’t get into detail but let’s just say that I did plenty of things that I couldn’t even do if my parents lived on the same street as me!
  3. Uncertainty. I wasn’t sure of what I wanted to do next. Would it be a wise move to actually move out? Would I regret my decision in six hours?

These are some serious negatives. I was starting to get pretty nervous about actually giving up my place.

Since I mentioned why I didn’t want to rent the place out, I must share with you why I actually was leaning towards renting out my place:

  • Travel more. I would be able to travel more without the stress of property ownership. I would be able to leave the country and not have to worry about much (aside from the usual tenant issues). I could go on more trips and see more of this amazing world.
  • Save money. I could also save so much money by cutting back on the plethora of costs that come with owning your own place. I would get excited thinking about the savings.
  • Make money. I would be able to make more money by renting the place out. I would also finally be able to focus more on my business since I wouldn’t have to worry so much. Sounded like a win-win to me.

As you can tell, all of the reasons in favor of renting out my place revolved around money.

I chose to rent out my place. I had to suck it up and move back with my parents (technically). Now I spend most of my time at my girlfriend’s place. I’ve also been able to do lots of traveling. I started off by buying a ticket to spend a month in Europe. I went all around Europe until I had to come back. Then in January I went to Cancun. I plan on traveling often now that I decided to rent out my condo.