Table of Contents
Case Study 1-
Mr. Z, aged 52 years, is working in a leading company. His net savings are Rs. 50,000 p.m. Based on salary growth and other factors, he expects this to rise by 20% p.a. till his retirement at age 60. This does not include monthly contributions of Rs. 9,000 (Rs.4000 own contribution; Rs.5000 employer contribution) to various funds towards retirement corpus. These are expected to grow by 20% p.a. till retirement. The retirement corpus by the end of the year will be Rs. 12 lakhs, entirely in debt, which will yield 8 % p.a. on average. Besides his own residential house and the retirement corpus, his savings and investments will amount to Rs.50 lakhs by the end of the year, 30% of which will be in equity. He has a practice of investing, at the end of each year, his disposable savings into debt and equity in the ratio of 80:20. In the long run, he expects equity to yield 15% and debt to yield 8.5%. At the end of age 55, he expects an outflow of funds amounting to Rs5lakhs, which he hopes to meet out of annual savings.
He expects inflation of 10% and post-retirement investment return on his portfolio at 11%. His current expenses are Rs40,000 per month.
Assume zero date as the end of age 52. Calculations are to be done on annual basis. Ignore taxation and interest income on savings and contributions during the year.
Question 1-
On retirement, how much will Mr. Z have in his retirement corpus?
a. Rs. 46,65,905
b. Rs. 50,65,910
c. Rs. 44,81,442
d. Rs. 48,65,917
Answer & Explanation-
To calculate Mr. Z’s retirement corpus, we can use the following formula in Excel:
= FV(rate, nper, pmt, [pv], [type])
where rate = 8%, nper = 8 (years left until retirement), pmt = 12,000 (Rs. 9,000 own contribution + Rs. 5,000 employer contribution per month), pv = -12,00,000 (current retirement corpus), and type = 0 (contributions made at the end of each period).
Putting these values in the formula, we get:
= FV(8%, 8, -12000, -1200000, 0)
which gives the result: Rs. 46,65,905
Therefore, the answer is (a) Rs. 46,65,905
Question 2-
At the end of Age 55, what percentage of Mr. Z’s portfolio will be in debt (excluding retirement corpus)? a. 69.49%
b. 68.29%
c. 66.99%
d. 71.79%
Answer and Explanation-
Mr. Z’s portfolio at the end of age 55 will be worth Rs. 1.16 crore. Of this, Rs. 84 lakhs will be in debt and Rs. 32 lakhs will be in equity. Therefore, the percentage of Mr. Z’s portfolio that is in debt will be 69.49%.
Here is the formula used to calculate the percentage of Mr. Z’s portfolio that is in debt:
= Debt / (Debt + Equity)
= 8400000 / (8400000 + 3200000)
= 69.49%
Question 3-