What does the monthly transfer have to be for the goal to hold nine times in ten?
A 40,000 goal, 6,000 saved and 36 months to go. The sheet says 900 a month gets there with 1,766 to spare. Across 20,000 futures 900 a month makes it in 71 in 100 runs, and 90% odds takes 950.
Finance Starter Monte Carlo Pro engine
After you install, this is the model to open.
What monthly saving gives 90% odds of hitting the goal on time?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.
What it does
A goal of 40,000, 6,000 saved already, 36 months until the date, and a return of 5% a year. As typed, 850 a month ends at 39,832 and falls 168 short, 900 ends at 41,766 with 1,766 to spare and 950 ends at 43,701 with 3,701 to spare, so 900 is the number most people would set the transfer to. Click Run with the return as a three-point estimate of -6%, 5% and 12% a year.
The seed is set to 7 and the trials to 20,000 so your first run reproduces these figures exactly. The balance against the goal at 850 a month has a median of -479.8, and 850 reaches the goal by the date in 42 in 100 runs. At 900 the median is 1,441.8 and the goal is reached in 71 in 100 runs, so the 1,766 to spare on the sheet is missing three times in ten.
At 950 the mean is 3,291.3 and the median 3,363.4, the tenth percentile is 37.0 and the fifth is -755.5, and the goal is reached in 90 in 100 runs, so 950 a month is the saving with 90% odds of hitting the goal by the date. Fifty a month, 1,800 over the three years, is what moving from 71 in 100 to 90 in 100 costs. The three candidates share one draw of the return in every trial, so the difference between their readings is the transfer alone.
The tornado has one bar, the return at 1.00, because the return is the only uncertain input; what the run adds is the odds, which the sheet cannot produce from a single rate. Second run: if the money sits in a savings account instead of the market, narrow the return draw in the Risk Analysis panel to 3%, 4% and 5% and rerun. The odds at 900 rise and the odds at 950 rise further, because a goal three years out is decided more by the transfer than by the return.
What the model cannot tell you: one annual return is drawn and held for all three years, so a bad first year and a good third are not in the range, and the true spread on a real portfolio is a little wider; the monthly transfer never misses, when real months do; nothing is taxed or charged fees; and the goal itself may move. To make it yours, put in your own goal, what is saved, the months to the date and the return you would defend, retype three monthly amounts you could actually afford, and set the same range on the draw in the Risk Analysis panel.
The model
It arrives on a tab called Template: Saving With 90% Odds, carrying these columns:
- Goal ($)
- 40000
with the model computed beside the data:
| Return per month (fraction) | 0.004074 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- A $10,000 product launch with an uncertain price, an uncertain quantity and an uncertain unit cost. One recalculation shows one NPV and tells you nothing. Twenty thousand show a mean of $24,879, a median of $24,291 and a positive NPV in 99.6% of them.What are the odds this project clears a positive NPV?
- When does your startup actually run out of money?When Does the Startup Run Out of Cash?
- What is the business really worth?What Is the Business Worth, as a Range?
- After this round, what do you actually walk away with?What Do the Founders Keep After the Round and the Exit?
- Does the launch still pay after it eats the flagship?Launch With Cannibalization
- What are the odds your product launch actually makes money?Product Launch Go/No-Go NPV
Every model like this one, and the method behind them: Monte Carlo simulation.