What would you teach an undergrad about personal finance using Excel?

[eyeonFI]

Hi Everyone, I know a lot of people here make their own Excel spreadsheets for various purposes, and this forum is a great resource for personal financial decisions. So I thought I would ask for your input…

I teach an undergraduate course. As part of the course we want to make sure students have some basic proficiency with Excel. By this, I mean they can use cell referencing, formulas, pivot tables, solver, and scenario manager to do some relatively simple things. I spend 2-3 hours of class time talking about it and giving demos, and then they do an assignment (which should not take more than a few hours, for the good students). These aspects of the course are pretty fixed (i.e. I cannot devote much more class time to it, and the listed topics are ones I must cover).

Right now the examples and assignment are all about fictional businesses (e.g., use solver to figure out how many of each product to make to maximize profits). I’ve been thinking I’d like to change these to sneak in a little education on personal finance, which might be directly related to decisions these students could actually face in the next few years. E.g., hopefully they will be evaluating job offers and starting a “first job” soon, which could involve many important financial decisions.

So, I would love to hear any ideas people on this forum have that would make for interesting and useful examples or assignments. If you have an actual XLS to share, that would be great to.

Thanks in advance!

 

[MichaelB]

I don't use Excel, (Lotus user) but two concepts that can be clearly expressed using a spreadsheet which I think are critical for a college student:

- the time value of money
- the total cost of credit

 

[Meadbh]

Great idea. One example that comes to mind is evaluating the financial impact of two job offers, one of which has a lower starting salary, no advancement prospects, and allows the new grad to continue to live with Mom and Dad, versus an entry level job with good future prospects in a more expensive city. There could be all sorts of interesting questions such as the cost of transport for family visits, cell phone bills, and clothing expenses, and you could build into the assignment that they have to save for an emergency fund. This would also be a good opportunity to look at the time value of money and find out where the break even point would be.

 

[Meadbh]

Great idea. One example that comes to mind is evaluating the financial impact of two job offers, one of which has a reasonable starting salary, no advancement prospects, and allows the new grad to continue to live with Mom and Dad, versus an entry level job with good future prospects in a more expensive city. There could be all sorts of interesting questions such as the cost of transport for family visits, cell phone bills, and clothing expenses, and you could build into the assignment that they have to save for an emergency fund. This would also be a good opportunity to look at the time value of money and find out where the break even point would be. And of course they would learn about taxes.

 

[scrabbler1]

I don't use Excel, (Lotus user) but two concepts that can be clearly expressed using a spreadsheet which I think are critical for a college student:

- the time value of money
- the total cost of credit

+1, including being mainly a Lotus user although I can use Excel, too.

 

[grumpy]

I have been using spreadsheets since Multimate in 1977 and used Excel extensively ever since I was financial manager of NASA's Hubble Space Telescope. I rarely if ever use functions, pivot tables, or any of the more esoteric features (although I found sort to be very useful). I think the most important thing in Excel is learning to structure the spreadsheet so that it supports the user's thought processes. Then learn the particular Excel tools that minimize the time needed to enter and manipulate the data. I have found spreadsheets to be very idiosyncratic - that is one person's spreadsheet makes perfect sense to that person but someone else trying to make changes to it will constantly be thinking "now why did he set it up that way?". I have noticed that some people love to use colors for fonts and cells but I find that very distracting.

 

[Big_Hitter]

see if they can put together a worksheet that will calculate a drawdown rate such that an individual will have a 95% chance of not outliving the funds

 

[pb4uski]

I agree with Michael that the time value of money is an important place to start. I once taught an intermediate accounting course at our local community college for a few years and I think my students were always perplexed why I started with Chapter 6 (Time value of money) rather than Chapter 1. But I think it is important.

While brings up a fun question that the sharper ones could figure out. You have a table to calculate the pv of an annuity of $1 a year discount at 5% for 30 years. What is the pv of an annuity of $1 discounted at 5% forever? and show the formula.

Here's an idea for an exercise.

How about a scenario where a fictional student graduating in their major gets a job in their major at the average starting salary for their major. This person decides to save 5% of their gross earnings for retirement in a 401k. In the first 10 years, with merit raises and promotions, the person expects a x% annual increase, y% in the second 10 years and z% thereafter and the person increase their savings by 30% of their raise until they are making the maximum annual contribution (which increases with inflation) and they can make additional contributions once they turn 50.

The 401k is invested in a balanced mutual fund and earns an annual return of x%.

Assuming that the student is 22 when they graduate and retire at 55, how much will they have amassed at age 55? Assuming a 3.5% WR, how do their withdrawals in their first year of retirement relate to their initial annual salary adjusted for inflation at z%.

Add graphs showing the buildup of the student's retirement savings and drawdowns to age 100 or when they run out of money, whichever is earlier.

I would think one could do a lot of interesting, eyeopening analyses with Excel with that scenario and sensitivity tables as to different savings and investment return rates, etc.

 

[Chuckanut]

I used the SS to teach younger students about the rule of 72, how money doubled in a certain number of years. And how they could use that to become millionaires with only a relatively small investment in an IRA account. They were usually unimpressed until I showed them that it's the last doubling that really pays off.

 

[Cooked]

Great idea. One example that comes to mind is evaluating the financial impact of two job offers, one of which has a lower starting salary, no advancement prospects, and allows the new grad to continue to live with Mom and Dad, versus an entry level job with good future prospects in a more expensive city. There could be all sorts of interesting questions such as the cost of transport for family visits, cell phone bills, and clothing expenses, and you could build into the assignment that they have to save for an emergency fund. This would also be a good opportunity to look at the time value of money and find out where the break even point would be.

I really like this premise because it it relevant to their situation. I would restate it:
1) Create a spreadsheet with N job option worksheets and a summary worksheet. Allow a new job offer to be included dynamically
2) Include costs for each option including some of the items that Meadbh mentioned
3) Extra credit: decide how to roll intangibles from different job offers into the summary worksheet. This may be numerical or textual.

 

[Meadbh]

Thanks, Cooked! That is why I suggested it. I also like pb4uski's idea, but I don't think young people can relate well to events that are decades away. I certainly couldn't relate to RE at that stage.

 

[tmm99]

How about how much they would be paying back for student loan debt for how long and their life is basically doomed from the start?

 

[sengsational]

They were usually unimpressed until I showed them that it's the last doubling that really pays off.

Like the the one about the magic of compound interest...the first hundred years are the hardest

 

[CCdaCE]

XIRR function.
Calculating the interest you pay on a loan, month by month. Optionally paying additional principal as an column.
Tracking expenses with monthly tabs.
Calculating 10% savings for the first ten years of employment with inputs for different raises, different return rates, as opposed to the next 20 years.
Breakeven analysis on equipment investment and/or all the usual engineering equip./investment return analysis.
Estimating NPV of future income streams.

I could go on..., but,

etc.

-CC

 

[pb4uski]

Thanks, Cooked! That is why I suggested it. I also like pb4uski's idea, but I don't think young people can relate well to events that are decades away. I certainly could'nt relate to RE at that stage.

For our generation I think you are absolutely right. When I was that age I had no interest in retirement savings.

But DD and many (but certainly not all) of her peers seem to be much more interested, perhaps from seeing their parents struggle (or succeed). DD and my nephew have much more retirement savings than some adults my age that I know.

Anyway, the idea is to show them with numbers that regular saving and investing can result in financial independence.

 

[sengsational]

... but I don't think young people can relate well to events that are decades away. I certainly couldn't relate to RE at that stage.

We've bought into the system already, so we don't think of the unthinkable, which is that some huge financial reorg happens and everyone becomes equal, or at least more equal (except for the super rich, who will, as they usually do, figure out a way to protect themselves). But a twenty year old might think that he might live another 100 years, which is a very long time for things to remain the same and stable. So putting his eggs in a basket the might collapse or be confiscated isn't worth the risk. I guess that over 100 years, that's a lot of time for change (do they call that "policy risk"). The conclusion might be to just live life to the fullest now and take your chances with the future.

 

[kaneohe]

This will probably be a bit rough on the instructor but at some point you may want to have each student propose his/her own problem and solution. I learned SS in the very early days. There was an Excel tutorial on the computer and I used to tote that beast home every weekend. Somehow the stuff never sunk in until I gave up and decided to make up my own problem that I had a real interest in solving. Finally I had to learn stuff that was relevant to me and not the million other things you can do w/ Excel. Once you get hooked, learning stuff as you need to seems easier than learning the whole universe , most of which you may never use.

 

[Animorph]

I made a quick spreadsheet for my DS's showing salary, amount saved (a fixed percent of salary), balance saved, investment gains (simple percent) for each year leading to retirement. Then a continuation with no salary but comparable withdrawals from the savings balance and a more conservative investment gain. You can vary the saving rate and investment rates and see how that affects income in retirement or retirement year. You could add SS and 401k/Roth/taxable if it needs to be more complex. That's the basis for early retirement planning for me.

 

[Delaney]

I think the best money tip for undergrads is for them to learn to live below their means (LBYM). I thought it was sad when a 23 year old coworker (not married, no kids, good salary) could not pay off his monthly credit card bill in full.

Therefore, I recommend the students do a spreadsheet to track spending by controllable categories. I don't think everyone has to adhere to a budget, but everyone should know where their money is going.

For example, I track food and restaurants separately, in part because restaurants are optional (though I guess food is not). They should learn how to calculate their real hourly wage (from Your Money or Your Life) and then be able to determine how many hours they need to work to pay for that expensive coffee. This is a lifelong skill, but they could do it based on current job and finances.

I like seeing how my expenses in each category compare from month to month. I consider some expenses to be annual such as my annual car insurance, so I put those expenses in a separate annual tab, not in the month when I pay them so I truly can compare how much I spend each month.

 

[Htown Harry]

Here's an idea for an exercise.

How about a scenario where a fictional student graduating in their major gets a job in their major at the average starting salary for their major. This person decides to save ...

Assuming that the student is 22 when they graduate and retire at 55, how much will they have amassed at age 55?

+1 on this idea, although I also like several others that have been posted.

Over in the "what would you tell your 22-year-old self?" thread, I posted a blogger's table of the results of an exercise similar to pb4uski's. A variation would be to give them those spreadsheet results and have them replicate the results in an Excel spreadsheet of their own making.

http://www.early-retirement.org/for...our-22-year-old-self-67700-3.html#post1344732

 

[Htown Harry]

Will this be an online course? MichaelB and Scrabbler1 might be interested.

Lotus 1-2-3 - Wikipedia, the free encyclopedia

On June 11, 2013 IBM announced it would withdraw the Lotus brand: IBM Lotus 123 Millennium Edition V9.x, IBM Lotus SmartSuite 9.x V9.8.0, and Organizer V6.1.0. IBM stated, "Customers will no longer be able to receive support for these offerings after September 30, 2014. No service extensions will be offered. There will be no replacement programs."

 

[eyeonFI]

Will this be an online course? MichaelB and Scrabbler1 might be interested.

Lotus 1-2-3 - Wikipedia, the free encyclopedia

Not an online course this term, but give it a few years and we'll see....

Thanks, everyone, for all the ideas. There seem to be a couple of themes...now I'll have to see what I can design before the class starts.

 

[MichaelB]

Will this be an online course? MichaelB and Scrabbler1 might be interested.

Lotus 1-2-3 - Wikipedia, the free encyclopedia

Well, truth be told, IBM walked away from Lotus years ago. Now that support is officially withdrawn I will have to learn something new (if thats even possible). I figure I have at best a decade, so I better get going. ABM it is ( anything but Microsoft).