What’s your Calculation Method?

 

The beauty of Mathematics lies in the positivity that Every problem has a solution and the solution can arrive in multiple ways. The important factor of a solution depends on the efficient use of variables to arrive at it.
To take up the case today, we will consider the example of Child Education planning.

 

Scenario: A child aged 10 years will require today, Rs.2 Lakhs p.a. for 3 years of Graduation starting at age 18 years and Rs.5 Lakhs p.a. for 2 years of post-Graduation starting at age 21 years.
We will use 3 methods to arrive at the solution and discuss the Pros & Cons of each of them.

 

 

Information:

We calculate the future values for each year of the Graduation and post-Graduation year considering the inflation for each year as 10.00%. p.a.
The Future values are as shown in the table corresponding to each year for the respective current values.

 

Method 1:
In this method we discount the future values of each year to the starting year of Graduation i. e. each year future value starting from age 19 to 22 years is discounted at the Debt returns of 5.00% p.a. till age 18 years and the future value at age 18 is also added to get the total amount required for funding the 5 years of education, which comes at Rs.38,71,682.

 

Formula (Excel function) used is
PV18 = FV18 + NPV (5% , FV19 : FV22) = 38,71,682

 

Now this Rs.38,71,682 is used as FV to find out the monthly investment required over the 8 years (18-10) considering 12.00% p.a. returns, which comes at Rs.24,657.

 

Formula (Excel function) used is
Monthly investment = PMT ( Nominal (12%,12)/12 , 8*12 , 0 , – 3871682 , 1) = 24,657

 

Method 2:
In this method we can find the SIP amount for each year Future value with 12.00% p.a. returns and add the total SIP amount to arrive at the SIP to be initiated.

 

Formula (Excel function) used for each calculation is
Monthly SIP = PMT (Nominal (12%,12)/12 , (18-10)*12 , 0 , – 428718 , 1) = 2,730
The SIP amount reduces once each goal year is reached to become 0 by age 22 years.
The final result for each year is as shown in the table below:

 

Method 3:
This is also called as Glide path.
In this method, we can distribute the SIP amount per month in a particular ratio of Equity & Debt right from start and fund the Goals only from Debt portfolio as per the schedule. We then find the SIP value by the Trial & Error method (alternately using an excel function “Goal Seek”).

 

For the example, we start with 100% Equity allocation till 3 years before the Graduation starts i.e. till age 15 years starting, the amount will be completely in Equity; from this point the Equity allocation drops by 20% each year and the SIP amount, as well as the corpus accumulated, is also reduced in the same ratio. As shown in the table below, the Equity allocation at age 15 years becomes 80% and Debt becomes 20%, thus splitting the SIP amount as well as the accumulated corpus by age 14 years end. The SIP is continued till age 21 years i.e. 1 year before the goal is completed.

 

We start with a random SIP amount of Rs.1,000 and using “Goal Seek”, we find the SIP value comes at Rs.20,131.
As can be seen the SIP in the last 3 years is completely in Debt providing less volatility to the invested amount as well as the accumulated amount from earlier Equity investment.

Having seen all 3 methods, let’s check out some pointers for comparing the Pros and Cons of each method, as shown in the table below :

 

Conclusion:

 

There may be more methods to arrive at the solution required.
Depending on the preference of each pointer, one may use the solution suitable to them.
So, What’s your Calculation Method?