RPEB Workbook

Balance at the end of age 40 = 1,354,529 (Set Begin, N=60 (5*12), I=9%, PV=-513,441, PMT= - 7500, FV(Solve) = 1,354,529, P/Y=12, C/Y=1) Balance at the end of age 50 = 5,117,523 (Set Begin, N=120 (10*12), I=9%, PV=-1,354,529, PMT= -10000, FV(Solve) = 5,117,523, P/Y=12, C/Y=1) Balance at the end of age 56 = 10,001,947 (Set Begin, N=72 (6*12), I=9%, PV=-5,117,523, PMT= - 15000, FV(Solve) =10,001,947, P/Y=12, C/Y=1) Balance at the end of age 57 = 10,902,122 { 10001947*(1+9%)} One-fourth is redeemed and invested in Liquid sch. At this stage , i.e. at 57 years Balance at the end of age 58 years in Liquid scheme = 2,875,435 (10902122/4)*(1+5.5%) Balance at the end of age 58 years in Balanced scheme = 8,912,485 (10902122*3/4)*(1+9%) Balance at the end of age 59 years in Liquid scheme =5,384,251 (2875435+8912485/4)*(1+5.5%) Balance at the end of age 59 years in Balanced scheme = 7,285,956 (8912485*3/4)*(1+9%) Balance at the end of age 60 years in Liquid scheme = ₹7,602,056 (5384251+7285956/4)*(1+5.5%) Balance at the end of age 60 years in Balanced scheme = ₹5,956,269 (7285956*3/4)*(1+9%) Balance for retirement funds at age 60 = 13,558,325 (7602056+5956269) 6. 18,55,153.5 Step-1: Find future value from 1st Apr to 30th Nov. (As the interest rate is 8.% p.a, Period: 8 Months) 427000 + 427000 x = 449773.33 Step-2: Find future value from 1stDec to 31st Mar . (Period: 4 Months) (449,773.33+150,000) + 599773.33 x = 615767.28 (* For FY Investment of ₹150,000 (lump sum) is done on lst Dec) Step 3: Finding out Future value of regular investment for 5 years remaining to mature, taking rate of 8% p.a= 18,55,153.5 (BGN, N= 5, 1= 8%, PV= - 615,767.28, Pmt = -150,000, FV (Solve) = 18,55153.5,,P/y=1, C/y=1) 7. ₹45,100 Step-1: As the requirement of monthly annuity will commences after 3 years from today. We will first find out FV of current investment amount i.e. 45 lakh, growing @ 9% p.a BGN, N=3, I=9, PV= -45,00,000, FV= Solve (5827630.5), P/y=1,C/y=l Step-2: Finding out regular monthly Annuity to earn for next 30 years from the corpus generated in Step 1 Set = BGN, N=360 (30*12), I=9, PV= -58,27,630.5, PMT (Solve) = 45,100, P/Y =12,C/Y =l) 8. (Answer: 15 years) 93

Step-1: Calculate FV of Equity in first 5 years till age 33 (28+5) Set = BGN, N = 60 (5 *12), I =9.5, PMT = - 5000, PV = 0, FV (Solve) =381081.1306, P/Y = 12, C/Y = 1 Step 2: Calculate FV of Equity and Debt at age 40 i.e. for next 7 (40-33) years A. Equity:-Set = BGN, N =84 (7 *12), I =9.5, PMT = -5000, PV = -381081, FV (Solve) =1308314, P/Y = 12, C/Y = 1 B. Debt - Set = BGN, N = 84 (7 *12), I =7.5, PMT = -5000, PV = 0, FV (Solve) =548421, P/Y = 12, C/Y = 1) Step-3: Calculate FV of Equity and Debt at age 60 for next 20 (60-40) years A. Equity - Set = BGN, N = 240 (20 *12), I =9.5, PMT = -5000, PV = -654157 (1308314*50%), FV= Solve (7429698.307), P/Y = 12, C/Y = 1 B. Debt - Set = BGN, N = 240 (20 *12), I =7.5, PMT = -15000, PV = -12,02,578 (1308314x50% +548421), FV (Solve) =13216381.88, P/Y = 12, C/Y = 1 Step-4: Add FV of Equity and Debt Total accumulated Funds = 7429698.307+13216381.88 = 2,06,46,080 Step 5: FV of expenses at the time of retirement Set = BGN, N =60-28, I =5.5, PMT = 0, PV = -22000, FV (Solve) = 122039.77, P/Y = 1, C/Y = 1 Step-6: No. of months Set = BGN, N = Solve (181.45), I =0.947867( 1.065/1.055)-1, PMT = 122039, PV = - 20646087, FV= 0, P/Y = 12, C/Y = 1 Therefore, Number of years = 181.45/12 = 15.12 years 9. Ans: - ₹9743590 Step-1: 10 lakhs/7.5% = 13,333,333 Step-2: 15 lac's/6.5% = 23,076,923 Step-3: Step 2-Step 3 = 97,43,590.077 10. ₹477,770.6856 Step-1: Set: Beg, N= 8, I = 10%, Pmt =1,00,000 * 60% = -60,000, P/Y=C/Y=1, Find FV: 754768.6146 Step-2: Set: Beg, N= 8, I = 7.5% Pmt= 1,00,000 * 40% = -40,000 Find FV: 449,193.9533 Step-3: Balance at age 36+8 =44, 754768.6146 + 449193.9533 = 1203962.568, Rebalancing now 50%:50% so in equity 1203962.568 * 50% = 601981.284 and the same in debt and out of 100000 now 50,000 will be invested in equity and debt Step-4: Set: Beg, N: 8, I = 10% PV = -601981.284,Pmt = -50000, Find FV = 1919374.19 Step-5: Set: Beg,N: 8, I = 7.5% PV = -601981.284 Pmt = -50000 N : 8 Find FV = 1635112.713 Step-6: Balance at age 44+8 =52, 1919374.19 + 1635112.713 = 3554486.903, Rebalancing now 94

40%:60% so in equity 3554486.903 * 40% = 1421794.761 and in debt 3554486.903 * 60% = 2132692.142 and out of 100000 now 40,000 will be invested in equity and 60,000 in debt Step-7: Set: Beg Rate: 10% PV = -1421794.761 Pmt = -40000 Nper: 8 Find FV = 3550922.416 Step-8: Set: Beg Rate: 7.5% PV = -2132692.142 Pmt = -60000 Nper: 8 Find FV = 4477400.074 Step-9: Balance at age 60, 3550922.416 + 4477400.074 = 8028322.49 Step-10: Lets assume survival upto 80 Set: Beg Nper: 20 PV = -8028322.49 Rate: Real Rate of return: between 6.5% and 4.5% =1.91, P/Y=C/Y=1 Find Pmt: 477,770.6856 11. ₹22,595 Step-1: Set = BGN, N= 60-25, I = 5.5, PV = -35000, FV = Solve (227983.87), P/Y = 1, C/Y = 1 Step-2: BGN, N= 300 (85-60)*12, I = ((1.075/1.055)-l)*100, PMT = -227983.87, PV = Solve (54625646.5), P/Y = 12, C/Y = 1 Step-3: BGN, N= 420(60-25)*12, I =8.5, PMT = -22595, FV =54625646.5, P/Y = 12, C/Y=1 12. (Answer: Rs.110 Lakhs) Step-1: Set = BGN, N= 15 (60-45), I =5.5, PV = - 40,000*.75, FV = Solve (66974.2947), P/Y =1, C/Y= 1 Step-2: BGN, N= 20 (80-60)*12, I = ((1.10/1.055)-1)*100, PMT=66974, PV = Solve (10,915,121), P/Y = 12, C/Y =1 13. Answer: Shortfall of ₹7.18 Lakhs) Step-1: FV of today monthly expense at the time of retirement Set = BGN, N=5, I=6, PV=-42000, FV= Solve (56205.48), P/Y=l, C/Y=l Step-2: PV of the entire cash flow requirement during the post - retirement phase. Set = BGN, N=360 (30*12) (As stated in the question, to provide 30 years postretirement expenses inflation-adjusted), I= 1.41509 ((1.075*/1.06)-l)*100, PV= Solve (1,65,19,897.27), PMT= 56205.48, P/Y=12, C/Y=l *Rate of Return of Annuity (Post Retirement) will 6% + 1.5% = 7.5% Step-3: PV of the entire cash flow receivable (pension from Employer)during the post - retirement phase. Set = BGN, N=30*12 (As stated in the question, to provide 30 years postretirement expenses inflation-adjusted), I=7.5, PV= Solve (29,48,371.77), PMT= 20,000, P/Y=12, C/Y=l Step-4: FV of ongoing investments at the time of retirement age. Set = BGN, N=5*12, I=9.25, PV= -60,00,000, Pmt= -20,000, FV= Solve (1,08,52,949.470), P/Y=l2, C/Y=l 95

Step-5: 16,519,897.267 Amount Required For Retirement Life 2,948,371.776 Less: PV of Pension income at Retirement Age 2,000,000.000 Less: Lumpsum Benefits from Employer 10,852,949.470 Less: FV of regular & Current Invt at Retirement Age Shortfall in Corpus required 718,576.021 14. Ans . 2244736.507 Investment-1: Pmt: 6,00,000 * 10% = 60,000 Rate: 8.5% Investment-2: Pmt: 600000 * 2% = -12000 Rate: 12% Step-1: Set: Beg, Pmt: -60000 Rate: 8.5% Nper: 5, P/Y=C/Y=1, Find FV = 385741.7709 Step-2: Set: Beg Pmt: -12000 Rate: 12% Nper: 5, P/Y=C/Y=1, Find FV = 85382.26852 Step-3: 385741.7709 + 85382.26852 = 471124.0394 Step-4: Set: Beg PV = -471124.0394 Pmt: -60000 Rate: 8.5% Nper: 5 Find FV = 1094150.585 Step-5: Set: Beg Pmt: -12000 Rate: 12% Nper: 5 Find FV = 85382.26852 Step-6: 1094150.585 + 85382.26852 = 1179532.854 Step-7: Set: Beg PV = -1179532.854 Pmt: -60000 Rate: 8.5% Nper: 5 Find FV = 2159354.238 Step-8: Set: Beg Pmt: -12000 Rate: 12% Nper: 5 Find FV = 85382.26852 Step-9: 2159354.238 + 85382.26852 = 22,44,736.507 15. (Answer: ₹3.79 Lakhs) Step-1: Current value of regular investments of Rs.2.00 Lakhs p.a. of last 10 years growing @ 3.5%. Set = BGN, N=10, I=3.5, PMT= -2,00,000, FV= Solve (24,28,398), P/Y=l, C/Y=l Step-2: Rebalancing of the portfolio As per question language, for the remaining period to retirement (i.e. 10 years), the accumulated funds in Step 1 will be rebalanced between Equity and Debt in the equal ratio, (i.e. 50:50) Rebalancing of the portfolio value • Debt = 24,28,398 * 50% = 12,14,199 • Equity= 24,28,398 * 50% = 12,14,199 Step-3: Future value of equity investment. As per question, equity portion of the portfolio, i.e. ₹12,14,199, will continue to grow @ 11% p.a. without any regular investment till the remaining period to retirement (10 years from now) Set = BGN, N=10, I=11, PV=-12,14,199, FV= Solve (34,47,622), P/Y=l, C/Y=l Step-4: Regular Investment in Debt 96

Corpus required at retirement age (As per question) 1,20,00,000 Less: Future Value of Equity portion of investment (Step 3) 34,47,622 Shortfall 85,52,377 The shortfall amount of ₹85,52,377 will be accumulated with the help of regular yearly investment in Debt Fund portfolio during the accumulation period, i.e. from age 40 to age 50 So we have to find out, regular investments to be done on yearly basis in Debt Fund taking into account the current value available in Debt Fund after rebalancing the portfolio (Step 2) Investment rate for Debt Fund is 8% p.a. Set =BGN, N=10, 1=8, PV= -12,14,199, FV=85,52,377 (Shortfall Amount), PMT= Solve (3,79,087), P/Y=l, C/Y=l 16. Ans (₹53,125 per month) Step-1: Set = BGN, N= 62-49, I = 5.5, PV = -7.80 lakhs* 0.70, FV = Solve (10,95,153), P/Y = 1, C/Y = 1 Step-2: Set = BGN, N= (80-62), I = ((1.07/1.055)-1)*100, PMT = 1095153, PV = Solve (A), P/Y = 1, C/Y = 1 Note: Return on investment will be 100 base points more than Rf. So Return will be 7% (6% + 1%) Step-2: To find PV of future estate amount of Rs.50.00 lakhs Set = BGN, N= (80-62), I = 7, FV = 50,00,000, PV = Solve (B), P/Y = 1 , C/Y = 1 Total corpus required at retirement age will be sum of (A + B) = ₹1,90,09,864.58 Step-3: Finding out regular investment Set = BGN, N = (62-49)*12, I = 12, FV = 19009864.58, PMT (Solve)= 53124.88, P/Y= 12, C/Y =1 17. Ans 12.61% Step-1: FV of today monthly expense at the time of retirement Set = BGN, N=20, I=7, PV=-27000, FV= Solve (1,04,481), P/Y=l, C/Y=l Step-2: PV of the entire cash flow requirement during the post - retirement phase. Set = BGN, N=30*12 (As stated in the question, to provide 30 years postretirement expenses inflation-adjusted), 1= ((1.08/1.07)-1)* 100, PV= Solve (3,28,33,627), PMT= 104,481, P/Y=12, C/Y=l Step-3: FV of current investment. Note: - As per question, ―Current investment amount of ₹18.00 Lakhs is allocated towards Retirement goal‖ and is growing @ 9.25% p.a. Set = BGN, N=20, I=9.5, PV = -18,00,000, FV= Solve (11054901),P/Y=l, C/Y=l 97

Step-4: Finding out rate of return for regular investment Corpus required during post retirement period (Step 2) 3,28,33,627 Less: Future Value of Current investment (Step 3) 1,10,54,901 Shortfall 2,17,78,726 The shortfall amount of ₹2.17 crore will be accumulated with the help of regular yearly investment of Rs.2.50 Lakhs during the accumulation period, i.e. from age 30 to age 50. So we have to find out, at what investment rate Mrs. A will be able to accumulate the shortfall amount, if she starts investing Rs.2.50 Lakhs on yearly basis for next 20 years. Set = BGN, N=20, I= Solve (12.61), PMT= -2,50,000, FV= 3,28,33,627 -1,10,54,901, P/Y=l, C/Y=l 18. Ans. 3 years 1. Find PV of ₹5.00 Lac (inflation linked) receivable every years for next 30 years, ROR @ 7.5% inflation rate @5.5% BGN, N=30, I=(( 1.075 / 1.055)-1)*100, Pv= solve (11,575,813.66) Pmt= 50,000, P/y=l, C/y =1 2. Find out No. of years required to accumulate the given amount (PV of step 1) with ongoing regular investment. BGN, N= Solve (8.026713042), 1=9.5, Pv= -35,00,000 Pmt= - 350,000, FV=11,575,813.66 P/y=l, C/y =1 3. Current age 55 + Additional 8 years required = Age 63 4. Retirement age 60 - Age 63 = 3 Years (Revised Retirement age) 19. Salary for HRA purpose Basic salary 600000 Dearness allowance 40% of 120000 48000 1. Actual HRA received (20000x12) 240000 2. Rent paid in excess of 10% of salary (240000-64800) 175200 3. 50% of salary 324000 Therefore Rs.175200 shall be exempt and the balance Rs.64800 shall be included in gross salary. 20. Calculation from 1.4.2016 to 31.12.2016 Salary for 9 months = 270000(basic)+90000(DA)+33750(commission) = 393750 HRA calculation for 9 months Actual HRA received = 135000 Rent paid – 10% of salary of relevant period = (90000 – 10% of 393750) = 50625 40% of salary = 157500 Exempted = 50625 Taxable = 135000 – 50625 = 84375 98

Calculation from 1.1.2017 to 31.3.2017 Salary for 3 months = 90000(basic)+30000(DA)+11250(commission) = 131250 Actual HRA received – 45000 Rent paid – 10% of salary of relevant period = (60000 – 10%of 131250) = 46875 40% of salary = 52500 Exempted = 45000 Taxable = 0 Total taxable HRA = 84375 + 0 = 84375 21. The exemption shall be to the extent of the minimum of the following three amounts: 1. Amount of gratuity received = Rs.600000 2. 15 days’ salary for every year of service = = Rs.510000 3. Rs.1000000 Therefore Rs.510000 shall be exempt from tax and the balance Rs.90000 shall be included in the gross salary. 22. The exemption shall be to the extent of the minimum of the following three amounts: 1. Actual amount of gratuity received Rs.1120000 2. 15 days’ salary for every year of service = = 1090385 3. Rs.1000000 Therefore Rs.1000000 shall be exempted and balance Rs.120000 shall be included in gross salary. Note: Dearness allowance shall be fully included in the meaning of salary as he is covered under the payment of gratuity act. 23. For Rs.1500 pension commuted – amount received Rs.60000 For Rs.1000 pension commuted – commuted amount shall be = Rs.40000 Hence, if the employee is also entitled to gratuity then out of the amount of Rs.60000 received as commutation of pension. Rs.40000 would be exempt and Rs.20000 would be taxable. Besides, the uncommuted pension of Rs.1500 p.m. will also be taxable. 24. a. 75% of commuted pension is equal to Rs.120000. hence commuted value of 1/3 of the Pension would amount to = Rs.53333 Therefore Rs.53333 shall be exempt and balance Rs.66667 would be taxable b. 75% of commuted pension is equal to Rs.120000. hence commuted value of 50% of pension would amount to = Rs.80000 Therefore Rs.80000 would be exempted and Rs.40000 would be taxable. 25. The minimum of the following four amounts will be exempt: 1. Leave encashment actually received = Rs.144000 2. 10 months’ average salary = 8800x10 = Rs.88000 99

3. Leave encashment for 6 [email protected] p.m. = Rs.52800 4. Amount specified by the government = Rs.300000 Hence Rs.52800 would be exempt and the balance of Rs.91200 would form part of gross salary 26. Rs.22400 27. Rs. 8,601 & Rs. 4,631 Amount required to be accumulated upto 9 years = 2500000/(1+5%) = Rs.2,380,952 Suppose, monthly investment made is: 100.00 Rs. Amount invested in equity funds for 9 years 65.00 Rs. 100*65% Amount invested in debt funds for 9 years 35.00 Rs. 100*35% N=9x12, I = 12, Pv=0, Pmt=-65, p/y=12, c/y=1, FV=solve(12261) Accumulation in equity funds after 9 years Rs.12,261 N=9x12, I = 9, Pv=0, Pmt=-35, p/y=12, c/y=1, FV=solve(5732) Accumulation in debt funds after 9 years Rs.5,732 Total funds accumulated in equity and debt funds 17,993 Rs. 12261+5732 Required cumulative investment per month 13,233 Rs. (2380952/17993)*(100) Investments in equity per month 8,601 Rs. 13233*0.65 Investments in debt per month 4,631 Rs. 13233*0.35 28. Current Cost of abroad trip Rs.1000000 FV of goal after 10 years Rs.1708144.46 Equity MF portfolio Rs.2500000 10% of equity MF Rs.250000 FV of equity after 10 years Rs.709855.25 Shortfall Rs.998289.21(1708144.46-709855.25) Let as assume initial investment every month is Rs.100 For 7 years balance and debt MF ratio is 50:50 After 7 year FV of balance MF Rs.5785.83 After 7 year FV of debt MF Rs.5387.26 Total accumulated fund Rs.11173.09 After 3 year FV of balance MF Rs.5577.73 After 3 year FV of balance MF Rs.12383.11 Total accumulated fund Rs.17960.84 SIP required Rs.5558 [(998289.21x100)/17960.84] 100