All of us at some point of time decide to make some investments; reason can be future goals or current tax planning.
Investments can be made in various modes ranging from Fixed Deposit in a Bank to Invest in stocks.
First of all, let me clear one thing here that I’m talking about investments and not trading.
Trading is entirely a different subject.
Today, I’m going to give you some small investment tools using which you can plan your savings very easily.
Resources Required: Microsoft Excel or Google Sheets (sheets.google.com)
1) Calculating Future Value:
Future value is the amount which we require after investing for a certain point of time.
Now, if you are depositing some amount every year in any kind of investment resource then the following formula in the Excel or Google Sheet will help you to find the amount you will receive after completion of period:
=FV(Rate, Period, Pmt, PV)
This sequence is to be mandatorily followed. Also, excel or GS will help you as soon as you enter
=FV( it will display the above formula.
Rate = Rate of return
Period = Number of years of investment
Pmt = Fixed payments
PV = Present Value of investment
Example: You are investing Rs 50,000 at 6% per annum for 10 years, then how much amount will you get?
So, just put the above in formula and remember the PV must be written with negative sign as -50000 In Excel, it will be look like following:
| Rate | 6% |
| Period | 10 |
| Pmt | 0 (because we are not doing periodical payments rather investing a lumpsum amount in one go) |
| PV | -50000 |
So, just use the above formula as FV(6%, 10, 0, -50000) and you will get the answer as: 89542.38
The benefit of above calculation:
Now, you can easily decide how much to invest and in which type of investment and for how much period as if you are planning for a certain amount to be received in future then yes, just change the figures and plan.
For example, if you are expecting more than 100000 after 10 years then either invest more than 50,000 or invest in higher return investments. This simple and small calculator will relax your mind.
2) SIP: Systematic Investment Plan
Suppose you are investing 15000 per month for 20 years at 12% per annum then how much will you get?
So, now in this case there is no PV as above but you are making small investments consistently every month. We again have to use the same formula but with a slight change.
=FV(Rate, Period, Pmt, PV)
This sequence is to be mandatorily followed. Also, excel or GS will help you as soon as you enter
=FV( it will display the above formula.
Rate = Rate of return
Period = Number of years of investment
Pmt = Fixed payments
PV = Present Value of investment
Now in place of Pmt use -15000 and the answer will be in front of your eyes. In Excel, it will be look like following:
| Rate | 12% |
| Period | 20 |
| Pmt | -15000 (because we are not doing periodical payments rather investing a lumpsum amount in one go. |
| PV | 0 (because we are doing periodical payments instead investing a lumpsum amount in one go) |
But the above amounts will not give you the right answer. you need to alter the figures as below which is completely logical too.
| Rate | =12%/12 (divide by 12 because you are making monthly payments) |
| Period | =20*12 (multiply by 12) |
| Pmt | -15000 (because we are not doing periodical payments rather investing a lumpsum amount in one go. |
| PV | 0 (because we are doing periodical payments instead investing a lumpsum amount in one go) |
You will get:
| Rate | 0.01 |
| Period | 240 |
| Pmt | -15000 |
| PV | 0 |
Now, put the above amounts in formula:
=FV(0.01, 240, -15000, 0) and the answer will be 14838830 By using the above calculations, you can easily manage your SIPs. Now, suppose you are making investments after every 6 months, then just do the following:
| Rate | =12%/2 (divide by 2 because you are making 6-monthly payments) |
| Period | =20*2 (multiply by 2) |
| Pmt | -15000 (because we are not doing periodical payments rather investing a lumpsum amount in one go. |
| PV | 0 (because we are doing periodical payments instead investing a lumpsum amount in one go) |
As simple as that.
3) Calculating Rate of Return:
Now, we will learn to calculate the required rate of return on our investment so as to receive the desired amount.
Example,
Investment (PV) = -100
Maturity Amount (FV) = 150 (desired amount)
Period = 4 years
Pmt = 0
Rate = ?
Use the following formula:
= Rate(periods, Pmt, PV, FV)
Again, the sequence is important. In this case Pmt will be 0 because we are making a lumpsum amount. so, put in the formula as =rate(4, 0, -100, 150) and you will get the answer as 11% which means you need to invest Rs 100 in an investing having an yield of 11% so as to receive 150 after 4 years.
Periodic yearly Payments:
Same calculations can be done if you are making periodical payments like every year instead of lumpsum amount.
Example,
Investment (PV) = 0
Maturity Amount (FV) = 550000 (desired amount)
Period = 30 years
Pmt = -9000
Rate = ?
Use the following formula:
= Rate(periods, Pmt, PV, FV)
=Rate(30, -9000, 0, 550000) and you will get 5% as the answer.
Monthly payments:
Example,
Investment (PV) = 0 (because periodic payments are being made)
Maturity Amount (FV) = 80000 (desired amount)
Period = 5 years, So, multiply it with 12 to convert it into months, it will be =5*12 = 60 months
Pmt = -100 per month
Rate = ?
Use the following formula:
= Rate(periods, Pmt, PV, FV)
=Rate(60, -100, 0, 80000) and you will get 7% as the answer but remember it 7% per month.
Different periodic payments:
Now, let us discuss the complicated case in which you are investing something in the beginning and then you will made different periodical payments.
Investment (PV) = -50
Maturity Amt. (FV) = 800
Pmt = -100, -200, -50, -100 (at fixed periods of time)
Now in this case the rate which is required is known as IRR i.e. internal rate of return.
Formula is: =IRR(cash flow amounts)
This formula has to be used very systematically. First of all arrange all the values in a sequence as:
| Cell A
PV |
B
PMT |
C
PMT |
D
PMT |
E
PMT |
F
FV |
| -50 | -100 | -200 | -50 | -100 | 800 |
Now use the formula =IRR(A:F) and you will get the answer as 17%. (cell names A to F are for your understanding only, in excel there will be different cell names and you just have to click on 1st cash flow amount and drag till last cash flow. It is the same way as we do SUM in excel)
Here, I am ending this and don’t just read it, practice it too on excel or google sheet.
For any queries in the above article, the author can be contacted at ca.atulkhurana@yahoo.com or Whatsapp at 9888855340 (No Calls)
Author Bio
