Which investment is better?

compare investmentsI have $10,000 to invest over the next six years. I have a chance to buy equipment that will save me $2000 a year for five years and after that I could sell it for $3000. Looks like $3000 ahead! But before I jump into this, I should compare it with other investment opportunities. Maybe I have a chance to invest it at 6% for the six years, and I figure the risks are comparable for the two choices.

Here’s a spreadsheet to look at investments like this. Fill in the interest rate you’re using for comparison and the amount you’re investing. Then fill in how much you will get each of years 1 through 6. (If you have to put in more money any year, enter that as a negative.) The cumulative column shows how much you’ll have total (positive in black or negative in red) at the end of each year, taking into account what you’ll give up by not investing the money.

IRR1The Cumulative column shows how much I’ll be ahead (black) or behind (red) relative to investing the money at 6%. I’m in the hole until Year 6, but I end up $765.45 ahead. All else being equal, this is a better than the 6% investment.

But how much better is it? Try various rates. Try 7%. The bottom line is still positive, so this is better than a 7% investment. Try 8%. Negative. Not as good. Now I can use Goal Seek to find the rate that gives comparable results. I tell Goal Seek to make the bottom line exactly zero by changing the rate. Here’s what I get.


The answer is that this investment is exactly as good as investing the money at 7.6%.

Here’s another way I could use this spreadsheet and Goal Seek. I’m looking for a 10% return on my investment since I think it’s risky. How much should I offer to pay for the equipment?


If I could buy the equipment for $9,275, I could make 10% on the investment.

The value of 7.6% that we got is called the internal rate of return, often abbreviated as IRR. This is a useful measure of how good the investment is. Take, for example, a similar investment that pays more each year, but has no salvage value at the end, as represented in this spreadsheet.


The internal rate of return here is 7.9%, a better investment.

Now let’s compare it to a completely different type of investment. Suppose someone borrows $10,000 and in six years (without any interest payments) pays you $15,000. That’s more than you’d get back from the other investments. But if we find the internal rate of return, it’s only 7.0%. Money sooner is more valuable than money later.


If you’re interested in how you’d build a spreadsheet like this, it’s the same techniques used in others—(1) create names for the inputs, (2) click on cells to get Excel to write a formula for you using only the current and preceding line and named inputs, and (3) AutoFill.

Take a look at the key formula for the cumulative in Year 1.


You’ll get this if you click on the cells of interest while thinking through what you’d compute. Start with the cumulative you had the year before (the $10,000 in the cell just above, C5) and add in a year of lost interest on it (Rate*C5). On top of that, you get back the Amount in/out, so add in the $2000 in the cell to the left (B6). Click on the cells to build this formula rather than worrying about cell numbers. Once again, Excel formulas are easier to write than to read. You may notice that this formula is exactly the same as the one we used to look at investment growth. It works just fine even if some of the numbers are negative. This formula uses only the current and previous rows and named inputs, so we can AutoFill it down to complete the spreadsheet.

By the way, Excel has a function for calculating the internal rate of return directly, IRR. In this spreadsheet, =IRR(B5:B11) gives you the same 7.6% we got before.

There’s a lot of data here, but how can I make sense of it?

notimeOnce in a while I find myself with a lot of really good information that I’d like to use. Great! Put it into Excel. Not always so easy, even if it’s already computerized. I ran across this recently in looking at Medicare payments for doctor visits. The mymedicare.gov site tells you everything about each of your doctor visits–dates, charges, payments, procedures. You can look at it, or you can download it as PDF or text. Not one of these gives you the one-row-per-doctor-visit format you’re used to seeing and working with in Excel. The text version seems to be the best, but here’s what it looks like if you copy it into Excel (plus a lot of blank lines that I’ve removed):

medicare text

One column, with keywords for what you’d like to become columns. Then below each claim are several “claim lines,” each with its own data, spanning several rows. This is good information, but it complicates things, because the number of claim lines varies, so you can’t just count off to find the next claim.

It’s those keywords that make it possible to work with this, because they’re in the same cell with the data. For example, to see all the dates you had a doctor visit, filter this on “Service Start Date”. Then copy that somewhere else. Filter on “Provider:” (don’t forget the “:”) to get a list of doctors that you can line up with the dates, and so on. Add column headers, do a global replace to get rid of the keywords, and you’ve got it.

I don’t usually need macros, but this is one of those tedious tasks that calls for a little automation. Here’s a data conversion spreadsheet, Medicare with macro. Yes, it’s all blank, but it includes a macro called Medicare. Put your Medicare text data in the first column of Sheet 1, run the macro, and you’ll get something that looks like this in Sheet 2. It also lists all the procedures in order.

Medicare converted

Now you can sort it by date, filter it by doctor, or use any of the other ways Excel can examine a bunch of information. Pivot tables, anyone? If you have data in a similar form from some other source you can edit the macro. Wouldn’t it be nice, though, if everybody gave us data already in Excel-friendly form?