ITFP WORKBOOK - PART 1

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 =7,96,779.85 Pm2=300 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 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 Page 51

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? 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 PM2 = 96 Solve for INT = 2155787.381 Page 52

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 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 Page 53

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 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 Page 54

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 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 Page 55

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 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 Page 56

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 0 150K 250K 150K 1550K. 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? 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 Page 57

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 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 Page 58

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% 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 89 -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 Page 59

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 =NPV(0.09,select 1 to 4 ) 3 = 20000 =139993.52 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=5000 4 = 7,500 5=2500 5 = 5,000 6=0 6 = 2,500 7=12500 7 =0 =NPV(0.09,select 1 to 7) 8 = 12,500 =37,681 NPV = SOLVE= 37,681 In cash function 1 is called beginning of But in excel while calculating NPV, 1 is first year. called end of 1st year. Page 60

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 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. Page 61

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. 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. Page 62

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 = 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? Sol. EXCEL RRR = {(1+R)/(1+I)}^(1/N)-1 = (1.08/1.03)^(1/12)-1 = 0.003958 Q15. 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 Page 63

CNVR = 12 EXCEL N = 10 =EFFECT(0.10,12) I =10.471% =0.10471 EFF 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) PV = 0 =-28985 FV = 1,37,95,175 PMT = Solve (-28,985) P/Y = 12 C/Y = 1 Page 64

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. 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? Page 65

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 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? Page 66

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 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 Page 67

STEP 3 STEP 4 SET: END/BEGIN SET: END/BEGIN N = 18 N =9 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 Page 68

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 Page 69

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,-50000,588019.37,1) N = 28 I = 12 =83.384 Quarters I =5 PV = -50000 =83.384/4 = 20.84 years PV = -150000 FV = 588019.37 FV = 588019.37 (SOLVE) P/Y = 1 P/Y = 1 C/Y = 4 C/Y = 1 Page 70

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 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. Page 71

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: 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 Page 72

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) 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/- Page 73

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 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% Page 74

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 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 =? Page 75

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. 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% Page 76

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% 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? Page 77

Sol. Tax adjusted roi = ROI*(1-TAX RATE) =10*(1-30%) ( ignoring cess) =7% p.a. 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) * 100 = 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 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. Page 78

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 4 = 29000 5 = 29000 6 = 29000 7 = 37000 8 = 37000 9 = 37000 10 = 37000 11 = 37000 12 = 50000 13 = 50000 Page 79

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 R1 + R2 + R3 + ……..Rn are the returns for the different periods n = The no of periods over which returns are calculated Geometric Return The geometric return measures the compound growth overtime. The geometric mean of a collection of data is defined as the nth root of the product of all the members of the data set, where n is the number of members. G.M. = [(1 + TR1) (1 + TR2) ................. (1 + TRn)]1/n- 1 where TR is a series of total returns in decimal form. By adding 1.0 to each total return produces a return relative. Return relatives are used in calculating geometric mean returns because TRs which can be negative cannot be used. Page 80

Q1. What is the arithmetic mean for the following returns? Year 1 5% Year 2 -3% Year 3 12% Sol. Arithmetic mean = (5-3+12)/3 = 4.67% Q2. What is the geometric mean for the following returns? Year 1 5% Year 2 -3% Year 3 12% Sol. Geometric mean = [(1.05)(0.97)(1.12)]1/3-1 = 0.04486 OR 4.49% Q3. The arithmetic return of 12%, 14% and 16% is _________. Sol. Arithmetic return = (12+14+16)/3 = 14% Q4. The geometric return of 10%, 18% and 24% is __________. Sol. Geometric return = [(1+0.10)(1+0.18)(1+0.24)]1/3-1 = 0.1719 or 17.19% Page 81