( ( N C L 2 This worksheet was designed to teach you how to work TVM, Stock and Bond problems and equations in Excel Worksheet Instructions: This is a tool to help you leam how to enter formulas into excel to solve TVM problems. Since the goal is for you to learn the formulas and how to enter them, I have given you the answers. What I want you to do is to keep entering formulas until you get the correct answer. (Your answer should match mine). So, in the first part of each section, I give you several examples of problems and how to solve them. Then in the second part, I give problems for you to solve. I'm providing the answer, so you know if you got it right o not. If you did not get the correct answer, try again. You will be graded on the formula that you have entered. Please fill in the standard formats, then enter the formula in the blue cell. Enter each formula manually, do NOT cut and paste formulas from other spreadsheets or 3 from my examples. The whole purpose of this exercise is for you to learn how to manually enter the formulas, so that you will be able to do it on the exam. There will be several TVM problems on exams. The more you practice, the easier it gets. 388888 8 CONDOREN 6567 10 12 13 14 15 16 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Section 1: Solving TVM problems for Single Amounts - Use this method for Savings Accounts, Growth Rates, Interest Rates, Inflation Rates Things to know: *One of the inputs needs to be negative look at the formulas below generally, when you pay money, enter as negative, when you receive money, enter as positive "When entering the RATE - don't forget to enter the % sign "There are NO payments for single amount problems, the interest goes back into the account each period, this is called compounding. "Once you start entering the formula, a pop-up box will appear showing you the formula. "When the pop-up box appears, you can also click the fx in the top taskbar, for help entering formulas. 36 37 Standard Format NPER (N) RATE (LY) Examples The future value of $100 received today and deposited at 6 percent for four years is NPER (N) RATE (LY) PV PMT FV Compute? PV PMT FV Compute? 4 6% 100 0 ? $126.25 NPER (N) RATE (VY) PV PMT FV Compute? -FV(C18 C17-C19) or -FV(6%, 4, 0,-100) The future value of $975 received today and deposited at 3.9% for four years is Formula goes here! The present value of $100 to be received 10 years from today, assuming an opportunity cost of 9% NPER (N) RATE (LY) PV PMT FV OK, now you do it, please enter your formula in the blue cells. Compute? Time Value of Money Formulas =FV (rate, nper, pmit, pv) PV (rale, nper, pml, fv) Future Value Present Value Rate =RATE (nper, pml, pv, fv) Number of period NPER (rate, pmt, pv, fv) Payment PMT (rate, nper, pv, fv) 10 9% NPER (N) RATE (VY) PV PMT FV Compute? 2 0 100 $42.24 -PV(F18,F17, F21) =-PV (9%, 10, 0, 100) The present value of $1265 to be received 10 years from today, assuming an opportunity cost of 2.9% ? SU05.43 The current dividend is $5, the dividend 6 years ago was $1.95, compute the growth rate NPER (N) RATE (UY) PV PMT FV Compute? 6 ? 1.95 0 5 16.99% =RATE(117-119,121) =RATE (6, 0,-1.95, 5) The current dividend is $9.5, the dividend 4 years ago was $4.95, compute the growth rate NPER (N) RATE (LY) PV PMT FV Compute 7 ? 17.70% With an interest rate of 3.5%, how long will it take for my $500 investment to triple? NPER (N) RATE (V/Y) PV PMT FV Compute? ? 3.5% 500 0 1500 31.94 =NPER(L18-L19,L21) =NPER (3.5%, 0, -500, 1500) With an interest rate of 8%, how long will it take for my $250 investment to quadruple? NPER (N) RATE (VY) PV PMT FV Compute?