• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Evergreen Small Business

Actionable Insights from Small Business CPAs

  • Home
  • Small Business FAQ
  • Monographs
    • Business Planning Workbook
    • Download Your Free Copy of the Thirteen Word Retirement Plan
    • Five Minute Payroll Monograph (2019 Edition)
    • LLC Operating Agreement
    • Maximizing PPP Loan Forgiveness
    • Maximizing Sec. 199A Deductions Monograph
    • Preparing Form 3115 for the Tangible Property Regulations
    • Preparing U.S. Tax Returns for International Taxpayers
    • Real Estate Tax Loopholes & Secrets
    • Red Portfolio Black Portfolio FAQ and Download
    • Sample Corporate Bylaws
    • Setting Low Salaries for S Corporations
    • Small Business Tax Deduction Secrets
    • Small Businesses and the Affordable Care Act (Obamacare)
    • Joining Our Affiliate Program
  • Our Bloggers
  • Free LLC Formation Kits
    • Alabama LLC
    • Alaska LLC
    • Arizona LLC
    • Arkansas LLC
    • California LLC
    • Colorado LLC
    • Connecticut LLC
    • Delaware LLC
    • Florida LLC
    • Georgia LLC
    • Hawaii LLC
    • Idaho LLC
    • Illinois LLC
    • Indiana LLC
    • Iowa LLC
    • Kansas LLC
    • Kentucky LLC
    • Louisiana LLC
    • Maine LLC
    • Maryland LLC
    • Massachusetts LLC
    • Michigan LLC
    • Minnesota LLC
    • Mississippi LLC
    • Missouri LLC
    • Montana LLC
    • Nebraska LLC
    • Nevada LLC
    • New Hampshire LLC
    • New Jersey LLC
    • New Mexico LLC
    • New York LLC
    • North Carolina LLC
    • North Dakota LLC
    • Ohio LLC
    • Oklahoma LLC
    • Oregon LLC
    • Pennsylvania LLC
    • Rhode Island LLC
    • South Carolina LLC
    • South Dakota LLC
    • Tennessee LLC
    • Texas LLC
    • Utah LLC
    • Vermont LLC
    • Virginia LLC
    • Washington LLC
    • West Virginia LLC
    • Wisconsin LLC
    • Wyoming LLC
  • S Corporation Kits
    • Alabama S Corporation
    • Alaska S Corporation
    • Arizona S Corporation
    • Arkansas S Corporation
    • California S Corporation
    • Colorado S Corporation
    • Connecticut S Corporation
    • Delaware S Corporation
    • Florida S Corporation
    • Georgia S Corporation
    • Hawaii S Corporation
    • Idaho S Corporation
    • Illinois S Corporation
    • Indiana S Corporation
    • Iowa S Corporation
    • Kansas S Corporation
    • Kentucky S Corporation
    • Louisiana S Corporation
    • Maine S Corporation
    • Maryland S Corporation
    • Massachusetts S Corporation
    • Michigan S Corporation
    • Minnesota S Corporation
    • Mississippi S Corporation
    • Missouri S Corporation
    • Montana S Corporation
    • Nebraska S Corporation
    • Nevada S Corporation
    • New Hampshire S Corporation
    • New Jersey S Corporation
    • New Mexico S Corporation
    • New York S Corporation
    • North Carolina S Corporation
    • North Dakota S Corporation
    • Ohio S Corporation
    • Oklahoma S Corporation
    • Oregon S Corporation
    • Pennsylvania S Corporation
    • Rhode Island S Corporation
    • South Carolina S Corporation
    • South Dakota S Corporation
    • Tennessee S Corporation
    • Texas S Corporation
    • Utah S Corporation
    • Vermont S Corporation
    • Virginia S Corporation
    • Washington S Corporation
    • West Virgina S Corporation
    • Wisconsin S Corporation
    • Wyoming S Corporation
  • Contact Nelson CPA
You are here: Home / Monographs / Red Portfolio Black Portfolio FAQ and Download

Red Portfolio Black Portfolio FAQ and Download

The list of questions and answers below probably supply most of the information people need to use the Red Portfolio Black Portfolio spreadsheet. However, if you find yourself with a question not answered here? Use the Nelson CPA contact form to send us your question. We’ll try to get it answered ASAP.

Getting started:
Where can I download current Red Portfolio Black Portfolio spreadsheet?
Why can’t I see spreadsheet formula?

Using spreadsheet:
How do I simulate accumulations?
How do I simulate withdrawals?
Does the spreadsheet calculate withdrawal failure rates?
Will spreadsheet estimate safe withdrawal rates?
How does the spreadsheet estimate returns?

Technical issues:
Can I use geometric means for simulations?
Where can I get arithmetic means and standard deviations?
What do Excel error messages mean and how do I fix?

Where Can I Download Current Spreadsheet?

You can grab the most up-to-date version of the spreadsheet here: RedPortfolioBlackPortfolio

A related comment: We do occasionally update the spreadsheet as we discover bugs or usability issues. Thus, you may want to check back for a more up-to-date version later.

Why Can’t I See the Spreadsheet Formulas?

Okay, an awkward topic: But one of the aggravations of publishing free content and free downloads via a blog? People steal your material.

To minimize that risk, then, I encrypted the spreadsheet with a password and then added copyright notice to the top of the page. I also hid the two worksheets that calculate the Red Portfolio Black Portfolio simulated returns and balances and the internal rates of return. Sorry if this step causes you any inconvenience.

How Do I Simulate Accumulations?

You take a handful of simple steps to simulate how much money you’ll accumulate in the two portfolios:

  1. Enter your initial investment, or current investment portfolio balance, into cell B4.
  2. Enter the annual addition you plan to add to your savings into cell B5.
  3. If you will increase your savings amount over time—such as for inflation—enter the percentage growth into cell B6.
  4. You describe the Red Portfolio’s characteristics by entering your current or planned portfolio’s standard deviation into cell B8 and its arithmetic mean into cell B9.
  5. You describe the “benchmark” Black Portfolio’s characteristics by entering the “benchmark” portfolio’s standard deviation into cell D8 and its arithmetic mean into cell D9.
  6. Press F9 to have Excel recalculate the workbook’s formulas and redraw the line chart. (If Excel doesn’t seem to fully redraw the line chart—which is problem we’ve spotted on some displays—try saving the workbook to force Excel to redraw the chart.)

How Do I Simulate Withdrawals?

I didn’t initially plan to support withdrawal simulations. It turns out though that you can do this simply by entering a negative value into cell B5. The specific steps you take are as follows:

  1. Enter the investment portfolio balance you anticipate at the start of your retirement into cell B4.
  2. Enter the annual withdrawal amount you plan to add to your savings into cell B5. The value entered should be a negative value.
  3. If you will increase your withdrawal amount over time—such as for inflation—enter the inflation into cell B6.
  4. You describe the Red Portfolio’s characteristics by entering your retirement portfolio’s standard deviation into cell B8 and its arithmetic mean into cell B9.
  5. You describe the “benchmark” Black Portfolio’s characteristics by entering the “benchmark” portfolio’s standard deviation into cell D8 and its arithmetic mean into cell D9.
    Press F9 to have Excel recalculate the workbook’s formulas and redraw the line chart. (If Excel doesn’t seem to fully redraw the line chart—which is problem we’ve spotted on some displays—try saving the workbook.)

Does the Spreadsheet Calculate Withdrawal Failure Rates?

No. Not really. But it visually shows you how many simulations fail when you’re modeling withdrawal scenarios. The trick? Be sure you use the Red Portfolio inputs for the portfolio you want to analyze failures for. When do that, each line that drops to zero represents a failure. With 100 simulations and 5 failures, for example, you can say that five percent of the simulations modeled “failed.”

You can also spot when a simulation fails. If a red line plummets to zero in year 12, that means retiree runs out of money in year 12. And I mention that for a reason. Probably early failures matter more than later failures. (A retiree is more likely to be living at the time an early failure occurs.) Also consider the effect of the long forecasting horizon of 40 years. Failures occurring far into the future may not matter.

Will Spreadsheet Estimate Safe Withdrawal Rates?

Sort of. The spreadsheet uses historical estimates of portfolio standard deviations and arithmetic means. That approach should mean you’re running simulations within the realm of possibility.

But that said, we’d describe the Red Portfolio Black Portfolio spreadsheet more as tool you use to visualize portfolio variability. And both upside and downside risk.

And then this thought. Using historical standard deviations and arithmetic means? Sure. Probably useful. But many analysts warn returns going forward will be dramatically. Thus you might want to experiment with lower arithmetic means. (As I was originally working on this FAQ, I looked up the returns the mutual fund company I use gives. And those numbers are well below the historical average. Yikes.)

How Does the Spreadsheet Estimate Returns?

The spreadsheet uses two Microsoft Excel functions to calculate returns that show a normal distribution and reflect the standard deviation and arithmetic mean you input. The actual building block formula shows below:

NORMINV(RAND(),StandardDeviation,ArithmeticMean)

An earlier blog post, Stock Market Monte Carlo Simulation, provides working examples of using that basic formula to create a Monte Carlo simulation spreadsheet. You might also want to look at this blog post: Small Business Monte Carlo Simulation.

One other note: The spreadsheet actually estimates two sets of returns: It calculates 100 scenarios for the Red Portfolio standard deviation and arithmetic mean. (Those scenarios get plotted in the chart’s red lines.) And it also calculates 100 separate scenarios for the Black Portfolio standard deviation and arithmetic mean. (Only the worst and best of those get plotted with the two, thick, dashed lines.)

Can I Use Geometric Means for Simulations?

You should use arithmetic means for Monte Carlo simulations. If an investment portfolio delivers 4% one year and then 12% the next year, for example, the arithmetic average equals 8% because (4%+12%)/2 equals 8%.

A geometric mean—also known as an internal rate of return and also known as the compound average growth rate—shows the compounding effects of time. Investment companies by the way report geometric means. When an online calculator tells you some mutual fund or exchange-traded fund earned X percent over two decades, yeah, that too is a geometric mean. But geometric means don’t work for Monte Carlo simulations. Geometric mean returns are slightly lower. And they already effectively adjust for the risk and bouncing about. With an investment that alternates between a 4% and 12% arithmetic return and so delivers an 8% arithmetic average, for example, the geometric average equals 7.93%.

Where Can I Get Arithmetic Means and Standard Deviations?

For the blog posts here and the example inputs, I used estimates of the standard deviation and arithmetic mean return that come from Aswath Damodaran’s web pages at New York University’s Stern Business School: Historical Returns on Stocks, Bonds and Bills. Using Professor Damodaran’s data, for a “100% stocks” portfolio I got a standard deviation of about 19.5% and an arithmetic average return of about 11.5%. For a balanced portfolio of “70% stocks and 30% bonds,” I calculated a standard deviation of about 14% and an arithmetic average of about 9.5%.

You can also get a great table of annual already-calculated arithmetic returns for several asset classes (from 1928 through 2022) from Wade Pfau’s excellent resource: Historical Market Returns.

A tool like Portfolio Visualizer’s Backtest Asset Class Allocation calculates standard deviations and geometric means of existing portfolios. But you can then convert the geometric mean to an arithmetic mean, and then use that for your simulations.

One other note: You can roughly estimate the equivalent arithmetic mean for a given geometric mean using a back Michael Kitces described in one of his blog posts about volatility. Specifically, you square the standard deviation and then adding half of this result to the geometric mean. If the geometric mean equals 10%, or .1, and the standard deviation equals 20%, or .2, the arithmetic mean equals approximately 12% as calculated with this formula:

10%+(20%*20%)/2

What do Excel Error Messages Mean and How Do I Fix?

Two situations in our testing produce errors in workbook calculations. First, if the internal rate of return for a scenario is so extreme–either good or bad–that Excel can’t calculate it? Excel will return the #NUM error. Your recourse in this case is to throw out that set of simulations and recalculate another set.

A second error also occasionally appears due to the a catastrophically bad sequence of returns at the very start of a scenario. Excel may return the #NA error in one or more of its simulation results. And that error will ripple through some of the formulas. Again, your record here is throw out the simulations just calculated and recalculate another set.

Primary Sidebar

Welcome

Nelson CPA publishes this blog to help and encourage small business owners. Click here to learn more about our firm.

S corporation Tools

Use our S corporation tax savings calculator to make a quick estimate of the annual tax savings per owner.

Use our S corporation reasonable compensation calculator to estimate appropriate shareholder-employee salaries.

Featured Posts

Use our free Washington state estate tax calculator to estimate estate taxes.

Washington State Estate Tax Calculator (2025 Version)

A new estate tax law passed by the Senate in April of 2025, and currently being considered by the House, taxes estates in excess of $3,000,000 at … [Read More...] about Washington State Estate Tax Calculator (2025 Version)

Nate Silver On The Edge provides great risk management insights for entrepreneurs

Nate Silver On the Edge: Actionable Insights for Entrepreneurs

I read Nate Silver’s On the Edge on a recent trip to the California desert. The book isn’t really about entrepreneurship or small business ownership … [Read More...] about Nate Silver On the Edge: Actionable Insights for Entrepreneurs

Deep work tax deductions might mean you work someplace isolated and without distraction.

Deep Work Tax Deductions

On a recent vacation, I read Cal Newport’s book, “Deep Work.” Which got me thinking about tax deductions for deep work. Newport’s ideas have great … [Read More...] about Deep Work Tax Deductions

International tax issues?

Preparing US tax returns for international taxpayers

Maximize S corporation tax savings

Setting Low S Corporation Salaries

Updated for 2019 tax year changes and now available in print from Amazon!!

Maximizing Sec. 199A Deductions

Free retirement planning help

Picture of Thirteen Word Retirement Plan book

Need to help clients with their PPP loan forgiveness applications?

Recent Comments

  • Small Business ChatGPT Tips and Tricks - Evergreen Small Business on Using ChatGPT in a Small Business
  • Merton Share Estimator - Evergreen Small Business on Super Safe Withdrawal Rate Calculator
  • Super Safe Withdrawal Rate Calculator - Evergreen Small Business on Merton Share Estimator
  • barry on How to Get Extra Year Section 199A Deductions
  • How to Get Extra Year Section 199A Deductions - Evergreen Small Business on Section 199A(i) Fiscal Year Change Extends Deduction

Archives

Copyright © 2025 Stephen L. Nelson, Inc. · News Pro On Genesis Framework · WordPress