Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
What would you teach an undergrad about personal finance using Excel?
Old 08-22-2013, 12:12 PM   #1
Recycles dryer sheets
eyeonFI's Avatar
 
Join Date: Mar 2005
Posts: 101
What would you teach an undergrad about personal finance using Excel?

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!
__________________

__________________
eyeonFI is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 08-22-2013, 12:16 PM   #2
Moderator
MichaelB's Avatar
 
Join Date: Jan 2008
Location: Rocky Inlets
Posts: 24,492
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
__________________

__________________
MichaelB is offline   Reply With Quote
Old 08-22-2013, 12:22 PM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2006
Posts: 11,019
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 is offline   Reply With Quote
Old 08-22-2013, 12:24 PM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2006
Posts: 11,019
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.
__________________
Meadbh is offline   Reply With Quote
Old 08-22-2013, 01:05 PM   #5
Thinks s/he gets paid by the post
 
Join Date: Nov 2009
Posts: 3,873
Quote:
Originally Posted by MichaelB View Post
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.
__________________
Retired in late 2008 at age 45. Cashed in company stock, bought a lot of shares in a big bond fund and am living nicely off its dividends. IRA, SS, and a pension await me at age 60 and later. No kids, no debts.

"I want my money working for me instead of me working for my money!"
scrabbler1 is offline   Reply With Quote
Old 08-23-2013, 02:23 PM   #6
Thinks s/he gets paid by the post
grumpy's Avatar
 
Join Date: Jul 2004
Posts: 1,321
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.
__________________
...you can check out any time you like, but you can never leave...
grumpy is offline   Reply With Quote
Old 08-23-2013, 02:40 PM   #7
Thinks s/he gets paid by the post
Big_Hitter's Avatar
 
Join Date: May 2013
Location: In the fairway
Posts: 4,099
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
__________________
Swing hard, look up
Big_Hitter is offline   Reply With Quote
Old 08-23-2013, 02:47 PM   #8
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 16,474
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.
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
pb4uski is online now   Reply With Quote
Old 08-23-2013, 05:37 PM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Chuckanut's Avatar
 
Join Date: Aug 2011
Location: West of the Mississippi
Posts: 6,337
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.
__________________
The worst decisions are usually made in times of anger and impatience.
Chuckanut is offline   Reply With Quote
Old 08-23-2013, 06:27 PM   #10
Recycles dryer sheets
Cooked's Avatar
 
Join Date: Jul 2013
Posts: 67
Quote:
Originally Posted by Meadbh View Post
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.
__________________
Don't sweat the petty stuff.
Cooked is offline   Reply With Quote
Old 08-23-2013, 06:33 PM   #11
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2006
Posts: 11,019
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.
__________________
Meadbh is offline   Reply With Quote
Old 08-23-2013, 07:28 PM   #12
Thinks s/he gets paid by the post
 
Join Date: May 2008
Posts: 2,264
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?
__________________
tmm99 is online now   Reply With Quote
Old 08-23-2013, 08:13 PM   #13
Thinks s/he gets paid by the post
sengsational's Avatar
 
Join Date: Oct 2010
Posts: 3,849
Quote:
Originally Posted by Chuckanut View Post
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
__________________
sengsational is offline   Reply With Quote
Old 08-23-2013, 08:28 PM   #14
Full time employment: Posting here.
CCdaCE's Avatar
 
Join Date: Apr 2006
Posts: 887
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
__________________
"There's those thinkin' more or less, less is more, but if less is more, how you keepin' score?
It means for every point you make, your level drops. Kinda like you're startin' from the top..." "Society" - Eddie Vedder
CCdaCE is offline   Reply With Quote
Old 08-23-2013, 09:37 PM   #15
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 16,474
Quote:
Originally Posted by Meadbh View Post
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.
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
pb4uski is online now   Reply With Quote
Old 08-25-2013, 09:22 AM   #16
Thinks s/he gets paid by the post
sengsational's Avatar
 
Join Date: Oct 2010
Posts: 3,849
Quote:
Originally Posted by Meadbh View Post
... 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.
__________________
sengsational is offline   Reply With Quote
Old 08-25-2013, 12:19 PM   #17
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 2,936
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.
__________________
kaneohe is offline   Reply With Quote
Old 08-25-2013, 03:58 PM   #18
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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.
__________________
Animorph is offline   Reply With Quote
Old 08-25-2013, 05:46 PM   #19
Recycles dryer sheets
 
Join Date: Mar 2012
Posts: 57
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.
__________________
Delaney is offline   Reply With Quote
Old 08-25-2013, 06:35 PM   #20
Thinks s/he gets paid by the post
Htown Harry's Avatar
 
Join Date: May 2007
Posts: 1,516
Quote:
Originally Posted by pb4uski View Post
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.

What would you tell your 22 year old self?
__________________

__________________
No doubt a continuous prosperity, though spendthrift, is preferable to an economy thriftily moral, though lean. Nevertheless, that prosperity would seem more soundly shored if, by a saving grace, more of us had the grace to save.

Life Magazine editorial, 1956
Htown Harry is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


 

 
All times are GMT -6. The time now is 01:51 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.