Daily Dose of Excel

The other day J-Walk posted How to Retire at Age 40:

If you were to take 20% of your annual income starting at age 20 and put it in a fund following the S&P 500 Index ($INX), that fund continued to grow at the long-term historical rate (12%) and you received a 4% raise each year, you could walk away from your job and live off the interest at age 41 matching your current salary — or quit at 43 and be able to give yourself a 4% “raise” each year from the interest, which is probably the better plan because it combats inflation. Original Source

Then today, he posted Retirement Revisited which proves out the math.

I’ve been working on this also and wanted to post my results too. Instead of assuming that the S&P 500 returns 12%, I used actual returns and based it on my actual life. Sort of.

I started at Standard & Poor’s – Indices S&P 500 and clicked on the “S&P 500 historical returns” link to download the Excel (xls) file. I don’t remember how much I made when I was 20, but it wasn’t much because I was still in college. In fact, I think I was a third-year freshman. I turned 20 in June of 1989 and let’s assume I made $15,000 per year. I’ve hid some columns and rows, but here’s the gist of it:

Column D is the monthly return for the S&P 500 for all 240 months between my 20th and 40th birthdays. Since I’m not 40 yet, the last 19 months are the average annual return of the index from June ’89 to October ’07 (7.98638%). That was computed thusly:

The interest rate for 221 months when I start at zero, put $250 per month in, and end at $125,150.57. The $125k is what I get from this worksheet when I don’t increase the monthly pay by 4% and is not shown in the screen shot.

Column N is just a counter showing the month. Column O is the monthly amount saved. The formula in O9 is

$O$1 is the annual salary at age 20. Divided by 12 months. Times 20% savings rate. Increased 4% every 12 months.

Column P shows the interest I earned for the period and column Q is the balance in my investment account.

P:

Q:

I’ll have $196k to live on. Let see if it will last.

Nope. If I live until 75, I’ll be $3 million in debt. At age 40, I’m making about $33,000 per year and that’s the standard I need to maintain. If I pull out 1/12 of that every month, I’ll be broke sometime in September of 2017. Damn, the Huskers might be good by then and I won’t have any money to go to the games.

Column B:

Column C:

$D$9 is the average return of ~8% per year from the other sheet.
Column D:

It’s not prettied up, by you can download SP500Monthly.zip.

Hi all

I create a small Excel add-in to copy a customized QAT from one workbook to another.
http://www.rondebruin.nl/imageqat.htm#QAT

Because it not use very common code I like to hear from you if you have problems
or if there are there better ways to do it ?

I hope a few of you can test it for me.
You can find a button on the Add-Ins tab to open the userform

Thanks

Here’s the situation: Four people sharing an Excel spreadsheet (Excel 97, Shared Workbook) and updating constantly. Column A has times at 15 minute intervals. Row 1 has names. The users enter an address at an intersection indicating where a certain person is supposed to be at a certain time.

The users are experiencing some instability and there are some Excel features that are unavailable when workbooks are shared. That’s not a big surprise, because sharing workbooks stinks. I usually recommend against it before I even know what the issues are.

I recommended Google spreadsheets. I’m sure there’s a better tool, but I just can’t think of it. What would you suggest?

I’ve never found a decent keyboard combination for selecting a single-column range whose length is determined by an adjacent column. If I double-click the fill handle, for example, it will fill a formula down as far as the column on the left has data. That’s nice when you have a lot of data.

I usually use Shift+PageDown to select what I need, then Cntl+D to fill down the formula. Well no more! Now I’m using this macro:

Make sure it’s a range, make sure it’s column B or greater, make sure there’s only one cell selected, and finally make sure there’s something in the column to the left. I’m using the key combination Cntl+Alt+DownArrow. Here’s how I set that up:

Hi all

I add a new page to my site with examples to change a button image
of a button in the QAT for all workbooks or for one workbook.
Making a QAT Add-in of the one workbook example seems a good way on
this moment if you want to change the buttons images of your QAT.

http://www.rondebruin.nl/imageqat.htm

Suggestions or comments are welcome
Note: I can’t post comments in this blog but I will read your comments.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

InfoWorld: Thin vs. Fat: Google’s plan to kill Microsoft Office

Case in point: Microsoft Office. Most people think of the big three — Word, Excel, and PowerPoint — as merely an integrated suite of stand-alone applications, albeit a wildly popular one. Take a closer look, however, and you see that Office is much, much more. Thanks to the inclusion of some robust integration APIs (Visual Basic for Applications, OLE automation, and various add-in interfaces), Office is a commercial development target in its own right. In fact, one of the easiest ways to break into the Windows development marketplace is by targeting Microsoft Office. Make it do something new or better and the world will beat a path to your door.

blist

If you can make a list, you can make a blist. If a blog is a web log, well, a blist is a web list. A blist is a list with some structure.

Didn’t we have the thin vs. fat debate in the early ’80s when we sat at dumb terminals allowing a ne’er seen Cray super computer compile our “Hello World” Pascal assignments? Didn’t fat win? Is this just where we are in the cycle?

I’d like to say cycle. I’d like to say that, while thin’s resurgence is obvious now, it will peak at some point and we will gradually migrate back to something that resembles fat. The problem I have is predicting what it is that will pull us back. What is it about fat that will bring us back to it and why isn’t it preventing the move to thin now? I can accept the argument that I’m simply framing this argument wrong. Maybe some things will stick with thin and others will never make it there.

Let’s talk spreadsheets. I’m pretty confident that no one reading this blog is ready to ditch Excel for an online spreadsheet. What are the issues? What are the must-have features in a spreadsheet?

The number one problem for me is being able to use it offline. There are very few instances where I’m not able to connect, but airports are some of those instances. I’m not paying $8/day to connect to the internet on a 1.5 hour layover. I’ve got to be able to get to my documents and executables in every situation – even if that means I have to plan which documents I’ll need.

For must have features, data validation, decent chart rendering, some sort of scripting, pivot tables, and array formulas come to mind. That’s beyond the basics of what makes a spreadsheet a spreadsheet. I’m sure there are others I’m not thinking of.

I don’t need collaboration, but I do need a templating system. I need to be able to have users create new spreadsheets from an existing template. Do any online spreadsheets have that now?

I use 2 computers at work. One has Excel 2003, the other has Excel 2007. I’ve been using the one with 2003 longer and had a few macros stored in my Personal workbook. I was in the process of copying and pasting them into the other computer after sending them there by email. (Yeah, I know I can install both Excel versions in the same computer but this is how things turned out and I find it handy for testing)

Anyway, I wondered how I was going to call them. I had the macros in the 2003 computer assigned to toolbar buttons with icons but how about 2007? I could use the QAT but realized the face ids were going to be different. Not such a big problem, but I didn’t feel like going through the drama of selecting a whole lot of new ones, not to mention updating personal workbooks for both computers every time I added any new code. So I came up with the below right click menu addin that I use for both computers on a shared drive.

Just paste or write any macro into the addin and update the menu by pushing “Make Right Click Menu”, either in the VBE or right click menu itself to add and save. The module names in the addin act as sub menu names and the macro names get added as menu items. Numbers get added to act as keyboard shortcuts.

Also, there is some code to add spaces to module and macro names so that Private Sub DeleteAllFilesInFolder() in module MiscellaneousMacros would show in the menu as DIY Context Menu – Miscellaneous Macros – 1. Delete All Files In Folder

It’s a dynamic menu and a pretty simple one to use. Here is the download link if you want to try it. Some example macros are also included. I think it should work with Excel versions 97 – 2007.

I’ve heard several people complain about the fact that the chart pattern fills are not available in Excel 2007. This feature can be useful if you print charts on a non-color printer. Although charts created with previous versions of Excel continue to display the pattern fills, there is no way to apply patterns fills using the Excel 2007 UI.

Microsoft’s Eric Patterson addressed this problem by creating an add-in. You can download it here: Chart Pattern Fills.

This add-in creates a new group (Patterns) on the Chart Tools / Format tab. The group has one control that, when clicked, shows the available patterns to apply to the selected chart element.

I’ve spent the last few days in Dayton and Cincinnati, Ohio. While there, I was fortunate enough to see Big Butter Jesus in person.

Story from RoadSideAmerica.Com

Heywood Banks Song on YouTube

I don’t know if folks know of the project that started off I believe at MIT to distribute cheap laptops to children in developing countries. Now called OLPC for one-laptop-per-child, its website is http://www.laptop.org/

David Pogue of the New York Times has a review of the laptop at http://www.nytimes.com/2007/10/04/technology/circuits/04pogue.html

People in the U.S. and Canada can participate until Nov. 26, 2007 in a give-one-get-one promotion. For $400 ($200 being tax-deductible) the participant donates one computer and receives the other: http://www.laptopgiving.org/en/index.php In addition, T-Mobile offers 1 year of free hotspot service to each participant.

Obviously, I plan to participate. This just might be the vanguard of a revolution in computer design and ubiquity.