Every parent needs planning for their expenses towards the education of their children. This is generally ignored as they take this expense it as it comes. However, in this world with so many uncertainties, it is better to plan for this expense as otherwise it can adversely effect the future of their children.
I will present 2 cases here.
In the first case, let us see the situation when your child has completed his/her class XII and is ready for graduation and higher studies. Let us say, you start planning at this stage. Though this is a bit late, still it makes sense to plan to ensure that the child has uninterrupted studies till completing all the education that you have planned for him/her.
In the second case, let us see the situation where you start planning at an early stage when you child in still in primary school and you are planning for the higher education for the child. This is more prudent way of going about this aspect of life.
Whatever be the case, one needs to understand the TIME VALUE OF MONEY and take advantage of it.
Our younger daughter decided to pursue a career in Nursing. She stated her plan that she wants to complete the graduation which would take 4 years of study. After that she wanted to complete the Masters, which would take another 2 years. And then she wanted to specialise in ICU Nursing, which would take 1 year. So, we had to plan to fund her education for the next 7 years.
We could not have planned for this earlier as we were never sure what education our daughter would like to pursue. This can be the case with many parents.
We found out that for the B.Sc. Nursing we would have to spend Rs. 75,000 per year for 4 years as college fees. For the first year, we would have to spend Rs. 10,000 extra for University Registration Fees, etc. In addition, we would have to keep aside Rs. 50,000 per year as fees for various activities like Hospital Duties, Medical Camps, Uniforms, and also for College Trips.
For M.Sc, the fees in her present University is Rs. 1,00,000 per year for 2 years. In addition, we can estimate another Rs. 50,000 per year for miscellaneous expenses.
For ICU Specialisation, we estimated the cost to be Rs. 2,00,000 for the 1 year. We were told that for the practicals, which would be done in some hospital, the students would given some stipend. We ignore this amount in our calculations.
So, the expense sheet for the next 7 years looks as follows.
|Year||College Fees||Extra Expenses||Total Amount|
|1||Rs. 85,000||Rs. 50,000||Rs. 1,35,000|
|2||Rs. 75,000||Rs. 50,000||Rs. 1,25,000|
|3||Rs. 75,000||Rs. 50,000||Rs. 1,25,000|
|4||Rs. 75,000||Rs. 50,000||Rs. 1,25,000|
|5||Rs. 1,00,000||Rs. 50,000||Rs. 1,50,000|
|6||Rs. 1,00,000||Rs. 50,000||Rs. 1,50,000|
|7||Rs. 2,00,000||–||Rs. 2,00,000|
The first thing I needed to do was to find the Present Value of this amount required. Present Value is required as Rs. 10,10,000 over the next 7 years would be more if this amount is available today. This is because the value of money keeps decreasing every year. So, if I have this amount in hand today, it would be more valuable in the future. Let us consider that the inflation rate is 6% in India.
Present Value can be calculated in Excel using the following formula:
=PV(rate, nper, pmt, FV)
Here, rate is the rate at which money would be discounted. In our case, we have considered this to be 6%.
nper is the period of discounting. In our case, Rs. 1,35,000 could be discounted for 0 years as it is as of today, Rs. 1,25,000 required in the year 2 would be discounted for 1 year and so on.
pmt is required if we are investing money at regular intervals (in this case, it would be every year). However, as we are not investing every year, this value would be set to 0.
FV is the future value required. This is Rs. 1,35,000 required in Year 1, Rs. 1,25,000 required in Year 2 and so on.
Based on this, the present value of this required amount would be as follows:
|Year||Amount Required||Present Value|
|1||Rs. 1,35,000||Rs. 1,35,000.00|
|2||Rs. 1,25,000||Rs. 1,17,924.53|
|3||Rs. 1,25,000||Rs. 1,11,249.56|
|4||Rs. 1,25,000||Rs. 1,04,952.41|
|5||Rs. 1,50,000||Rs. 1,18,814.05|
|6||Rs. 1,50,000||Rs. 1,12,088.73|
|7||Rs. 2,00,000||Rs. 1,40,992.11|
So, if I keep aside Rs. 8,42,000 today, it would meet the expenses for the next 7 years of my daughter’s education.
But how much do I actually require?
However, in all likelihood, I would not keep this amount idle and would definitely invest it over these 7 years. Suppose that I invest this amount in a simple Fixed Deposit with a Private Bank like HDFC Bank Ltd in India. They give an interest of 7% presently on this kind of deposit.
In that case, this amount of Rs. 8,42,000 would be Rs. 12,63,614.96 at the end of 7 years starting today.
We can calculate this in Excel using the Future Value formula as follows:
=FV(rate, nper, pmt, PV)
To calculate, we should consider, rate = 7% (per annum), nper = 6 (years), pmt = 0 and PV = (Rs. 8,42,000).
However, we need to factor the fact that we would be withdrawing from this amount every year to pay the fees. For simplicity (though this is the actual situation in the case of my daughter’s education), let us consider that we need to pay the fees once in the year at the beginning of the year.
So, we have the following situation:
- We have Rs. 8,42,000 in hand.
- We invest this amount in Fixed Deposit at the rate of 7% per annum for the next 6 years.
- We withdraw the college fees from this amount at the beginning of every year.
- The amount remaining continues to earn the interest.
In this case, our calculations would be as follows:
|Year||Amount at the beginning of the Year||Amount Spent ……… …..||Amount Remaining ………||Interest earned over next 1 year|
|1||Rs. 8,42,000.00||Rs. 1,35,000.00||Rs. 7,07,000.00||Rs. 49,490.00|
|2||Rs. 7,56,490.00||Rs. 1,25,000.00||Rs. 6,31,490.00||Rs. 44,204.30|
|3||Rs. 6,75,694.30||Rs. 1,25,000.00||Rs. 5,50,694.30||Rs. 38,548.60|
|4||Rs. 5,89,242.90||Rs. 1,25,000.00||Rs. 4,64,242.90||Rs. 32,497.00|
|5||Rs. 4,96,739.90||Rs. 1,50,000.00||Rs. 3,46,739.90||Rs. 24,271.79|
|6||Rs. 3,71,011.70||Rs. 1,50,000.00||Rs. 2,21,011.70||Rs. 15,470.82|
|7||Rs. 2,36,482.52||Rs. 2,00,000.00||Rs. 36,482.52|
So, you notice that we are left with Rs. 36,482.52 after meeting all the expenses.
If you start with an initial amount of RS. 8,18,000 with the same plan, you would notice that you are left with Rs. 464.99 at the end of 7 years. Other way to get to this figure is to discount the present value at the same rate that you expect the investment plan to give returns.
Instead of investing in Fixed Deposit, if you chose to invest in Mutual Funds with expectation to earn 12% per annum, the amount required would be about Rs. 7,17,000.
What if I do not have this amount upfront?
In this case, we would have to build this corpus over time. The situation we are considering is that we have paid the 1st years fees of Rs. 1,35,000.00 and now we start gathering the remaining amount for the next 5 years.
From the Present Value table, we notice that we require Rs. 10,10,000 – Rs. 1,35,000 = Rs. 8,75,000. If we decide to deposit this amount through a recurring deposit earning an interest of 6% per annum, the monthly instalment amount could be calculated using the PMT function as follows:
=PMT(rate, nper, PV, [FV])
So here ww could calculate with the Present Value of the Money or the Future Value of the money. We calculate with the Present Value as follows:
See, that we divided the rate by 12 as 6% is the annual rate of interest and we are calculating the monthly instalment. Also, we multiplies the nper by 12 for the same reason.
The monthly instalment amount, in this case, comes to Rs. 14,501.28.
Let us take the case of my Sister-In-Law. When her daughter was 8 years old, she wanted a plan from me for her daughter’s higher education. At this stage, we did not know what education her daughter would pursue. So, we made some assumptions.
Our first assumption was that if her daughter studies for 6 years to complete a graduation in 4 years and then a MBA in 2 years. We simplified and considered that suppose we require Rs. 2,00,000 per annum for 6 years as on today’s rate. And also, suppose that this amount would grow by 3% per annum till her daughter is ready for college.
Applying Future Value formula, we gathered that she would require about Rs. 2,60,954.64 per annum for 6 years by the time her daughter is 17 years old and ready to go to college. This implies that she requires a corpus of about Rs. 15,65,727.82.
Applying PMT formula, we get that this implies that she needs to save Rs. 10,969.10 per month for the next 9 years considering she invests in Recurring Deposit at the rate of 6% per annum.
However, as she is planning an investment horizon of 9 years, it would be prudent to consider investment avenues like Mutual Funds. However, Mutual Funds are risky with no guaranteed returns. After some study, we considered that suppose the chances of returns from Mutual Funds are as follows:
- There is a 50% chance that Mutual Funds will return at the rate of 12% per annum over 9 years.
- There is a 25% chance that Mutual Funds will return at the rate of 8% per annum over 9 years.
- There is a 25% chance that Mutual Funds will return at the rate of 4% per annum over 9 years.
The combined probability of these odds is that Mutual Funds could return at the rate of 9% per annum.
So, we finally decided that we would invest Rs. 10,000 per month in SIP in 5 Mutual Funds for the next 9 years. At 9% per annum returns, this investment of Rs. 10,000 per month would become Rs. 16,54,832.23 at the end of 9 years.