Introduction
In order to determine how much money is to be invested by a person till retirement so that he can lead basic, comfortable or luxurious lifestyles, a series of calculations using Excel have been carried out, involving the concepts of time value of money.
Difference Between Present Value and Future Value. The future value measures the nominal future sum of money that a given sum of money is worth assuming a certain interest rate. On the other hand, the present value is a future amount of money that has been discounted to reflect its current value.
Relatedness Between Present Value and Future Value Calculations. PV and FV are related throught he following relationship: FV = PV(1 + i)n, where ‘i' stands for rate of interest and ‘n’ stands for number of years.
Difference Between Compunding and Discounting. Compounding determines the future value of a present value using the compound interest. On the other hand, discounting determines the present value of some future value, using a discount rate.
Part 1
Part 2
The negative signs indicate that the reflected amounts have to be given away for investment so as to get the amounts in the FV column, which would be received as positives.
Part 3
The basic, comfortable and luxurious lifestyles that the person lives today amount to $50,000, $100,000 and $150,000 respectively. On retirement after 32 years, these amounts would have undergone a change. The amounts required to sustain similar lifestyles after 32 years accounting for a 3% inflation rate are calculated using FV formulae in Excel:
Part 4
Having determined the amounts a person would need annually for each type of lifestyle, the person would need to take into account that he would retire at 67, and probably live till 90. This implied that the person would need an annual amount as reflected in Table 3 above for 23 years. To answer the question as to how much amount the person would need to possess on the date of retirement, the rate at which he could invest the retirement corpus is taken as 12%, and the inflation is 3%. Thus, 12% is the nominal rate of interest, while 3% is the inflation rate. The real rate of interest could be calculated using the formula:
(1 + nominal return rn) = (1 + real return rr)* (1+ inflation π ).
In our case, (1 + 12%) = (1 + rr)*(1+3%). This yields a real rate of return to be 8.73%. Using the adjusted interest rates to account for inflation, the required value at retirement is calculated using Excel as under:
Part 5
Given that the amounts required on retirement are as at Table 4, the final step is to determine how much money is to be annually invested at the S&P 500 Index for the remaining 32 years of working life. This is calculated using the PMT function as under: -
While doing the calculations, the PV of Annuities is taken as FV, and the requisite calculations done in Excel.
Conclusion
The series of calculations above bring forth the idea of investment for the future, present values, PMT and future values. The person would ultimately need to pay annual amounts as at Table 5 for 23 years to achieve a corpus as at Table 5, which would fund his retirement years- now assumed as 32 years.
