In the most recent posts I discussed at high level the type of asset classes available, their advantages and how we plan to use each of them in our investment plan. In this I am sharing an excel based calculator that will help to identify monthly investments needed to achieve a goal.
You can plan upto 8 goals using this calculator. It will calculate upto next 40 years. There might be some rare occasions where we might plan a goal farther than this. If you are, then unhide the “Calculations” worksheet and extend the formula to further rows as required – 12 rows for each year.
There are 3 sets of information that you need to provide
- Specify the corpus required for the goal – either in today terms or future value. If specifying the value in today terms, also mention the rate of inflation for that particular goal
- Specify the year at which the goal is targeted
- Specify the time period till which investments towards the goal are possible. It is assumed that you will invest from today.
- Specify the yearly increase in the investment amount. This is based on assumption that investment will increase as your salary/income increases. If no increase, mention 0%.
- Mention the horizon for short term, medium term and long term
- Mention the asset allocation for each term and expected ROI for each asset. The average return is calculated automatically.
All cells in BLUE need an input. All cells in Pink are calculated.
You will need to manually use Goal Seek option to calculate the monthly investment required. This has to be done for each goal. I could not find an automated way to run this for now.
Update: Included option to add any existing corpus into the calculations. Corrected 1 formula error for tracking yearly increase in investment.
Finally the link to the excel calculator
I am looking to see how the Goal Seek execution can be automated via a formula or macro etc. Please run some trial and error scenarios and share any feedback or improvements.