WORKBOOK Page 1
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. 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. Page 2
(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 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. Page 3
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 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 Page 4
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 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)? Page 5
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 1 to 5 entries = -100000 6 to 10 entries = 0 11 to 20 = 100000 IRR = Solve Page 6
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? Sol. Step1: Begin, Step 2: Begin N = 1, N = 1, I = 10 I = 14 PV = - 10,000 PV = -11,000 FV = SOLVE = 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 Page 7
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 N Step 2 Begin Step 3 Begin N = 8, I =7 N = 10 I = 10%, PV = 8.75% I = 9.25% PV = - 30,000 C/Y = -66,112.70 PV = -118,929.55 C/Y = 4 FV = 1, C/Y = 1 FV =SOLVE = 66,112.70 = SOLVE = 118,929.55 FV = SLOVE = 2,88,074.13 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 Page 8
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, 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 Page 9
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 – 2 STEP - 3 STEP – 1 =PV(0.0825,4,0, 98783.15,1) =PV(0.0925/4,3*4,0, 719,446.25 ,1) =PV(0.08/2,3*2,0,1250000,1) =-719446.65 =-542832.42 =-987893.15 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 P/Y = 1 C/Y = 1 Page 10
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 gettripled 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 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? Page 11
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 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 Page 12
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. 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 Page 13
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. = CNVR Press = CNVR Press = CNVR Press = 12 N =4 N =2 N = 12 I = 12 I = 12 I = Solve = 12.68 EFF = Solve = 12.55 EFF = Solve = 12.36 EFF =EFFECT(0.12,12) EXCEL =EFFECT(0.12,2) =12.68% =EFFECT(0.12,4) =12.36% =12.55% OR =(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. = CNVR =EFFECT(0.10,4) Press =4 =10.38% N = 10 OR I = Solve = 10.38 =(1+10%/4)^4 -1 EFF =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% Page 14
B. = CNVR EXCEL Sol. =4 =EFFECT(0.20,4) Press = 20 =21.55% N = Solve = 21.55 OR I =(1+20%/4)^4 -1 EFF =21.55% C. = CNVR EXCEL Sol. =2 =EFFECT(0.36,2) Press = 36 =39.24% N = Solve = 39.24 OR I =(1+36%/2)^2 -1 EFF =39.24% D. = CNVR EXCEL Sol. = 365 =EFFECT(3,365) Press = 300 =1884.0759% N = Solve = 1884.0759 OR I =(1+300%/365)^365 -1 EFF =1884.0759% E. = CNVR EXCEL Sol. = 52 =EFFECT(0.50,52) Press = 50 =64.4788% N = Solve = 64.4788 OR I =(1+50%/52)^52 -1 EFF =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% Page 15
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) 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. Page 16
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) 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% Page 17
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 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 Page 18
Effective roi from smaller unit to bigger unit = (1+r%)^(n) -1 =(1.02)^(12)-1 = 0.26824 =26.824% p.a. 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 It is very simple, we can write 2%*12 = 24% p.a. comp monthly Page 19
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 = e -10.10 = 10.52% Q4. If the continuous time return is 11.8% find the monthly return Sol. Effective rate of return = e -10.118 = 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 = e -10.105 = 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: 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.04 EFF : Solve = 11.34 Difference = 11.34 - 11.04 = 0.30% Page 20
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: 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 Page 21
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 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 Page 22
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 \\ Page 23
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 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 Page 24
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) 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 Page 25
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 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% I = 10% PMT = -20,000 PMT = - 30,000 FV = Solve ( 1,22,102) PV = -122,102 C/Y = 1 FV = Solve (7,94,823.87) P/Y = 1 P/Y = 1 C/Y = 1 Page 26
EXCEL STEP-2 STEP-1 =FV(.10,10,-30000,-122102,0) =FV(.10,5,-20000,0,0) =794823.87 =122102 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 N = 36 I =9 I = 9% PMT = 3000 PMT = 2000 PV =SOLVE =-66159.94 PV = SOLVE = -113921.3652 P/Y =12 FV = 66159.94 C/Y =12 C/Y = 12 P/Y = 12 Page 27
EXCEL STEP-2 STEP-1 =PV(.09/2,3*12,2000,66159.94,1) =PV(.09/12,2*12,3000,0,1) =-66159.94 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-2 STEP-3 STEP-1 =PV(1.1^(1/2)-1,6,25000,283943.01,1) =PV(0.10,5,100000,346924.83,1) =PV(1.1^(1/12)-1,15,20000,0,1) =-3,46,924.83 =-632399.57 =-2,83,943.01 Page 28
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 Page 29
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 PMT = 0 FV = 0 FV = 195042.30 P/Y = 1 P/Y = 1 C/Y = 1 C/Y = 1 PV = Solve = 195042.30 PV =Solve = ( 108910.60) EXCEL STEP-2 STEP-1 =PV(0.06,10,0,195042.30,1) =PV(0.06,10,25000,0,1) =-108910.60 =-195042.30 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. It is a question of backward calculation. STEP1. = Begin Step 2 Set = 10*12 Set End N = 7% N =5 I = 30000 I = 7% PMT =0 PMT = 0 FV = 12 FV = 2598862.73 P/Y = 12 PV = Solve = -1833250.867 C/Y = Solve = -2598862.73 P/Y = 1 PV 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 Page 30
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 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. = End Step 2. Set = 10 Set = End N = 6% N =9 I = 30,000 I = 6% PMT =0 PMT = O FV = 12 FV = 219057.9982 C/Y = Solve = -219057.9982 P/Y = 1 PV C/Y = 12 PV =SOLVE=- 1,27827.5441 EXCEL STEP-1 STEP-2 =PV(0.06/12,120,30000,0,0) =PV(0.06/12,9*12,0, 219057.9982,1) =-219057.9982 =- 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? Page 31
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 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 Page 32
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? 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 Page 33
EXCEL STEP-2 =PMT(1.12^(1/12)-1,240,-28638456.22,0,0)) STEP-1 =303,173.39 =FV(1.12^(1/4)-1,100,-50000,0,1) =2,86,38,456.22 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? 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 Easy to solve in excel sheet. Q12. 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 N =20 I = 9% I = 9% Page 34
PMT =30,000 FV =-ANS P/Y = 12 P/Y =1 C/Y =4. C/Y =4 PV =33,75,239.15solve 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 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 Page 35
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% 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 Page 36
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 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 Page 37
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 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 Page 38
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 6 36 199650 890020.8 7 37 219615 1269839.54 8 38 241576.5 1739987.16 9 39 265734.15 2318919 10 40 292307.565 3028631.99 11 41 321538.3215 3895324.07 12 42 353692.1537 4950174.55 13 43 389061.369 6230267.34 14 44 427967.5059 7779682.97 15 45 470764.2565 9650790.16 16 46 517840.6822 11905773.1 17 47 569624.7504 14618437 18 48 626587.2254 17876342.8 19 49 689245.948 21783330.5 20 50 758170.5427 26462500.5 833987.597 32059738.2 917386.3567 38747877.5 Page 39
Q2. Mrs. Sharma aged 20, decides to save Rs.100000 today and thereafter he increases his saving 10% every year. Calculate the accumulated amount at age 60 if rate of interest is 14% p.a. compounded monthly? Sol. The Formula of calculating future value of a growing annuity when payments are made in the beginning of every period is: FV = PMT [{(1+r)n – (1+g)n}]* ( 1 + r) r–g 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 20 100000 100000 1 21 110000 224930 2 22 121000 379512.049 3 23 133100 569273.1979 4 24 146410 800675.6864 5 25 161051 1081267.566 6 26 177156.1 1419856.914 7 27 194871.71 1826713.261 8 28 214358.881 2313800.432 9 29 235794.7691 2895045.606 10 30 259374.246 3586650.161 11 31 285311.6706 4407448.7 12 32 313842.8377 5379323.629 13 33 345227.1214 6527683.768 14 34 379749.8336 7882016.788 15 35 417724.8169 9476526.712 16 36 459497.2986 11350869.45 17 37 505447.0285 13551001.29 18 38 555991.7313 16130157.51 19 39 611590.9045 19149980.93 20 40 672749.9949 22681823.08 21 41 740024.9944 26808244.26 22 42 814027.4939 31624742.62 Page 40
23 43 895430.2433 37241746.93 24 44 984973.2676 43786913.02 25 45 1083470.594 51407769.73 26 46 1191817.654 60274767.4 27 47 1310999.419 70584789.59 28 48 1442099.361 82565198.04 29 49 1586309.297 96478491.41 30 50 1744940.227 112627670.4 31 51 1919434.25 131362415.8 32 52 2111377.675 153086202.2 33 53 2322515.442 178264487.6 34 54 2554766.986 207434142.6 35 55 2810243.685 241214303.8 36 56 3091268.053 280318867.4 37 57 3400394.859 325570869.2 38 58 3740434.344 377919034.3 39 59 4114477.779 438456823.9 40 60 503918427.7 Q3. Mr. A is 25 years of age and he is getting a salary of Rs1,50,000/- per annum. He wishes to save 12 % of his annual salary every year at the end of the year. If his salary increases by 15 % every year and ROI is 10 % per annum then what will be his accumulated savings at the age of 50? Sol. The Formula of calculating future value of a growing annuity when payments are made at the end of every period is: FV = PMT [{(1+r)n – (1+g)n}] r–g FV=150,000*.12*(1.1^25 – 1.15^25)/ (0.10 - 0.15) = 79,50,328.80 Q4. Mrs. Sharma decides to save Rs.2000 today and thereafter she increases his saving 2% p.m. Calculate the accumulated amount after 40 years if rate of interest is a. 1% pm b. 12% p.a. compounding monthly Sol. The Formula of calculating future value of a growing annuity when payments are made in the beginning of every period is: FV = PMT [{(1+r)n – (1+g)n}]* ( 1 + r) r–g Sol. A 1% p.m. FV = 2000* [{(1.01) ^480 – (1.02)^480} / (.01 – .02) ]* (1.01) Page 41
FV=2688933344 (If saving monthly n, g and r should be monthly) Sol. B. 12% p.a. compounding monthly = 1% pm Therefore answer will be same. Q5. Mrs. Y aged 34, invests Rs.80000 now and wants to increases her investment Rs.2000 every year. Calculate the accumulated corpus she will be having after 26 years or age 60 if rate of interest is 12% p.a.? Sol. It is a case Growing Annuity with Fixed Amount The Formula of calculating future value of a growing annuity with fixed amount when payments are made in the beginning of every period is: FV = [A * Sn + D * (Sn – n) / r] * (1+r) = [80,000*150.33… + 2000* ( 150.33…-26)/0.12] * (1.12) (Calculation of Sn: set = end, N=26, i=12, PMT = -1, FV = solve = 150.3339345) FV= 1,57,90,820.64 OR EXCEL YEAR(BEGIN) AGE GROWTH Rs.2000 ROI 12% P.A. 0 34 INVESTMENT FV 1 35 80000 2 36 82000 80000 3 37 84000 171600 4 38 86000 276192 5 39 88000 395335.04 6 40 90000 530775.2448 7 41 92000 684468.2742 8 42 94000 858604.4671 9 43 96000 1055637.003 10 44 98000 1278313.444 11 45 100000 1529711.057 12 46 102000 1813276.384 13 47 104000 2132869.55 106000 2492813.895 2897951.563 Page 42
14 48 108000 3353705.751 15 49 110000 3866150.441 16 50 112000 4442088.493 17 51 114000 5089139.113 18 52 116000 5815835.806 19 53 118000 6631736.103 20 54 120000 7547544.435 21 55 122000 8575249.767 22 56 124000 9728279.74 23 57 126000 11021673.31 24 58 128000 12472274.11 25 59 130000 26 60 14098947 15790820.64 Q6. Mrs. Sharma decides to save Rs.100000 today and thereafter he increases his saving Rs.5000 every year. Calculate the accumulated amount after 40 years if Roi is 14% p.a. compounded monthly? Sol. The Formula of calculating future value of a growing annuity with fixed amount when payments are made in the beginning of every period is: FV = [A * Sn + D * (Sn – n) / r] * (1+r) First we need to convert 14% p.a compounding monthly into annual effective CNVR n =12, I = 14, EFf = solve = 14.93420292 = [100000 * 1746.33….+5000 * ( 1746.33…-40)/ 1.1493…] * (1.1493…) (Calculation of Sn: set = end, N=40, i=14, PMT = -1, C/Y = 14, FV = solve = 1746.334509) = 266373647 Q7. Mr. S aged 35, saves Rs.2 lacs at the end of year and increases his saving 5000 every year. Calculate total corpus after 25 years or age 60 if rate of interest is 12% p.a. Sol. The Formula of calculating future value of a growing annuity with fixed amount when payments are made at the end of every period is: FV = [A * Sn + D * ( Sn – n ) / r ] = [200000 * 133.33.. + 5000 * (133.33.. – 25) / 0.12] FV = 31180511 Page 43
EXCEL YEAR(BEGIN) AGE GROWTH 5000 P.A. ROI 12% P.A. 0 35 INVESTMENT FV 1 36 0 0 2 37 200000 3 38 205000 200000 4 39 210000 429000 5 40 215000 690480 6 41 220000 988337.6 7 42 225000 1326938.112 8 43 230000 1711170.685 9 44 235000 2146511.168 10 45 240000 2639092.508 11 46 245000 3195783.609 12 47 250000 3824277.642 13 48 255000 4533190.959 14 49 260000 5332173.874 15 50 265000 6232034.739 16 51 270000 7244878.907 17 52 275000 8384264.376 18 53 280000 9665376.101 19 54 285000 11105221.23 20 55 290000 12722847.78 21 56 295000 14539589.52 22 57 300000 16579340.26 23 58 305000 18868861.09 24 59 310000 21438124.42 25 60 315000 24320699.35 320000 27554183.27 31180685.26 Q8. Mr. X is 35 years of age and wishes to save 80000 at the end of the year and wants to increase his saving by 15 % every year and ROI is 15 % per annum then what will be his accumulated savings at the age of 60? Sol. It is a case of growing annuity with fixed percentage when rate of interest and growth rate are same. We use formula FV =[ A* n(1+R)n-1 ] Here A means first payment FV = 80000*25*(1.15)^24 = 5,72,50,352.38 Page 44
Q9. What is the present value of perpetuity of a series of annual cash-flows of Rs.5000 which starts 1 year from now interest rate 12% p.a.? Sol. Present value of annuity in perpetuity if it is in the end of every period = annuity required/roi = 5000/0.12 = 41667 Q10. If Mr. Sharma needs Rs.2 lacs in the beginning of every month forever, what is the amount he needs to invest today if rate of interest 12% p.a. compounding monthly? Q11. Sol. Present value of annuity in perpetuity if it is in the beginning of every period = (annuity required/roi) *(1+roi) = (200000/.01) * (1.01) = 20200000 What is the present value of perpetuity of a series of annual cash-flows of Rs.5000 which starts 2 years from now interest rate 12% p.a.? Sol. First we need to calculate the PV after 2 years = (5000/0.12) = 41667 Now we need to find the present value of Rs.41667 at 12% per annum 41667/1.12^2 = 33217 Q12. What is the present value of perpetuity to be invested at the end, of a series of monthly cash flows of Rs.1420 which starts 2 years from now at interest rate of 13%per annum compounded monthly? Sol. Present value after 24 months = (PMT/i) +PMT = (1420/.01083)+1420 = Rs.132537.2669 I = 13/12 = 1.0833 This Rs.132537.2669 is received after 24 months so we need to find the present value of Rs.132537.2669 @13% per annum compounded monthly. SET: END/BEGIN N =2 I = 13 PV = -102336.39 (SOLVE) FV = 132537.2669 C/Y = 12 Page 45
QUESTIONS ON ANNUITIES Q1. Ram is considering investing in a residential triplex project for which the EMI will be Rs.1248.33 per month. If the triplex costs Rs.172000 to build, and he can get a mortgage with monthly payments for 25 years at 9% p.a. compounded monthly interest, how much would be paid by Ram as down payment? Sol Set: End EXCEL N = 25 x 12 =PV(0.09/12,300,-1248.33,0,0) I =9 =148753 PV = Solve = 148753 PMT = -1248.33 P/Y = 12 C/Y = 12 Down payment = 172000-148753 = Rs.23247 Q2. Assume you can afford to make mortgage payments of Rs. 1550 per month, and the market rate of interest is 8% p.a. compounded monthly for monthly end payment loans with a term of 30 years. If you pay 20% of the purchase price of the house as a down payment, what is the most expensive property you can buy? Sol. EXCEL Set: End =PV(0.08/12,360,-1550,0,0) N = 30 x 12 =211239.41 I =8 PMT = -1550 PV = Solve = 211239.41 P/Y = 12 C/Y = 12 Value of house that can be purchased = 211239/0.8 = 264049.26 Faculty Comment: The PV calculated in step 1 is 80% of the value of house. (Let the cost of property x, therefore 80% of x = 211239.41) Q3. An investment of Rs.8000 per quarter end for 6 years at annual interest rate of 8.5%, compounded quarterly. How much amount will accumulated by the end of 6 years? Sol. EXCEL =FV(0.085/4,24,-8000,0,0) Set : End =247121.67 N =6x4 I = 8.5 PMT = -8000 FV = Solve = 247121.67 P/Y = 4 C/Y = 4 Page 46
Q4. You borrow Rs.100000 which is to be repaid in equal monthly installments for a period of 3 years. The interest rate is 9% p.a. compounded monthly. What will be the monthly end payment? Sol EXCEL Set: End =PMT(0.09/12,36,100000,0,0) N = 3 x 12 =-3179.97 I =9 PV = 100000 PMT = Solve = -3179.97 P/Y = 12 C/Y = 12 Q5. Mr. Y is planning a trip with a family for which he is considering to invest Rs.15000at the end of every year for first 5 years and Rs.25000 at the end of every year for next 7 years and Rs.40000 thereafter. He is considering accumulating Rs. 645000 after 15years. If he requires a 15% annual return, what is the maximum amount she can accumulate for his trip and determine the surplus or deficit if any. Sol STEP2 STEP 3 STEP1 SET : END SET: END SET : END N =7 N =3 N =5 I = 15 I = 15 I = 15 PV = -101135 PV = -545692 PMT = -15000 PMT = -25000 PMT = -40000 FV = 101135(SOLVE) FV = 545692(SOLVE) FV = 968831(SOLVE) P/Y = 1 P/Y = 1 P/Y =1 C/Y = 1 C/Y =1 C/Y =1 Maximum amount that he can accumulate after 15 years = Rs.968829.32 Surplus amount = Rs.968831 - 645000 = Rs. 323831 Q6. Rohan is planning for his early retirement at the age of 50. His current age is 35. He estimated that he will need Rs. 28500 per month at the end upto the age of 75 after his retirement. Calculate the corpus that would be required to meet the post retirement expenses and what amount he should invest at the end of every month if the rate of interest on the savings is 8.50% per annum? Sol STEP 2 STEP 1 SET : END SET : END N = 180 N = 300 I = 8.50 I = 8.50 PMT = 10331.24 (SOLVE) PV = Solve -3634442.75 FV = 3634442.75 PMT = 28500 P/Y = 12 P/Y = 12 C/Y = 1 C/Y = 1 Page 47
Q7. Mr. Ram aged 30 years plans to retire at the age of 55. He plans to keep some amount aside at the end of each year for first 15 years so that he can withdraw Rs.150000 per year end for 10 years once he gets retired with the first withdrawal at the age of 56. How much he should keep aside each year end if he can earn return of 8.25% per annum on the investments? Sol STEP 1 STEP 2 STEP 3 SET : END SET : END SET : END N = 10 N = 10 N = 15 I = 8.25 I = 8.25 I = 8.25 PV = -995260 (SOLVE) PV = -450461 (SOLVE) PMT = 16270 (SOLVE) PMT = 150000 FV = 995260 FV = 450461 Q8. Ram aged 55 years has some amount in an annuity which will pay him after 5 years Rs.30000 per annum at the beginning of every year for 10 years. Given the rate of interest at 8% p.a., calculate how much amount he has invested now? Sol. Step 1 Step 2 Set: Begin Set: End N = 10 N =5 I =8 I =8 PMT = 30000 PV = Solve = -147963.3 PV = Solve = -217406.63 FV = 217406.63 Q9. Mitesh invested some amount in a scheme that starts paying after 10 years, Rs.20000 per year for five years at the beginning of each year and there after Rs.10000 at the beginning of each year for the next five years. How much Mitesh has invested, if return on the investment is 10% throughout the term period? Sol. Step 1 Step 2 Step 3 Set : Begin Set : Begin Set : End N =5 N =5 N = 10 I = 10 I = 10 I = 10 PMT = 10000 PMT = 20000 FV = 109288.89 PV = Solve = -41698.65 FV = 41698.65 PV = Solve = -42135.59 PV = Solve = -109288.89 Q10. Mr. Sohan (aged 40 years) is working in a pharma company. He invested Rs.300000 in a term deposit which enables him to earn a certain amount at the beginning of each year for 10 years starting five years from today. Assuming the rate of interest of6.75% p.a. Calculate the amount that Mr. Sohan will receive each year? Sol. Step 1 Step 2 Set: End Set: Begin N=5 N = 10 I = 6.75 I = 6.75 PV = -300000 PV = -415872 FV = Solve = 415872 PMT = Solve = 54827.68 Page 48
AMORTIZATION Amortization: Amortization is the distribution of a single lump-sum cash flow into many smaller cash flow installments, which can be determined by the amortization schedule. Under amortization every installment consists of both principal and interest. Greater amount of the payment is applied to interest at the beginning of the amortization schedule, while more money is applied to principal at the end. Flat Interest Rate Flat interest rate, as the term implies, means an interest rate that is calculated on the full amount of the loan throughout its tenure without considering that monthly repayments (EMIs) gradually reduce the principal amount. As a result, the Effective Interest Rate is noticeably higher than the nominal Flat Rate quoted in the beginning. The formula of calculating fixed rate of interest is – Interest Payable per Installment = (Original Loan Amount * No. of Years * Interest Rate p.a.) / Number of Installments For Q, if you take a loan of Rs 1, 00,000 with a flat rate of interest of 10% p.a. for 5 years, then you would pay: = (100000 + 10% of 100000*5)/5 =Rs30000 per year. This method is particularly used to calculate the interest payable for personal loans and vehicle loans. In this method, you have to pay interest on the entire loan amount throughout the loan tenure. It is actually less popular among the borrowers because even if you gradually pay down the loan, the interest does not decrease. Reducing / Diminishing Interest Rate Reducing/ Diminishing balance rate, as the term suggests, means an interest rate that is calculated every month on the outstanding loan amount. In this method, the EMI includes interest payable for the outstanding loan amount for the month in addition to the principal repayment. After every EMI payment, the outstanding loan amount gets reduced. Therefore, the interest for the next month is calculated only on the outstanding loan amount. The formula for calculating reducing balance interest is – Interest Payable per Installment = Interest Rate per Installment * Remaining Loan Amount For Q, if you take a loan of Rs 1, 00,000 with a reducing rate of interest of 10% p.a. for 5 years. Your annual repayment would be Rs.26380 instead of Rs.30000 which is paid as per flat rate of interest. In the first year interest would be charged on 100000 and 2nd year interest would be charged on Rs.90000; in the second year interest would be charged on Rs.83620 (100000-16380) and so on. Unlike the flat rate method, you would end up paying Rs.131900 instead of Rs. 1.5 lakh. This method is particularly used to calculate the interest payable for housing, mortgage, property loans, overdraft facilities, and credit cards. Page 49
Difference between Flat Interest Rate and Reducing Balance Rate In flat rate method, the interest rate is calculated on the principal amount of the loan. On the other hand, the interest rate is calculated only on the outstanding loan amount on monthly basis in the reducing balance rate method. Flat interest rates are generally lower than the reducing balance rate. Calculating flat interest rate is easier as compared to reducing balance rate in which the calculations are quite tricky. In practical terms, the reducing rate method is better than the flat rate method. Q1. Mr. Sharma has taken a housing loan of Rs 20 lacs for 25 years @ 8% p.a. reducing monthly, calculate the following: a. EMI (equated monthly installment ) EXCEL Sol. Go to CMPD =PMT(0.08/12,300,2000000,0,0) Set = End =-15436.32 N =300 I =8 PV =20,00,000 P/Y = 12 C/Y =12, PMT = solve = -15,436.32.. b.. Total interest paid in first 5 years =CUMIPMT (rate, nper, PV, start, end, type) Sol. Go to AMRT Pm1 =1 start, end means Interest on Start to End instalment i.e.1 to 60. Pm2=60, SINT=-7,71,658.29 =CUMIPMT(0.08/12,300,2000000,1,60,0) NOTE: =771658.29 Here PM1 means payment number starts with. And PM2 means payment number ends with. c. Principal paid in last year =CUMPRINC(rate, nper, PV, start, end, type) Sol. Go to AMRT =CUMPRINC(0.08/12,300,2000000,289,300,0) Pm1=24*12+1=289 =1,77,452.61 Pm2=300 SPRN=1,77,452.61 d. Interest paid in 14th installment =IPMT(rate, per, nper, PMT, PV,[FV], [type]) Sol. Go to AMRT =IPMT(0.08/12,14,300,-15436.3243,2000000,0,0) Pm1=14 =13,143.60 Pm2=14 INT=13,143.60 Page 50
Search
