PFM - WORKBOOK

PERSONAL FINANCIAL MANAGEMENT WORKBOOK CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 1

CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 2

RULE OF TIME VALUE OF MONEY USING FV 200V 1. CMPD : a. We can set our calculator either BEGIN mode or END mode when we don’t use PMT. We can say that in case of using n, I, PV, FV, C/Y we can set out calculator either BEGIN or END mode. b. N means number of periods in normal case without considering PMT. For ex. Mr. invested Rs.100 for 10 years 6 months, then N would be 10+6/12 c. I means rate of interest. d. PV means present value. For ex. I invest Rs.20000 for 10 years. Here PV is 20000. PV means lump sum payment. Present value can be negative or positive as per situation. e. PMT means regular payment. For ex. Saving Rs.2000 p.a. for 10 years, therefore 2000 is PMT. It can be positive or negative f. FV means future value. For ex. If I receive Rs. 1 crore after 10 years. Here FV is 1 crore. FV is always lump sum payment received or paid after some periods. g. P/Y means number of payments in a year. For ex. I invest Rs.1000 p.m. for 12 years. Here P/Y is 12 h. C/Y means how many compounding in a year. For ex. rate of interest 12% p.a. compounding monthly therefore C/Y=12. 2. When money comes in (receiving or cash inflows) consider positive sign, when money goes out (investing or cash outflows) consider negative sign. 3. When there is role of regular payment means PMT in a step, following points should be kept in mind: a. We should always consider set BEGIN or END as per the question. b. If nothing mentioned about regular saving whether in the beginning or end of every period, We always consider BEGIN, reason in all schemes we have to deposit money in advance. c. During post retirement life if it is a case of deferred annuity, if nothing mentioned about the withdrawal of money (begin or end). We should consider always BEGIN as we need money immediately after retirement but in immediate annuity we consider END. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 3

d. In case of loan if nothing mentioned about repayment whether is made in the beginning or end of every period, we should consider END as logically first we get money then very next period we make repayment. e. N means total number of payments. Ex1. Mr. Sharma saves or withdraws Rs.2000 p.m. for 10 years. Here N is 10*12 = 120 Ex2. Mr. X saves or withdraws Rs.5000 per quarter for 10 years. Here N is 10*4 = 40 f. P/Y means total number of payments made in a year. Ex1. Mr. X saves or withdraws 2000 pm for 10 years, here P/Y=12 but N=10*12=120. (As n means total numbers of payments made) Ex2. Mr. X saves Rs.2000 per quarter for 15 years. Calculate future value if ROI 10% p.a. compounding half yearly. First we should check whether there is role of regular payment in this question. If yes we should consider first of all set begin or end Here we will consider set=begin (as if nothing mentioned saving in the beginning or end we always consider BEGIN) N=15*4=60 (as N is total number of payments are made in that period). I=10 PV=0 (as there is no lump sum payment) PMT= -2000 P/Y=4 (total number of payment in a year) C/Y=2 (total number of compounding in a year) FV=solve=275680.6996 4. If we need to calculate the present value of regular payment which is increasing by inflation or growth like in salary, we should always use real rate of return, otherwise generally we never use RRR. For Ex. Mr. Sharma needs Rs.5000 p.a. in the beginning of every year inflation adjusted for 30 years. Calculate how much amount he should invest today if rate of interest is 12% p.a. and inflation 10% p.a. SET=BEGIN N =30 I =(12-10)/1.10 PMT =5000 PV =solve=116921.050 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 4

In case of salary we can calculate the net present value of all future income. We can solve it by using growing annuity formula also. First we can calculate the future value using growing annuity formula and then discount it by 12% for 30 years. But better to use RRR. 5. We never use real rate of return when we invest money for future value. 6. We use inflation when cost of a goal ( Household Expenses, Car, Education, House, Marriage, World Tour Etc) is given in today’s term ( present cost ) and we want to find cost of the same in future. Following examples will help you to comprehend this: i. Current cost of house hold expenses Rs.1 lac p.a., inflation 6% p.a. if you calculate cost of HHE p.a. after 30 years, we have to inflate it for 30 years considering it as PV. As we need to know HHE annually we are not adding all expenses in this question therefore can’t consider it as PMT. Step to solve: Set = end/begin N =30 I =6 PV = 100000 FV = solve or we can use formulae FV = PV(1+r)^n j. Current cost of house hold expenses Rs.50000 p.m. inflation 7% p.a. if you want to know your monthly house hold expenses after 25 years, you simply inflate it by 7% for 25 years. Step to solve : Set = end/begin N = 25 (don’t consider 25*12 as you need to know only monthly expenses after 25 years) I =7 PV = 50000 FV =solve or FV = 50000(1.07)25 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 5

In CMPD function if n and i in same unit, P/Y and C/Y must be 1. For ex. Ram saves Rs.2000 per month for 10 years in a scheme that generates 2% p.m. interest, calculate future value? CMPD Set = begin ( as nothing mentioned begin or end, we always consider begin ) N = 10*12 = 120 ( as total number of payments ) I =2 PMT = -2000 P/Y =C/Y=1 ( as n and I in same unit, same unit means both are in terms of months ) FV = ? 8. CASH FUNCTION: a. Cash function is always better to use in cases where payments are not constant. b. In cash editor 1 means beginning of first period ( month or year), 2 means beginning of second period or end of 1st year. c. Whenever we calculate future value, we need to take care of last entry. For example Mr. X saves 2000 today and 3000 next year and calculating future value after 2 years. We put 2000 in first entry 3000 in second entry and third entry must be zero as 3rd entry is end of 2 years or beginning of 3rd year. d. When we calculate future value after 10 years or 15 years , 11th entry or 16th entry must be utilized as 11th entry means end of 10th and 16th entry means end of 15th. e. We can use RRR to calculate the net present value of payments which are increasing by some rate. Following examples will help you to comprehend the same: Ex. Current cost of higher education 5lacs p.a. for first 2 years and Rs3 lacs for next 3 years. Inflation 8% p.a. and rate of interest 12% p.a. what is the net present cost of education? i. Case 1 : Higher education starts now. Solution by using cash function: I = (12-8)/1.08 1 = 500000 2 = 500000 3 = 300000 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 6

4 = 300000 5 = 300000 NPV = solve ii. Case 2 if higher education starts after 15 years. Solution by using cash function: I = (12-8)/1.08 1 to 15 entries = 0 16 = 500000 17 = 500000 18 = 300000 19 = 300000 20 = 300000 NPV = solve f. Internal rate of return i.e. IRR is used to calculate the rate of interest of uneven cash inflows and outflows. Following examples will help you to comprehend the same: Ex. 1 If I invest Rs.2000 today and receive Rs.1200 after 1 year, Rs.600 after 2 years, Rs.500 after 4 years. Calculate rate of interest (IRR or CAGR)? Sol. We cannot use CMPD. We have to use CASH FUNCTION 1 = -2000 2 = 1200 3 = 600 4 =0 5 = 500 (as 5th entry means end of 4th or beginning of 5th) IRR = Solve Ex. 2 There is a scheme in which Rs.100000 p.a. to be invested for first 5 years and inflows 1 lac p.a. will start from the end of 10th year (beginning of 11th year) for 10 years. Now in this case you need to calculate the rate of interest (IRR OR CAGR). Sol. We can solve it by using CASH FUNCTION not CMPD CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 7

1 to 5 entries = -100000 6 to 10 entries =0 11 to 20 = 100000 IRR = Solve CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 8

TIME VALUE OF MONEY Calculation of Future Value Future value measures the nominal future of money that a given sum of money is “worth” at a specified time in the future assuming a certain interest rate. Future Value = Present value (1+r)n Q1. Calculate the maturity amount of Rs. 1,18,000 if invested at 8% per annum for 5 years. SOL. Set = Begin EXCEL N =5 =FV(0.08,5,0,-118000,1) I =8% =173380.71 PV = - 118,000 FV =SOLVE =173380.71 Or FV = 118000*1.08^5 Q2. You have just inherited Rs.250,000 and plans to deposit it immediately in a bank account that pays 10% interest per year compounded monthly. Calculate the maturity value after 10 years? SOL. Set = Begin EXCEL N =10 =F V(0.10/12,10*12,0,-250000,1) I = 10% =676760.37 PV = - 250,000 P/Y = 1 C/Y = 12(as number of compounding in a year is 12) FV =SOLVE = 676,760.37 Q3. Mr. Manish invested Rs. 10,000 in a mutual fund scheme. During the first year the investment earned 10% p.a. for the year. During the second year, he earned only 14% p.a. on his investment. Calculate the amount that will accumulate in his account at the end of two years? CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 9

Sol. Step1: Begin, Step 2: Begin N = 1, N = 1, I = 10 PV = - 10,000 I = 14 FV = SOLVE = 11,000 PV = -11,000 FV = SOLVE = 12,540 EXCEL STEP 2 STEP1 =FV(0.14,1,0,-11000,1) =FV(0.10,1,0,-10000,1) =12540 =11000 Q4. You have deposited Rs.2500 in an account that promises to pay 8% p.a. compounded quarterly for the next five years. How much money will you have in the account at the maturity? Sol. Begin, EXCEL N =5 =FV(.08/4,5*4,0,-2500,1) I = 8% =3714.86 PV = -2500 C/Y = 4 FV = 3714.86 solve Q5. Mr. A aged 35, has Rs.30,000 to be invested today for next 25 years. He plans to retire at the age of 60 years. What would be the retirement corpus he can arrange through his investment if return on investment is 10% p.a. compounded quarterly for first 8 years and then decreases to 8.75% per annum for 7 years and again increases to 9.25% per annum thereafter? Sol. Step 1:Begin Step 2 Begin Step 3 Begin =7 N = 10 N = 8, N = 8.75% I = 9.25% = -66,112.70 PV = -118,929.55 I = 10%, I = 1, C/Y = 1 = SOLVE = 118,929.55 FV = SLOVE = PV = - 30,000 PV C/Y = 4 C/Y FV =SOLVE = 66,112.70 FV 2,88,074.13 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 10

EXCEL STEP-1 STEP – 2 STEP-3 =FV(0.10/4,8*4,0,-30000) =FV(0.0875,7,0,-66112.70,1) =FV(0.0925,10,0,- 118929.55,1) =66112.70 =118929.55 =288074.13 Q6. Mr. Rajesh has deposited today Rs.2 lacs in debt instruments at the rate of interest 10% per annum for around 3 years and further invested this amount in equity stocks for 8 years giving 12% per annum compounding monthly. How much amount will be accumulated by the end of 11 years? Step 1: Begin, Step 2: Begin N =3 N =8 I = 10% I = 12% PV = -200,000 PV = -266,200 FV = 266,200 Solve C/Y = 12 FV = 691,926.45 Solve CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 11

CALCULATION OF PRESENT VALUE Present value is the value on a given date of future payment or series of future payments, discounted to reflect the time value of money and other factors such as investment risk. PV= Future Amount/(1+interest Rate)^term Q1. How much must be invested today, at 9% p.a., to accumulate Rs.100000 in seven years from today? Sol. Set: End/begin, EXCEL N = 7, =PV(0.09,7,0,100000,1) I =9 =-54703.42 PV = Solve= -54703.42, FV = 100000 OR PV = 1,00,000/1.09^7 Q2. How much should be invested to receive Rs.200000 after five years from now assuming rate of interest is 8.5% p.a.? Sol. Set: End/Begin, EXCEL N = 5, =PV(0.085,5,0,200000,1) I = 8.5, =-133009.08 PV = Solve = -133009.08, FV = 200000 Q3. Sheetal wishes to earn a 11% p.a. compounding quarterly return on an investment she is considering. She receives Rs.25000 four years from today. How much she should be willing to invest today? Sol. Set: Begin/end, EXCEL N = 4, =PV(0.11/4,4*4,0,25000,1) I = 11%, = -16196.85 C/Y = 4, CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 12

FV = 25,000. PV = -16,196.85 Solve Q4. Megha bought a house this morning for Rs.120000. If this house has increased in value by 11% per year for the last 50 years then what was the value of the house 12 years ago? Sol. Set : begin/end EXCEL N = 12, =PV(0.11,12,0,120000,1) I = 11%, =-34300.89 FV = 120,000. PV = -34,300.89 Solve Q5. Geeta has got approximately Rs. 1250000 after 10 years from the redemption of mutual fund on which she has got 9.25% compounded quarterly for first 3 years, 8.25% per annum for the next 4 years and 8% per annum compounded semi-annually for remaining years. Estimate the amount she would have deposited 10 years ago? SOL. In this question we need to solve from back. We can say discounting. Step 1 Step 2 Step 3 Set: Begin/End, Set: Begin/End Set: Begin/End N =3 N =4 N = 3, I = 8% I = 8.25% I = 9.25% C/Y = 2 C/Y = 1 C/Y = 4 FV = 12, 50,000. FV = 98783.15 FV = 719,446.25 PV = solve =-987893.15 PV = solve = -719,446.65 PV = solve = -542,832.42 EXCEL STEP – 1 STEP – 2 STEP - 3 =PV(0.08/2,3*2,0,1250000,1)=PV(0.0825,4,0, 98783.15,1) =PV(0.0925/4,3*4,0, 719,446.25 ,1) =-987893.15 =-719446.65 =-542832.42 Q6. An investor would require Rs.1000000 when she retires in 20 years. If she can earn a 10 percent annual return, how much she would need to invest today lump sum? Set: Begin/End N = 20 I = 10 FV = 10,00,000 PV = -148,643.62 Solve CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 13

P/Y = 1 C/Y = 1 CALCULATION OF INTEREST & TERM Interest The interest is calculated upon the value of the assets in the same manner as upon money. Interest can be thought of as “rent on money”. For Q, if you want to borrow money from the bank, there is a certain rate you have to pay according to how much you want loaned to you. Term Term is defined as a limited period of time, a point in time at which something ends, or a deadline, as for making a payment. Rule of 72: To estimate the number of periods required to know double an original investment. No. of years = 72/Rate of Interest or CAGR Ex. If interest rate is 8% p.a., how many years to get the investment double as per Rule 72 Years = 72/ROI = 72/8 = 9 years. Rule of 69: this rule also tells you in how many periods (years ) will take to double your investment. 69/ interest rate + 0.35 = years For Q, if interest rate is 10%, the investment will double in how many years as per rule 69. 69/10 + 0.35 = 7.25 years Rule of Tripling of Investments Rule of 114: To estimate how long it takes to triple your money, divide 114 by interest rate (CAGR) 114 / interest rate = years For Q : how many years an investment will take to get tripled in interest rate is 10% p.a. 114/10 = 11.4 years Q1. I invested Rs.50000 in a fund offering ROI of 12% p.a. approximately how many years will it take to reach Rs.100000 as per Rule 72 ? Sol. 72/12 = 6 Years CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 14

Q2. Mr. Sharma deposits Rs.10000 in an investment. Ten years later it is worth Rs.17910.What rate of return did the investor earned on the investment? sol. Set : End/begin, EXCEL N = 10, = RATE(10,0,-10000,17910,1) I = Solve = 6.0, =6% PV = -10000, FV = 17910 Q3. What annual rate of interest is the bank charging, if you borrow Rs.1 lacs and repay Rs.120000 at the end of 4 years and a quarter? Sol. Set: End/Begin, =RATE(4+1/4,0,100000,-120000,1) N = 4+1/4, =4.38% I = Solve = 4.38, PV = -100000 FV =120000 Q4. If Rs.1200 grows to Rs.1500 in 5 years, what is the annual compound rate of interest? Sol. Set: End/Begin N =5 I = Solve = 4.56 PV = -1200 FV = 1500 Q5. If I invest Rs.100 today in a scheme that generates 10% p.a. interest rate and receive Rs.300 after 10 years, what annual rate of interest I receive in this plan? Sol. Set: End/Begin, N = 10 I = solve = 11.61% PV = -100 FV = 300 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 15

Q6. How many years it will take Rs.10000 to become 15000 if rate of interest under the scheme is 8% p.a.? Sol. Set: End/Begin, EXCEL I =8% =NPER(0.08,0,-10000,15000,1) PV = --10000 =5.268 YEAR FV = 15000 N = SOLVE = 5.268 YEAR Q7. Mr. Sharma wants to get his daughter married which cost Rs.20 lakhs, if he invest Rs.8 lakhs today in a Mutual fund scheme which generate 12% p.a. comp monthly return. When he can get his daughter Married? Sol. Set: End/Begin, EXCEL I =12% =NPER(0.12/12,0,-800000,2000000,1) PV = -800000 = 92.08646 months as rate is 1% p.m. FV = 2000000 =92.08646/12 C/Y = 12 =7.67387 YEARS N = SOLVE =7.67387 YEARS CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 16

Nominal and Effective rate of return Nominal Interest Rate: The nominal interest rate is the periodic interest rate times the number of periods per year; for Question, a nominal annual interest rate of 12% based on monthly compounding means a 1% interest rate per month. For example: 12% p.a. compounding monthly 16% p.a. compounding half yearly If there is more than one compounding in given period or interest it is called nominal rate of interest. In above examples number of compounding in a year 12 and 2 respectively. For easy to understand we can say that nominal rate of interest is difficult to understand for a layman. Effective Interest Rate: The actual interest rate that accrues, after taking into consideration the effects of compounding The effective interest rate is always calculated as if compounded annually. People can understand effective rate of interest easily. For example 12% p.a. 15% p.a. When there is one compounding in a period or interest mentioned. In above example in a year only one compounding. Some more examples: 2% p.m. it is also effective rate of interest but we will say monthly effective. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 17

CONVERSION OF INTEREST RATE Use of Financial Calculator 200V CNVR, i.e. conversion mode is used for converting rate of interest When we press CNVR in our calculator we get 4 options: 1. N = 0, 2. I% = 0, 3. EFF : 4. APR: Where N is the total number of compounding I is the interest rate EFF is the effective rate of interest APR is the nominal rate of interest A. CONVERSION FROM NOMINAL RATE OF INTEREST INTO EFFECTIVE RATE OF INTEREST The effective rate is calculated in the following way, where r is the effective rate, i is the nominal rate and n is the number of compounding periods per year r = (1 + i / n)n– 1 Ex1. What is the effective rate of interest for 12%p.acompounded monthly, quarterly, semiannually? Sol. Press = CNVR Press = Press = CNVR CNVR N =4 N =2 N = 12 I = 12 I = 12 I = 12 EFF = Solve = 12.55 EFF = Solve = 12.36 EFF = Solve = 12.68 =EFFECT(0.12,12) EXCEL =EFFECT(0.12,2) =12.68% =EFFECT(0.12,4) =12.36% =12.55% OR CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 18

=(1+12%/12)^12 -1 =(1+12%/4)^4 -1 =(1+12%/2)^2 -1 Ex2. What is the effective annual yield of an investment paying at a 10% p.a. compounded quarterly? Sol. =EFFECT(0.10,4) Press = CNVR =10.38% N =4 OR I = 10 EFF = Solve = 10.38 =(1+10%/4)^4 -1 =10.38% Ex.3. Convert the following nominal rate of interest into effective rate of interest: A, 15% p.a. compounding monthly B. 20% p.a. compounding quarterly C. 36% p.a. compounding half yearly D. 300% p.a. compounding daily E. 50% p.a. compounding weekly Sol. A. EXCEL Sol. =EFFECT(0.10,12) Press = CNVR =16.075% N = 12 OR I = 15 =(1+15%/12)^12 -1 EFF = Solve = 16.075 =16.075% B. EXCEL Sol. =EFFECT(0.20,4) Press = CNVR N =4 =21.55% I = 20 OR EFF = Solve = 21.55 =(1+20%/4)^4 -1 =21.55% C. EXCEL Sol. =EFFECT(0.36,2) Press = CNVR N =2 =39.24% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 19

I = 36 OR EFF = Solve = 39.24 =(1+36%/2)^2 -1 =39.24% D. EXCEL Sol. =EFFECT(3,365) Press = CNVR N = 365 =1884.0759% I = 300 OR EFF = Solve = 1884.0759 =(1+300%/365)^365 -1 E. =1884.0759% Sol. Press = CNVR EXCEL N = 52 =EFFECT(0.50,52) I = 50 EFF = Solve = 64.4788 =64.4788% OR =(1+50%/52)^52 -1 =64.4788% A. CONVERSION FROM EFFECTIVE RATE OF INTEREST INTO NOMINAL RATE OF INTEREST Ex1. 15% p.a. return is equal to ………p.a. compounded monthly Sol. Press = CNVR EXCEL N = 12 =NOMINAL(0.15,12) I = 15 =14.057% APR = Solve = 14.057% Ex2. 12% p.a. return is equal to ………p.a. compounded quarterly Sol. Press = CNVR EXCEL N =4 =NOMINAL(0.12,4) I = 12 =11.4949% APR = Solve = 11.4949% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 20

Ex3. 24% p.a. return is equal to ………p.a. compounded half yearly Sol. Press = CNVR EXCEL N =2 =NOMINAL(0.24,2) I = 24 =22.71% APR = Solve = 22.71% Ex4. 18% p.a. return is equal to ………p.a. compounded weekly Sol. Press = CNVR EXCEL N = 52 =NOMINAL(0.18,52) I = 18 =16.5778% APR = Solve = 16.5778% Ex5. 8% p.a. return is equal to ………p.a. compounded daily Sol. Press = CNVR EXCEL N = 365 =NOMINAL(0.08,365) I =8 =7.6969% APR = Solve = 7.6969% B. CONVERSION FROM EFFECTIVE RATE OF INTEREST INTO EFFECTIVE RATE OF INTEREST 1.. In this section we are going to change effective annual rate of interest into monthly, quarterly, half yearly effective. There is a formula which can be used in excel sheet to convert effective to effective rate of interest Effective roi in smaller unit = (1+r%)^(1/n) -1 Ex1. Convert 12% p.a. into monthly effective? Sol. First we need to convert 12% p.a. into nominal i.e. ____% p.a. comp monthly For this Press = CNVR EXCEL N = 12 =NOMINAL(0.12,12) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 21

I = 12 =11.38655% APR = Solve = 11.38655% Now we can write 12% p.a. = 11.38655% p.a. comp monthly. Finally if we divide 11.38655% by 12 we can get ….% p.m. 11.38655%/12 = 0.948879% p.m. OR Effective roi in smaller unit = (1+r%)^(1/n) -1 =(1.12)^(1/12)-1 = 0.009489 =0.9489% p.m. Ex2. Convert 18% p.a. into monthly effective? Sol. First we need to convert 18% p.a. into nominal i.e. ____% p.a. comp monthly For this Press = CNVR EXCEL N = 12 =NOMINAL(0.18,12) I = 18 =16.666% APR = Solve = 16.666% Now we can write 18% p.a. = 16.666%p.a. comp monthly. Finally if we divide 16.666% by 12 we can get ….% p.m. 16.666%12 = 1.3888% p.m. OR Effective roi in smaller unit = (1+r%)^(1/n) -1 =(1.18)^(1/12)-1 = 0.013888 =1.3888% p.m. Ex3. Convert 12% p.a. into quarterly effective? Sol. First we need to convert 12% p.a. into nominal i.e. ____% p.a. comp quarterly For this Press = CNVR EXCEL N =4 =NOMINAL(0.12,4) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 22

I = 12 =11.4949% APR = Solve = 11.4949% Now we can write 12% p.a. = 11.4949% p.a. comp quarterly. Finally if we divide 11.4949%by 4 we can get ….% per quarter 11.4949%/4 = 2.8737% per quarter OR Effective roi in smaller unit = (1+r%)^(1/n) -1 =(1.12)^(1/4)-1 = 0.028737 =2.8737% per quarter Ex4. Convert 20% p.a. into half-yearly effective? Sol. First we need to convert 20% p.a. into nominal i.e. ____% p.a. comp half-yearly For this Press = CNVR EXCEL N =2 =NOMINAL(0.20,2) I = 20 =19.089% APR = Solve = 19.089% Now we can write 20% p.a. = 19.089% p.a. comp half-yearly. Finally if we divide 19.089% by 2 we can get ….% half-yearly 19.089%/2 = 9.5445% per half-year OR Effective roi in smaller unit = (1+r%)^(1/n) -1 =(1.20)^(1/2)-1 = 0.095445 =09.5445% per half-year Ex5. Convert 15% p.a. into daily effective? Sol. First we need to convert 15% p.a. into nominal i.e. ____% p.a. comp daily For this CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 23

Press = CNVR EXCEL N = 365 =NOMINAL (0.15,365) I = 15 =13.97887% APR = Solve = 13.97887% Now we can write 15% p.a. = 13.97887% p.a. comp daily. Finally if we divide 13.97887% by 365 we can get ….% daily. 13.97887%/365 = 0.038298% daily OR Effective roi in smaller unit = (1+r%)^(1/n) -1 =(1.15)^(1/365)-1 = 0.00038298 =0.038298% daily 2.. In this section we are going to change effective monthly or quarterly or half-yearly rate of interest into annual effective. There is a formula which can be used in excel sheet to convert effective to effective rate of interest Effective roi from smaller unit to bigger unit = (1+r%)^(n) -1 Ex1. Convert 2% p.m. into annual effective? Sol. First we need to convert 2% p.m. into nominal i.e. ____% p.a. comp monthly It is very simple, we can write 2%*12 = 24% p.a. comp monthly Now we make it effective annually Press = CNVR EXCEL N = 12 =EFFECT(0.24,12) I = 24 =26.824% EFF = Solve = 26.824% OR Effective roi from smaller unit to bigger unit = (1+r%)^(n) -1 =(1.02)^(12)-1 = 0.26824 =26.824% p.a. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 24

Ex2. Convert 5% p.m. into annual effective? Sol. First we need to convert 5% p.m. into nominal i.e. ____% p.a. comp monthly It is very simple, we can write 5%*12 = 60% p.a. comp monthly Now we make it effective annually Press = CNVR EXCEL N = 12 =EFFECT(0.60,12) I = 60 =79.5856% EFF = Solve = 79.5856% OR Effective roi from smaller unit to bigger unit = (1+r%)^(n) -1 =(1.05)^(12)-1 = 0.795856 =79.5856% p.a. Ex1. Convert 2% p.m. into annual effective? Sol. First we need to convert 2% p.m. into nominal i.e. ____% p.a. comp monthly It is very simple, we can write 2%*12 = 24% p.a. comp monthly Now we make it effective annually Press = CNVR EXCEL N = 12 =EFFECT(0.24,12) I = 24 =26.824% EFF = Solve = 26.824% OR Effective roi from smaller unit to bigger unit = (1+r%)^(n) -1 =(1.02)^(12)-1 = 0.26824% =26.824% p.a. Ex1. Convert 2% p.m. into annual effective? Sol. First we need to convert 2% p.m. into nominal i.e. ____% p.a. comp monthly CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 25

It is very simple, we can write 2%*12 = 24% p.a. comp monthly Now we make it effective annually EXCEL Press = CNVR =EFFECT(0.24,12) N = 12 =26.824% I = 24 EFF = Solve = 26.824% OR Effective roi from smaller unit to bigger unit = (1+r%)^(n) -1 =(1.02)^(12)-1 = 0.26824% =26.824% p.a. EX4. If the continuous time return is 10%, find the annual return. Sol Effective rate of return = e0.10-1 = 10.52% Q4. If the continuous time return is 11.8% find the monthly return Sol. Effective rate of return = e0.118-1 = 12.51% Monthly return = (1.1251)1/12-1 = 0.99% Q5. If the continuous time return is 10.5% find the quarterly return Effective rate of return = e0.105-1 = 11.07% Quarterly return = (1.1107)1/4-1 = 2.66% Q6. If the nominal rate of interest is 8.5% and compounding is done half yearly, the effective rate of interest will be: CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 26

Sol Press: CNVR N =2 I = 8.5 EFF: Solve = 8.68 Q7. The difference between the effective rate of return of a bond with a coupon rate of10.75% when compounded weekly and semiannually is Sol Press: CNVR Press: CNVR N =2 N = 52 I = 10.75 I = 10.75 EFF : Solve = 11.34 EFF : Solve = 11.04 Difference = 11.34 - 11.04 = 0.30% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 27

Annuities Annuity: The term annuity is used in reference to any terminating stream of fixed annuity payments over a specified period of time. Examples of Annuity > Rent, Loan EMI’S, Pension, etc Types of annuities: 1. Ordinary Annuity/Annuity in Arrears An ordinary annuity is essentially a level stream of cash flows made at the end of each period for a fixed period of time. 2. Annuity Due An annuity due is essentially a level stream of cash flows made in the beginning of each period for a fixed period of time. 3. Deferred Annuity A type of annuity contract that delays payments of income, installments or a lumpsum until the investor elects to receive them. This type of annuity has two main phases, the Savings Phase in which you invest money into the account, and the income phase in which the plan is converted into an annuity and payments are received 4. Growing Annuity A growing annuity is a finite number of cash flows growing at a constant rate. Growing annuity is of two types: a. Growing Annuity by Fixed percentage b. Growing annuity by Fixed amount A. Growing Annuity by Fixed percentage: CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 28

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) (1+r) – (1+g) or FV = PMT [{(1+r)n – (1+g)n}]* (1+r) r-g Where, PMT= first payment g = Growth rate r = Interest Rate n = Number of payments The Formula of calculating future value of a growing annuity when payments are made at the end of every period is: FV = PMT,*(( )( ) +- )( ) or FV = 1stPMT,*( ) ( ) +- IMP: Growing annuity with fixed percentage when growth and rate of interest are same. Future Value of growing annuity with fixed percentage when rate of interest and growth rate are same and saving in the beginning of every period = [A*n (1+R)n-1 ] * (1+R) or A * n(1+R)n Future Value of growing annuity with fixed percentage when rate of interest and growth rate are same and saving at the end of every period = [A*n (1+R)n-1] A stands for first payment, R is rate of interest and n is total number of payments B. Growing annuity by Fixed amount CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 29

The Formula of calculating future value of a growing annuity with fixed amount when payments are made in the beginning of every period is: [{A * Sn + D * (Sn – n)} / r] * (1+r) Where, A = First payments Sn = it is a future value of ordinary annuity (set = End) of Re. -1(PMT = -1) considering number of payments and rate of interest as per the Question N = number of payments R = rate of interest The Formula of calculating future value of a growing annuity with fixed amount when payments are made at the end of every period is: [A * Sn + D * (Sn – n) / r] 5. Annuity in Perpetuity Perpetuity forever is an annuity whose payment continues forever. Present Value of annuity in perpetuity if received at the end of every period = Annuity required/ rate of interest Ex. If I need Rs.100000 at the end of every year forever how much should be invested today if rate of interest is 10% p.a. Present value = 100000/0.10 = 1000000 Present Value of annuity in perpetuity if received in the beginning of every period = (Annuity required/ rate of interest) * (1 + rate of interest) Or = (Annuity Required/ Rate of Interest) + Annuity Required CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 30

Questions Based On Ordinary Annuity and Annuity Due 1. Mr. Sharma invests Rs.20000 in the beginning of every year for 20 years, return on investment 12% p.a. how much would be the maturity? Sol. Set: Begin EXCEL N = 20 =FV(0.12,20,-20000,0,1) I = 12 =1613974.71 PMT = - 20000 FV = solve = 16,13,974.71 2. If I need Rs.2 lacs after 10 years, how much I should deposit in the beginning of every year if rate of interest 15% p.a. compounding half yearly? Sol. EXCEL Set: Begin =PMT(0.15/2,10*2,0,200000,1) N = 10 =-8292.7 I = 15% FV = 2,00,000 PMT = -8292.70 solve C/Y = 2, P/Y = 1 3. How much you shall deposit in the beginning of every year from the age of 30 till age 65 in an account paying 9% p.a. compounded monthly to accumulate Rs.1million? Sol. Set: Begin, EXCEL N = 35 =PMT(0.09/12,35*12,0,1000000,1) I = 9% =-3887.06 FV = 10,00,000 PMT = -3,887.06 Solve P/Y = 1, C/Y = 12 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 31

4. Mr. Sharma invests Rs.1000 pm for 20 years in mutual fund that generates 12% p.a. return, what will be the maturity amount? Sol. Set: Begin, EXCEL PMT = - 1000 in excel we need to convert p.a. return into monthly N = 240 =nominal(.12,12) = .1138655 then divide by 12 I = 12 =.1138655/12 = 0.00948879 P/Y = 12, C/Y = 1 =FV(0.00948879,20*12,-1000,0,1) FV = solve = 9,19,857.35 =919857.35 OR Conversion roi annually to monthly =(1.12)^(1/12)-1 =0.00948879 5. Calculate the maturity of deposits of Rs.10000 per quarter in a scheme that generates 12% per annum compounding quarterly for 10 years. Set Begin, =EXCEL N = 10*4 =FV(0.12/4,10*4,-10000,0,1) I = 12 =776632.97 PMT = -10,000 FV = Solve= 7,76,632.97) P/Y = 4 C/Y = 4 6. You have just invested Rs.5 lacs in a mutual fund scheme that generates 7% p.a. return. How much you can withdraw at end of every month for 20 years? Set End, EXCEL N = 20*12 =PMT(1.07^(1/12)-1,20*12,-500000,0,0) I =7 =3812.223 PV = -5,00,000 PMT = Solve ( 3812.223) P/Y = 12, C/Y = 1 7. A 5 years ordinary annuity annually has a future value of Rs. 1,00,000/-. If ROI is 8 % per annum, then how much will be the amount of each payment? Set: End EXCEL N =5 =PMT(0.08,5,0,100000,0) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 32

I = 8% =-17045.64 FV = 1,00,000 PMT = Solve (-17,045.64) 8. Neha deposits Rs.2,000/- at the end of every month in an account and is getting interest @ 12% per annum compounded monthly. How much will be her nest egg after 10 years? Set: End EXCEL N = 120 =FV(.12/12,120,-2000,0,0) I = 12% =460077.37 PMT =-2,000 FV = Solve ( 4,60,077.37) P/Y = 12 C/Y = 12 9. Manav invests Rs. 500/- at the end of every 6 months towards a fund to pay for his children education. If the investment pays ROI @ 9 % per annum, compounded Semi Annually, then what will be the corpus after 10 years? Set: End, EXCEL N = 20 =FV(.09/2,20,-500,0,0) I = 9% =15685.71 PMT = -500 FV = Solve= 15,685.71 P/Y = 2 C/Y = 2 10. A person saves Rs. 5,000/- at the end of every quarter for 9 years @ 15 % per annum compounded quarterly. What amount would he be having after 9 years? Set: End EXCEL N = 9*4 =FV(.15/4,36,-5000,0,0) I = 15% =368443.41 PMT = -5,000 FV = Solve = 3,68,443.41 P/Y = 4 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 33

C/Y = 4 11 Manav wishes to have a retirement corpus of Rs.2,50,000/- in 30 years time. Assuming that he can earn a ROI of 12 % per annum, what amount he should invest at the end of every year into a fund to reach his goal? Set: End, EXCEL N = 30 =PMT(.12,30,0,250000,0) I = 12 =-1035.91 FV = 2,50,000 PMT = Solve (-1035.91) 12. Mansi has deposited Rs. 7,00,000/- in a bank today @ ROI of 10 % per annum. She wants to know that if she withdraws this money at the end of every month for 7 years, then how much will be the each installment amount? Set: End EXCEL N = 84 =PMT(1.1^(1/12)-1,84,-700000,0,0) I = 10 =11465.52 PV = -7,00,000 PMT = SOLVE ( 11,465.52) P/Y = 12 13. Manav saves Rs. 20,000/- at the end of every year for 5 years and Rs. 30,000/- a year for 10 years thereafter. What will be the total amount in his account after 15 years, if ROI is 10 % per annum? STEP 1 SET: END STEP 2 SET: END N =5 N = 10 I = 10% PMT = -20,000 I = 10% FV = Solve ( 1,22,102) PMT = - 30,000 C/Y = 1 PV = -122,102 P/Y = 1 FV = Solve (7,94,823.87) P/Y = 1 C/Y = 1 EXCEL STEP-2 STEP-1 =FV(.10,10,-30000,-122102,0) =FV(.10,5,-20000,0,0) CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 34

=122102 =794823.87 14. Rahul decides to deposit Rs. 5,000/- at the end of every month into an account yielding 12 % per annum compounded monthly for 20 years. What will be the accumulated value in this account after 20 years? SET: END EXCEL N = 20*12 =FV(.12/12,240,-5000,0,0) I = 12 =4946278.82 PMT = -5,000 FV = Solve (49,46,278.82) P/Y = 12 C/Y =12 15. What is the Present Value of an annuity which pays Rs. 10,000/ at the end of every quarter for 3 years, assuming ROI @ 7% p.a.? set: End EXCEL N = 12 =PV(1.07^(1/4)-1,12,-10000,0,0) I = 7% =107689.336 PMT = -10,000 PV = Solve ( 1,07,689.336) P/Y = 4 C/Y = 1 16. What is the PV of an Annuity Due which provides Rs. 2,000/- in the beginning of every month for first 3 years and then Rs. 3,000/- per month for next 2 years. And ROI is 9 % per annum compounded monthly? It is a case of backward calculation STEP-1 STEP-2 Set: Begin, Set: Begin N = 24 I =9 N = 36 PMT = 3000 I = 9% PV =SOLVE =-66159.94 PMT = 2000 P/Y =12 PV = SOLVE = -113921.3652 C/Y =12 FV = 66159.94 C/Y = 12 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 35

EXCEL P/Y = 12 STEP-1 =PV(.09/12,2*12,3000,0,1) STEP-2 =-66159.94 =PV(.09/2,3*12,2000,66159.94,1) 17. Mani deposits Rs.50,000/- in a bank account which pays interest @ 10 % per annum. How much can be withdrawn in the beginning of each quarter for 5 years and 9 months? Set: Begin EXCEL N = 23 =PMT(1.1^(1/4)-1,5*4+9/3,-50000,0,1) I = 10% =-2790.365 PV = -50,000 PMT = Solve=2790.365 P/Y = 4 18. What amount needs to be invested today at 10 % per annum, so that it pays Rs. 1 lac per annum for 5 years, Rs.25000 per half yearly for next 3 years and Rs.20000 per month for next 5 quarters? ( all payments are in the beginning of each period ) It is case again backward calculation. We need to discount all the payments Step 1 Step 2 Step 3 Set: Begin N = 5*3 Set: Begin Set: Begin I = 10 MT = 20,000 N =6 N =5 PV = Solve (-2,83,943.01) P/Y = 12 I = 10 I = 10 C/Y =1 PMT =25000 PMT = 1,00,000 FV = 2,83,943.01 FV = 3,46,924.83 PV = Solve (-3,46,924.83) PV = Solve (-632399.57) P/Y = 2 P/Y = 1 C/Y = 1 C/Y = 1 EXCEL STEP-1 STEP-2 STEP-3 =PV(1.1^(1/12)-1,15,20000,0,1) =PV(1.1^(1/2)-1,6,25000,283943.01,1) =PV(0.10,5,100000,346924.83,1) =-2,83,943.01 =-3,46,924.83 =-632399.57 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 36

19. Mr. Vijay has to accumulate approximately Rs.25 lakhs for his daughter’s wedding 11 years down the line for which he invests Rs.25000 at the end of each quarter for the said period at 9% per annum compounded quarterly. Calculate the surplus or deficit he will face at the end of 11 years, also determine the additional amount he should save every quarter end to reach his target. Sol. STEP 2 STEP 1 SET : END SET : END N = 44 N = 44 I =9 I =9 PMT = -33847 (SOLVE) PMT = -25000 FV = 2500000 FV = 1846516 P/Y = 4 P/Y = 4 C/Y = 4 C/Y = 4 EXCEL STEP-2 =PMT(0.09/4,44,0,2500000,0) STEP-1 =-33847 =FV(0.09/4,11*4,-25000,0,0) =1846516 Deficit he is facing to reach his target = Rs.2500000 – Rs.1846516 = Rs.653484 Additional amount he can save = Rs.33847 - Rs.25000 = Rs. 8847 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 37

Questions Based On Deferred Annuity Q1. Mr. Sharma is aged 50 years at present. He has invested some amount in an annuity which will pay him after 10 years Rs. 25,000/- p.a. at the beginning of every year for 10 years. Rate of interest is 6% p.a. Calculate how much amount he has invested now? Sol. It is a question of backward calculation. Step1. Step 2 Set = Begin Set Begin N = 10 N = 10 I = 6% I = 6% PMT = 25000 FV = 0 PMT = 0 P/Y = 1 FV = 195042.30 C/Y = 1 P/Y = 1 PV = Solve = 195042.30 C/Y = 1 PV =Solve = ( 108910.60) EXCEL STEP-2 STEP-1 =PV(0.06,10,0,195042.30,1) =PV(0.06,10,25000,0,1) =-195042.30 =-108910.60 Q2. Mr. Mohit is 55 years old at present. He has invested some amount in an annuity which will pay him after 5 years Rs. 30,000/- p.m. in the beginning of every month for 10 years. Rate of interest is 7% p.a compounded monthly. Calculate how much amount he has invested now? Sol. CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 38

It is a question of backward calculation. STEP1. Step 2 Set = Begin Set End N = 10*12 N =5 I = 7% PMT = 30000 I = 7% FV = 0 PMT = 0 P/Y = 12 C/Y = 12 FV = 2598862.73 PV = Solve = -2598862.73 PV = Solve = -1833250.867 P/Y = 1 C/Y = 12 EXCEL STEP-2 STEP-1 =PV(0.07/12,60,0, 2598862.73,1) =PV(0.07/12,120,30000,0,1) =-1833250.867 =-2598862.73 Q3. Mr. Vipin is now 50 years old. He has invested Rs. 1, 50,000/- in an annuity which will pay him after 10 years a certain amount per half year at the beginning for 10 years. Rate of interest is 8% p.a. Calculate how much he will receive at the beginning of every half year after 10 years? STEP1. = End/begin STEP 2 Set = 10 Set = Begin N = 8% N = 10*2 I = –150000 I =8 PV =0 PV = -323838.7496 PMT =1 PMT = solve =22773.1356 P/Y =1 P/Y = 2 C/Y = Solve = 323838.7496 C/Y = 1 FV EXCEL STEP-2 STEP-1 =PMT(1.08^(1/2)-1,20, -323838.7496,0,1) =FV(0.08,10,0,-150000,1) =22773.1356 =323838.7496 Q4. Mr. Raju is now 40 years old. He has invested some amount in an annuity which will pay him from CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 39

the end of 10th year from today, Rs. 30,000/- p.a. for 10 years. Rate of interest is 6% p.a. compounded monthly Calculate how much he has invested today? Sol. It is a question of backward calculation. It is mentioned from the end of 10th year, so we can calculate present value of Rs.30000 annuity at end of year 9. STEP 1. Step 2. Set = End Set = End N = 10 N =9 I = 6% I = 6% PMT = 30,000 PMT = O FV =0 FV = 219057.9982 C/Y = 12 P/Y = 1 PV = Solve = -219057.9982 C/Y = 12 EXCEL PV =SOLVE=- 1,27827.5441 STEP-1 =PV(0.06/12,120,30000,0,0) STEP-2 =-219057.9982 =PV(0.06/12,9*12,0, 219057.9982,1) =- 1,27827.5441 Q5. Mr. Gopal is working in a reputed company and earning Rs. 3,00,000/- p.a. and is now 48 years old. He has invested Rs. 3, 00,000/- in an annuity plan lump sum today which will pay him after 5 years a certain amount p.m. at the end of every month for 10 years. Rate of interest is 8% p.a. Calculate monthly income out of this investment? STEP1. = END STEP 2 Set =5 Set = End N =8 N = 120 (10*12) I = -3,00,000 I =8 PV =0 PV = -440798 PMT =0 FV = 0 FV =1 PMT = Solve = 5,283 P/Y =1 P/Y = 12 C/Y = Solve = 4, 40,798 C/Y = 1 FV CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 40

EXCEL STEP-2 STEP-1 =PMT(1.08^(1/12)-1,120,-440798,0,0) =FV(0.08,5,0,-300000,1) =5283 =4, 40,798 Q6. Mr. Viki is now 50 years old. He has invested some amount in an annuity which will pay him from the end of 5th year Rs. 30,000/- p.a. for 10 years. Rate of interest is 7% p.a. compounded monthly. Calculate how much he has invested now? STEP1. = End STEP 2 Set = 10 Set = END/BEGIN N = 7% N =4 I =30,000 I = 7% PMT =1 FV = 208495 P/Y = 12 PV = Solve=- 157705.28 C/Y =Solve =-208495 P/Y = 1 PV C/Y = 12 EXCEL Annuity required annually therefore we need to convert roi into annual effective. =effect(.07,12) =.07229 STEP-1 STEP-2 =PV((1+.07/12)^(12)-1,10,30000,0,1) =PV((1+.07/12)^(12)-1,4,0, 208495,1) =-208495 =- 157705.28 Q7. Mr. Naman is working in a reputed company and earning Rs. 5,00,000/- p.a. and is now 50 years old. He has invested Rs. 2, 50,000/- in an annuity which will pay him after 5 years a certain amount p.a. at the end of every year for 10 years. Rate of interest is 8% p.a. Calculate how much he will receive at the end of every year after 5 years? STEP1. = END / BEGIN STEP 2 Set =5 Set = END/BEGIN N =8% N = 10 I = -2,50,000 I = 8% PV =0 PV = -3,67,332 PMT FV = 0 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 41

FV =0 PMT = 54,743 Solve P/Y = 1 P/Y = 1 C/Y = 1 C/Y = 1 FV =Solve =3,67,332 Q8. Mr. Sharma is working in a reputed company and earning Rs.3,00,000/- p.a. and is now 48 years old. Now he has invested 50% of his half yearly salary in an annuity plan which will pay him after 5 years a certain amount p.m. at the end of every month for 12 years. Rate of interest is 8% p.a. compounded semi-annually. Calculate how much he will receive at the end of every month after 5 years? Sol. STEP1. STEP 2 Set =END / BEGIN Set = END N =5 N = 144 I =8 I = 8% PV = -75000 PV = -111018.3214 C/Y = 2 PMT = SOLVE = 1193.81 FV =Solve =111018.3214 P/Y = 12 C/Y = 2 While solving step-2, in excel our rate of interest must be in effective monthly as payment required every month. Steps of conversion: =effect(.08,2) =0.0816 it is annual effective Now we need to convert in nominal but compounding monthly =nominal(0.0816,12) =0.078698 p.a. comp monthly Now we can divide it by 12 to make monthly effective =0.078698/12 = 0.0065581969 EXCEL STEP-2 STEP-1 =PMT(0.0065581969,144,-111018.3214,0,0) =FV(0.08/2,10,0,-75000,1) =1193.81 =111018.3214 Q9. Mr. X deposits Rs. 50000 in the beginning of every quarter for 25 years in an annuity plan. After 25 years how much he will receive at the end of every month for 20 years if ROI is 12% p.a? CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 42

STEP 1 STEP 2 SET: BEGIN Set: End N =25*4, N = 20*12 I =12% I = 12% PV =0, PV =-ANS PMT =-50,000, P/Y =12 P/Y =4, C/Y =1 C/Y =1, PMT =303,173.39= SOLVE FV =2,86,38,456.22 =Solve EXCEL STEP-1 STEP-2 =FV(1.12^(1/4)-1,100,-50000,0,1) =PMT(1.12^(1/12)-1,240,-28638456.22,0,0)) =2,86,38,456.22 =303,173.39 Q10. Mr. S has invested Rs.50lacs now in a deferred annuity plan which will provide monthly annuity in the beginning of every month after 35 years from now for 20 years. Calculate annuity amount if ROI is 12% p.a. compounding half yearly? STEP 1 STEP 2 SET: BEGIN SET: BEGIN N =35 N =20*12 PV = 50,00,000 I = 12% I =12%, PV =-ANS P/Y =1, P/Y =12 C/Y =2. C/Y = 2 FV =29,53,79,650.90solve PMT = 31,62,119.01 solve EXCEL In second step which we did in Q8, same we need to convert Roi first into annual effective and then nominal and finally we need to divide by 12 to make it monthly effective. Q11. Mr. X is aged 40 years at present. He has invested some amount in an annuity which will pay him after 20 years Rs. 85,000/- p.a. at the beginning of every year for 20 years. Rate of interest is 6.8% p.a. Calculate how much amount he has invested now? CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 43

STEP 1 STEP 2 SET: BEGIN SET: BEGIN N =20, N =20 I =6.8%, I = 6.8% PMT =85,000, FV = -ANS P/Y =1, PV =2,62,06,319 solve C/Y =1, P/Y =1 PV =-976,857.21 solve C/Y =1 Q12. Easy to solve in excel sheet. Mr. M is 35 years old at present. He has invested some amount in an annuity which will pay him after 20 years Rs. 30,000/- p.m. at the beginning for 20 years. Rate of interest is 9% p.a. compounded quarterly. Calculate how much amount he has invested now? STEP 1 STEP 2 SET: BEGIN SET: BEGIN N = 20*12 I = 9% N =20 PMT =30,000 I = 9% P/Y = 12 FV =-ANS C/Y =4. P/Y =1 PV =33,75,239.15solve C/Y =4 PV = -5,69,166.34 solve EXCEL In first step we need to convert Roi first into annual effective and then nominal and finally we need to divide by 12 to make it monthly effective. Steps of conversion: =effect(.09,4) =0.0930833 it is annual effective Now we need to convert in nominal but compounding monthly =nominal(0.0930833,12) =0.0893333 p.a. comp monthly Now we can divide it by 12 to make monthly effective =0.0893333/12 = 0.00744444 Q13. Mr. S is now 40 years old. He has invested Rs. 41,50,000/- in an annuity which will pay him after 20 years a certain amount in the beginning of every half year for 20 years. Rate of interest CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 44

is 12% p.a. compounded monthly. Calculate how much he will receive at the beginning of every half year after 20 years? STEP 1 STEP 2 SET: BEGIN SET: BEGIN N = 20 N =20*2 I = 12% I = 12% PV = 41,50,000 PV =-ANS FV = Solve ( 4,52,04,097.66) P/Y =2 C/Y = 12 C/Y =12 PMT =Solve ( 28,84,617.57) EXCEL In 2nd step we need to convert Roi first into annual effective and then nominal and finally we need to divide by 2 to make it half yearly effective. As we know if we make payments half yearly, roi should be effective half yearly. Steps of conversion: =effect(.12,12) =0.126825 it is annual effective Now we need to convert in nominal but compounding monthly =nominal(0.126825,2) =0.12304 p.a. comp half yearly Now we can divide it by 2 to make half yearly effective =0.12304/2 = 0.06152 Q14. Mr. R is now 25 years old. He has invested some amount in an annuity which will pay him from the end of 20th year from today, Rs. 30,000/- p.a. for 30 years. Rate of interest is 12% p.a. compounded monthly Calculate how much he has invested today? Sol. It is a question of backward calculation. It is mentioned end to 20th year, it means beginning of 21st year. If we calculate present value at 20 years, we need to consider begin. STEP 1 STEP 2 SET: BEGIN SET: END N = 30 N =20 I = 12 I =12% CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 45

PMT =30,000 FV =-ANS PV = Solve= -259131.92 PV = Solve=-23789.82 P/Y = 1 C/Y = 12 EXCEL Annuity is required annually, so we need to convert roi into annual effective in first step. STEP-1 STEP-2 =PV((1+12%/12)^(12)-1,30,30000,0,1) =PV(0.12/12,240,0,259131.92,1) =-259131.92 =-23789.82 Q15. Mr. Gopal is working in a reputed company and earning Rs. 3,00,000/- p.a. and is now 48 years old. He has invested Rs. 3,00,000/- in an annuity which will pay him after 5 years a certain amount p.m. at the end of every month for 10 years. Rate of interest is 8% p.a. Calculate monthly income out of this investment? Step 1 Step 2 SET: BEGIN SET END N =5 N =10*12 I = 8% I = 8% PV = -3,00,000 PV = - 4,40,798.42 FV = Solve 4,40,798.42 PMT = Solve 5,283.3 P/Y = 1 P/Y = 12 C/Y = 1 C/Y = 1 Q16. Mr. M is 55 years old at present. He has invested some amount in an annuity which will pay him Rs.3,40,000 in the beginning of every quarter for 12 years after 5 years from now. Rate of interest is 11% p.a. Calculate how much amount he should invest every month from now? Sol. It is a question of backward calculation. STEP 1 STEP 2 SET: BEGIN SET BEGIN N = 12*4 N = 5*12 I = 11% I = 11% PMT = 3,40,000 FV = -94,28,722.41 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 46

PV = - 94,28,722.41 PMT = Solve=-119176.66 P/Y = 4 P/Y =12 C/Y = 1 C/Y =1 EXCEL STEP-2 STEP-1 =PMT(1.11^(1/12)-1,60,0, 9428722.41,1) =PV(1.11^(1/4)-1,48,340000,0,1) =-119176.66 =- 94,28,722.41 Q17. Mr. X is now 20 years old. He is investing Rs.20000 in the beginning of every month in an annuity which will pay him some amount at the end of every month after 30 years for 20 years. Rate of interest is 12% p.a. compounded monthly. Calculate how much he will receive? STEP 1 STEP 2 SET: BEGIN SET: END N = 360 N =240 I = 12% I = 12% PMT = -20,000 PV =-70598275.48 FV = Solve=70598275.48 PMT = Solve=777347.82 P/Y = 12 P/Y =12 C/Y = 12 C/Y =12 EXCEL STEP-2 STEP-1 =PMT(0.12/12,240,-70598275.48,0,0) =FV(0.12/12,360,-20000,0,1) =777347.82 =70598275.48 Q18. Mr. N would require Rs.10 lacs in the beginning of every year after 25 years from now for 20 years at rate of interest 12% p.a. How much he should deposit in the beginning of every month? STEP 1 STEP 2 SET: BEGIN SET BEGIN N = 20 N =25*12 I = 12 I =12% PMT = -10,00,000 FV =83,65,776.85 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 47

PV = Solve 83,65,776.85 PMT = Solve -4914.66 P/Y = 1 P/Y =12 C/Y = 1 C/Y =1 EXCEL In step-2 we need to convert roi into effective monthly. =(1.12)^(1/12)-1 Q19. Mr. S is working in a company and earning Rs.3,00,000/- p.m. and saving 10% of his monthly salary at the end of every month in an annuity plan which will pay him after 25 years a certain amount p.m. at the end of every month for 22 years. Rate of interest is 8% p.a. compounded monthly. Calculate how much he will receive at the end of every month after 25 years? STEP 1 STEP 2 SET: END SET: END N = 25*12 N =22*12 I = 8% I =8% PMT =-30,000 PV =-28530791.84 FV = Solve= 28530791.84 PMT = Solve = 230008.94 P/Y = 12 P/Y =12 C/Y = 12 C/Y =12 EXCEL STEP-2 STEP-1 =PMT(0.08/12,22*12,-28530791.84,0,0) =FV(0.08/12,300,30000,0,0) =230008.94 =28530791.84 20. Mrs. X deposits Rs. 5000 in the beginning of every month for 15 years in an annuity plan. After 15 years how much she will receive at the end of every month for 20 years if ROI is 9.5% p.a. SET: BEGIN SET: END N = 15*12 N = 20*12 I = 9.5% I = 9.5 PMT = -5000 PV = -1925399.59 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 48

FV = 1925399.59 = SOLVE PMT = 17459.56= SOLVE P/Y = 12 P/Y = 12 C/Y = 1 C/Y = 1 EXCEL STEP-2 STEP-1 =PMT(1.09^(1/12)-1,240,-1925399.59,0,0) =FV(1.09^(1/12)-1,180,-5000,0,1) =17459.56 =1925399.59 Questions Based on Growing Annuity and Annuity in Perpetuity Q1. Mr. X is 30 years of age and decides to invest Rs.1,50,000 at year end and increases his saving 10 % every year. If ROI is 18 % p.a. What will be his accumulated corpus at the age of 50? Sol. It is a case of growing annuity with fixed percentage saving at the end of every period. The Formula of calculating future value of a growing annuity when payments are made at the end of every period is: FV = PMT [{(1+r)n – (1+g)n}] r–g FV=1,50,000 * ((1.18) ^20 – (1.10)^20) /(0.18 – 0.10) FV = 3,87,47,877.48 Or EXCEL YEAR(begin) AGE GROWTH 10% ROI 18%P.A. 0 30 INVESTMENT FV 1 31 0 2 32 0 3 33 150000 150000 4 34 165000 342000 5 35 181500 585060 199650 890020.8 219615 1269839.54 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 49

6 36 241576.5 1739987.16 7 8 37 265734.15 2318919 9 10 38 292307.565 3028631.99 11 12 39 321538.3215 3895324.07 13 14 40 353692.1537 4950174.55 15 Q2. Mrs. 16 41 389061.369 6230267.34 Sharma aged 20, 17 18 42 427967.5059 7779682.97 decides to save today 19 he 20 43 470764.2565 9650790.16 Rs.100000 saving 44 517840.6822 11905773.1 and thereafter amount of 45 569624.7504 14618437 increases his p.a. monthly? 46 626587.2254 17876342.8 10% every year. 21783330.5 Calculate the 47 689245.948 accumulated 48 758170.5427 26462500.5 32059738.2 at age 60 if rate 49 833987.597 38747877.5 interest is 14% 50 917386.3567 compounded 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 FV = 100000[ (1.1493)^40 – (1.10)^40] (1.1493) 0.1493 – 0.10 FV = 503918428 (We need to convert 14% p.a. compounding monthly into annual effective as saving annually) (CNVR n = 12, I = 14, EFF = solve = 14.93 OR EXCEL =EFFECT(0.14,12) = 0.1493) EXCEL GROWTH 10% P.A. ROI 14.93% P.A. YEAR(begin) AGE INVESTMENT FV 0 1 20 100000 100000 2 21 110000 224930 22 121000 379512.049 CFP Level 1 - Module 1 - Personal Financial Management - Workbook Page 50