Investment answers using excel

We can derive some important investment answers using excel.

In this blog post we see some of the most common investment questions being answered effortlessly using excel.

Generally we do very lengthy calculation in excel to arrive at a particular answer.

But why to do those lengthy calculations when excel has some inbuilt formulas which can give results faster.

So, lets see how excel answers our investment queries.

#Q1. How much corpus can be build by Investing in SIP?

Suppose a person wants to invest in mutual funds.

He wants to start a SIP of Rs.1,000/month for next 10 years.

He invests in a diversified equity mutual fund which is expected to give a return of 15% per annum.

What will be corpus built after 10th year?

To answer this question we can use the “Future Value” formula of excel.

The future value formula is like this:

Future value = FV (rate, nper, pmt, pv, type)

Lets see what each term used inside the formula means:

• Rate – It is the expected rate of return expected from the mutual fund (15%/12). We are dividing it by 12, as we are making monthly payments. So returns is accrued each month.
• Nper – Period for which the money stays invested in years or months (10 years x 12 months).
• Pmt – SIP value that is being invested each month (-Rs.1,000). Value is shown as (-)ve as cash is flowing out of our pocket.
• Pv –  In this case there is no lumpsum investment, hence its value will be zero (Rs.0).
• Type – Put zero (0) for end of period, or put one (1) for beginning of period. In SIP the payment is due at the end of period. Hence we will use zero (0).

Future Value formula:

What we want to know here?

We want to know what will be the total value of the systematic Investment Plan (SIP) at the end of the 10th year.

Means, we want to know the Future Value of our investment.

Hence, we are using the Future Value formula.

So now, lets start putting the values and try to get the answer:

Future value = FV (rate, nper, pmt, pv, type)

= FV (15%/12, 10*12, -1000, 0, 0)

Future value of SIP is Rs.275,217. 

#Q2. How much corpus can be build by Investing in lump-sum?

Suppose a person wants to invest in mutual funds.

He wants to invest a lump-sum amount of Rs.120,000, and keep it invested for next 10 years.

He invests in a diversified equity mutual fund which is expected to give a return of 15% per annum.

What will be corpus built after 10th year?

To answer this question we can again use the “Future Value” formula of excel

The future value formula is like this:

Future value = FV (rate, nper, pmt, pv, type)

Lets see how each term are valued in this example:

• Rate – 15%.
• Nper – 10 years.
• Pmt – there is no SIP, hence pmt will be zero (0).
• Pv –  In this case there is a lump-sum investment of -Rs.120,000.
• Type – Here also the payment is due at the end of period. Hence we will use zero(0).

Future Value formula:

What we want to know here?

We want to know what will be the total value of the lump-sum investment at the end of the 10th year.

Means, we want to know the Future Value of our investment.

Hence, we are using the Future Value formula.

So now, lets start putting the values and try to get the answer:

Future value = FV (rate, nper, pmt, pv, type)

= FV (15%, 10, 0, -120000, 0)

Future value of amount invested in lump-sum is -Rs.485,467. 

#Q3. How much to invest to generate income of Rs.10k/month?

Suppose a person wants to invest some money to generate monthly income.

He wants to invest some lump-sum amount and earn monthly income for next 10 years.

He decides to invest in debt linked mutual fund which is expected to give a return of 8% per annum.

What amount should he invest to generate the desired income?

To answer this question we can again use the “Present Value” formula of excel

The Present value formula is like this:

Present value = PV (rate, nper, pmt, pv, type)

Lets see how each term are valued in this example:

• Rate – 8%.
• Nper – 10 years.
• Pmt – +Rs.10,000 [value will be (+)ve as cash flow is in the pocket].
• Pv –  There is no lumpsum investment. Hence it will vbe zero (0).
• Type – Here also the payment is due at the end of period. Hence we will use zero(0).

Present Value formula:

What we want to know here?

We want to know how much money one must invest presently to start earning a monthly income of Rs.10K/month for next 10 years. .

Means, we want to know the Present Value of our investment.

Hence, we are using the Present Value formula.

So now, lets start putting the values and try to get the answer:

Present value = PV (rate, nper, pmt, pv, type)

= PV (8%/12, 10*12, 10000, 0, 0)

Present value of lump-sum investment to be made is Rs.824,215. 

#Q4. What will be the worth of Rs.10K of today, 12 years from now?

We know that, with passage of time, the purchasing power of currency goes down.

So, a dollar of today is stronger than a dollar 12 years from now.

Why this happens?

Learn about time value of money from here…

For simplicity, I am assuming that the cause of devaluation of currency (with time) is inflation.

It will be a safe assumption to consider average inflation in India for next 12 years as 6%.

Means, every year the value of our Indian Rupee goes down by 6%.

So what will be the value of Rs.10,000, 12 years from now?

To answer this question we can again use the “Future Value” formula of excel

The future value formula is like this:

Future value = FV (rate, nper, pmt, pv, type)

Lets see how each term are valued in this example:

• Rate > –6% (negative value is used as inflation erodes our money).
• Nper – 12 years.
• Pmt – there is no SIP, hence pmt will be zero (0).
• Pv –  In this case there is a lumpsum value to be considered which is –Rs.10,000 (negative value is used cash flow is out of our pocket).
• Type – Here also the payment is due at the end of period. Hence we will use zero(0).

Future Value formula:

What we want to know here?

We want to know what will be the value of Rs.10,000 due to erosion effect of inflation.

Means, we want to know the Future Value of our money.

Hence, we are using the Future Value formula.

So now, lets start putting the values and try to get the answer:

Future value = FV (rate, nper, pmt, pv, type)

= FV (-6%, 12, 0, -10000, 0)

Future value of Rs.10,000, 12 years from now will be Rs.4,759. 

#Q5. How to invest money for income generation?

Suppose there is a person who has Rs.1 Crore available for investment.

As the value is big, he wants to take extra care while putting this amount to work.

The first thing he did was to do a self-check of his investment-personality.

He found that he feels more comfortable in maintaining a balanced portfolio.

What is a balanced portfolio? A portfolio which is evenly balanced between debt and equity linked investments.

He also re-checked his investment priority.

He found that, for him a steady stream of income generation is more important than future gains.

Though it was also true that, he did not want to lose completely on future gains front.

This is a typical case of any average Indian. Hence I thought that, use of this example will find relevance with more readers.

For such a person, who want income generation, what should be the investment approach?

#First Step

The #first step should be to decide how one wants to invest his money.

Here the person decides to invest his money in two parts:

1. Fixed Deposit – which will give him the desired monthly income.
2. Balanced Mutual Fund – which will give him the necessary capital appreciation.

#Second Step

The #second step should be to quantify ones income requirement.

• Present monthly income requirement is Rs.40,000/month.
• Average inflation for next 10 years: 6% per annum.

Means, if todays income requirement is Rs.40K/month, with passage of time this income requirement will grow due to inflation.

How the monthly income will grow?

The above table highlights that, in 10 years time, the monthly income requirement will grow from Rs.40K to Rs.71.6K due to inflationary pressure.

What does it mean for the investor?

It means, instead of Rs.40K/month the average monthly income (for next 10 years) is Rs.54,442/month.

In the initial months, extra income will be generated (more than the required Rs.40K).

The investor must not use this extra cash. Instead it must be invested (like in Bank’s recurring deposit).

The invested fund will be get utilised in later years from 6th to 10th (see above table).

So, in second step we have concluded how to arrive at the actual monthly income requirement.

In this example, it is Rs.54,442/month, instead of Rs.40,000/month (for next 10 years).

#Third Step

The #third step should be to quantify how much to be invested in fixed deposit to generate Rs.54,442/month for next 10 years.

From the above calculation table shown, it is clear that to generate Rs.54,442/month, the investment amount should be Rs.44,87,218.

As the person has only Rs.1 Crore available for investment, if he uses Rs.44.88 Lakhs for fixed deposit, what will be left balance is Rs.55.12 lakhs.

#Fourth Step

The #fourth step will be to know the future value (after 10 years) of Rs.55.12 lakhs when invested in Balance Fund.

From the above calculation table you can understand that, after 10 years, Rs.55.12 lakhs will grow to become Rs.2.23 crore @15% per annum.

#Fifth Step

The #fifth step will be to repeat the above 4 steps for further next 10 years.

First 10 years:

• Rs.44.87 Lakhs invested in FD.
• Income of Rs.54.442/month generated by FD.
• At the end of 10th year, the income stops and the FD also gets fully consumed.
• On once side the FD is consumed. But money invested in Balanced Fund grows to become Rs.2.23 Crore.
• This fund (Rs.2.23 Crore) can now be utilised for next 10 years.

Next 10 Years:

• Again part of money will be invested in FD and remaining in Balance Fund.
• As explained above, try to do the math on your own to know the following answers:
  • What will be the monthly income requirement for next 10 years?
  • How much money (out of Rs.2.23 Crore) must be invested in FD?
  • What will be the value of money, which is invested in Balanced Fund, after 10 years?

This way the investor is able to keep rolling his money and earn a fixed income till eternity.

Isn’t it a great way of income generation?

Conclusion…

We often do lengthy calculation in excel to get our investment queries answered.

But by the use of in built formulas, investment answers using excel can be obtained quickly.

Initially, the use of formulas may seem complicated, but upon repeated usage the difficulty will ease out.

In this blog post, the use of “Future Value” and “Present Value” formulas has been explained with examples.

I hope it will help & encourage you to include more of these formulas to get your investment answers using excel.

Have a happy investing.