YouTube2Text

Time Value of Money Using MS Excel | Part-IV | Dr. Rishabh Gupta — Transcript

by GYAN PRASAR KENDRA · 3,141 words · 499 segments · language en · Watch on YouTube

Full transcript

  1. 0:00afternoon to one and all. Myself, Dr.
  2. 0:02Rishabh Gupta.
  3. 0:04And today's session we will be
  4. 0:06understanding the MS Excel application
  5. 0:08on time value of money.
  6. 0:11So, after understanding the basic
  7. 0:12concepts of time value of money
  8. 0:15involving compounding and discounting
  9. 0:18techniques, we shall now focus upon that
  10. 0:21how we can solve these numericals based
  11. 0:24upon these techniques using Microsoft
  12. 0:27Excel.
  13. 0:28So, here we begin.
  14. 0:30In order to calculate the future value
  15. 0:33of single cash flows,
  16. 0:35series of equal cash flows, or series of
  17. 0:38unequal cash flows, there is a separate
  18. 0:41Excel function in Microsoft which is
  19. 0:44given by future value function is equals
  20. 0:47to
  21. 0:48FV
  22. 0:49rate NPER
  23. 0:52, PMT
  24. 0:54, PV , type.
  25. 0:57Here FV represents the future value. The
  26. 1:00rate represents the rate of interest.
  27. 1:03NPER represents the number of period for
  28. 1:07an investment based upon the interest
  29. 1:10rate. PMT is used in case of annuity.
  30. 1:13So, if the cash flows are in the form of
  31. 1:15a annuity, then we will use PMT. So, it
  32. 1:19is used in case of constant payments to
  33. 1:22be made and at a constant interest rate.
  34. 1:25Here PV represents the present value of
  35. 1:28investment. Type zero means if the
  36. 1:31payment is made at the end of the
  37. 1:33period, while type one means if the
  38. 1:36payment is made at the beginning of the
  39. 1:38period. Also, the double bracket and
  40. 1:42this present value and type represents
  41. 1:45that these values are optional.
  42. 1:47Further, in order to get a positive
  43. 1:50value of future value function, we must
  44. 1:53put a negative sign in front of PV in
  45. 1:56the function.
  46. 1:57Same is for the present value function
  47. 1:59as well. In MS Excel, we just need to
  48. 2:02type equals to PV. The moment I type
  49. 2:06equals to PV, then it automatically ask
  50. 2:10for rate, NPER,
  51. 2:13PMT, future value, and type.
  52. 2:17And the last function is PMT function.
  53. 2:19If we wish to calculate the annuity
  54. 2:21payments, then it will be equals to PMT.
  55. 2:25Again, the moment I type equals to PMT,
  56. 2:29then it will ask for a rate,
  57. 2:31comma NPER, comma present value, or
  58. 2:35future value, or type.
  59. 2:38So, let's first practice two numericals
  60. 2:40based upon this MS Excel Excel function.
  61. 2:43Problem one.
  62. 2:45Mr. Rahul has invested a sum of rupees
  63. 2:4810,000 at the beginning of the year at
  64. 2:52an interest rate of 10% compounded
  65. 2:56annually.
  66. 2:57How much amount will he get after the
  67. 3:00end of 3 years?
  68. 3:01So, in this case, we need to calculate
  69. 3:04that what is the future value of rupees
  70. 3:0610,000, which are invested at the
  71. 3:10beginning of the year, and the rate of
  72. 3:12interest is 10% compounded annually. So,
  73. 3:16this is your solution one. In this case,
  74. 3:19present value
  75. 3:23is minus 10,000.
  76. 3:26As I already told you that in order to
  77. 3:28get a positive future value, we must put
  78. 3:31a minus sign in front of present value.
  79. 3:35Also, one thing we should know that
  80. 3:37while writing the figure in the against
  81. 3:39the present value, we must avoid putting
  82. 3:42a comma. In this case, the time period
  83. 3:45NPER is three. The rate of interest is
  84. 3:5010% since this is not the case of PMT,
  85. 3:53so it will be considered as zero.
  86. 3:57And type, since we are making the
  87. 3:59investment at the beginning of the
  88. 4:00period, so it is type one. Again, the
  89. 4:04function or MS Excel function for future
  90. 4:06value is equals to FV. The moment I
  91. 4:11write equals to FV and double click on
  92. 4:14it, then it will ask for rate of
  93. 4:16interest, which is 10% in this case.
  94. 4:20Just click on 10%. Put comma. Then it is
  95. 4:24asking for NPER, which is three.
  96. 4:28PMT, which is zero.
  97. 4:32Then present value, again
  98. 4:35it is minus 10,000, so I am just need to
  99. 4:37click on this cell C2.
  100. 4:41And the type is, since the payment is
  101. 4:43made at the beginning of the period, so
  102. 4:46we need to type one. Alternatively, I
  103. 4:49can also write one as well in this case.
  104. 4:52Bracket close and enter.
  105. 4:54So the value comes out to be rupees
  106. 4:5613,310
  107. 4:59rupees. So if that you are investing
  108. 5:01rupees 10,000 rupees at an interest rate
  109. 5:04of 10% payment is made at the beginning
  110. 5:07of the period and time period is three
  111. 5:10years, then the future value in this in
  112. 5:13such scenario is rupees 13,310.
  113. 5:16Now let's focus on problem two. Mr.
  114. 5:19Rahul has invested rupees 10,000, rupees
  115. 5:235,000, and rupees 2,000 at the starting
  116. 5:27of first year,
  117. 5:29second year, and third year.
  118. 5:31Calculate the compound value of
  119. 5:34investment at the end of third year,
  120. 5:37when the interest rate is at the rate of
  121. 5:3910% compounded annually. Now this is a
  122. 5:43case of future value of series of
  123. 5:45unequal cash flow. Since Mr. Rahul is
  124. 5:48investing unequal cash flows at
  125. 5:50different year, first, second, and third
  126. 5:53year. Secondly, the investment is being
  127. 5:56done at the beginning of the period, at
  128. 5:59the beginning of first year, at the
  129. 6:00beginning of second year, and at the
  130. 6:02beginning of third year. Now, based upon
  131. 6:05this information, I need to calculate
  132. 6:07the future value.
  133. 6:08Now, based upon this information,
  134. 6:11your rate is 10%, PMT is zero, time
  135. 6:17period is 3 years. Now, the year first,
  136. 6:21second, and third, cash inflows are
  137. 6:23there, which is 10,000, 5,000,
  138. 6:27and 2,000.
  139. 6:30Now, future value in this scenario, if
  140. 6:33you could recall our session on
  141. 6:34compounding technique, then I have told
  142. 6:37you that in case of future value of
  143. 6:39series of unequal cash flows, we need to
  144. 6:42follow a long cut procedure. So, rupees
  145. 6:4510,000 is being invested at the
  146. 6:47beginning of the first year, and you
  147. 6:49need to calculate the future value at
  148. 6:51the end of the third year. So, the
  149. 6:54remaining time period for which rupees
  150. 6:5610,000 is being invested is 3 years in
  151. 7:00this case.
  152. 7:01While in the second case, it is 2 years,
  153. 7:04and in the last case, it is 1 year.
  154. 7:07So, again, I need to type equals to FV.
  155. 7:10The moment I type equals to FV, it is
  156. 7:12asking for a rate.
  157. 7:15Time period in in this case is
  158. 7:183 years minus zero, PMT, again, it is
  159. 7:22zero, present value in this case is
  160. 7:2410,000. Again, in order to get the
  161. 7:27positive figure, I need to put a minus
  162. 7:29sign over here, and then one. It comes
  163. 7:32out to be 13,310.
  164. 7:35Now, repeating the same exercise for
  165. 7:37year two, rupees 5,000 is being invested
  166. 7:40at the beginning of the 2 years. So,
  167. 7:43again, this amount will remain invested
  168. 7:45for 2 years, which means second year for
  169. 7:48a whole and third year for a whole. So,
  170. 7:50again, you need to type equals to future
  171. 7:52value. The rate is 10%. Just click on
  172. 7:56it.
  173. 7:57Then, NPER, which is total time period,
  174. 8:013 years, minus one. So, since the amount
  175. 8:04remained invested for the 2 years, so I
  176. 8:06am writing as 3 minus one, which is two.
  177. 8:10PMT again is zero. Present value is
  178. 8:13minus
  179. 8:145,000, which is your C15.
  180. 8:18Comma, since the payment is being made
  181. 8:20at the beginning of the period, I need
  182. 8:22to type one over here.
  183. 8:27Bracket close and enter. So, it comes
  184. 8:29out to be 6050.
  185. 8:33Same goes for year three as well.
  186. 8:35Equals to FV,
  187. 8:37rate,
  188. 8:39comma, time period minus one.
  189. 8:43I have already explained the logic
  190. 8:45behind this C1 two or three years minus
  191. 8:48one year or three years minus two year
  192. 8:51in my previous session of compounding
  193. 8:53technique. Comma, PMT is zero. Present
  194. 8:57value minus 2,000. So, this means it
  195. 8:59comes out to be 2420.
  196. 9:02You need to write equals to sum for
  197. 9:04calculating the future value.
  198. 9:07Just select this all the three cells and
  199. 9:09bracket close.
  200. 9:11The answer comes out to be 21,000
  201. 9:15780.
  202. 9:17Now, focus on question three.
  203. 9:19Mr. Rahul has invested an amount of
  204. 9:21rupees 10,000 each at the end of first,
  205. 9:24second, and third year.
  206. 9:26Calculate the compound value of his
  207. 9:28investment at the end of third year
  208. 9:31if the interest rate is 10% compounded
  209. 9:34annually.
  210. 9:35What will be your answer
  211. 9:37if the outflows are being made at the
  212. 9:39beginning of the period? So, this is a
  213. 9:41perfect case of PMT. Now, we need to
  214. 9:45calculate that if Mr. Rahul is investing
  215. 9:47rupees 10,000 each in all the three
  216. 9:49years.
  217. 9:50And that is at the end of the time
  218. 9:52period. I need to calculate the future
  219. 9:55value of series of equal cash flow or
  220. 9:57the future value of annuity.
  221. 10:00Now, this is your solution three.
  222. 10:02Present value in this case is zero. Time
  223. 10:06period is three years or I can say NPER
  224. 10:10is three years. Rate is 10% in this
  225. 10:13case. Now, PMT, which also represents
  226. 10:16the annuity, is minus
  227. 10:2010,000.
  228. 10:21So, I need to calculate the future
  229. 10:23value. Again, the formula remains the
  230. 10:24same. Equals to FV.
  231. 10:27The moment I type equals to FV, again it
  232. 10:30is asking for rate,
  233. 10:32which is 10% in this case.
  234. 10:34Then NPER is three.
  235. 10:37PMT, again you need to click on C22.
  236. 10:42Present value is zero.
  237. 10:44And since the payment is made at the end
  238. 10:47of the period, so I need to type zero.
  239. 10:50Bracket close.
  240. 10:51The answer comes out to be 33,000
  241. 10:54100. If it's a case of payment being
  242. 10:58made at the beginning of the period,
  243. 11:00then I just need to type one instead of
  244. 11:03zero. In such scenario, it will comes
  245. 11:05out to be 36,410.
  246. 11:08So, if the payment is made at the
  247. 11:10beginning of the period, your future
  248. 11:12value of annuity is 36,410.
  249. 11:16If the payment is being made at the end
  250. 11:18of the period, then the future value
  251. 11:20comes out to be 33,100.
  252. 11:24Now, let's focus on problem number four.
  253. 11:27Mr. Rahul has invested 10,000 now for
  254. 11:30three years at an interest rate of 10%
  255. 11:32per annum
  256. 11:34compounded semi-annually. Find how much
  257. 11:37amount he will get after 3 years. Now,
  258. 11:40this is very much similar to the
  259. 11:41previous question. The only difference
  260. 11:43is that instead of compounding annually,
  261. 11:46we are going compounding semi-annually.
  262. 11:48What will be the impact? Because of the
  263. 11:51semi-annual payment, it will impact your
  264. 11:54interest rate as well as your time
  265. 11:56period. So, this is your solution for
  266. 11:59present value in this case is minus
  267. 12:0110,000 since you are making the one-time
  268. 12:04investment.
  269. 12:05Year in this case is three and NPER is
  270. 12:10six.
  271. 12:11How we have calculated this NPER? We are
  272. 12:13saying the compounding is being made
  273. 12:15semi-annually. So, again, three
  274. 12:18multiplied by two, which comes out to be
  275. 12:20six. If we can say the compounding is
  276. 12:23being made quarterly, then it would be 3
  277. 12:25years multiplied by four, that would be
  278. 12:2812. The rate in this case is 5%. How we
  279. 12:31have calculated? Again, I am saying if
  280. 12:33the rate of interest is 10% and the
  281. 12:36compounding is being made semi-annually,
  282. 12:38so it is rate divided by two, that is
  283. 12:4110% divided by two, which comes out to
  284. 12:43be 5% in this case. PMT is zero.
  285. 12:47It is not a case of annuity, so it will
  286. 12:48be zero.
  287. 12:50Future value equals to FV. Again, I The
  288. 12:53moment I write equals to FV and I
  289. 12:56double-click on it, it is asking for
  290. 12:58rate.
  291. 12:59I am clicking on it, put comma, NPER.
  292. 13:03Mind it, there is a difference between
  293. 13:04NPER and year, especially in the
  294. 13:07numericals where compounding is being
  295. 13:08done either on semi-annually, monthly,
  296. 13:11or quarterly. PMT, again, this is not a
  297. 13:14numerical of PMT, so we will simply type
  298. 13:16zero. Present value, and the payment is
  299. 13:19being made at the end of the period, it
  300. 13:21comes out to be 13,400
  301. 13:24rupees
  302. 13:2596 paisa. In this case, the future value
  303. 13:28is rupees 13,400 rupees approximately.
  304. 13:32Now, let's focus on problem number five.
  305. 13:34In these problem, we will try to
  306. 13:36calculate the present value. We have
  307. 13:37already learned that in order to compute
  308. 13:39the present value, there is a different
  309. 13:41function, which is equals to present
  310. 13:43value. The rest component remains the
  311. 13:46same. So, let's focus on problem number
  312. 13:48five. Mr. Rahul is supposed to get
  313. 13:51rupees 10,000 after 3 years at a given
  314. 13:55discount rate of 10% per annum. Find the
  315. 14:00amount of investment he must make in
  316. 14:03order to get an amount of rupees 10,000
  317. 14:06after 3 years.
  318. 14:08Now, in this case, Mr. Rahul is supposed
  319. 14:10to get an amount of rupees 10,000, but
  320. 14:13he will get this amount after 3 years.
  321. 14:15Discount rate is being given, time
  322. 14:17period is given, and the future value is
  323. 14:20given, which is minus 10,000. You need
  324. 14:23to calculate the present value. Again,
  325. 14:25this is not a case of PMT, present value
  326. 14:28of series of equal cash flow. This is a
  327. 14:30perfect case of present value of a
  328. 14:33single cash flow. This is your solution
  329. 14:36five. So, future value is minus 10,000.
  330. 14:40Again, in order to get a positive
  331. 14:41present value, I need to write minus
  332. 14:43sign in front of future value. NPER is 3
  333. 14:48years. Rate is 10%. PMT is zero, and we
  334. 14:54need to calculate the present value. In
  335. 14:56order to calculate the present value, we
  336. 14:58need to type equals to PV. The moment I
  337. 15:02type equals to PV and double click on
  338. 15:05it, again it is asking for rate.
  339. 15:09Click
  340. 15:10on C36, {comma}
  341. 15:13then click on NPER,
  342. 15:16{comma}
  343. 15:17PMT, which is zero in this case,
  344. 15:20future value, which is minus 10,000 or
  345. 15:24represented by C34.
  346. 15:26And comma, since we need to calculate
  347. 15:29the amount at the end of the period, you
  348. 15:31need to type zero over here. The amount
  349. 15:34comes out to be 7,513
  350. 15:38rupees 15 paisa.
  351. 15:40So, in order to get rupees 10,000 at the
  352. 15:43end of 3 years where the discount rate
  353. 15:45is 10%, you need to invest rupees 7,513
  354. 15:51rupees 15 paisa as of now. Now, let's
  355. 15:53focus on problem number six, which talks
  356. 15:56about the present value of series of
  357. 15:59unequal cash flow. So, Mr. Rahul has to
  358. 16:01receive a sum of rupees 10,000,
  359. 16:055,000 rupees, and 2,000 rupees at the
  360. 16:08end of first year,
  361. 16:10second year, and third year from an
  362. 16:12investment proposal.
  363. 16:14Using the MS Excel, you need to
  364. 16:15calculate the present value of series of
  365. 16:18cash flows if the rate of interest or if
  366. 16:20the discount rate is 10%.
  367. 16:23Now, in this case, I need to calculate
  368. 16:25the present value of series of unequal
  369. 16:28cash flow. Solution six, in this case,
  370. 16:31the rate is 10%, PMT is zero, year is
  371. 16:35first year, second year, and third year.
  372. 16:39The cash inflows are 10,000, 5,000,
  373. 16:44and 2,000.
  374. 16:46So, if you can recall our discussion on
  375. 16:48discounting technique, I have told you
  376. 16:51that in case of present value of series
  377. 16:53of unequal cash flows, we need to follow
  378. 16:55a long cut procedure. If I'm saying the
  379. 16:58value at the end of first year is
  380. 17:0010,000, and I need to calculate its
  381. 17:03present value, so the time period is
  382. 17:05only one in this case.
  383. 17:07Similarly, if I'm saying that the values
  384. 17:10at the end of second year is 5,000, then
  385. 17:13the time period in this case is only 2
  386. 17:15years. While in the last case, time
  387. 17:17period is 3 years.
  388. 17:19Again, we need to calculate the present
  389. 17:21value. So, we need to type a function
  390. 17:23equals to PV and double-click on it.
  391. 17:27Again, it is asking for rate. NPER in
  392. 17:29this case is
  393. 17:311 year.
  394. 17:33PMT is zero. Future value at the end of
  395. 17:36first year is 10,000. So, again, I told
  396. 17:39you that in order to calculate the
  397. 17:40positive present value, I have put a
  398. 17:42minus sign in front of C43.
  399. 17:46And it is a case of zero, so I need to
  400. 17:49type zero over here. Bracket close. It
  401. 17:52comes out to be 9090.91.
  402. 17:55Similarly, repeat this exercise for year
  403. 17:57two as well. Equals to present value.
  404. 18:00Rate. NPER, which is 2 years.
  405. 18:04PMT is zero. Future value is 5,000.
  406. 18:10And zero.
  407. 18:12It comes out to be 4132.23.
  408. 18:16For the last year, equals to present
  409. 18:18value.
  410. 18:20Double-click on it.
  411. 18:22Interest rate is 10%.
  412. 18:25Comma.
  413. 18:27NPER
  414. 18:29is 3 years.
  415. 18:31Comma. PMT in this case is zero.
  416. 18:35Future value is minus C45. This will
  417. 18:39give you 1502.63.
  418. 18:42In order to get the total value,
  419. 18:45equals to sum.
  420. 18:49Double-click on it and select this
  421. 18:51series. It comes out to be 14,725
  422. 18:55rupees.77.
  423. 18:57So, the present value of series of
  424. 18:59unequal cash flow in this numerical is
  425. 19:0214,725
  426. 19:04rupees.77 paisa.
  427. 19:07Now, let's focus on problem seven,
  428. 19:10which is a case of present value of
  429. 19:11series of equal cash flow. Mr. Rahul
  430. 19:14wishes to calculate the present value of
  431. 19:16annuity
  432. 19:18consisting of cash inflows of rupees
  433. 19:2010,000 per year for 3 years. The
  434. 19:24discount rate is 10%.
  435. 19:26And you also need to calculate your
  436. 19:28answer if the inflows are being made at
  437. 19:30the beginning of the period.
  438. 19:32Now, in this case, this is a case of
  439. 19:34present value of series of equal cash
  440. 19:36flow. This is your solution seven. Now,
  441. 19:40future value in this case is zero. NPER
  442. 19:45is 3 years. Rate is 10%. PMT is minus
  443. 19:5210,000.
  444. 19:54Present value equals to PV. Double click
  445. 19:57on it. Select the rate column. Comma.
  446. 20:01Then NPER, again comma.
  447. 20:05PMT, future value, and zero. So, your
  448. 20:08answer comes out to be 24,868
  449. 20:12rupees point 52 paisa. We are assuming
  450. 20:15that it is being made at the end of the
  451. 20:17period. If it is being made at the
  452. 20:19beginning of the period, then instead of
  453. 20:21typing zero, I will type one.
  454. 20:23And the answer in such scenario comes
  455. 20:25out to be 27,355
  456. 20:29point 37.
  457. 20:31Now, let's focus on the last problem,
  458. 20:33problem number eight. Mr. X deposit
  459. 20:36rupees 5 lakh on his retirement in a
  460. 20:39bank on which the interest rate is
  461. 20:41provided at a rate of 10% per annum.
  462. 20:45How much money in equal installment can
  463. 20:47be withdrawn annually for a period of 10
  464. 20:49years?
  465. 20:50Now, this is a case of annuity. I need
  466. 20:52to calculate the PMT in this case. So,
  467. 20:56present value is 5 lakh rupees, number
  468. 20:58one.
  469. 20:59Time period is 10.
  470. 21:02Interest rate is 10%. Based upon this
  471. 21:05information, I need to type a separate
  472. 21:08Excel function and the Excel function is
  473. 21:10equals to PMT. So, future value is zero,
  474. 21:15NPER is 10. Now, in this case, present
  475. 21:19value is minus 5 lakh, NPER is 10 years,
  476. 21:25rate is 10%.
  477. 21:27So, I need to select a percentage over
  478. 21:30here
  479. 21:31and then write
  480. 21:3310. And we need to calculate the PMT.
  481. 21:36Equals to PMT.
  482. 21:39The moment you double click on PMT,
  483. 21:41again it is asking for a
  484. 21:44comma, NPER, which is 10 in this case.
  485. 21:49Present value,
  486. 21:51which is minus 5 lakh.
  487. 21:53Future value is optional.
  488. 21:56And this is also optional. The amount
  489. 21:57comes out to be 81,372
  490. 22:00rupees, 70 paisa. It means that if Mr. X
  491. 22:05wishes to deposit rupees 5 lakh on his
  492. 22:08retirement, then he must the equal
  493. 22:11installment which must be withdrawn is
  494. 22:13equals to 81,372
  495. 22:16rupees, 70 paisa.
  496. 22:18So, that's all from my side. We'll meet
  497. 22:21you in my next session covering a new
  498. 22:23concept with a new series. Have a nice
  499. 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.