Time Value of Money Using MS Excel | Part-IV | Dr. Rishabh Gupta — Transcript
Full transcript
- 0:00afternoon to one and all. Myself, Dr.
- 0:02Rishabh Gupta.
- 0:04And today's session we will be
- 0:06understanding the MS Excel application
- 0:08on time value of money.
- 0:11So, after understanding the basic
- 0:12concepts of time value of money
- 0:15involving compounding and discounting
- 0:18techniques, we shall now focus upon that
- 0:21how we can solve these numericals based
- 0:24upon these techniques using Microsoft
- 0:27Excel.
- 0:28So, here we begin.
- 0:30In order to calculate the future value
- 0:33of single cash flows,
- 0:35series of equal cash flows, or series of
- 0:38unequal cash flows, there is a separate
- 0:41Excel function in Microsoft which is
- 0:44given by future value function is equals
- 0:47to
- 0:48FV
- 0:49rate NPER
- 0:52, PMT
- 0:54, PV , type.
- 0:57Here FV represents the future value. The
- 1:00rate represents the rate of interest.
- 1:03NPER represents the number of period for
- 1:07an investment based upon the interest
- 1:10rate. PMT is used in case of annuity.
- 1:13So, if the cash flows are in the form of
- 1:15a annuity, then we will use PMT. So, it
- 1:19is used in case of constant payments to
- 1:22be made and at a constant interest rate.
- 1:25Here PV represents the present value of
- 1:28investment. Type zero means if the
- 1:31payment is made at the end of the
- 1:33period, while type one means if the
- 1:36payment is made at the beginning of the
- 1:38period. Also, the double bracket and
- 1:42this present value and type represents
- 1:45that these values are optional.
- 1:47Further, in order to get a positive
- 1:50value of future value function, we must
- 1:53put a negative sign in front of PV in
- 1:56the function.
- 1:57Same is for the present value function
- 1:59as well. In MS Excel, we just need to
- 2:02type equals to PV. The moment I type
- 2:06equals to PV, then it automatically ask
- 2:10for rate, NPER,
- 2:13PMT, future value, and type.
- 2:17And the last function is PMT function.
- 2:19If we wish to calculate the annuity
- 2:21payments, then it will be equals to PMT.
- 2:25Again, the moment I type equals to PMT,
- 2:29then it will ask for a rate,
- 2:31comma NPER, comma present value, or
- 2:35future value, or type.
- 2:38So, let's first practice two numericals
- 2:40based upon this MS Excel Excel function.
- 2:43Problem one.
- 2:45Mr. Rahul has invested a sum of rupees
- 2:4810,000 at the beginning of the year at
- 2:52an interest rate of 10% compounded
- 2:56annually.
- 2:57How much amount will he get after the
- 3:00end of 3 years?
- 3:01So, in this case, we need to calculate
- 3:04that what is the future value of rupees
- 3:0610,000, which are invested at the
- 3:10beginning of the year, and the rate of
- 3:12interest is 10% compounded annually. So,
- 3:16this is your solution one. In this case,
- 3:19present value
- 3:23is minus 10,000.
- 3:26As I already told you that in order to
- 3:28get a positive future value, we must put
- 3:31a minus sign in front of present value.
- 3:35Also, one thing we should know that
- 3:37while writing the figure in the against
- 3:39the present value, we must avoid putting
- 3:42a comma. In this case, the time period
- 3:45NPER is three. The rate of interest is
- 3:5010% since this is not the case of PMT,
- 3:53so it will be considered as zero.
- 3:57And type, since we are making the
- 3:59investment at the beginning of the
- 4:00period, so it is type one. Again, the
- 4:04function or MS Excel function for future
- 4:06value is equals to FV. The moment I
- 4:11write equals to FV and double click on
- 4:14it, then it will ask for rate of
- 4:16interest, which is 10% in this case.
- 4:20Just click on 10%. Put comma. Then it is
- 4:24asking for NPER, which is three.
- 4:28PMT, which is zero.
- 4:32Then present value, again
- 4:35it is minus 10,000, so I am just need to
- 4:37click on this cell C2.
- 4:41And the type is, since the payment is
- 4:43made at the beginning of the period, so
- 4:46we need to type one. Alternatively, I
- 4:49can also write one as well in this case.
- 4:52Bracket close and enter.
- 4:54So the value comes out to be rupees
- 4:5613,310
- 4:59rupees. So if that you are investing
- 5:01rupees 10,000 rupees at an interest rate
- 5:04of 10% payment is made at the beginning
- 5:07of the period and time period is three
- 5:10years, then the future value in this in
- 5:13such scenario is rupees 13,310.
- 5:16Now let's focus on problem two. Mr.
- 5:19Rahul has invested rupees 10,000, rupees
- 5:235,000, and rupees 2,000 at the starting
- 5:27of first year,
- 5:29second year, and third year.
- 5:31Calculate the compound value of
- 5:34investment at the end of third year,
- 5:37when the interest rate is at the rate of
- 5:3910% compounded annually. Now this is a
- 5:43case of future value of series of
- 5:45unequal cash flow. Since Mr. Rahul is
- 5:48investing unequal cash flows at
- 5:50different year, first, second, and third
- 5:53year. Secondly, the investment is being
- 5:56done at the beginning of the period, at
- 5:59the beginning of first year, at the
- 6:00beginning of second year, and at the
- 6:02beginning of third year. Now, based upon
- 6:05this information, I need to calculate
- 6:07the future value.
- 6:08Now, based upon this information,
- 6:11your rate is 10%, PMT is zero, time
- 6:17period is 3 years. Now, the year first,
- 6:21second, and third, cash inflows are
- 6:23there, which is 10,000, 5,000,
- 6:27and 2,000.
- 6:30Now, future value in this scenario, if
- 6:33you could recall our session on
- 6:34compounding technique, then I have told
- 6:37you that in case of future value of
- 6:39series of unequal cash flows, we need to
- 6:42follow a long cut procedure. So, rupees
- 6:4510,000 is being invested at the
- 6:47beginning of the first year, and you
- 6:49need to calculate the future value at
- 6:51the end of the third year. So, the
- 6:54remaining time period for which rupees
- 6:5610,000 is being invested is 3 years in
- 7:00this case.
- 7:01While in the second case, it is 2 years,
- 7:04and in the last case, it is 1 year.
- 7:07So, again, I need to type equals to FV.
- 7:10The moment I type equals to FV, it is
- 7:12asking for a rate.
- 7:15Time period in in this case is
- 7:183 years minus zero, PMT, again, it is
- 7:22zero, present value in this case is
- 7:2410,000. Again, in order to get the
- 7:27positive figure, I need to put a minus
- 7:29sign over here, and then one. It comes
- 7:32out to be 13,310.
- 7:35Now, repeating the same exercise for
- 7:37year two, rupees 5,000 is being invested
- 7:40at the beginning of the 2 years. So,
- 7:43again, this amount will remain invested
- 7:45for 2 years, which means second year for
- 7:48a whole and third year for a whole. So,
- 7:50again, you need to type equals to future
- 7:52value. The rate is 10%. Just click on
- 7:56it.
- 7:57Then, NPER, which is total time period,
- 8:013 years, minus one. So, since the amount
- 8:04remained invested for the 2 years, so I
- 8:06am writing as 3 minus one, which is two.
- 8:10PMT again is zero. Present value is
- 8:13minus
- 8:145,000, which is your C15.
- 8:18Comma, since the payment is being made
- 8:20at the beginning of the period, I need
- 8:22to type one over here.
- 8:27Bracket close and enter. So, it comes
- 8:29out to be 6050.
- 8:33Same goes for year three as well.
- 8:35Equals to FV,
- 8:37rate,
- 8:39comma, time period minus one.
- 8:43I have already explained the logic
- 8:45behind this C1 two or three years minus
- 8:48one year or three years minus two year
- 8:51in my previous session of compounding
- 8:53technique. Comma, PMT is zero. Present
- 8:57value minus 2,000. So, this means it
- 8:59comes out to be 2420.
- 9:02You need to write equals to sum for
- 9:04calculating the future value.
- 9:07Just select this all the three cells and
- 9:09bracket close.
- 9:11The answer comes out to be 21,000
- 9:15780.
- 9:17Now, focus on question three.
- 9:19Mr. Rahul has invested an amount of
- 9:21rupees 10,000 each at the end of first,
- 9:24second, and third year.
- 9:26Calculate the compound value of his
- 9:28investment at the end of third year
- 9:31if the interest rate is 10% compounded
- 9:34annually.
- 9:35What will be your answer
- 9:37if the outflows are being made at the
- 9:39beginning of the period? So, this is a
- 9:41perfect case of PMT. Now, we need to
- 9:45calculate that if Mr. Rahul is investing
- 9:47rupees 10,000 each in all the three
- 9:49years.
- 9:50And that is at the end of the time
- 9:52period. I need to calculate the future
- 9:55value of series of equal cash flow or
- 9:57the future value of annuity.
- 10:00Now, this is your solution three.
- 10:02Present value in this case is zero. Time
- 10:06period is three years or I can say NPER
- 10:10is three years. Rate is 10% in this
- 10:13case. Now, PMT, which also represents
- 10:16the annuity, is minus
- 10:2010,000.
- 10:21So, I need to calculate the future
- 10:23value. Again, the formula remains the
- 10:24same. Equals to FV.
- 10:27The moment I type equals to FV, again it
- 10:30is asking for rate,
- 10:32which is 10% in this case.
- 10:34Then NPER is three.
- 10:37PMT, again you need to click on C22.
- 10:42Present value is zero.
- 10:44And since the payment is made at the end
- 10:47of the period, so I need to type zero.
- 10:50Bracket close.
- 10:51The answer comes out to be 33,000
- 10:54100. If it's a case of payment being
- 10:58made at the beginning of the period,
- 11:00then I just need to type one instead of
- 11:03zero. In such scenario, it will comes
- 11:05out to be 36,410.
- 11:08So, if the payment is made at the
- 11:10beginning of the period, your future
- 11:12value of annuity is 36,410.
- 11:16If the payment is being made at the end
- 11:18of the period, then the future value
- 11:20comes out to be 33,100.
- 11:24Now, let's focus on problem number four.
- 11:27Mr. Rahul has invested 10,000 now for
- 11:30three years at an interest rate of 10%
- 11:32per annum
- 11:34compounded semi-annually. Find how much
- 11:37amount he will get after 3 years. Now,
- 11:40this is very much similar to the
- 11:41previous question. The only difference
- 11:43is that instead of compounding annually,
- 11:46we are going compounding semi-annually.
- 11:48What will be the impact? Because of the
- 11:51semi-annual payment, it will impact your
- 11:54interest rate as well as your time
- 11:56period. So, this is your solution for
- 11:59present value in this case is minus
- 12:0110,000 since you are making the one-time
- 12:04investment.
- 12:05Year in this case is three and NPER is
- 12:10six.
- 12:11How we have calculated this NPER? We are
- 12:13saying the compounding is being made
- 12:15semi-annually. So, again, three
- 12:18multiplied by two, which comes out to be
- 12:20six. If we can say the compounding is
- 12:23being made quarterly, then it would be 3
- 12:25years multiplied by four, that would be
- 12:2812. The rate in this case is 5%. How we
- 12:31have calculated? Again, I am saying if
- 12:33the rate of interest is 10% and the
- 12:36compounding is being made semi-annually,
- 12:38so it is rate divided by two, that is
- 12:4110% divided by two, which comes out to
- 12:43be 5% in this case. PMT is zero.
- 12:47It is not a case of annuity, so it will
- 12:48be zero.
- 12:50Future value equals to FV. Again, I The
- 12:53moment I write equals to FV and I
- 12:56double-click on it, it is asking for
- 12:58rate.
- 12:59I am clicking on it, put comma, NPER.
- 13:03Mind it, there is a difference between
- 13:04NPER and year, especially in the
- 13:07numericals where compounding is being
- 13:08done either on semi-annually, monthly,
- 13:11or quarterly. PMT, again, this is not a
- 13:14numerical of PMT, so we will simply type
- 13:16zero. Present value, and the payment is
- 13:19being made at the end of the period, it
- 13:21comes out to be 13,400
- 13:24rupees
- 13:2596 paisa. In this case, the future value
- 13:28is rupees 13,400 rupees approximately.
- 13:32Now, let's focus on problem number five.
- 13:34In these problem, we will try to
- 13:36calculate the present value. We have
- 13:37already learned that in order to compute
- 13:39the present value, there is a different
- 13:41function, which is equals to present
- 13:43value. The rest component remains the
- 13:46same. So, let's focus on problem number
- 13:48five. Mr. Rahul is supposed to get
- 13:51rupees 10,000 after 3 years at a given
- 13:55discount rate of 10% per annum. Find the
- 14:00amount of investment he must make in
- 14:03order to get an amount of rupees 10,000
- 14:06after 3 years.
- 14:08Now, in this case, Mr. Rahul is supposed
- 14:10to get an amount of rupees 10,000, but
- 14:13he will get this amount after 3 years.
- 14:15Discount rate is being given, time
- 14:17period is given, and the future value is
- 14:20given, which is minus 10,000. You need
- 14:23to calculate the present value. Again,
- 14:25this is not a case of PMT, present value
- 14:28of series of equal cash flow. This is a
- 14:30perfect case of present value of a
- 14:33single cash flow. This is your solution
- 14:36five. So, future value is minus 10,000.
- 14:40Again, in order to get a positive
- 14:41present value, I need to write minus
- 14:43sign in front of future value. NPER is 3
- 14:48years. Rate is 10%. PMT is zero, and we
- 14:54need to calculate the present value. In
- 14:56order to calculate the present value, we
- 14:58need to type equals to PV. The moment I
- 15:02type equals to PV and double click on
- 15:05it, again it is asking for rate.
- 15:09Click
- 15:10on C36, {comma}
- 15:13then click on NPER,
- 15:16{comma}
- 15:17PMT, which is zero in this case,
- 15:20future value, which is minus 10,000 or
- 15:24represented by C34.
- 15:26And comma, since we need to calculate
- 15:29the amount at the end of the period, you
- 15:31need to type zero over here. The amount
- 15:34comes out to be 7,513
- 15:38rupees 15 paisa.
- 15:40So, in order to get rupees 10,000 at the
- 15:43end of 3 years where the discount rate
- 15:45is 10%, you need to invest rupees 7,513
- 15:51rupees 15 paisa as of now. Now, let's
- 15:53focus on problem number six, which talks
- 15:56about the present value of series of
- 15:59unequal cash flow. So, Mr. Rahul has to
- 16:01receive a sum of rupees 10,000,
- 16:055,000 rupees, and 2,000 rupees at the
- 16:08end of first year,
- 16:10second year, and third year from an
- 16:12investment proposal.
- 16:14Using the MS Excel, you need to
- 16:15calculate the present value of series of
- 16:18cash flows if the rate of interest or if
- 16:20the discount rate is 10%.
- 16:23Now, in this case, I need to calculate
- 16:25the present value of series of unequal
- 16:28cash flow. Solution six, in this case,
- 16:31the rate is 10%, PMT is zero, year is
- 16:35first year, second year, and third year.
- 16:39The cash inflows are 10,000, 5,000,
- 16:44and 2,000.
- 16:46So, if you can recall our discussion on
- 16:48discounting technique, I have told you
- 16:51that in case of present value of series
- 16:53of unequal cash flows, we need to follow
- 16:55a long cut procedure. If I'm saying the
- 16:58value at the end of first year is
- 17:0010,000, and I need to calculate its
- 17:03present value, so the time period is
- 17:05only one in this case.
- 17:07Similarly, if I'm saying that the values
- 17:10at the end of second year is 5,000, then
- 17:13the time period in this case is only 2
- 17:15years. While in the last case, time
- 17:17period is 3 years.
- 17:19Again, we need to calculate the present
- 17:21value. So, we need to type a function
- 17:23equals to PV and double-click on it.
- 17:27Again, it is asking for rate. NPER in
- 17:29this case is
- 17:311 year.
- 17:33PMT is zero. Future value at the end of
- 17:36first year is 10,000. So, again, I told
- 17:39you that in order to calculate the
- 17:40positive present value, I have put a
- 17:42minus sign in front of C43.
- 17:46And it is a case of zero, so I need to
- 17:49type zero over here. Bracket close. It
- 17:52comes out to be 9090.91.
- 17:55Similarly, repeat this exercise for year
- 17:57two as well. Equals to present value.
- 18:00Rate. NPER, which is 2 years.
- 18:04PMT is zero. Future value is 5,000.
- 18:10And zero.
- 18:12It comes out to be 4132.23.
- 18:16For the last year, equals to present
- 18:18value.
- 18:20Double-click on it.
- 18:22Interest rate is 10%.
- 18:25Comma.
- 18:27NPER
- 18:29is 3 years.
- 18:31Comma. PMT in this case is zero.
- 18:35Future value is minus C45. This will
- 18:39give you 1502.63.
- 18:42In order to get the total value,
- 18:45equals to sum.
- 18:49Double-click on it and select this
- 18:51series. It comes out to be 14,725
- 18:55rupees.77.
- 18:57So, the present value of series of
- 18:59unequal cash flow in this numerical is
- 19:0214,725
- 19:04rupees.77 paisa.
- 19:07Now, let's focus on problem seven,
- 19:10which is a case of present value of
- 19:11series of equal cash flow. Mr. Rahul
- 19:14wishes to calculate the present value of
- 19:16annuity
- 19:18consisting of cash inflows of rupees
- 19:2010,000 per year for 3 years. The
- 19:24discount rate is 10%.
- 19:26And you also need to calculate your
- 19:28answer if the inflows are being made at
- 19:30the beginning of the period.
- 19:32Now, in this case, this is a case of
- 19:34present value of series of equal cash
- 19:36flow. This is your solution seven. Now,
- 19:40future value in this case is zero. NPER
- 19:45is 3 years. Rate is 10%. PMT is minus
- 19:5210,000.
- 19:54Present value equals to PV. Double click
- 19:57on it. Select the rate column. Comma.
- 20:01Then NPER, again comma.
- 20:05PMT, future value, and zero. So, your
- 20:08answer comes out to be 24,868
- 20:12rupees point 52 paisa. We are assuming
- 20:15that it is being made at the end of the
- 20:17period. If it is being made at the
- 20:19beginning of the period, then instead of
- 20:21typing zero, I will type one.
- 20:23And the answer in such scenario comes
- 20:25out to be 27,355
- 20:29point 37.
- 20:31Now, let's focus on the last problem,
- 20:33problem number eight. Mr. X deposit
- 20:36rupees 5 lakh on his retirement in a
- 20:39bank on which the interest rate is
- 20:41provided at a rate of 10% per annum.
- 20:45How much money in equal installment can
- 20:47be withdrawn annually for a period of 10
- 20:49years?
- 20:50Now, this is a case of annuity. I need
- 20:52to calculate the PMT in this case. So,
- 20:56present value is 5 lakh rupees, number
- 20:58one.
- 20:59Time period is 10.
- 21:02Interest rate is 10%. Based upon this
- 21:05information, I need to type a separate
- 21:08Excel function and the Excel function is
- 21:10equals to PMT. So, future value is zero,
- 21:15NPER is 10. Now, in this case, present
- 21:19value is minus 5 lakh, NPER is 10 years,
- 21:25rate is 10%.
- 21:27So, I need to select a percentage over
- 21:30here
- 21:31and then write
- 21:3310. And we need to calculate the PMT.
- 21:36Equals to PMT.
- 21:39The moment you double click on PMT,
- 21:41again it is asking for a
- 21:44comma, NPER, which is 10 in this case.
- 21:49Present value,
- 21:51which is minus 5 lakh.
- 21:53Future value is optional.
- 21:56And this is also optional. The amount
- 21:57comes out to be 81,372
- 22:00rupees, 70 paisa. It means that if Mr. X
- 22:05wishes to deposit rupees 5 lakh on his
- 22:08retirement, then he must the equal
- 22:11installment which must be withdrawn is
- 22:13equals to 81,372
- 22:16rupees, 70 paisa.
- 22:18So, that's all from my side. We'll meet
- 22:21you in my next session covering a new
- 22:23concept with a new series. Have a nice
- 22:25day.
About this transcript
This page contains the full transcript of Time Value of Money Using MS Excel | Part-IV | Dr. Rishabh Gupta by GYAN PRASAR KENDRA , generated from the public captions YouTube serves with the video. The transcript has 3,141 words across 499 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.
What you can do with it
Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.
Free YouTube transcript tool
YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.