PFM - WORKBOOK

3 23 133100 569273.1979 4 24 146410 800675.6864 5 25 161051 1081267.566 6 26 177156.1 1419856.914 7 27 194871.71 1826713.261 8 28 214358.881 2313800.432 9 29 235794.7691 2895045.606 10 30 259374.246 3586650.161 11 31 285311.6706 4407448.7 12 32 313842.8377 5379323.629 13 33 345227.1214 6527683.768 14 34 379749.8336 7882016.788 15 35 417724.8169 9476526.712 16 36 459497.2986 11350869.45 17 37 505447.0285 13551001.29 18 38 555991.7313 16130157.51 19 39 611590.9045 19149980.93 20 40 672749.9949 22681823.08 21 41 740024.9944 26808244.26 22 42 814027.4939 31624742.62 23 43 895430.2433 37241746.93 24 44 984973.2676 43786913.02 25 45 1083470.594 51407769.73 26 46 1191817.654 60274767.4 27 47 1310999.419 70584789.59 28 48 1442099.361 82565198.04 29 49 1586309.297 96478491.41 30 50 1744940.227 112627670.4 31 51 1919434.25 131362415.8 32 52 2111377.675 153086202.2 33 53 2322515.442 178264487.6 34 54 2554766.986 207434142.6 35 55 2810243.685 241214303.8 36 56 3091268.053 280318867.4 37 57 3400394.859 325570869.2 38 58 3740434.344 377919034.3 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 51

39 59 4114477.779 438456823.9 40 60 503918427.7 Q3. Mr. A is 25 years of age and he is getting a salary of Rs1,50,000/- per annum. He wishes to save 12 % of his annual salary every year at the end of the year. If his salary increases by 15 % every year and ROI is 10 % per annum then what will be his accumulated savings at the age of 50? Sol. The Formula of calculating future value of a growing annuity when payments are made at the end of every period is: CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 52

FV = PMT [{(1+r)n – (1+g)n}] r–g FV=150,000*.12*(1.1^25 – 1.15^25)/ (0.10 - 0.15) = 79,50,328.80 Q4. Mrs. Sharma decides to save Rs.2000 today and thereafter she increases his saving 2% p.m. Calculate the accumulated amount after 40 years if rate of interest is a. 1% pm b. 12% p.a. compounding monthly Sol. The Formula of calculating future value of a growing annuity when payments are made in the beginning of every period is: FV = PMT [{(1+r)n – (1+g)n}]* ( 1 + r) r–g Sol. A 1% p.m. FV = 2000* [{(1.01) ^480 – (1.02)^480} / (.01 – .02) ]* (1.01) FV=2688933344 (If saving monthly n, g and r should be monthly) Sol. B. 12% p.a. compounding monthly = 1% pm Therefore answer will be same. Q5. Mrs. Y aged 34, invests Rs.80000 now and wants to increases her investment Rs.2000 every year. Calculate the accumulated corpus she will be having after 26 years or age 60 if rate of interest is 12% p.a.? Sol. It is a case Growing Annuity with Fixed Amount The Formula of calculating future value of a growing annuity with fixed amount when payments are made in the beginning of every period is: FV = [A * Sn + D * (Sn – n) / r] * (1+r) = [80,000*150.33… + 2000* ( 150.33…-26)/0.12] * (1.12) (Calculation of Sn: set = end, N=26, i=12, PMT = -1, FV = solve = 150.3339345) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 53

FV= 1,57,90,820.64 OR EXCEL YEAR(BEGIN) AGE GROWTH Rs.2000 ROI 12% P.A. 0 34 INVESTMENT FV 1 35 80000 2 36 82000 80000 3 37 84000 171600 4 38 86000 276192 5 39 88000 395335.04 6 40 90000 530775.2448 7 41 92000 684468.2742 8 42 94000 858604.4671 9 43 96000 1055637.003 10 44 98000 1278313.444 11 45 100000 1529711.057 12 46 102000 1813276.384 13 47 104000 2132869.55 14 48 106000 2492813.895 15 49 108000 2897951.563 16 50 110000 3353705.751 17 51 112000 3866150.441 18 52 114000 4442088.493 19 53 116000 5089139.113 20 54 118000 5815835.806 21 55 120000 6631736.103 22 56 122000 7547544.435 23 57 124000 8575249.767 24 58 126000 9728279.74 25 59 128000 11021673.31 26 60 130000 12472274.11 14098947 15790820.64 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 54

Q6. Mrs. Sharma decides to save Rs.100000 today and thereafter he increases his saving Rs.5000 every year. Calculate the accumulated amount after 40 years if Roi is 14% p.a. compounded monthly? Sol. The Formula of calculating future value of a growing annuity with fixed amount when payments are made in the beginning of every period is: FV = [A * Sn + D * (Sn – n) / r] * (1+r) First we need to convert 14% p.a compounding monthly into annual effective CNVR n =12, I = 14, EFf = solve = 14.93420292 = *100000 * 1746.33….+5000 * ( 1746.33…-40)/ 1.1493…+ * (1.1493…) (Calculation of Sn: set = end, N=40, i=14, PMT = -1, C/Y = 14, FV = solve = 1746.334509) = 266373647 Q7. Mr. S aged 35, saves Rs.2 lacs at the end of year and increases his saving 5000 every year. Calculate total corpus after 25 years or age 60 if rate of interest is 12% p.a. Sol. The Formula of calculating future value of a growing annuity with fixed amount when payments are made at the end of every period is: FV = [A * Sn + D * ( Sn – n ) / r ] = [200000 * 133.33.. + 5000 * (133.33.. – 25) / 0.12] FV = 31180511 EXCEL YEAR(BEGIN) AGE GROWTH 5000 P.A. ROI 12% P.A. 0 35 INVESTMENT FV 1 36 0 0 2 37 200000 3 38 205000 200000 4 39 210000 429000 5 40 215000 690480 6 41 220000 988337.6 225000 1326938.112 1711170.685 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 55

7 42 230000 2146511.168 8 43 235000 2639092.508 9 44 240000 3195783.609 10 45 245000 3824277.642 11 46 250000 4533190.959 12 47 255000 5332173.874 13 48 260000 6232034.739 14 49 265000 7244878.907 15 50 270000 8384264.376 16 51 275000 9665376.101 17 52 280000 11105221.23 18 53 285000 12722847.78 19 54 290000 14539589.52 20 55 295000 16579340.26 21 56 300000 18868861.09 22 57 305000 21438124.42 23 58 310000 24320699.35 24 59 315000 27554183.27 25 60 320000 31180685.26 Q8. Mr. X is 35 years of age and wishes to save 80000 at the end of the year and wants to increase his saving by 15 % every year and ROI is 15 % per annum then what will be his accumulated savings at the age of 60? Sol. It is a case of growing annuity with fixed percentage when rate of interest and growth rate are same. We use formula FV =[ A* n(1+R)n-1 ] Here A means first payment FV = 80000*25*(1.15)^24 = 5,72,50,352.38 Q9. What is the present value of perpetuity of a series of annual cash-flows of Rs.5000 which starts 1 year from now interest rate 12% p.a.? Sol. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 56

Present value of annuity in perpetuity if it is in the end of every period = annuity required/roi = 5000/0.12 = 41667 Q10. If Mr. Sharma needs Rs.2 lacs in the beginning of every month forever, what is the amount he needs to invest today if rate of interest 12% p.a. compounding monthly? Q11. Sol. Present value of annuity in perpetuity if it is in the beginning of every period = (annuity required/roi) *(1+roi) = (200000/.01) * (1.01) = 20200000 What is the present value of perpetuity of a series of annual cash-flows of Rs.5000 which starts 2 years from now interest rate 12% p.a.? Sol. First we need to calculate the PV after 2 years = (5000/0.12) = 41667 Now we need to find the present value of Rs.41667 at 12% per annum 41667/1.12^2 = 33217 Q12. What is the present value of perpetuity to be invested at the end, of a series of monthly cash flows of Rs.1420 which starts 2 years from now at interest rate of 13%per annum compounded monthly? Sol. Present value after 24 months = (PMT/i) +PMT = (1420/.01083)+1420 = Rs.132537.2669 I = 13/12 = 1.0833 This Rs.132537.2669 is received after 24 months so we need to find the present value of Rs.132537.2669 @13% per annum compounded monthly. SET: END/BEGIN N =2 I = 13 PV = -102336.39 (SOLVE) FV = 132537.2669 C/Y = 12 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 57

QUESTIONS ON ANNUITIES Q1. Ram is considering investing in a residential triplex project for which the EMI will be Rs.1248.33 per month. If the triplex costs Rs.172000 to build, and he can get a mortgage with monthly payments for 25 years at 9% p.a. compounded monthly interest, how much would be paid by Ram as down payment? Sol Set: End EXCEL N = 25 x 12 =PV(0.09/12,300,-1248.33,0,0) I =9 =148753 PV = Solve = 148753 PMT = -1248.33 P/Y = 12 C/Y = 12 Down payment = 172000-148753 = Rs.23247 Q2. Assume you can afford to make mortgage payments of Rs. 1550 per month, and the market rate of interest is 8% p.a. compounded monthly for monthly end payment loans with a term of 30 years. If you pay 20% of the purchase price of the house as a down payment, what is the most expensive property you can buy? Sol. EXCEL Set: End =PV(0.08/12,360,-1550,0,0) N = 30 x 12 =211239.41 I =8 PMT = -1550 PV = Solve = 211239.41 P/Y = 12 C/Y = 12 Value of house that can be purchased = 211239/0.8 = 264049.26 Faculty Comment: The PV calculated in step 1 is 80% of the value of house. (Let the cost of property x, therefore 80% of x = 211239.41) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 58

Q3. An investment of Rs.8000 per quarter end for 6 years at annual interest rate of 8.5%, compounded quarterly. How much amount will accumulated by the end of 6 years? Sol. EXCEL Set : End =FV(0.085/4,24,-8000,0,0) N =6x4 =247121.67 I = 8.5 PMT = -8000 FV = Solve = 247121.67 P/Y = 4 C/Y = 4 Q4. You borrow Rs.100000 which is to be repaid in equal monthly installments for a period of 3 years. The interest rate is 9% p.a. compounded monthly. What will be the monthly end payment? Sol EXCEL Set: End =PMT(0.09/12,36,100000,0,0) N = 3 x 12 =-3179.97 I =9 PV = 100000 PMT = Solve = -3179.97 P/Y = 12 C/Y = 12 Q5. Mr. Y is planning a trip with a family for which he is considering to invest Rs.15000at the end of every year for first 5 years and Rs.25000 at the end of every year for next 7 years and Rs.40000 thereafter. He is considering accumulating Rs. 645000 after 15years. If he requires a 15% annual return, what is the maximum amount she can accumulate for his trip and determine the surplus or deficit if any. Sol STEP2 STEP 3 STEP1 SET : END SET: END SET : END N =7 N =3 N =5 I = 15 I = 15 I = 15 PV = -101135 PV = -545692 PMT = -15000 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 59

FV = 101135(SOLVE) PMT = -25000 PMT = -40000 P/Y = 1 FV = 545692(SOLVE) FV = 968831(SOLVE) C/Y = 1 P/Y = 1 P/Y =1 C/Y =1 C/Y =1 Maximum amount that he can accumulate after 15 years = Rs.968829.32 Surplus amount = Rs.968831 - 645000 = Rs. 323831 Q6. Rohan is planning for his early retirement at the age of 50. His current age is 35. He estimated that he will need Rs. 28500 per month at the end upto the age of 75 after his retirement. Calculate the corpus that would be required to meet the post retirement expenses and what amount he should invest at the end of every month if the rate of interest on the savings is 8.50% per annum? Sol STEP 2 STEP 1 SET : END SET : END N = 180 N = 300 I = 8.50 I = 8.50 PMT = 10331.24 (SOLVE) PV = Solve -3634442.75 FV = 3634442.75 PMT = 28500 P/Y = 12 P/Y = 12 C/Y = 1 C/Y = 1 Q7. Mr. Ram aged 30 years plans to retire at the age of 55. He plans to keep some amount aside at the end of each year for first 15 years so that he can withdraw Rs.150000 per year end for 10 years once he gets retired with the first withdrawal at the age of 56. How much he should keep aside each year end if he can earn return of 8.25% per annum on the investments? Sol STEP 1 STEP 2 STEP 3 SET : END SET : END SET : END N = 10 N = 10 N = 15 I = 8.25 I = 8.25 I = 8.25 PV = -995260 (SOLVE) PV = -450461 (SOLVE) PMT = 16270 (SOLVE) PMT = 150000 FV = 995260 FV = 450461 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 60

Q8. Ram aged 55 years has some amount in an annuity which will pay him after 5 years Rs.30000 per annum at the beginning of every year for 10 years. Given the rate of interest at 8% p.a., calculate how much amount he has invested now? Sol. Step 1 Step 2 Set: Begin Set: End N = 10 N =5 I =8 I =8 PMT = 30000 PV = Solve = -147963.3 PV = Solve = -217406.63 FV = 217406.63 Q9. Mitesh invested some amount in a scheme that starts paying after 10 years, Rs.20000 per year for five years at the beginning of each year and there after Rs.10000 at the beginning of each year for the next five years. How much Mitesh has invested, if return on the investment is 10% throughout the term period? Sol. Step 1 Step 2 Step 3 Set : Begin Set : Begin Set : End N =5 N =5 N = 10 I = 10 I = 10 I = 10 PMT = 10000 PMT = 20000 FV = 109288.89 PV = Solve = -41698.65 FV = 41698.65 PV = Solve = -42135.59 PV = Solve = -109288.89 Q10. Mr. Sohan (aged 40 years) is working in a pharma company. He invested Rs.300000 in a term deposit which enables him to earn a certain amount at the beginning of each year for 10 years starting five years from today. Assuming the rate of interest of6.75% p.a. Calculate the amount that Mr. Sohan will receive each year? Sol. Step 1 Step 2 Set: End Set: Begin N=5 N = 10 I = 6.75 I = 6.75 PV = -300000 PV = -415872 FV = Solve = 415872 PMT = Solve = 54827.68 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 61

AMORTIZATION Amortization: Amortization is the distribution of a single lump-sum cash flow into many smaller cash flow installments, which can be determined by the amortization schedule. Under amortization every installment consists of both principal and interest. Greater amount of the payment is applied to interest at the beginning of the amortization schedule, while more money is applied to principal at the end. Flat Interest Rate Flat interest rate, as the term implies, means an interest rate that is calculated on the full amount of the loan throughout its tenure without considering that monthly repayments (EMIs) gradually reduce the principal amount. As a result, the Effective Interest Rate is noticeably higher than the nominal Flat Rate quoted in the beginning. The formula of calculating fixed rate of interest is – Interest Payable per Installment = (Original Loan Amount * No. of Years * Interest Rate p.a.) / Number of Installments For Q, if you take a loan of Rs 1, 00,000 with a flat rate of interest of 10% p.a. for 5 years, then you would pay: = (100000 + 10% of 100000*5)/5 =Rs30000 per year. This method is particularly used to calculate the interest payable for personal loans and vehicle loans. In this method, you have to pay interest on the entire loan amount throughout the loan tenure. It is actually less popular among the borrowers because even if you gradually pay down the loan, the interest does not decrease. Reducing / Diminishing Interest Rate Reducing/ Diminishing balance rate, as the term suggests, means an interest rate that is calculated every month on the outstanding loan amount. In this method, the EMI includes interest payable for the outstanding loan amount for the month in addition to the principal repayment. After every EMI payment, the outstanding loan amount gets reduced. Therefore, the interest for the next month is calculated only on the outstanding loan amount. The formula for calculating reducing balance interest is – CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 62

Interest Payable per Installment = Interest Rate per Installment * Remaining Loan Amount For Q, if you take a loan of Rs 1, 00,000 with a reducing rate of interest of 10% p.a. for 5 years. Your annual repayment would be Rs.26380 instead of Rs.30000 which is paid as per flat rate of interest. In the first year interest would be charged on 100000 and 2nd year interest would be charged on Rs.90000; in the second year interest would be charged on Rs.83620 (100000-16380) and so on. Unlike the flat rate method, you would end up paying Rs.131900 instead of Rs. 1.5 lakh. This method is particularly used to calculate the interest payable for housing, mortgage, property loans, overdraft facilities, and credit cards. Difference between Flat Interest Rate and Reducing Balance Rate  In flat rate method, the interest rate is calculated on the principal amount of the loan. On the other hand, the interest rate is calculated only on the outstanding loan amount on monthly basis in the reducing balance rate method.  Flat interest rates are generally lower than the reducing balance rate.  Calculating flat interest rate is easier as compared to reducing balance rate in which the calculations are quite tricky.  In practical terms, the reducing rate method is better than the flat rate method. Q1. Mr. Sharma has taken a housing loan of Rs 20 lacs for 25 years @ 8% p.a. reducing monthly, calculate the following: a. EMI (equated monthly installment ) EXCEL Sol. Go to CMPD =PMT(0.08/12,300,2000000,0,0) Set = End =-15436.32 N =300 I =8 PV =20,00,000 P/Y = 12 C/Y =12, PMT = solve = -15,436.32.. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 63

b.. Total interest paid in first 5 years =CUMIPMT (rate, nper, PV, start, end, type) Sol. Go to AMRT Pm1 =1 start, end means Interest on Start to End instalment i.e.1 to 60. Pm2=60, SINT=-7,71,658.29 =CUMIPMT(0.08/12,300,2000000,1,60,0) NOTE: =771658.29 Here PM1 means payment number starts with. And PM2 means payment number ends with. c. Principal paid in last year =CUMPRINC(rate, nper, PV, start, end, type) Sol. Go to AMRT =CUMPRINC(0.08/12,300,2000000,289,300,0) Pm1=24*12+1=289 =1,77,452.61 Pm2=300 SPRN=1,77,452.61 =IPMT(rate, per, nper, PMT, PV,[FV], [type]) =IPMT(0.08/12,14,300,-15436.3243,2000000,0,0) d. Interest paid in 14th installment =13,143.60 Sol. Go to AMRT Pm1=14 Pm2=14 INT=13,143.60 e. Total principal paid in first 12 years =CUMPRINC(rate, nper, PV, start, end, type) Sol. Go to AMRT =CUMPRINC(0.08/12,300,2000000,1,144,0) Pm1=1 =505786.98 Pm2=144 SPRN=5,05,786.98 f. Total interest paid in last 12 years =CUMIPMT(rate, nper, PV, start, end, type) Sol. Go to AMRT =CUMIPMT(0.08/12,300,2000000,157,300,0) Pm1=13*12+1 Pm2=300 =7,96,779.85 SINT=7,96,779.85 g. Outstanding loan after 12.5 years =PV(0.08/12,25*12-12.5*12,-15436.324,0,0) Sol. Go to AMRT =14,60,811.20 Pm1=1 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 64

Pm2=12.5*12 BAL=14,60,811.20 i. Total principal paid from 12th year till 20th year Sol. Go to AMRT =CUMPRINC(rate, nper, PV, start, end, type) Pm1=11*12+1 = CUMPRINC(0.08/12,300,2000000,133,240,0) Pm2=240 =7,95,855.98 SPRN=7,95,855.98 Q2. Mr. X has taken a housing loan of Rs.5000000 of 20 years @ 11% p.a. reducing monthly on floating bases. The bank would increases rate of interest to 12% p.a. reducing monthly after 3 years, what would be the new EMI? Sol. Set=End EXCEL N = 240 =PMT(0.11/12,240,5000000,0,0) PV = 50,00,000 =-51,609.42 I =11 FV =0 P/Y =12 C/Y =12 PMT =solve = -51,609.42 Now we need to calculate the outstanding loan after 3 years Go to AMRT: =PV(0.11/12,240-36,-51609.42,0,0) PM1 =1 =47,54,960.38 PM2 =36 BAL =47,54,960.38 =PMT(0.12/12,240-36,4754960.38,0,0) Now we need to calculate the new EMI =-54739.84 SET =END N =240-36 PV =-47,54,960.38 I =12% P/Y =C/Y=12 PMT = solve = -54,739.84 Q3. Mr. Sharma has taken a personal loan of Rs.5 lacs for 5 years @ 12% p.a. on flat rate of interest. What would be his EMI? Find the interest p.a. but reducing monthly o fthe same loan? CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 65

Sol. Flat rate of interest means interest is charged on loan amount initially of the whole duration on principal amount and is amortized interest and principal in loan duration. Therefore interest of first year= PRT/100 = 5,00,000*5*12/100 = 60000 Interest of 5 years = 60000*5 = 300000 Total of principal and interest = 500000 + 300000 = 800000 Now monthly repayment as per flat rate of interest = 8,00,000/60 = 13,333.33 Assuming interest is based on reducing monthly balance CMPD EXCEL SET =END, =RATE(60,-13333.333,500000,0,0) N =60, =0.016925 but it is monthly effective PV =5,00,000, therefore nominal reducing monthly PMT =13,333.33, =0.016925*12 P/Y =12, =20.309% p.a. reducing monthly C/Y =12, I =20.309% Q4. Mr. S has taken a loan of Rs.30,00,000 at Interest rate is 12.50% p.a. reducing monthly for 25 years. Calculate: 1. Monthly payment which is paid in the at the end of every month 2. Total interest paid from the end of 3rd year to 8th year 3. Total principal paid in first 7 years. 4. Interest paid in 24th installment. 5. Outstanding loan after 11 years 6. Interest paid in last 4 years 7. Principal paid in first 3 years SOL. 1 EMI on Loan: Set: End N = 300 I =12.50 PV = 3000000 PMT = -32710.6241(solve) P/Y = 12 C/Y = 12 2. Total interest paid from 3rd year to 8th year PM1 = 25 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 66

PM2 = 96 Solve for INT = 2155787.381 3. Total principal paid in first 7 years. PM1 = 1 PM2 = 84 Solve for PRN = 194631.2391 4. Interest paid in 24th installment. PM1 = 24 PM2 = 24 Solve for INT =30856.87 5. Outstanding loan after 11 years PM1 = 1 PM2 = 132 Solve for BAL = 2589569.643 6. Interest paid in last 4 years PM1 = 253 PM2 = 300 Solve for INT = 339461.89 7. Principal paid in first 3 years PM1 = 1 PM2 = 36 Solve for PRN = 63403.511 Q5. Mr. Sharma has taken a personal loan of Rs. 50 lacs for 5 years @ 12% p.a. flat rate of interest. What Would be his EMI? According to you what is the reducing rate of interest of above loan? SOL. Assuming the rate of interest is reducing monthly Calculate the EMI EXCEL Press CMPD =PMT(0.12/12,60,5000000,0,0) N = 60 =-11122.22 I =12 PV = 5000000 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 67

PMT = -11122.22 P/Y = 12 C/Y = 12 But in question it is clearly mentioned flat rate of interest Calculate the Flat EMI = =[(600000 X 5) + 5000000]/60 = 133333.33 Put the New EMI In CMPD to calculate the new reducing interest rate Press CMPD EXCEL SET = END =RATE(60,-133333.33,5000000,0,0) N = 60 =0.016925 but it is effective monthly I =20.31(solve) =0.016925*12 PV = 5000000 =20.31% p.a. reducing monthly PMT = -133333.33 P/Y = 12 C/Y = 12 Q6. Mr. Rana has taken a home loan of Rs.20 lacs for 20 years @ 9% p.a. reducing monthly. If interest rate is increased to 10% after 3 years. Mr. Rana would request the bank not to increase the EMI. Therefore bank would increase number of EMIs. How many EMIs will be increased? Sol. First we need to calculate EMI as per 9% interest. SET = END EXCEL N = 240 =PMT(0.09/12,240,2000000,0,0) I =9 =-17994.51912 PV =2000000 P/Y =12 C/Y =12 PMT = SOLVE =-17994.51912 After 3 years ROI is increased so we need to calculate the outstanding loan. GO TO AMRT PM1 =1 =PV(0.09/12,240-36,-17994.51912,0,0) PM2 =36 =1876767.405 BAL =SOLVE=1876767.405 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 68

Now interest rate is to be considered 10%, if EMI same this means n would change SET = END N = SOLVE=245 =NPER(0.10,-17994.51912, 1876767.405,0,0) I =10 =245 PV =1876767.405 P/Y =12 C/Y =12 PMT =-17994.51912 This means 41 more EMIs Mr. Rana would be paying. Q7. Mr. X has taken a housing loan of Rs.25 crore for 25 years @ 12.25% p.a. reducing monthly, calculate the following: a. EMI b. Total interest paid in first 4 years c. Principal paid in 16thinstalment d. Total principal paid in 22nd year e. Interest paid in last 8 years f. Outstanding loan after 3.5 years g. Outstanding loan after paying 11yrs h. Total principal paid from beginning of the 8th year till 23th year SOL. a. EMI on Loan: Set: End N = 300 I =12.25 PV = 250000000 PMT = -2679359.463(solve) P/Y = 12 C/Y = 12 b. Total interest paid in first 4 years PM1 = 1 PM2 = 48 Solve for INT = 120776113.9 C. Principal paid in 16th installment PM1 = 16 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 69

PM2 = 16 Solve for PRN =148221.48 D. Total Principal paid in 22nd year PM1 = 253 PM2 = 264 Solve for PRN = 20893629.7 E. Interest paid in last 8 years PM1 = 17*12+1 PM2 = 25*12 Solve for INT= 93748376.42 F. Outstanding loan after 3.5 years PM1 = 1 PM2 = 42 Solve for BAL = 243367051.5 G. Outstanding loan after paying 11 years PM1 = 1 PM2 = 132 Solve for BAL = 214820698.3 H. Total principal paid from beginning of the 8th year till the end of 23th year PM1 = 85 PM2 = 276 Solve for SPRN = 176427600 Q8. Mr. Gupta wants to take personal loan of Rs.20 lakhs for 3 years from a bank which offers 10% p.a. reducing balance basis, how much EMI he will have to pay? Sol. CASIO FC 200V EXCEL Set – END =PMT(0.10/12,36,200000,0,0) N = 3*12 =-64534.374 I = 10 PV = 2000000 P/Y = 12 C/Y = 12 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 70

PMT = solve = -64534.374 Q9. Your client has been offered a personal loan of Rs.15 lakhs for 5 years at rate of interest 9% p.a. on reducing balance basis. Loan amount is to be paid in annual payments. He wants to know how much he will have to pay in the beginning of every year. Sol. CASIO FC 200V EXCEL Set – BEGIN =PMT(0.09,5,1500000,0,1) N =5 =-353797 I =9 PV = 1500000 PMT = solve = -353797 Q10. Mr. X has taken personal loan of Rs.1 lakh for 7 years from a bank which offers 12% p.a. flat rate of interest. How much EMI he will have to pay? Sol. Interest for one year = 12% of 100000 = 12000 Interest for 7 years = 12000*7 = 84000 Total liability to repay = 100000+84000 = 184000 ( as it is flat rate of interest ) EMI = 184000/84 = 2190.476 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 71

CASH FUNCTION Q1. An investment is expected to produce the cash flow of Rs.1 5000, Rs. 12000 and Rs. 18000 at the end of every year. If the required rate of return is 14%, what will be the present value of this investment? Sol. CASH: I=14%, EXCEL 1234 1234 0 15K 12K 18K. 15000 12000 18000 NPV=34,540.99 =NPV(0.14,select values) =34,540.99 NOTE: In EXCEL while calculating NPV, it considers each payment at the end of every period. Q2. Given a 12% p.a. interest rate, an asset that generates cash flows (end) of Rs.150000 in year 1, Rs. 250000 in year 2, and Rs. 150000 in year 3, and then sold for Rs.1550000 at the end of year 4 has a present value of ___________________. Sol. Cash: I%=12 EXCEL 123 4 5 put values in 1 TO 4 ENTRIES then calculate NPV 150K 1550K. 0 150K 250K NPV=14,25,047.1 Q3. Calculate the present value of cash inflows which start from today 1000, after 1 year 2000 then 3000 every year for next 3 years, if rate of interest is 10% p.a. Sol. Cash I %=10 EXCEL 12345 12345 1000 2000 3000 3000 3000 1000 2000 3000 3000 3000 NPV=9600.5 =NPV(0.10,select values) =8727.73 Why? CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 72

Because excel consider every payment end of every period. While calculating NPV it has discounted one year extra of each payments. Therefore if we give one compounding to NPV, we can get correct answer. Correct answer = 8727.73*1.1 = 9600.5 Q4. Mr. Y has decided to deposit Rs.2000 in the beginning of every year for first 2 years and Rs.3000 for next 4 years and Rs.1000 for further 4 years. If ROI is 12% p.a. how much he will have at the time of maturity of 10 years? Sol. CASH. I=12%, 1 2 3 4 5 6 7 8 9 10 11 2K 2K 3K 3K 3K 3K 1K 1K 1K 1K 0 Last entry is 0 as 11 is called end of 10 years or beginning of eleventh year. NFV = SOLVE = 42379 EXCEL We don’t have NFV function. So first we need to calculate NPV then we will compound the nPV to calculate FV. =NPV(0.12,select value as mentioned above) =12182.94982 But it has discounted one extra year, we will compound to know correct NPV Correct NPV = 12182.94982*1.12 =13644.90379 For future value as per question after 10 years we will compound NPV for 10 years. Therefore FV = 13644.90379*1.12 = 42379 Q4. Mr. X starts depositing Rs.40000 in the beginning of every year for 4 years in scheme and Rs.50000 p.a. for next 4 years getting ROI 8% p.a. Calculate Future Value after 12 years? Sol. CASH: I=8%, 123456789 10 11 12 13 -40K -40K -40K -40K -50K -50K -50K -50K 0 0 00 0 Why 13 zero, as in cash function 13 is called beginning of 13th year or end of 12th year. NFV = 6,91,357.39 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 73

EXCEL =NPV(0.08, select values from 1 to 8) =254210.6775 Correct NPV = 254210.6775*1.08 =274547.5317 Now we can calculate FV FV = 274547.5317*1.08^12 = 691357.39 Q5. Mr. X is being offered 2 investments which have periodic cash flows. Please advise him which one is better? Scheme 1: Scheme 2: Initial outflow 37,000 40000 Cash Inflows 3000 6000 5000 NIL Sol. CASH NIL 5000 8000 4800 1250 NIL 98000 90600 T: 1 2 3 4 5 6 7 IRR S1: -37 3 5 0 8 1.25 98 23.6 S2: -40 6 0 5 4.8 0 90.6 20.3 S1 is better. EXCEL =IRR( select values from 1 to 7) Q6. Determine the internal rate of return for a project that costs Rs.1,78,000 and inflows are of Rs.22,000 in the first year, Rs.16,000 in the second year, Rs.18,000 in the third year and Rs.2,00,000 in the sixth year. Sol. Cash 1234567 -178K 22K 16K 18K 0 0 200K IRR =SOLVE= 7.54% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 74

Q7. Determine the internal rate of return for a project that costs Rs.45362 and is expected to yield Rs.2500 per year for the first five years, Rs.3500 per year for the next two years, and Rs.58000 at the end? Sol. Cash 1 234 56 7 8 9 -45632, 2500, 2500, 2500, 2500, 2500, 3500, 3500, 58000 IRR =SOVLE =8.07% Q8. Your friend has agreed to deposit Rs.3000 in your brokerage account in the beginning of each of the next five years. You estimate that you can earn 9 percent a year on your investments. How much will you have in your account immediately after your uncle makes his last deposit? Sol. EXCEL Go to cash function =NPV(0.09,select values from 1 to 5) I =9 =11668.95 1 = 3000 correct NPV = 11668.95*1.09 = 12719.16 2 = 3000 3 = 3000 4 = 3000 NFV = 12719.16*1.09^4 = 17954.13 5 = 3000 NFV = Solve = 17954.13 Why we have not put 6th entry 0 as he wants to know FV exactly after making 5th payment, means in the beginning of 5th payment or end of 4th year. This means 5th payment will not get any compounding. Q9. Given a 9% p.a. interest rate, an asset that generates cash flows of Rs.10000 in year1, Rs. 20000 in year 2, and Rs. 10000 in year 3, and then sold for Rs. 150000 at the end of year 4 has a present value of______ Sol. EXCEL Go to Cash 1 10000 I =9 2 20000 Cash = Exe 3 10000 1 =0 4 150000 2 = 10000 3 = 20000 =NPV(0.09,select 1 to 4 ) =139993.52 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 75

4 = 10000 5 = 150000 NPV = Solve= 139993.52 Q10. Vinay is evaluating an investment that will provide the following returns at the end of each of the following years: year 1, Rs. 12,500; year 2, Rs. 10,000; year 3, Rs. 7,500; year 4, Rs. 5,000; year 5, Rs. 2,500; year 6, Rs. 0; and year 8, Rs. 12,500. Vikas believes that he should earn an annual rate of 9 percent on this investment. How much should he pay for this investment? Sol. CASH: EXCEL I% = 9 1=12500 1 =0 2=10000 2 = 12,500 3=7500 3 = 10,000 4 = 7,500 4=5000 5 = 5,000 5=2500 6 = 2,500 6=0 7 =0 7=12500 8 = 12,500 =NPV(0.09,select 1 to 7) NPV = SOLVE= 37,681 =37,681 In cash function 1 is called beginning of But in excel while calculating NPV, 1 is called end of 1st year. first year. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 76

Questions Based on Inflation  Inflation is a situation of rising prices in the economy.  A more exact definition of inflation is a sustained increase in the general price level in an economy. Inflation means an increase in the cost of living as the price of goods and services rise.  The rate of inflation measures the annual percentage change in the general price level.  Inflation leads to a decline in the value of money. “Inflation means that your money won’t buy as much today as you could yesterday.”  If the prices of goods rise. The same amount of money will purchase a smaller quantity of goods. Inflation-Adjusted Return or Real Rate of Return Inflation eats away at the value of every stream of cash flows, including salaries, pension payments and coupon payments. In many cases, the real interest rates on savings accounts are negative. For instance, if a savings account pays 1.5% per year but inflation is 3%, the saver is effectively losing money every year he has the money in the account. However, putting money in a savings account is better than putting it in a coffee can in the backyard, where it will lose the full 3% value every year due to inflation. Accordingly, it is important to consider the effects of inflation when making an investment that promises to provide a future stream of cash flows. After all, what is worth Re1 today may not be worth Re.1 tomorrow if it is not invested? How Does an Inflation-Adjusted Return Work? Let's say Mr. Sharma buys Company XYZ stock for Rs.5. A year later, the stock is trading at Rs.6. Sharma's return is 20%. However, inflation during the year was 4%. Using this information and the formula above, Sharma's inflation-adjusted return is: RRR=[(1+ROI)/(1+INF)]-1 This formula is used in excel. But in financial calculator FC 200V, we can use RRR=[{(1+ROI)/(1+INF)}-1]*100% Or = (R-I)/(1+R%) Inflation Adjusted Return = {(1 + Return)/(1 + Inflation Rate)– 1} * 100 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 77

Or We can use this formula: RRR = (return – inflation)/(1+ inflation ) [(1+.20)/(1+0.04)]-1 = 15.38% Sharma's return may look like it's 20%, inflation-adjusted return is only 15.38%. Q1. Calculate real rate of return if rate of interest is 10% p.a. and inflation 4% p.a. Sol. FC 200V EXCEL RRR = (10-4)/(1+4%) =1.10/1.04-1 = 6/1.04 =0.05769 =5.769% p.a. Q2. Calculate the real rate of return if the rate of inflation to be 4.5% p.a. and rate of interest is 16% p.a.? Sol. FC 200V EXCEL RRR = (16-4.5)/(1+4.5%) =1.16/1.045-1 = 11.5/1.045 =0.1100478 =11.00478% p.a. Q3. A bond that pays interest annually yields a 7.25 percent rate of return. The inflation rate for the same period is 3.5 percent. What is the real rate of return on this bond? Sol. RRR = (7.25-3.5)/1.035 = 3.62% Q4. If you are promised a nominal return of 12% on a one year investment, and you expect the rate of inflation to be 3%, what real rate do you expect to earn? Sol. RRR = (12-3)/1.03 = 8.74% Q4. If the real rate of return is 5.3% p.a. and inflation is 4%, find the nominal rate of interest. Sol. RRR = (ROI – INF)/1+INF EXCEL 5.3 = (ROI – 4)/1.04 RRR=1+R/1+Inf -1 5.3*1.04 + 4 = ROI R = (1+RRR)(1+INF) -1 ROI = 9.51% =1.053*1.04-1 = 0.09512 Q5. If the real interest rate is 5.3% and inflation is 7.5%, find the nominal rate of interest. Sol. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 78

RRR = (ROI – INF)/1+INF EXCEL 5.3 = (ROI – 7.5)/1.075 RRR=1+R/1+INF -1 5.3*1.075 + 7.5 = ROI R = (1+RRR)(1+INF) -1 ROI = 13.1975% =1.053*1.075-1 = 0.131975 Q6. If the nominal interest rate is 12.3% and inflation is 7.6%, find the real rate of interest SOL. 4.37% Q7. If the nominal interest rate is 12.4% and inflation is 7.8%, find the real rate of interest? Sol 3.99% Q8. If the interest rate is 12% and the real interest rate is 2%, find the inflation rate. Sol. RRR = [(1 +ROI)/(1+INF) – 1] * 100 RRR=1+R/1+INF -1 2 = [(1.12)/(1+INF) – 1] * 100 1+2% = 1.12/1+INF 2/100 = [(1.12)/(1+INF) – 1] 1+INF = 1.12/1.02 .02+1 = (1.12)/(1+INF) INF = 1.12/1.02 -1 (1+INF) = 1.12/1.02 INF = 0.098 (1+INF) = 1.098 INF = 1.098 – 1 = .098 = 9.8% p.a. Q9. If the interest rate is 15% and the real interest rate is 4%, find the inflation rate? Sol. 10.577% Q10. If the interest rate is 8% and the real interest rate is 2%, find the inflation rate? Sol. 5.8823% Q11. Calculate real rate of return annually if rate of interest 8% p.a. comp monthly and inflation is 3% p.a. Sol. First we need to convert nominal rate of return into effective rate of return CNVR EXCEL N = 12 =EFFECT(0.08,12) I =8 =0.082999 EFF = 8.2999% RRR = (8.2999-3)/(1+3%) RRR=1.082999/1.03-1 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 79

= 5.2999/1.03 =0.051455 =5.1455% p.a. Q12. Calculate real rate of return annually if rate of interest 12% p.a. comp quarterly and inflation is 5% p.a. Sol. First we need to convert nominal rate of return into effective rate of return CNVR EXCEL N =4 =EFFECT(0.12,4) I = 12 =0.1255 EFF =12.55% RRR = (12.55-5)/(1+5%) RRR=1.1255/1.05-1 = 7.55/1.05 =0.051455 =7.19% p.a. =0.0719 NOTE: While using excel, if we need inflation adjusted monthly annuity during post retirement life, our RRR should be monthly. Q13. If rate of interest 12% p.a. and inflation 5% p.a., calculate monthly real rate of return? Sol. EXCEL RRR = {(1+R)/(1+I)}^(1/N)-1 = (1.12/1.05)^(1/12)-1 = 0.005393 Q14. If rate of interest 8% p.a. and inflation 3% p.a., calculate monthly real rate of return? Q15. Sol. EXCEL RRR = {(1+R)/(1+I)}^(1/N)-1 = (1.08/1.03)^(1/12)-1 = 0.003958 If rate of interest 10% p.a. compounding monthly and inflation 4% p.a., calculate quarterly real rate of return? Sol. First we need to convert nominal rate of return into effective rate of return CNVR EXCEL N = 12 =EFFECT(0.10,12) I = 10 =0.10471 EFF =10.471% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 80

EXCEL RRR = {(1+R)/(1+I)}^(1/N)-1 = (1.10471/1.04)^(1/4)-1 = 0.015205 Q16. Solve the questions mentioned below on the basis of following information: Current age of a person = 30 Retirement age = 60 Life expectancy = 85 Rate of Interest = 12% p.a. Inflation = 7% p.a. Current house hold expenses = 4 lacs p.a. Current cost of marriage = 25 lacs Current cost of education = 10 lacs Current cost of house = 50 lacs Current cost of world tour =10 lacs A. How much should be saved monthly for 10 years for higher education if money is required after 10 years? Sol. Cost of education today=1000000 Cost after 10 years @7% inflation N=1000000*(1.07)^10=1967151.357 Set: Begin EXCEL N = 120 =PMT(1.12^(1/12)-1,120,0,1967151.357,1) I = 12 =-8780.51 FV = 19.67 P/Y = 12 C/Y = 1 PMT = Solve (-8780.51) B. How much should be saved monthly for 15 years for house if it is required after 15 years? Sol. Cost of house today=5000000 Cost after 15 years @7% inflation N=1,37,95,157 Begin N = 180 EXCEL I = 12% =PMT(1.12^(1/12)-1,180,0,13795157,1) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 81

PV = 0 =-28985 FV = 1,37,95,175 PMT = Solve (-28,985) P/Y = 12 C/Y = 1 C. How much should be saved quarterly for 20 years for marriage if money is required after 20 years? Sol. Current cost of marriage = Rs.2500000 Cost of marriage after 20 years = 2500000*1.07^20 = 96,74,211 Begin N = 20*4 =80 EXCEL I = 12 =PMT(1.12^(1/4)-1,80,0,9674211,1) PV = 0 =-31255 FV = 9674211 PMT = Solve (-31255) P/Y = 4 C/Y = 1 D. What would be annual house-hold expenses at age 60 Sol. Current annual household expenses = Rs.400000 Household expense at 60 = 400000*1.07^30 = Rs.30,44,902 E. How much corpus will be required at age 60 if Rs.10 lacs fixed amount per annum is required during post retirement life? Sol : Begin N = 25 EXCEL I = 12% =PV(0.12,25,1000000,0,1) PMT = 10,00,000 =-8784315 FV = 0 PV = Solve=- 87,84,315 F. How much corpus will be required at age 60 if Rs.10 lacs per annum inflation adjusted is required during post retirement life. Sol. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 82

In this question if we need inflation adjusted annual annuity, we need annual rear rate of return. RRR= (12 – 7)/( 1+ .07) = 5/1.07 = 4.6728% EXCEL Begin, =PV(1.12/1.07-1,25,1000000,0,1) N =25, =-15248586.44 I = 4.6728....%, PMT =10,00,000, FV =0, PV = Solve-1,52,48,586.44 G. If saving Rs.5000 pm during pre-retirement life, i. how much corpus would be at age 60? ii. How much can be withdrawn fixed monthly amount during post retirement life? iii. How much can be withdrawn monthly inflation adjusted during post retirement life? Sol. FC 200 V SOL- I SOL - II SOL - III Set Begin, set begin set begin N =360, N =300 I =(12-7)/1.07 I =12, I = 12 PV =-15404866 PV =0, PV =-1,54,04,866 PMT = SOLVE=85960.77 PMT =-5000, P/Y =12 P/Y = 12 P/Y =12, C/Y =1 C/Y = 1 C/Y =1, PMT =Solve 1,53,849 N = 25*12 FV =Solve1,54,04,866 EXCEL SOL – II SOL – 1 =PMT(1.12^(1/12)-1,300,-15404866,1) =FV(1.12^(1/12)-1,360,-5000,0,1) =153849 =15404866 SOL-III =PMT((1.12/1.07)^(/12)-1,300,-15404866,0,1) =85960.77 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 83

Q17. Your Uncle expects to live another 10 years. (Should he live longer, he feels you would be pleased to provide for him.) He currently has Rs.50000 in savings which he wishes to spread evenly in terms of purchasing power over the remainder of his life. Since he feels inflation will average 6 percent annually, his annual beginning-of-year withdrawals should increase at a 6% growth rate. If he earns 8 percent on his savings not withdrawn, how much should his first withdrawal be to be made in the beginning of each year? Sol. EXCEL Set: Begin =PMT(1.08/1.06-1,10,=50000,0,1) N = 10 =5430 I = RRR = 1.8867 PV = - 50000 PMT = Solve = 5430 P/Y = 1 C/Y = 1 Q18. Mr. Aman aged 25 has invested Rs. 1,00,000 for a world tour with his family after12 years .The current cost for the tour is Rs.5lacs and the inflation rate is expected to be 5.5% per annum for first 5 years and 6.5% per annum thereafter. He is also planning to invest around Rs 60,000 in the gold ETF after 5 years so that he is able to enjoy the trip probably without any deficit. Rate of interest for the entire period on investments is 11% per annum. Calculate the surplus or deficit amount for him to achieve this target? Sol. STEP 2 STEP 1 SET : END/BEGIN SET : END/BEGIN N =7 N =5 I = 6.5 I = 5.5 PV = -653480 PV = -500000 FV = 1015499.12 Solve FV = 653480 Solve Total cost of world tour after 12 years = Rs.1015499.12 OR Cost of world tour after 12 years = 500000*1.055^5 *1.065^7 = Rs.1015499.12 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 84

STEP 3 STEP 4 SET: END SET : END N = 12 N =7 I = 11 I = 11 PV = -100000 PV = -60000 FV = 349845.05 Solve FV = 124569.60 Solve or or =100000*1.11^12 =60000*1.11^7 =349845.05 =124569.60 Total maturity value after 12 years of both investments = 349845.05+124569.60= Rs.474414.65 Thus Aman would be facing a deficit = 1015499.12 - 474414.65= Rs.541084.47 Q19. Mr. Dinesh has invested in a fund Rs.75, 000/- for his daughter’s marriage which is expected 18 years down the line. The cost of marriage in today’s monetary term costs Rs.90000/- and inflation is expected to be at 6% for the first ten years and 5% for the next eight years. Mr. Dinesh will also be contributing Rs.25000/- at the end of 9 years from now to the above said fund. Interest Rate for the entire period is assumed to remain fixed at 12% p.a. Calculate surplus or deficit amount for Mr. Dinesh to achieve his target? Sol To solve this problem, we need to know actual marriage expenses after 18 years. STEP 1 STEP 2 SET: END/BEGIN SET: END/BEGIN N = 10 N =8 I =6 I =5 PV = -90000 PV = -161176.29 FV = S= 161176.29 Solve FV = 238130.79 Solve OR Cost of marriage after 18 years =90000*1.06^10*1.05^8 So, marriage expenses after 18 yrs. will be Rs.238130/- Now, we have to calculate future value of Rs.75000/- for a period of 18 years and Future Value of Rs.25000/- for a period of 9 years STEP 3 STEP 4 SET: END/BEGIN SET: END/BEGIN N = 18 N =9 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 85

I = 12 I = 12 PV = -75000 PV = -25000 FV = 576747.43 Solve FV = 69326.96 Solve So, the amount he will have at the end of 18 years will be 576747+69327 = Rs.646074/- Marriage Expenses after 18 years are Rs.238130/- He will have a surplus of Rs. (646074-238130) = Rs.407944/- Q20. Rahul aged 36 wants to generate the corpus Rs.10 lacs for his daughter wedding 20 years down the line in today’s term. What amount he should invest starting today to generate this corpus. Expected return is around 8.5% per annum compounded semiannually and inflation rate is 6% per annum. Sol STEP 2 STEP 1 SET : END SET : END N = 20 N = 20 I = 8.5 I =6 PV = -606840.78(SOLVE) PV = -1000000 FV = 3207135.47 FV = 3207315.47 Solve P/Y = 1 P/Y = 1 C/Y = 2 C/Y = 1 Q21. Mr. X is planning to send his son for higher education for 5 years after 7 years and the current cost of education is approximately Rs.6 lacs which are expected to inflate at 4% per annum. Talking about the current situation he has around Rs.50000 invested in a fixed deposit giving him 8.5% per annum for seven years .What additional amount should be invested today so that he is able to meet the gap. Expected rate of return is 8.25% per annum compounded quarterly. Sol STEP 2 STEP 1 SET: END SET: END N =7 N =7 I = 8.5 I =4 PV = -50000 PV = -600000 FV = 88507.11 Solve FV = 789559.06 Solve The gap = 789559.06-88507.11 = 701051.95 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 86

STEP 3 =PV(0.0825/4,7*4,0,701051.95,1) SET: END =-395818.91 N =7 I = 8.25 PV = -395818.91(SOLVE) FV = 701051.95 P/Y = 1 C/Y = 4 Q22. Ram requires Rs. 500000 after 12 years for his children education in today’s term. In order to meet the goal he has invested Rs. 250000 in a fixed deposit offering 9.50% per annum compounded semiannually for first 4 years. The maturity proceeds will be invested @ 8.5% p.a. compounded quarterly for next 5 years. The amount received at the end of 9th year will be invested in equity for remaining tenure to meet the corpus requirement, what return should be offered by equity if interest rate is compounded annually to satisfy his requirement? Expected inflation is 6% per annum. Sol STEP 2 STEP 3 STEP 1 SET : END SET : END SET : END N =4 N =5 N = 12 I = 9.5 I = 8.50 I =6 PV = -250000 PV = -362386.70 PV = -500000 FV = 362386.70 (SOL) FV = 551840.58 (SOL) FV = 1006098 (SOLVE) P/Y = 1 P/Y = 1 P/Y = 1 C/Y = 2 C/Y = 4 C/Y = 1 STEP 4 SET : END N =3 I = 22.16(SOLVE) PV = -551840.58 FV = 1006098 P/Y = 1 C/Y = 1 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 87

Q23. Vinay is planning to buy a car 8 years down the line costing him Rs.350000 in today’s term. Currently he has made the investment in a fixed deposit amounting Rs.250000 for a period of 3 years @ 9.75% per annum compounded monthly. For how long he should invest the maturity proceeds @ 11% per annum compounded monthly to achieve his goal? (Assuming inflation rate to be 5% per annum.) Sol. STEP 2 STEP 3 STEP 1 SET : END SET : END SET : END N =3 N = 3.97 (SOLVE) N =8 I = 9.75 I = 11 I =5 PV = -250000 PV = -334547 PV = -350000 FV = 334547 (SOL) FV = 517109.40 FV = 517109.40 (SOL) P/Y = 1 P/Y = 1 P/Y = 1 C/Y = 12 C/Y = 12 C/Y = 1 OR OR =FV(0.0975/12,36,0,-250000,1) =NPER(0.11/12,0,- 334547,517109.4,1) Q24. Mr. X aged 30, is planning a trip at his retirement which costs Rs.150000 in present term. He will retire at 58. The cost of trip is expected to rise by 5% per annum. Assuming he has made an investment of Rs. 50000 @12% per annum compounded quarterly, check whether he can take the trip at retirement or not? Sol. STEP 2 EXCEL STEP 1 SET : END STEP - 2 SET : END N = 20.84 (SOLVE) =NPER(0.12/4,0,- N = 28 50000,588019.37,1) I =5 I = 12 =83.384 Quarters =83.384/4 = 20.84 years PV = -150000 PV = -50000 FV = 588019.37 (SOLVE) FV = 588019.37 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 88

P/Y = 1 P/Y = 1 C/Y = 1 C/Y = 4 Financial Ratios Ratios help to go in deep insight analysis of the client’s financial health. These ratios provide information either predictive or diagnostic about the client’s financial situation. Ratios provide information about the following 4 aspects of the client’s financial situation. • Liquidity • Debt • Risk Exposure • Net Worth From Personal Financial Planning point of view the following ratios are important to study: • Basic liquidity ratio • Expanded liquidity ratio • Life Insurance Coverage Ratio • Tax burden ratio Liquidity Ratios Liquidity ratios are useful in interpreting an individual’s ability to handle financial needs when faced with a decline in income, or to take advantage of a financial opportunity that may suddenly appear. To measure a liquidity following two information/ financial variables of the client are required: 1. Liquid Assets: Cash or cash equivalent, Cheque on hand, Bank balance, Money Market Fund etc. 2. Monthly expenses: Interest expenses, Living expenses, Real estate tax, but not Income tax, TDS etc. Types of Liquidity Ratios: 1) Basic Liquidity Ratio: This ratio indicates how long can a person survive or sustain his monthly expenses with his present liquid assets. A basic liquidity ratio can be derived from the following equation: Basic Liquidity Ratio = Liquid Assets/ Monthly Expenses CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 89

Liquid Assets include cash and Near Cash assets like cash in hand Mr. Manish has following information as on 31/03/2007 His value of total liquid assets Rs. 1,50,000 His total monthly expenses Rs. 35,000 So, Mr. Manish’s Basic Liquidity ratio will be Rs. 150000/35000 = 4.285 2) Expanded Liquidity Ratio = Liquid assets and other financial assets/ Monthly expenses Other Financial Assets are Fixed Deposit, Blue chip stocks, Bonds, Mutual Funds etc. (which are easily encashable). It is not advisable to take above assets in their current value. Fixed deposits are taken at 100%. Stocks, bonds, mutual funds have lower liquidity and volatility in their market value can decline their market value. Generally these are taken at 50% of their current value. DEBT Ratios Debtor owes money to others. Q of debts are credit card balance, Housing loans, EMI, Tax liability etc. Client’s financial position can be improved through the prudent use of debt financing. But often debt financing is used in an inappropriate manner and causes falling into debt trap. From adequate liquid assets debt can be repaid if necessity arises. Types of Debt Ratios: 1) Liquid Asset Coverage Ratio = Liquid Assets / Total Debt 2) Solvency Ratio = Liquid and other Financial Assets / Total Debt Other Financial Assets to be taken at Full Value for Solvency Ratio Life Insurance Coverage Ratio Life Insurance coverage ratio determines how the individual’s dependants are adequately provided for in case of death of the individual. It also determines the individual’s quantum of current cash flow which comes out from his salary. Life Insurance Coverage Ratio = (Net Worth + Death Benefits of Principal Wage earner)/ Salary of Principal Wage Earner Mr. Manish has following information as on 31/03/2018: His Net Worth Rs. 15,50,000 His Death Benefits Rs. 20,00,000 His Salary for the year Rs.6,00,000 So, Mr. Manish’s Life Insurance Coverage Ratio will be as follow: CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 90

Life Insurance Coverage Ratio = (15.50.000+20.00.0000)/6,00,000 = 5.92 Normally a ratio of 7 to 10 would indicate that beneficiaries of the main bread earner’s life insurance policy could support themselves for an extended period of time. But this ratio must be carefully understood as the liquidity of each person’s net worth is quite different. Q1. Mr. Sumit is 32 years old and is working in a multinational company. He is married and has two children aged 6 and 2.He gets a salary of Rs.36000 p.m. net of taxes His expenses are Rs.14000 p.m. Other than his living expenses, he pays an EMI ofRs.10032 p.m. for the housing loan of Rs.1200000 which he has taken recently. His sister is getting married in the month of September for which he wishes to spend an amount of Rs.2, 00,000.His investments are as follows: (Present Value) Rs. Residential House 25, 00,000 Mutual Fund Units 3, 00,000 Bank FD maturing this month 1, 80,000 Term Insurance Policy 20, 00,000 Cash at bank 50,000 Compute Life Insurance Coverage Ratio? SOL. Life Insurance coverage ratio = (Net worth + Death Benefit) / Salary of the principal wage earner Net Worth = Assets – Liabilities Assets = Residential House + Mutual Funds + Bank FD + Cash at bank Assets = 2500000 + 300000 + 180000 + 50000 = 3030000 Liabilities = 1200000 Net worth = 3030000 – 1200000 = 1830000 Life insurance coverage ratio = (1830000 + 2000000) / 432000 = 8.86 Q2. Mr. Kumar is 56 years old going to retire in 2 years time. He is getting a salary ofRs.62000 p.m. net of taxes and expenses are Rs.32000 p.m. He assumes that after retirement in the beginning of 2018, he will require only Rs, 22000 p.m. He will get a pension of Rs.19000. His salary is increasing @10%p.a. His other investments are as follows: Rs. Cash at Bank 122000 Fixed Deposit Maturity amount 750000 (Maturing in Jan, 2007) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 91

Retirement Benefits 2675000 Outstanding Loan amount on House 400000 Other Liabilities 420000 Mutual Funds (Equity) 1025000 Balanced Funds 520000 He has to spend Rs.1000000 on daughter’s marriage in 2009.On his outstanding loan he pays an EMI of Rs.18000 p.m. On retirement he has planned to invest Rs.1500000 in Senior Citizen Saving Scheme and submit form 15 G to avoid tax deduction at source. Calculate Net worth of Mr. Kumar? Sol.Net Worth= Assets-Liabilities Assets = 122000 + 750000 + 1025000 + 520000 = 2417000 Liabilities = 400000 + 420000 = 820000 Net worth = 2417000 – 820000 = 1597000 Q3. Sohan has following assets. Calculate his Expanded Liquidity Ratio. Cash In Hand : Rs. 42,000/- Cash at Bank : Rs. 50,000/- FD at Bank : Rs. 1,25,000/- Shares : Rs. 1,00,000/- Post Office MIS : Rs. 2,00,000/- Mutual Funds : Rs. 60,000/- Monthly expenses : Rs. 20,000/- SOL. Liquid Assets = Rs. (42,000 + 50,000) = 92,000 Other Financial Assets = Rs. (1,25,000 + 50,000 + 30,000) = 2,05,000 (Bank FD at 100 %, Shares and Mutual Funds at 50 %). Monthly Expenses = Rs. 20,000/- So, Expanded Liquidity Ratio = 92,000 + 2,05,000/20,000 Answer = 14.85 (Should be more than 6) Q4. Following are the income and expenditure details of Mr. Arun. Calculate the Savings Ratio. Gross Income 800,000 Social security contributions (EPF) 85,000 Taxes 70,000 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 92

Household expenses 250,000 EMIs (for the year) 220,000 PPF Contribution 60,000 LIC premium 20,000 Medical insurance 15,000 Mutual funds (mainly ELSS) 30,000 Sol: Savings Ratio = Take home pay/Income available for saving Take home pay = Income –taxes - social security contributions = 800000 - 70000 - 85000 = 645000 Income available for saving = Take home pay – household expenses – EMI = 645000 – 250000 – 220000 = 175000 Savings Ration = 175000/645000 = 27.13% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 93

Different types of Returns 1. Holding Period Return “It is the total returns inclusive of capital appreciation and regular income in the form of rent, dividends etc. right from the point when investor buys the assets till the point of sale or calculating return.” HPR =(Ending Price - Beginning Price + Cash Dividend)/ Beginning Price Where, Ending Price = E1 Beginning Price = B0 Cash Dividend / Interest = D Ex1. Swami Krishnamurthy bought a house five years ago for Rs.1500000. Today the house is worth Rs.2250,000. What is the Holding Period Return? Sol. We have, E = Rs.2250000 B = Rs.1500000 D=0 By applying the formula we get, (2250000-1500000)/1500000 = 0.50 or 50% Ex. 2 Suppose by selling an investment for Rs.1500 a person yield 60% returns during “n” years. During the period he also earns Rs.100 as the interest. What amount has the person invested? Sol. We have, E= Rs.1500 B=? D= Rs.100 HPR = 60% by applying the formula we get, (1500- B0) +100 = 0.6 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 94

B= Rs.1000 Ex.3A building that is held for 8 months, during which time it generates Rs.24000 in rent a income (in excess of costs), and then sold for an Rs.30000 profit. Its original purchase price was Rs.250000. Calculate the holding period return? Sol. we have, E= 250000+30000= Rs.280000 B= Rs.250000 D= 24000 HPR =? By applying the formula we get, [(280000- 250000) + 24000]/250000 = 21.6% Ex4. What will be the holding period return of a 30 years bond paying an annual coupon of Rs. 80 and selling at face value (Rs. 1000)? Assuming the bond holder sells the bond after a period of one year at par value. Sol. We have, End Value = Rs.1000 Begin Value = Rs.1000 Interest = Rs.80 HPR =? By applying the formula we get, [(1000 + 80) – 1000]/1000 = 0.08 or 8% 2. Compounded annual growth rate (CAGR) Compounded Annual Growth Rate is the year - over - year growth rate of an investment over a specified period of time. The compound annual growth rate is calculated by taking the Nth root of the total percentage growth rate, where n is the number of years in the period being considered. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 95

Ex.1 If the annual growth rates of a factory in 5 years are 3.5%, 5.4%, 6.8%, 7.3% and6.5% respectively, then the compounded growth of output per annum for the given period is Sol. We know that, CAGR = *(1+R1)(1+R2)(1+R3)…………..(1+RN)+1/5-1 = [(1.035)(1.054)(1.068)(1.073)(1.065)]1/5 – 1 = 5.89% Ex.2 Sharma made an investment of Rs.100000/- in a listed company 4 years ago. Calculate the compounded annual rate of return if Sharma sells his investment for Rs. 560000/- Sol. We know that, CAGR = (ending value /starting value)1/n – 1 = (560000/100000)1/4– 1 = 53.83% Ex.3 Rs.1,00,000 invested 5 years ago is now Rs.6 lakh, what is the compounded annual growth rate? Sol. We know that, CAGR = (ending value /starting value) 1/n – 1 = (600000/100000)1/5 – 1 = 43% EX.4 As a college student thirty years ago, Amit purchased Rs.5000 shares of Wall Chand agar Industries. He recently sold the stock for Rs.105000. During his holding period, he received a total of Rs.12000 as cash dividends. Both his original and selling commissions were Rs.50 each. Calculate the CAGR. Sol. CAGR = [(105000 + 12000 - 50) / 5000 + 50]1/30- 1 x 100 = (23.158)1/30 - 1 x 100 = 11.04% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 96

EX.5 Calculate CAGR from the data given below On 1st Jan 2015, I started my investment portfolio with Rs.10000 On 1st Jan 2016, I made a loss, so my portfolio drops to Rs.8000 On 1st Jan 2017, I recouped my loss somewhat and my portfolio stands at Rs.9500 On 1st Jan 2018, my portfolio ends at Rs.12000 Sol We know that, CAGR = (ending value /starting value) 1/n – 1 = (12000/10000)1/3– 1 = 6.26% 3. Inflation adjusted rate of return Inflation Adjusted Rate of Return: Inflation adjusted rate of return is a measure that accounts for the return period’s inflation rate. Inflation adjusted returns reveals the return on an investment after removing the effects of inflation. It is calculated as follows: Inflation Adjusted Return ={(1 + Return)/(1 + Inflation Rate)– 1} * 100 Or We can use this formula: RRR = (return – inflation)/(1+ inflation ) 4. Tax adjusted rate of return Tax adjusted rate of return is more appropriate return as it takes in to consideration tax. Tax adjusted roi = ROI*(1-TAX RATE) Ex. Mr. Sharma has got return of 10% p.a. on his 1 year FD and he falls in 30% tax bracket. Calculate tax adjusted return? Sol. Page 97 Tax adjusted roi = ROI*(1-TAX RATE) =10*(1-30%) ( ignoring cess) =7% p.a. CFP Level 1 - Module 1 - Personal Financial Management - Workbook

5. Tax and Inflation adjusted return If we want to know the minimum return which can adjust inflation and tax, this return fulfill the purpose. Formula used to calculate the required rate for maintaining same level of investment taking the effect of tax and inflation: = {(Inflation)/(1 - tax rate)} x 100 Ex1. What rate of return is required to maintain the same level of investment if the inflation rate is 7.14% and the tax rate is 30%. Sol Required rate to maintaining the same level of investment =( Inflation/1-tax rate) * = 0.0714/(1-0.30) x 100 = 0.1020 x 100 = 10.20% Or We can solve in the following way 7.14/(1-0.30) = 7.14/.7 = 10.2% Ex2. What rate of return is required to maintain the same level of investment if the inflation rate is 6% and the tax rate is 20%. Sol. Required rate to maintaining the same level of investment = 6 / (1-0.20) = 7.5% 6. Internal Rate of Return The internal rate of return (IRR) is a capital budgeting metric used by firms to decide whether they should make investments. The IRR is the annualized effective compounded return rate which can be earned on the invested capital, i.e., the yield on the investment. A project is a good investment proposition if its IRR is greater than the rate of return that could be earned by alternate investments (investing in other projects, buying bonds, even putting the money in a bank account). Thus, the IRR should be compared to any alternate costs of CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 98

capital including an appropriate risk premium. In general, if the IRR is greater than the project’s cost of capital, or hurdle rate, the project will add value for the company. IRRs can also be compared against prevailing rates of return in the securities market. If a firm can’t find any projects with IRRs greater than the returns that can be generated in the financial markets, it may simply choose to invest its retained earnings into the market. Q1. If you invest Rs.250 today then receive the following cash flows: At the end of year 1 Rs.97 At the end of year 2 Rs.65 At the end of year 3 Rs.99 At the end of year 4 Rs.152 What is the IRR? Sol 1 Step 1: Press cash button Step 2: Go to cash editor and press execute (EXE) Step 3: Insert cash inflows and outflows as shown below. Keep in mind cash outflows will be negative 1 = -250 2 = 97 3 = 65 4 = 99 5 = 152 Press ESC and solve IRR = 21.26% Q2. Determine the internal rate of return for a project that costs Rs.188, 419 and is expected to yield after-tax cash flows of Rs.29,000 per year for the first five years,Rs.37,000 per year for the next five years and Rs.50,000 per year for the following five years. Sol. Step 1: Press cash button Step 2: Go to cash editor and press execute (EXE) Step 3: Insert cash inflows and outflows as shown below. Keep in mind cash outflows willbe negative 1 = -188419 2 = 29000 3 = 29000 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 99

4 = 29000 5 = 29000 6 = 29000 7 = 37000 8 = 37000 9 = 37000 10 = 37000 11 = 37000 12 = 50000 13 = 50000 14 = 50000 15 = 50000 16 = 50000 Press ESC and solve IRR = 16.13% Q3. Determine the internal rate of return for a project that costs Rs.98,000 and wouldyield after-tax cash flows of Rs.2,000 for the first year, Rs.4,000 for the second year, Rs. 17,000 for the third year, Rs. 39,000 for the fourth year, Rs. 23,000 for the fifth year, and Rs. 29,000 the sixth year. Sol 1 = - 98,000 2 = 2000 3 = 4000 4 = 17000 5 = 39000 6 = 23000 7 = 29000 Press ESC and solve IRR = 3.48% 7. Arithmetic Return and Geometric Return Arithmetic Return “An average of the sub period returns, calculated by summing the sub period returns and dividing by the number of sub periods” Formula used, Arithmetic Return = (R1 + R2 + R3 + ……..Rn) / n where CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 100