By Tensing Rodrigues*
Today let us learn to solve some really tough investment problems with Excel.
Future value of a single amount.
If you deposit Rs.1,000 in a bank at the beginning of every year for five years, and if the bank pays 10% interest per annum, compounded half-yearly, what will be its value at the end of five years?
You use the same formula = FV(rate,nper,,pv), but change rate and nper. 10 per cent compounded half-yearly for one year is the same as 5 per cent compounded yearly for two years. So the rate becomes 0.05 (not 0.5) and the nper becomes two. Try it out.
Future Value of an Annuity
If you deposit Rs 1,000 in a bank at the beginning of every year for five year and if the bank pays 10 per cent interest per annum, what will be its value at the end of five years?
Again you use the same formula, but with a twist; here you fill in the value for pmt, and leave pv blank. pmt is what you deposit at the beginning of every year. In addition you have to fill in type; if you deposit at the beginning of the year, type = 1, and if you deposit at the end of the year type = 0. So the formula is =FV(rate,nper,pmt, ,type); filled in it is =FV(0.1,5,-1000, ,1). The answer is Rs.6,715.61.
How much should you deposit in a bank at the beginning of every year for five years if the bank pays 10 per cent per annum and if you wish to receive Rs 6,716 at the end of five years?
This is the exact opposite of the previous problem. This sort of a problem comes up in a situation where you need to accumulate a certain amount at the end of a certain period, and you want to know how much you need to save and invest regularly for that. The formula used is =PMT(rate,nper, ,fv,type). In this formula there is only one new parameter fv. Everything else remains the same. fv is the amount which you need at the end of the period. So we fill in as follows =PMT(0.1,5, ,6716,1). The answer is Rs 1,000. The answer as the computer returns it will have a minus sign before it because that is an amount that you have to ‘pay out’; that is deposit not receive.
If you deposit Rs one lakh in a bank which pays 10 per cent per annum how much can be withdrawn annually (at the end of the year) for a period of 10 years ?
This sort of a problem comes up in a situation where you deposit a certain amount and are going to receive it back or withdraw it over a period of time; say something like providing for a pension, or providing for the education of a child, etc. The formula used is =PMT(rate,nper,pv,type) where PMT is amount you will receive regularly and pv is the lump sum deposit that you make. You fill it in as follows =PMT(0.1,10,-100000,0). The answer is 16,274.54. That is, if you deposit Rs one lakh in a bank which pays 10 per cent per annum you can withdraw annually (at the end of the year) Rs16,275 for a period of 10 years.
Present Value of an Annuity
You are expecting to receive Rs1,000 at the beginning of every year for the next five years. What is the present value of these annuities if the rate of interest is 10 per cent?
The formula is =PV(rate,nper,pmt, ,type). We shall not discuss the situation where this formula can be used as we shall do that when we discuss the use of Excel formulas for financial planning. Nevertheless try solving the problem for practice.
Next week let us learn how to use these tools to solve some practical problems in financial planning, like calculating the sum assured for a term insurance plan, calculating the premium for an endowment plan, calculating the pension corpus required, calculating the monthly contribution required to be made to the pension corpus and calculating the pension that can be drawn.
*The author is an investment consultant. Readers can send their comments and queries to [email protected]