# What’s the expected value of your Powerball ticket?

As the Powerball jackpot grows to over \$300 million, we start to wonder if maybe buying a ticket is “worth it.” While the lottery is “worth it” in that ticket sales goes to things like state education, buying tickets is typically not worth it for yo because the projected payoff is far less than the ticket price.

The concept of Expected Value is a central idea in probability and statistics and refers to a weighted average outcome. For our Powerball example, the expected value equals the probability of getting each combination of winning numbers, multiplied by the payoff of the combinations.

Thehas a table with the odds and the payouts of each winning combination of numbers, making our job relatively easy.

We can transcribe these into a spreadsheet:

There isn’t much logic to this one. First we convert the odds column into probability, by taking one over the odds plus one.

Then we multiply the probabilities with the respective payouts by using the Sumproduct() Function:

Since Powerball tickets cost \$2, it seems we’re still losing about 65 cents on each ticket even with the jackpot above \$300 million.

It looks even worse given the \$302 million is the simple sum of annuity payments, while the cash value is much less at \$187.3 million.

That takes us down to 96 cents.

Taking into account a 35% tax rate would then take us down to 62 cents. And then there’s the chance that there are multiple jackpot winners, resulting in a split (we won’t get into that one here, but . Finally, let’s use the Goalseek function in excel to see when it becomes worth it to buy a lottery ticket taking into account a 35% tax bracket.

Pull up Goalseek from the Data Tab –> What-If Analysis –> Goal Seek

Then we set the after tax expected value cell (D16) to \$2, by changing the jackpot cell (B5). Basically Goalseek is Excel’s functionality for “guessing and checking”, for those who are not as algebraically inclined…

The answer? \$806 million! And that isn’t accounting for potential splits which is pretty likely once the jackpot gets that high.

# How much money do you need to retire?

As mentioned before, Every Quora session reveals financial success stories, and some practical life advice.

One topic that comes up frequently is how much money one needs to stop working. That is a question that spreadsheets are well-suited to solve! Let’s build our

• Current Age – The older you are, the less time you’ll need your money to last.
• How much money you have saved up – This pile of money is what will earn returns to support your lifestyle, since you won’t be working
• The annual return on your investments – More risk, more return…
• Your expenses – Retiring isn’t cheap! When you quit your job, you’ll have to find health and dental insurance, you’ll still have to pay taxes on your investment earnings, and your kids will still need to go to college (taking out student loans builds character?)… we also need to assume expense inflation. This site assumes the average annual expenditure is about \$56,000, including taxes.

That’s pretty much it! Here’s our spreadsheet so far:

Most of our logic will follow our post, Personal Finance Made Easy (with Spreadsheet Modeling!). We’ll forecast our investment income, expenses, and the balance of our money saved for each year going forward in each column in our spreadsheet. First, we’ll set up year 1 pulling in the inputs from our assumptions cells. The investment income each year will equal the money saved (B15) times our assumed investment return (b7), with a fixed reference on the cell B7:

Our net income is the investment income minus expenses, which we will add to our initial money saved balance to get the ending balance:

Now for year 2, the current age will increase by 1, the beginning money saved for year 2 is the ending money saved from year 1, investment income will be the same formula, and expenses will increase by inflation:

Now we can paste the formula from column C as far to the right as we need in order to simulate future years:

(This guy probably needs to keep working…)

Now that we have a projection of how much money we’ll have at each year in the future, we can add in a couple cells to ask interesting questions of our spreadsheet, like: Will I have enough money to last until I am 100 years old? We can use an HLOOKUP() to look up how much money is saved at age 100:

Finally, we can make use of a data table to see how much money we need to have saved at what age to stop working and cover expenses until we’re age 100:

Looks like if we have about 2.5 million, we’re generally in the clear, or \$2 million after age 40. I’m realizing now that this doesn’t include social security and future one time college expenses, and a progressive tax formula, but I’ll leave those updates as an exercise for the reader.

One more interesting data table – how does the ability to retire change with the assumed return on investments? We’ll assume the user is age 35.

Every percent of extra return makes a huge difference on the resulting asset balance at age 100. While the 35 year old could return with \$2.5 million at 4%, they’d have to keep working until \$4 million if they were only earning 2%. This is the downside of the current low interest rate environment and

You can access the Excel spreadsheet here: How much money do you need to retire? Financial Freedom Calculator

# How to decide (fairly) where to grab lunch with friends

Taking a walk and grabbing lunch is one of the simple pleasures of the workday. Unless of course, you bring your lunch to work. Sometimes with a group of people it is hard to decide on where everyone should go to lunch together. Maybe only one person enjoys the guilty pleasure of Taco Bell, while the others want to stick with Whole Foods. Or maybe half of the group wants burgers and the other half pizza.

Here’s a relatively simple Google Mobile spreadsheet. It’s part 2 of our mobile phone spreadsheet series (See Part 1 on Tracking New Year’s Resolutions) that uses a weighted lottery to fairly determine where to go for lunch, taking into account each person’s individual preferences. Basically, each person gets 10 “points” to allocate to three restaurant choices. Each point is effectively a lottery ticket, and the spreadsheet randomly chooses the restaurant, with the probability weighted by how many points each restaurant has received. Continue reading

# Track New Year’s Resolutions on Mobile Sheets

This is the first in a series of posts focused on the Google Sheets app on our mobile phone, rather than the typical desktop spreadsheets. We use the mobile Google Sheets app to set our New Year’s Resolutions and track what percentage of days we have fulfilled our promises. Hopefully, having this tracker on our phone and nearby at all times makes it slightly easier to fulfill our resolutions! Continue reading

# Keep New Year’s Resolutions with Spreadsheets

What are your new year’s resolutions? As in most cases, coming up with the goals is easy…but achieving them is another story! According to aarticle published a couple of years ago, only 8% of Americans achieve their resolutions. How can we keep our resolutions? Can we be better at goal-setting? As an organizational and prioritization tool, spreadsheets can keep us accountable and help us reach our goals. In our busy lives, it can be a challenge to keep track of and prioritize everything we set out to do. Spreadsheets can help by making us better managers of our lives. Continue reading

# What makes a good business? Part 3 of the IRR trilogy

This post was inspired by reading a Quora response where the child of a top 1% family says that one of the advantages of his upbringing is that his parents taught him about how business works. “Business” is a somewhat broad term that some schools say takes two years and \$120k in tuition to “master”. However, as we typically do in this blog, we can educate ourselves on some of the central concepts of business on our own through spreadsheets!

Our hope is that anyone with internet access can find this page and play around with spreadsheets to begin to think about what makes a good business. Now if only this site could also provide the exotic travel experiences, connections, and the ability to take risks with a safety net that growing up in the 1% also provides… Continue reading

Is it better to buy or rent a house? Advice on this problem comes in all shapes and sizes, from the dogmatic idea that homeownership is the American dream, to some more nuanced methods like calculating the price to rent ratio. What would you do if you found a great house and are deciding whether to buy it or keep on renting?

# Is medical school “worth it”? An introduction to Internal Rate of Return (“IRR”)

Finance hiring is down, law school grads are having a tough time finding real law jobs, so what is an ambitious but risk averse college student to do with his or her life these days? Okay, right now the answer is computer science. Yes seriously, do computer science. But let’s pretend it is the year 2001 and the only other option respectable option is medical school. But doesn’t med school take a lot of time (4 years school plus 3-7 years residency/fellowship) and cost a lot of money? How can we figure out if going to med school and not earning doctor money until 7 years from now is worth it financially relative to just entering the workforce and working for those 7 years? Continue reading

# How to analyze a nonprofit Form 990 with a spreadsheet

“The more you give, the more you get, that’s being alive” – The Money Song, Avenue Q

Charitable giving serves a valuable purpose  in  our society. It allows organizations in health, education, social services and others to provide benefits to people who otherwise couldn’t afford them. It allows people who have built up wealth to give back and make a difference. The federal government even subsidizes charitable giving by allowing donations to be deducted from income reported for taxes (effectively kicking in up to 39.6% of each donation). It’s a great system that is meant to fund those people and organizations in need. At least that is how it should be. Continue reading