2 This worksheet was designed to teach you how to work TVM, Stock and Bond problems and equations in Excel Worksheet Instructions This is stool to help you learn how to enter formules into extel to solve TVM problems. Since the goal is for you to learn the formulas and how to enter ther, I have given you the answers. What I want you to co is to keep entering formules until you get the correct answer our ser should match mine! So, in the firat part at each section, I give you several samples of problems and how to solve them. Then in the second part, I give problems for you to solve. I'm providing the answer, sa you know if you get it right ar nat. If you did not get the correct answer, try again. You will begradador the formula that you have entered. Please fill in the standard formars, the enter the formule in the bluccell. Enter each formula manually do NOT cut and paste formule from other spreadsheets or from my examples. The whole purpose of this exercise is for you to learn how to manually enter the formulas, 50 that you will be able to do it on theesar. There will be several TV problems an exams The more you practice, the easier it gets Section 1: Solving TVM problems for Single Amounts - Use this method for Savings Accounts, Growth Rates, Interest Rates, Inflation Rates Things to know Standard Format NT FIRIN Time Value of Money Formulas *One of the inputs needs to be negative look at the formulas below RATE YO Future Value =FV (rata, npat, prt, pol generally, when you pay morey, enter as negative, when you receive money, enter as positive PV Present Value =PV (ratu, pui, prt 1) "When entering the RATE-dont forget to enter the sign PMI Rate = RATE Inper pet, pw. M *Therare NO payments for single amount problems, FV Number of periods - NPERI, p.w. the interest goes back into the account cach period, this is called compounding Forma harul Perymer - PMT (sh, npr, py, ) "Once you star.ortering the formula, a pop up box will appear showing you the formula When the pop-up box appears you can also click thefe in the tap 1 har for help entering farmi.las Examples 9 10 11 12 13 14 15 The focure value of $100 received today and depeshed at percentury The gresent we od 100 10 be received 10 years tramid, portunity to The contendis 5. the end years ago war $1.05, compute the powth inte with an interest of 3.5%, how bigwili wake form $500 Investment in triple NPER IN RATE PV PM FV Campul 16 17 18 19 20 21 22 23 24 25 26 27 28 29 4 65 100 U 2 $126.25 =FVD18,1217 -019) ar FW16%, 4, 0, -1001 NPLE (NI 10 RATELY % 94 PV ? PM] 0 0 FV 100 compaale? $42.24 = PUG18.6:17 (21) -.PW (9%, 10, 0, 100) NPORN 6 RATHY ) 7 PV 1.9S PI 0 FV 5 Cate? 16.99% =RATE(K17.-1961) =RATE (6,0-1.95, 5) NPUR (N! ? RATE (1) 3.54 PV 500 PMI 0 FV 1500 Computer 31.94 =NPERIN 18. -N19 N21) =NPER (3.5%, 0, 500, 1500) J>> DO NOT OUT & PASTE FORMULAS - ENTER THEM MANUALLY 21 ore 22 The compounded 23 martesile 24 pery 25 26 27 28 29 30 31 32 33 How long would it take for you to save $1,000,000 if you deposit $40,000 per year into an account beginning today that pays 12 percent interest per anum? A new house interest you. To buy it, you will have to borrow $145,000 and you want to make payments once a year. Your mortgage goes through for a thirty year loan with an interest rate of 5.9% APR. What will your annual payments be? 34 35 36 NPER I'Y (Race) PV PMT FV Node. Faraloon the freedos y Because the burles you the money in the bio In the wall day them NPER I'Y (Rnte) PV I'MT FV 27 General Info - TVM General Info - Bonds Part 1 Part 2 Formula Bar B D E F G H T J ? NPER L'Y (Rate) PV I'MT EV Hote: For a loan the price isted as P Because the rise in the Vam wilhely arend 32 33 34 35 36 37 38 39 40 41 NPER LY (Rate) PV PMT FV |Compile Pris CPT (Computc)? Company Puri CPT (Compute)? A company has common stock that pays dividends each year. The dividend five years ago was $3.45. The expected dividend is $4.92. What is the growth rate of the dividends? 42 43 Marc has purchased a new car for $15,000. He paid $2,500 as a down payment and he paid the balance by a loan from his hometown bank. The loan is to be paid on a monthly basis for two years charging 6.9% percent interest. How much are the monthly payments? 44 45 46 47 48 5 ? NPER L'Y (Rate) PV PMT NPER LY (Rate) PV MT FV |Cauwenig Pries CPT (Compute)? FV Corpusing Furious CPT (Compute) 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Find the present value of the following stream of cash flows, assuming that the firm's opportunity cost is 7.5%. What is the future value of the same stream of cash flows, assuming that the firm's opportunity cost is 5.5%. Year Amount INDER INPE General Info - TVM General Info - Bonds Part 1 Part 2 + B C D E F H 5 Year 1 ? 2 2 INPER L'Y (Rule) PV PMT FV Corning Price CPT (Camule) Amount 10,000 23,295 35,000 24,505 25,000 3 NPER 1:Y (Ralo) PV PMT FY Cepende CPT (Compute)? 4 ? 5 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 To expand its business, the Kingston Outlet factory would like to issue a bond with par value of $1,000, coupon rate of 10 percent, and maturity of 10 years from now. What is the value of the bond if the required rate of return is a) 8 percent, b) 10 percent, and c) 12 percent? Vino Corp issued bonds bearing a coupon rate of 12%, paying coupons semi-annually The bonds have 3 years remaining to maturity and are currently priced at $940 per bond. What is the yield to maturity for the bonds? 8% 10% 12% % NPER RATEC PV Coupon Paicel PMT RATE ( Mark Rule PV Muri) FV Can Pin NPER RATE (Cep |PV . PMT RATE Mkut Rule) PV Market FV Gradin Paris Compule! 82 83 ? 84 85 86 87 Compute 88 89 90 91 92 93 94 95 General Info - TVM General Info - Bonds Part 1 Part 2 + There is a mistake on the Assignment 2-A spreadsheet. For the question: Ken has just inherited $6,200. He would like to use this money to buy his mom Hayley a new scooter costing $7,000 two years from now. He deposits his money in an account paying 7.2% interest compounded semi-annually, but he needs to know if this generate enough money for him to buy the scooter? How much money will Ken have in two years? There is a "?" in the cell for PV. It should be in the FV cell, as this is a Future Value Question. 2 This worksheet was designed to teach you how to work TVM, Stock and Bond problems and equations in Excel Worksheet Instructions This is stool to help you learn how to enter formules into extel to solve TVM problems. Since the goal is for you to learn the formulas and how to enter ther, I have given you the answers. What I want you to co is to keep entering formules until you get the correct answer our ser should match mine! So, in the firat part at each section, I give you several samples of problems and how to solve them. Then in the second part, I give problems for you to solve. I'm providing the answer, sa you know if you get it right ar nat. If you did not get the correct answer, try again. You will begradador the formula that you have entered. Please fill in the standard formars, the enter the formule in the bluccell. Enter each formula manually do NOT cut and paste formule from other spreadsheets or from my examples. The whole purpose of this exercise is for you to learn how to manually enter the formulas, 50 that you will be able to do it on theesar. There will be several TV problems an exams The more you practice, the easier it gets Section 1: Solving TVM problems for Single Amounts - Use this method for Savings Accounts, Growth Rates, Interest Rates, Inflation Rates Things to know Standard Format NT FIRIN Time Value of Money Formulas *One of the inputs needs to be negative look at the formulas below RATE YO Future Value =FV (rata, npat, prt, pol generally, when you pay morey, enter as negative, when you receive money, enter as positive PV Present Value =PV (ratu, pui, prt 1) "When entering the RATE-dont forget to enter the sign PMI Rate = RATE Inper pet, pw. M *Therare NO payments for single amount problems, FV Number of periods - NPERI, p.w. the interest goes back into the account cach period, this is called compounding Forma harul Perymer - PMT (sh, npr, py, ) "Once you star.ortering the formula, a pop up box will appear showing you the formula When the pop-up box appears you can also click thefe in the tap 1 har for help entering farmi.las Examples 9 10 11 12 13 14 15 The focure value of $100 received today and depeshed at percentury The gresent we od 100 10 be received 10 years tramid, portunity to The contendis 5. the end years ago war $1.05, compute the powth inte with an interest of 3.5%, how bigwili wake form $500 Investment in triple NPER IN RATE PV PM FV Campul 16 17 18 19 20 21 22 23 24 25 26 27 28 29 4 65 100 U 2 $126.25 =FVD18,1217 -019) ar FW16%, 4, 0, -1001 NPLE (NI 10 RATELY % 94 PV ? PM] 0 0 FV 100 compaale? $42.24 = PUG18.6:17 (21) -.PW (9%, 10, 0, 100) NPORN 6 RATHY ) 7 PV 1.9S PI 0 FV 5 Cate? 16.99% =RATE(K17.-1961) =RATE (6,0-1.95, 5) NPUR (N! ? RATE (1) 3.54 PV 500 PMI 0 FV 1500 Computer 31.94 =NPERIN 18. -N19 N21) =NPER (3.5%, 0, 500, 1500) J>> DO NOT OUT & PASTE FORMULAS - ENTER THEM MANUALLY 21 ore 22 The compounded 23 martesile 24 pery 25 26 27 28 29 30 31 32 33 How long would it take for you to save $1,000,000 if you deposit $40,000 per year into an account beginning today that pays 12 percent interest per anum? A new house interest you. To buy it, you will have to borrow $145,000 and you want to make payments once a year. Your mortgage goes through for a thirty year loan with an interest rate of 5.9% APR. What will your annual payments be? 34 35 36 NPER I'Y (Race) PV PMT FV Node. Faraloon the freedos y Because the burles you the money in the bio In the wall day them NPER I'Y (Rnte) PV I'MT FV 27 General Info - TVM General Info - Bonds Part 1 Part 2 Formula Bar B D E F G H T J ? NPER L'Y (Rate) PV I'MT EV Hote: For a loan the price isted as P Because the rise in the Vam wilhely arend 32 33 34 35 36 37 38 39 40 41 NPER LY (Rate) PV PMT FV |Compile Pris CPT (Computc)? Company Puri CPT (Compute)? A company has common stock that pays dividends each year. The dividend five years ago was $3.45. The expected dividend is $4.92. What is the growth rate of the dividends? 42 43 Marc has purchased a new car for $15,000. He paid $2,500 as a down payment and he paid the balance by a loan from his hometown bank. The loan is to be paid on a monthly basis for two years charging 6.9% percent interest. How much are the monthly payments? 44 45 46 47 48 5 ? NPER L'Y (Rate) PV PMT NPER LY (Rate) PV MT FV |Cauwenig Pries CPT (Compute)? FV Corpusing Furious CPT (Compute) 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Find the present value of the following stream of cash flows, assuming that the firm's opportunity cost is 7.5%. What is the future value of the same stream of cash flows, assuming that the firm's opportunity cost is 5.5%. Year Amount INDER INPE General Info - TVM General Info - Bonds Part 1 Part 2 + B C D E F H 5 Year 1 ? 2 2 INPER L'Y (Rule) PV PMT FV Corning Price CPT (Camule) Amount 10,000 23,295 35,000 24,505 25,000 3 NPER 1:Y (Ralo) PV PMT FY Cepende CPT (Compute)? 4 ? 5 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 To expand its business, the Kingston Outlet factory would like to issue a bond with par value of $1,000, coupon rate of 10 percent, and maturity of 10 years from now. What is the value of the bond if the required rate of return is a) 8 percent, b) 10 percent, and c) 12 percent? Vino Corp issued bonds bearing a coupon rate of 12%, paying coupons semi-annually The bonds have 3 years remaining to maturity and are currently priced at $940 per bond. What is the yield to maturity for the bonds? 8% 10% 12% % NPER RATEC PV Coupon Paicel PMT RATE ( Mark Rule PV Muri) FV Can Pin NPER RATE (Cep |PV . PMT RATE Mkut Rule) PV Market FV Gradin Paris Compule! 82 83 ? 84 85 86 87 Compute 88 89 90 91 92 93 94 95 General Info - TVM General Info - Bonds Part 1 Part 2 + There is a mistake on the Assignment 2-A spreadsheet. For the question: Ken has just inherited $6,200. He would like to use this money to buy his mom Hayley a new scooter costing $7,000 two years from now. He deposits his money in an account paying 7.2% interest compounded semi-annually, but he needs to know if this generate enough money for him to buy the scooter? How much money will Ken have in two years? There is a "?" in the cell for PV. It should be in the FV cell, as this is a Future Value