Excel Assignment: using Finance functions like PV$,$ NPV$,$ FV $.$

Help a young working professional retire early by making a financial calculator which will

help her predict the future cash flows and corpus, based on her goals.

Assume she is of $25$ years of age and wishes to retire by $50.$ The major goals in her life are

marriage $($expenditure $25$ lacs$),$ kid$$s education $($one kid, major expenditure of $30$ lacs when

she attains a certain age$),$ and a car $(30$ lacs$).$

She should be able to add one$/$two more goals if she chooses to$.$

The calculator should be able to provide the flexibility to allow her to choose the above

expenses after certain number of years $($and also change the expense amounts$)$

The professional wants to create a certain minimum corpus after the retirement. She also

wishes to invest it at a rate of $8\%($or anything else she assumes$),$ and regularly withdraw a

certain amount to meet her expenses after retirement, which grows at the rate of a constant

annual inflation until the age of $80.$ To achieve that, she has to save from now onwards, till

retirement. Assume a constant saving rate $($i$.$e$.$ percentage of salary$)$ every month, and

constant annual growth in her salary and a certain amount of return from her investments.

The calculator should be good enough to help her choose these parameters.

For emergency medical expenses, she should also have a certain fixed amount in her

account at any given point in time, after retirement. This amount she can choose in the

calculator and the calculator should alert her on the screen if at any point in time, the corpus

falls below this amount because of the regular withdrawal.

Question $1.$ Make a financial calculator based on the above, which should be flexible enough

to work for all professionals of various ages and various retirement years.

Question $2.$ If her annual salary is $6$ lakhs, the growth rate in salary is $5\%,$ what should be

the savings rate to make a corpus of $2$ crores by retirement $($without considering the

expenses of the goals above$)$ if her investments fetch a return of $13\%?$ Age and retirement

age are to be assumed as give above.

Question $3.$ Make a two$-$way data table of her remaining wealth after $80$ years of age,

against the parameters Starting Salary and Savings Rate, with all the other parameters

being constant at some assumed value.