How to Plan For a Financial Goal? (Using Excel)

For the last few articles, whatever I have written has all been based on goal-based investment planning. But I have not addressed the issue of how to actually make a plan for a particular goal. How much to invest each year, and how should the portfolio grow every year? I will address these in this article, I will provide a step-by-step process to make a very simple goal-based investment calculator using excel.

What is a Financial Goal?

All of us have some aspirations and dreams, when we make a plan for a dream and start working to achieve it, it becomes a goal. Among all our goals a few, which satisfy certain conditions are the financial goals.

A financial goal always have these:

• Time period (Period in which the goal is to be reached)
• A target corpus (Amount you require at the end of the time period)
• Purpose or use of the target corpus

For example a person wants Rs 10,00,000/- after 5 years to buy a car.

You should first try and make a list of all your financial goals, complete with the time period and target corpus. Chances are that you may not be able to invest in all of them. In such a case, you have to omit or postpone some of the goals and increase the time period of others. Through some trial and error, you will have a plan for all your goals with the monthly investment required and portfolio value at end of each year.

Before we start making our plan we have to understand the effect inflation have on our financial plan.

Effect of Inflation

Inflation is a simple concept, but some don’t understand it, and some don’t realize the effect it has on our financial plans. It is imperative to have a basic idea about the effects of inflation for our calculations and planning for a financial goal.

Put simply inflation is the rate at which the value of money is decreasing. Imagine a certain object which use to cost Rs 100/- a year ago, and now it costs Rs 110/-. This is a 10% increase in just a year, we can say that the cost of that object increased. Or, we can say the value of money decreased by 10%, it takes Rs 110/- to buy an object which Rs 100/- could buy a year ago. Inflation works like a negative compound interest compounded annually.

Whenever we make a plan for a goal it is easy to find out the present value of that goal. For the goal of child education, you can find out the cost of a UG course at present. We can also determine the approximate value of inflation by looking at the trend of the last few years. We have the present value, the time period (after how many years the child will take admission in the course), and we have the rate of interest. Using the compound interest formula we can calculate the future value of the goal, which will be our target corpus.

Next let us have look at one Excel function, one formula, and one excel feature we will use for our plan. I have clubbed them under a single heading of Formula Used.

Formula Used for Planning

We will be mainly using the following three formulas in excel to make our plan for a financial goal.

Future Value (FV): This is a financial function in excel. This is used to calculate the future value of a lumpsum investment or equal periodic investments. The syntax for future value is.

=FV(rate,nper,pmt,[pv],[type])

let us understand all the arguments used,

• rate: This is the rate of interest per period, for example in the case of monthly SIP it will be monthly return, in the case of annual SIP it will be annual returns.
• nper: This is the number of periods, again for example in the case of monthly SIP it is the number of months, in the case of annual SIP it is the number of years.
• pmt: In case of equal periodic investment, like SIP, this is the amount of these regular investments. This is a required value, so in the case of lump-sum investment, you leave this blank. As this is money outflow, the FV function takes a negative value and if you provide a positive value the result of FV will be negative.
• pv: The square brackets mean it is optional. This is the amount of lump-sum investment whose future value you want to calculate. In case it is not mentioned excel takes it as zero.
• type: This is used to indicate whether payment is made at the start of the period or at the end. 0 represents the end of the period, 1 represents the start. If not mentioned it is taken as 0.

For SIP (periodic investment) the syntax that will be used is, =-FV(rate/100,nper,pmt,,1)
For lump-sum investment the syntax used will be, =-FV(rate/100,nper,,pv,1)
Minus sign is used as the amount will be mentioned in positive but FV takes value in negative for the amount. Rate is divided by 100 as we use percentage value.

Goal Seek: It is used when you know the final result, but want to know one of the inputs. In a cell, if you are using a formula that takes input value from another cell, you can use Goal Seek to determine the value of the input cell by putting the required value of the result. For example, in cell B1 you have put the formula for the addition of A1, A2, and A3, and you want to determine the value of A1 so that value B1 becomes 1000, you can use goal seek.

To access Goal Seek in Excel you have to go to the Data tab, go to Data Tools, in What If Analysis you can find Goal Seek. It will ask you for three values,

• Set Cell: The cell which contains the formula whose value we know. In our example, it will be B1.
• To Value: In this, we have to enter the value we want to have in the Set Cell. In our example, it will be 1000.
• By Changing Cell: The cell whose value has to be changed to get “To Value” in the “Set Cell”. In our case, it will be cell A1.

Monthly Return: We can estimate the annual rate we might get or should expect, but the investment is done on a monthly basis. In our calculations and FV formula, we will need a monthly rate of return that is equivalent to the annual rate of return, i.e. both result in the same amount of returns. Many people just divide the annual rate by 12, which is not at all correct and the difference is substantial for long-term planning. Thus you might find people suggesting you use the rate/12 argument for the FV function, please avoid that. Rather we can calculate the monthly rate of return from the annual rate of return by using the following formula.

R_m = ((1 + (R_a/100) )^1/12 – 1)*100

Where Rm is the monthly rate of return in percentage
Ra is the annual rate of return in percentage

Let the Planning Begin

I am getting excited even when I am just writing about it. The task of actually making a plan after reading all the theories is very exciting, at least for me it is. So let us make a plan for a single goal using excel.

First and foremost we need to determine three things.

• Time Period
• Present Value of the goal
• Rate of Inflation for this Goal

Please do not consider a low value of inflation, because it is better to plan for the worst than actually facing the worst.

After these three we have to determine the following.

• Asset Allocation (you can read this to learn about asset allocation)
• Rebalancing (you can read this to learn about asset allocation)
• Equity reduction (you can read this to learn about asset allocation)
• At what percentage you will increase your monthly investment each year. This will depend on how much growth you expect in your earnings annually. For example, if you think your earning will increase 10% every year you can increase your investment by at least 5% each year.

So let us open our excel and make our plan one step at a time. In excel columns are represented by alphabets and rows by numbers. So, cell A1 means cell of the first row of A column, and B13 means cell of 13^th row of column B.

STEP 1

In this step we will be entering the basic data which will be constant throughout the plan period. In your spreadsheet put the following text value.

You will enter the value of these in column B, so B1 will have a present value of your goal, B2 will have a time period of your goal, and B3 will have the rate of inflation for this goal.

The final corpus will be calculated using FV formula in B4 cell. So in B4 write the following formula.

=-FV(B3/100,B2,,B1,1)

Take note that after B2 the two commas are required and negative sign before FV

Again in column C you will put some text values.

Cell Number	Value OR Formula	Description
C1	Put text “Return Expected form Equity”	Heading for value in D1
C2	Put text “Return Expected form Debt”	Heading for value in D2
C3	Put text “Annual Increase in investment”	Heading for value in D3

The value of these will be entered in column D, so D1 will have the value of the annual return you expect from equity, D2 will have the value of the annual return you expect from debt, and D3 will have the percentage by which you can increase your investment each year.

Remember to keep your expected return from equity and debt realistic, a high expectation will increase the chances of you ending up short of your target corpus. A 10% return from equity and 5% from debt should be the maximum you should expect.

These will be annual rates but our investments will be monthly, so for sake of calculation we need monthly returns, we can calculate these using formula mentioned earlier.

• In E1 put text value “Monthly return for Equity” and in F1 use the following formula, = (((1 + (D1/100))^(1/12)) – 1)*100
• In E2 put text value “Monthly return for Debt” and in F2 use the following formula, = (((1 + (D2/100))^(1/12)) – 1)*100

You spreadsheet should look like the one in image.

STEP 2

This step will include putting in values and formulas for each year of our plan. We will be entering the formulas for the first and second year manually and thereafter we can simply copy the formulas of the second year for the rest of the years of our plan.

Row 6 will have the title for the columns representing different values for each year.

Cell Number	Value OR Formula	Description	Manually Filled OR Calculated
A6	Put text “Year”	Column A will indicate the year.	Value to be entered manually for each row/year.
B6	Put text “Percentage of Equity Investment”	Column B will contain the percentage of equity investment for that year as per asset allocation for the year.	Value to be entered manually for each row/year.
C6	Put text “Percentage of Debt Investment”	Column B will contain the percentage of debt investment for that year as per asset allocation for the year.	Value to be entered manually for each row/year.
D6	Put text “Total Monthly Investment”	Column D will contain the value of the monthly investment for that year.	Value to be entered manually for the first year, for the rest it will be calculated depending on the value of D3.
E6	Put text “Total Equity Corpus”	Column E will contain the value of the Equity corpus at the end of the respective year, before rebalancing	Will be calculated based on the formula.
F6	Put text “Total Debt Corpus”	Column E will contain the value of the Debt corpus at the end of the respective year, before rebalancing	Will be calculated based on the formula.
G6	Put text “Equity Corpus after Rebalancing	Column E will contain the value of the Equity corpus at the end of the respective year, after rebalancing	Will be calculated based on the formula.
H6	Put text “Debt Corpus after Rebalancing”	Column E will contain the value of the Debt corpus at the end of the respective year, after rebalancing	Will be calculated based on the formula.
I6	Put text “Total Corpus”	Column E will contain the value of the total corpus at the end of the respective year.	Will be calculated based on the formula.

Now let us enter the values and formulas for first year, which will be contained in row 7

Cell Number	Value OR Formula	Description
A7	Put numeric value “1”	Indicate first year
B7	Blank, for now, the value of equity percentage for the first year will be entered later
C7	Enter formula “=100-B7”	The debt percentage will be 100 minus the equity percentage.
D7	Blank, for now, the value of the monthly investment for the first year will be entered later
E7	Enter formula “=-FV($F$1/100,12,(D7*(B7/100)),,1)”	F1 contains the monthly rate of return for equity, 12 is the number of months, and for the amount, we have multiplied monthly investment with the percentage of equity investment. Using this we get the equity corpus at the end of the year before rebalancing. ($ signs are for fixing the row and column number)
F7	Enter formula “=-FV($F$2/100,12,(D7*(C7/100)),,1)”	F2 contains the monthly rate of return for debt, 12 is the number of months, and for the amount, we have multiplied monthly investment with the percentage of debt investment. Using this we get the debt corpus at the end of the year before rebalancing. ($ signs are for fixing the row and column number)
G7	Enter formula “=((E7+F7)*(B8/100))”	We do the rebalancing according to the asset allocation of next year, as rebalancing is done at the end of the current year.
H7	Enter formula “=(E7+F7)-G7”	Subtracting the value of rebalanced equity corpus from the total corpus will give us rebalanced debt corpus.
I7	Enter formula “=G7+H7”	Simply adding the debt and equity corpus to get the total corpus at the end of the year.

In the same exact manner let us enter the values and formulas for row 8 which correspond to year 2 of our plan.

Cell Number	Value OR Formula	Description
A8	Put numeric value “2”	Indicate the second year
B8	Blank, for now, the value of equity percentage for the second year will be entered later
C8	Enter formula “=100-B8”	The debt percentage will be 100 minus the equity percentage.
D8	Enter formula “=D7+(($D$3/100)*D7)”	This will calculate the monthly investment for the current year based on the monthly investment for last year, and the annual increment you have mentioned in cell D3. ($ signs are for fixing the row and column number)
E8	Enter formula “=(-FV($F$1/100,12,(D8*(B8/100)),,1))+G7+(G7*($D$1/100))”	This will add the equity corpus of last year (G7), return on last year’s equity corpus, and the corpus due to monthly equity investment of the current year. ($ signs are for fixing the row and column number)
F8	Enter formula “=(-FV($F$2/100,12,(D8*(C8/100)),,1))+H7+(H7*($D$2/100))”	This will add the debt corpus of last year (H7), return on last year’s debt corpus, and the corpus due to monthly debt investment of the current year. ($ signs are for fixing the row and column number)
G8	Enter formula “=((E8+F8)*(B9/100))”	We do the rebalancing according to the asset allocation of next year, as rebalancing is done at the end of the current year.
H8	Enter formula “=(E8+F8)-G8”	We do the rebalancing according to the asset allocation of next year, as rebalancing is done at the end of the current year.
I8	Enter formula “=G8+H8”	Simply adding the debt and equity corpus to get the total corpus at the end of the year.

You spreadsheet should look like the one in image.

Now you have to simply select the 8^th row i.e. the row for second year and drag it down as much as you need.

STEP 3

After Step 2 your planner is ready, all that is left is to enter the values and use this excel sheet to actually make a goal-based investment plan. You will be entering the following values for your goal.

• Present Value of your goal (B1 cell)
• Time Period (B2 cell)
• Inflation rate (B3 cell)
• Expected rate of return Equity (D1 cell)
• Expected rate of return Debt (D2 cell)
• How much you can increase your monthly investment in percentage (D3 cell)
• Your asset allocation for equity and debt will have to be entered manually for each year (column B and C for each year)
• At last, you have to enter monthly investment for first-year (D7 cell)

You will have to determine the first-year monthly investment through trial and error until your final year corpus comes equal to your required corpus (B4 cell). Or you can use the Goal Seek function in Excel. For this find the Goal Seek function in Excel, you can find it under Data > Data Tools > What If Analysis > Goal Seek.

In Goal Seek you have to enter the following three values.

Set Cell: The cell in which we want to have a particular value. In our case, it is the cell containing the total corpus of last year.
To Value: In this, we have to enter the value we want to have in Set Cell. In our case, it is the value of the target corpus that has been calculated in cell B4.
By Changing Cell: The cell whose value we want to change to get “To Value” in the “Set Cell”. In our case, this is the monthly investment of the first year i.e, cell D7.

At the end of this, you will have the monthly investment required each year throughout the goal period. You will also have the equity and debt corpus at end of each year. The real performance of your investment will obviously differ from this plan. You can compare the actual corpus and planned corpus at the end of each year to monitor and manage your investment. This plan can also work as a benchmark for your investment.

Make plans for all your financial goals using this and you will have an overall investment plan. You can also monitor the performance of investments easily by comparing them to your plan. Thus making the questions like, “Should I sell XYZ fund?”, “Should I book profit?”, etc a bit easier to answer.

If you think the information provided here can help others. Please spread the word.