Microsoft Excel training videos

Using Solver for Financial Planning

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.






Reference

Using Solver in Excel for financial planning