Since this is an assignment of some sort, it would be unethical for us to provide turnkey solutions.
As I noted in a discussion of a similar question (same assignment facts) by someone else 4 weeks ago, the interpretation of the facts and the assignment are not clear to me.
Perhaps you can clarify.
In particular, it is unclear which facts should be taken as immutable, and which facts you are expected to vary in order to find solutions.
For example, based on the
best-case interpretation of the facts as given, it is
not possible (!) to achieve the first goal of $45,000 at the end of year 10.
So clearly, some of the facts are not immutable.
(The best-case interpretation is: initial income is $32,000, increasing 3% per year, investing 10% of the previous year's income for each of 10 years, initially all in savings at 1% interest for the first 5 years, then all in securities (stocks and bonds) at 3.5% return for the next 5 years.)
-----
As to your question about how to use Solver, perhaps the following example might point you in the right direction.
The key to using Solver is to set up a "model" (set of calculations) with one or more "variables" (cell values) that Solver can modify.
For example, the following design might model the calculations for the first 5 years.
| what-if investment.xlsx |
|---|
|
|---|
| A | B | C | D | E |
|---|
| 1 | $32,000.00 | Income (year 0) | | | |
|---|
| 2 | 3.00% | %Income growth | | | |
|---|
| 3 | 10.00% | %Invest (years 1-5) | | | |
|---|
| | | | | |
|---|
| 5 | 1.00% | %Interest on savings | | | |
|---|
| 6 | 3.50% | %Return on securities | | | |
|---|
| | | | | |
|---|
| | | | | |
|---|
| 9 | | | | | |
|---|
| 10 | | | Savings |
|---|
| 11 | Year | Year-end Income | Year-start Deposit | Year-end Interest | Year-end Balance |
|---|
| 12 | 0 | $32,000.00 | | | |
|---|
| 13 | 1 | $32,960.00 | $3,200.00 | $32.00 | $3,232.00 |
|---|
| 14 | 2 | $33,948.80 | $3,296.00 | $65.28 | $6,593.28 |
|---|
| 15 | 3 | $34,967.26 | $3,394.88 | $99.88 | $10,088.04 |
|---|
| 16 | 4 | $36,016.28 | $3,496.73 | $135.85 | $13,720.62 |
|---|
| 17 | 5 | $37,096.77 | $3,601.63 | $173.22 | $17,495.47 |
|---|
|
|---|
You can click on the cells in order to see the formulas.
Note that I do not round the calculation. It is important not do that when using Solver (or Goal Seek). Rounding and calculations that are not "continuous" can cause Solver to fail to find a solution.
Note that based on 10% in A3, E17 shows the balance after 5 years, namely about $17,495.
But suppose we did not know that. Instead, suppose we want to find the %investment each year (A3) that results in a balance of $17,500 after 5 years (E17).
Initially, clear A3 (%Invest). It is prudent to start with an empty cell or zero (if that does not cause errors in the model). A non-zero starting value might influence the Solver result. (But sometimes, that is useful.)
In Excel 2010, I would click Data > Solver to open the Solver menu. If Solver is not available to you, you can use Goal Seek instead. Click Data > What-If Analysis > Goal Seek.
(Sometimes, Solver is better to use for one reason or another. If Solver is not available, someone might explain how to make it available. Your profile says you are using Office 365, and I am not familiar with the click-by-click navigation in Office 365 Excel.)
In Solver or Goal Seek, enter the following set-up:
Objective cell: E17
To value of: 17500
By changing: A3
Then click Solve or OK.
You should see that A3
appears to be 10.00%, as we might expect based on the original values. But note that the more precise value of A3 is about 10.0025913509246%.
(Your results might vary. Also note that E17 might not be
exactly 17,500.00.)
If that is an approach that you can work with, I leave it to you to expand the design to include the calculations after the first 5 years, based on your interpretation of the problem.
If you have further Excel questions about your design, be sure to include it in your follow-up responses.
