ICA23-I+%281%29

pdf

School

University of Minnesota-Twin Cities *

*We aren’t endorsed by this school

Course

2551

Subject

Finance

Date

Jan 9, 2024

Type

pdf

Pages

Uploaded by AdmiralTree7176

In-Class Assignment #23 Walks on the beach, time for hobbies, trips around the world – retirement sounds nice! However, you will need to create a plan to comfortably retire someday . You’ve decided you want to retire in 33 years with $2 million in the bank account at that time. You haven’t saved anything yet, but don’t worry, you have time on your side. You want to determine how much you must save each year to reach your goal. You have also heard that your investment return and risk vary significantly based on which asset class you invest in. 1. Open the datafile for today’s in -class assignment. 2. Pull historical returns of different asset classes: Go to the website provided on the “Historical Returns” worksheet. You may need to CTRL + Click to get to the website. Scrape the data from the web. We need the first six columns (“Year” and the returns of five different asset classes). We also need all the years provided (1928-2022). P aste this information in the “ Historical Returns ” worksheet. 3. Summarize historical returns: Summarize the average (mean), 1 st quartile (25 th percentile), and 3 rd quartile (75 th percentile) of each asset class in the table provided (E1:H7). Hint: use the formula =QUARTILE.INC(array,1) to determine the 1 st quartile and =QUARTILE.INC(array,3) to determine the 3 rd quartile. Note: This is a little tedious – try to think of the most efficient way to do this. Also, please see the note regarding the different asset classes (for example, S&P 500 = “ Stocks ” and US T.Bond = “ TBonds ” ) 4. Create drop-down lists: Create a drop-down to select one of the five asset classes in cell B2. Create another drop-down to select one of the three return scenarios (average, 25 th percentile, 75 th percentile) in cell C2. Remember, you can use Data Validation to do this. 5. Look up the rate of return: Using the inputs selected from the drop-down lists , use either VLOOKUP and MATCH or INDEX and MATCH to look up the appropriate rate of return. VLOOKUP and MATCH • MATCH can be used to create a dynamic column index number in VLOOKUP. • MATCH requires a lookup value (what you’re searching for ), a lookup array (where you’re looking for it), and a match type (generally, we want an exact match). • Be sure that the MATCH lookup array is the same width as the VLOOKUP lookup array! INDEX and MATCH • MATCH can be used to create a dynamic row number and column number in INDEX. • MATCH requires a lookup value (what you’re searching for ), a lookup array (where you’re lookin g for it), and a match type (generally, we want an exact match). • Be sure that the MATCH lookup array is the same height and width as the INDEX lookup array! 6. Use a financial function: We’re going to use the financial function =PMT to calculate how much we must save each year to reach our goal. By building our model this way, we can toggle between different asset classes and return scenarios to see how much we must save under different scenarios!

PMT has four key arguments, all of which you have an answer for: • RATE = the rate of return (What rate of return does your scenario select?) • NPER = number of periods (How many years until retirement?) • PV = present value (What have you saved so far?) o If you have anything currently saved, you must input it as a negative number . In other words, if you have saved $1,000 you would enter -$1,000. o Why? The +/- sign with financial functions can be thought of as money flowing into your pocket (+) or out of your pocket (-). If you have current savings and you’re putting them towards retirement, that is money flowing out of your pocket into the retirement savings account today. • FV = future value (How much do you want to have saved when you retire?) Notice that your PMT function outputs a negative number. Similar to the note above on present value (PV), this just means you will need money flowing out of your pocket each year to get to the positive future value you desire. If this doesn’t make sense, don’t sweat it at this point – you can learn much more about this in your fundamentals of finance course. 7. Test your model by reviewing different scenarios. Make sure your outputs change as you’d expect when you change your inputs. 8. Create scenarios using Scenario Manager: Under “ What- If Analysis” there is a tool called Scenario Manager. Create three scenarios per the instructions below. Once you’re complete, toggle through the scenarios and make sure your assumptions change as you would expect. • Early: Asset/Return = S&P 500 (average), NPER = 20, PV = -$25,000 (enter as a negative number!), FV = $1,000,000 • Average: Asset/Return = S&P 500 (average), NPER = 33, PV = 0, FV = $2,000,000 • Late: Asset/Return = S&P 500 (average), NPER = 40, PV = 0, FV = $3,000,000 Click the “Summary” button in Scenario Manager. The output cell is Annual Contribution. The summary populates in a new tab but does not have the assumption or output titles - update these. 9. Review the graph and formulas in the blue cells (D14:E90). These formulas are used to see the cumulative savings you anticipate generating over time. Can you understand what the formulas are doing? As you change your assumptions and/or scenarios, do you see how the graph changes? 10. If time permits, discuss these thought questions with those around you. Upload your file. • If you select the average returns scenario, which asset should you select to minimize your annual contribution? Does this intuitively make sense looking at average returns? • If you select Stocks as your investment asset and you earn 75 th percentile returns how much do you need to save each year? What about if you earn 25 th percentile returns? Why do you think these numbers are so far apart? • Why do you think Corporate Bonds (lending money to a corporation) results in a higher return than U.S. Treasury Bonds [aka TBonds] (lending money to the U.S. government)?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version