|
|
Let's say I'm planning for my
retirement. I'm
going to contribute a certain amount at the beginning of each year
towards my 'retirement fund'. I also plan to incease my contribution to
my retirement fund by $500. After 40 years when I retire, I wish to
have $100000 per annum for my retired life every year for the next 20
years. I wish to withdraw this money in the beginning of every year. I
am assuming that my investments will earn 10% per annum during the
first 20 years and during all other years I expect a return of 5% only.
Now what is the least amount I should contribute every year for the
next 40 years so that I can achieve my financial goal of having $100000
every year during my retirement? We can start with a trial amount
of say,$2500 for the first year. Using standard arithmetic formulas we
calculate the values over 60 years. Then we use the solver to find out
our starting minimum contribution as shown in the Excel training video. |
In the solver window under 'set target
cell'
enter $c$6, click on 'Min' option button against 'Equal to:' and under
'subject to constraints select the range $F$46:$F$65 and add the
constraint $F$46:$F$65>=0 to ensure that the balance for years 41 to
60 is always positive or 0. Under 'options' check the 'Assume linear
model' and 'Assume Non-Negative' options and then click solve. We find
our first year contribution should be $2265.51. As you can see solver
can be used to analyze financial situations where the investment
returns are not constant. |