ASSIGNMENT HELP
MachineFinance Deliverable for Time Value of Money PEMBA 2021 Please read these instructions before starting! I hate for you to spin your wheels pointlessly and these instructions will help you to focus your time more efficiently. You have already read IFM Web Chapter 28 (Time Value of Money). If not, read it now. It is posted on the page you got this assignment from. Read in your text, Intermediate Financial Management, for this assignment Chapter 4 (bonds). You will also need to read Section 8.11 in Chapter 8 to help with the constant growth questions. Please work the following problems on Excel. You may discuss these problems among yourselves, but you may not email, post, or in any way share the Excel spreadsheets with each other. I want each of you to program for yourself the solutions in Excel! This exercise is as much about Excel as it is about Finance skills. Note also, I’ve provided at the end of these instructions links to instructional videos for many of these basic skills. Feel free to make as much use of them as you like. Finally, you’ll have to wrestle with these concepts at first. You’ll get the hang of them, I promise. But if after reading the material in the text and making an effort at working the problems you find yourself spinning your wheels and not getting anywhere then by all means STOP and move on to a different problem! And post a question on the Discussion Board or make a note to attend one of the help sessions. Don’t (as some of my past students have done) spend hours and hours not accomplishing anything except to get angry and frustrated. My goal for you is to acquire these preliminary skills, not to haze you. This isn’t the first year of your residency program! How to get help: 1. Your quickest route to help is to first check to see if someone else has asked the same question on the Finance Discussion board. If so, then chances are I’ve answered it and bingo, done! 2. If no one has asked it yet, then you post the question on the discussion board. Don’t be afraid to be first. I check it multiple times a day and will usually be very responsive. Notice I didn’t say to email me with your questions. Don’t do that. It isn’t that I don’t want to correspond with you. It’s that you asking and I answering questions on the discussion board makes the interaction much more time efficient for you and for your classmates! I hate it when you guys have to waste your time, and the discussion board helps. A lot! 3. There will be a few online help sessions. Dr. Laura Cole and Angel Normal will run some periodic help sessions. They will help you with concepts—both finance and Excel. However, I’ve instructed them NOT to work these specific problems in help sessions. They (and I if I run any of the help sessions) will work related material problems and answer both general and specific questions but won’t work these specific problems, and won’t work the same problems but with different numbers. You’ll find the help sessions to be more helpful if you do the reading and make your own effort on the material first. Also, please note, they are only providing this service during help sessions. Don’t email them with questions about how to work problems or do things in Excel—post those questions on the Discussion Board and I’ll answer them. Their job is to help you in real time live on their Zoom help sessions, like office hours. Note about the Discussion Board. I won’t work the specific problems from this problem set for you on the discussion board. I will answer conceptual questions and help get you started. I’ll answer other specific questions too—I just won’t provide a worked out solution on the discussion board. Remember what I said during the RP. Certainly you need to be able to calculate answers to these questions. But it is so much more important that you understand what the answer means and why you are calculating what you are calculating. I love to answer those types of questions on the Discussion Board! When working these problems, please make sure of the following: 1. Use Excel. Don’t use a calculator. 2. You needn’t type into your Excel spreadsheet the problem wording. If you really want the problem wording in your spreadsheet, you can cut and paste from this Word document into your spreadsheet, but this isn’t necessary. Just number your answers. 3. Don’t hardwire numbers into your formulas. If you need a number that is already in your spreadsheet, then use a cell reference to refer to it; don’t copy it down and retype it or copy it electronically. Be sure to have a parameters section for each problem and a section where you do the calculations. I want to be able to change the inputs to the problems and get correct answers for the new parameters. 4. Label your parameters. 5. Work the problems going from top to bottom…. that is, start Problem 1 somewhere near Row 1 and Column A, and start Problem 2 somewhere below that and also in Column A. Please work all of the problems on the same worksheet tab. Please don’t use a separate tab for each problem. Please don’t scroll to the right to work more problems. Work them from top to bottom. (Of course you can use more columns than just Column A and B and C. But if you use, say Columns A through E for Problem 1, do not move on over to Column F to start Problem 2. Move down to a row below the ones you used for Problem 1 to start Problem 2’s solution.) 6. When there are multiple steps in a problem, please break the problem up into those steps. Don’t try and create really complicated formulas that do the whole thing in one step. There is no extra credit for complexity! 7. Don’t email, post, send, or share your Excel solution files with anyone. Don’t ask someone to share his or her solution files with you. You may discuss the problems with each other and even work on the problems together in pairs or larger groups, but each of you, individually, must program your Excel spreadsheet solutions yourself and submit them yourself. 8. When in doubt DRAW A TIME LINE! Helpful videos I’ve recorded: See the Finance Resources section link on the Canvas home page. https://www.youtube.com/user/pdaves https://www.youtube.com/channel/UC-eMW9TiqxYKTMV1O1aMWsQ Also, feel free to search on YouTube for instructional videos for Excel. For example, if you search on “amortization schedule” you’ll find a ton of videos (including mine, probably) explaining how to construct one in Excel. Although not as much fun as my in-class example, they might be a lot quicker to find. Accessing instructional videos (especially for Excel) is perfectly acceptable, I encourage you to do that! And if you find something particularly useful, please post the link on the discussion board. Your classmates will thank you and so will I! Finally, and this is so important I’m putting it in big, bold letters right here before the problems themselves: If you get stuck, don’t spin your wheels for a long time! Stop if you get stuck and do something else. If you need help, post a question on the discussion board! I’ve had students say “I spent 20 hours on problem x. I just couldn’t figure out how to get started.” And my response is “????????? you had better be exaggerating!” “For goodness sakes, don’t do that ever again! If you’ve already read the material then spend half an hour or 45 minutes. If you haven’t read the material then read it first and try again. If you need a hand after that, then post a question or wait and attend an online help session.” Your time is limited and valuable, and wheel spinning is a terrible use of it. Whew…. that’s a lot of instructions! But it’s the only time I’ll say all this stuff. Now on to the problems! Problems 1. You plan to buy a piece of machinery that costs $1.2 million. You’ll put down 20% and finance (that is, borrow) the rest at 9% per year for 3 years, with monthly payments. a. Create an Excel model that calculates the monthly payment and an amortization schedule for the loan. b. You plan to replace the machinery with a more advanced model at the end of 3 years. You predict you will be able to sell the old machinery for $500,000 at that time, so you want to set up your loan so that rather than completely amortizing the amount borrowed, you end up at the end of 3 years still owing $500,000. (This is the same as setting your loan up with a $500,000 balloon payment at the end of the loan.) Set up an Excel model that calculates your monthly payment under this new scenario and construct an amortization schedule for the loan. Verify that your loan balance at the end of three years is, indeed, $500,000. Hint, this ending balance is the FV in your PMT function. Just be careful about whether it needs to be a positive or a negative value! 2. You are 45 years old and plan on retiring at age 60. You are in good health and expect to live to age 85. Your retirement account earns 9% per year. a. How much must you save per month, with your first contribution at the end of the month, until retirement if you wish to withdraw $15,000 per month in retirement income? Your first withdrawal will be at the end of the first month of retirement and your last retirement contribution will be on the day you retire. Hint: this takes multiple steps. First, you need to figure out how much you must accumulate to pay yourself $15,000 a month for 25 years. This is a PV of an annuity problem. Second, you need to find out how much you must save (how much are your payments) each month to grow to this amount over 15 years. This is a PMT problem. b. You don’t think you can save that much per month. How much must you save per month if you delay retirement from age 60 to age 65? Hint: this changes the number of years you draw your retirement income and increases the number of years you save for it. c. Upon second thought, you’ve realized that with inflation, $15,000 per month is unlikely to meet your consumption and travel needs when you retire. Rather, you would like to guarantee that during the first month of