This assignment concerns with a sensitivity analysis for a retirement account. You are making some anticipatory...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
This assignment concerns with a sensitivity analysis for a retirement account. You are making some anticipatory calculations to invest in your retirement account. You plan to invest in your retirement account in two stages: the first stage is from Year 1 to Year 15. The second stage is from Year 16 to Year 40. Your tasks are as follows (be sure that you format and label every data item so that it is understandable and easy to read): 1. Create a Model for Stage 1 (Year 1 to Year 15): Assume that today is the beginning of the month and you open a retirement account. You immediately put an initial amount of $3,000 in this account. At the end of each month for the next 15 years, you plan to invest a fix monthly investment amount. Select an initial number, say $100.00, for the monthly investment amount and an initial number, say 4%, for the expected annual rate. Using Excel's future value function, FV(), and simple arithmetic calculations, create a Stage 1 model that computes the amount of money in your retirement account, the total investment amount, as well as the total interest earned at the end of Year 15. Using the numbers given, the amount of money at the end of Year 15 should be $30,069.95, the total investment amount at the end of Year 15 should be ($21,000.00), and the total interest earned at the end of Year 15 should be $9,069.95. 2. Create a Model for Stage 2 (Year 16 to Year 40): In the second stage, you plan to double your monthly investment amount. Moreover, you expect that the annual rate would increase to 1.5 of the previous stage's annual rate. Thus, the first and second stages are "chained" together as follows: a. The monthly payment in Stage 2 is twice of the monthly payment in Stage 1. b. The annual rate in Stage 2 is 1.5 times of the annual rate in Stage 1. c. The "initial" investment amount in Stage 2 is the amount of money in the retirement account at the end of Stage 1. Using the Excel's future value function, FV(), and simple arithmetic calculations, create a Stage 2 model that computes the amount of money in your retirement account, the total investment amount, as well as the total interest earned at the end of Year 40. Using the numbers given, the amount of money at the end of Year 40 should be $272,860.23, the total investment amount at the end of Year 40 should be $(81,000.00), and the total interest earned at the end of Year 40 should be $191,860.23. 3. Create a Scenario Summary: You would like to have four scenarios for the values of Stage 1's two input parameters (monthly payment and annual rate), as follows: Scenario 1 Scenario 2 Scenario 3 Scenario 4 Monthly $ (200.00) $ (300.00) $ (350.00) $ (400.00) payment Annual rate 3.00% 4.00% 4.50% 5.00% Using Excel's Scenario Manager, create a scenario summary to find out the amount of money in retirement account, the total investment amount, as well as the total interest earned at the end of Year your 40. This assignment concerns with a sensitivity analysis for a retirement account. You are making some anticipatory calculations to invest in your retirement account. You plan to invest in your retirement account in two stages: the first stage is from Year 1 to Year 15. The second stage is from Year 16 to Year 40. Your tasks are as follows (be sure that you format and label every data item so that it is understandable and easy to read): 1. Create a Model for Stage 1 (Year 1 to Year 15): Assume that today is the beginning of the month and you open a retirement account. You immediately put an initial amount of $3,000 in this account. At the end of each month for the next 15 years, you plan to invest a fix monthly investment amount. Select an initial number, say $100.00, for the monthly investment amount and an initial number, say 4%, for the expected annual rate. Using Excel's future value function, FV(), and simple arithmetic calculations, create a Stage 1 model that computes the amount of money in your retirement account, the total investment amount, as well as the total interest earned at the end of Year 15. Using the numbers given, the amount of money at the end of Year 15 should be $30,069.95, the total investment amount at the end of Year 15 should be ($21,000.00), and the total interest earned at the end of Year 15 should be $9,069.95. 2. Create a Model for Stage 2 (Year 16 to Year 40): In the second stage, you plan to double your monthly investment amount. Moreover, you expect that the annual rate would increase to 1.5 of the previous stage's annual rate. Thus, the first and second stages are "chained" together as follows: a. The monthly payment in Stage 2 is twice of the monthly payment in Stage 1. b. The annual rate in Stage 2 is 1.5 times of the annual rate in Stage 1. c. The "initial" investment amount in Stage 2 is the amount of money in the retirement account at the end of Stage 1. Using the Excel's future value function, FV(), and simple arithmetic calculations, create a Stage 2 model that computes the amount of money in your retirement account, the total investment amount, as well as the total interest earned at the end of Year 40. Using the numbers given, the amount of money at the end of Year 40 should be $272,860.23, the total investment amount at the end of Year 40 should be $(81,000.00), and the total interest earned at the end of Year 40 should be $191,860.23. 3. Create a Scenario Summary: You would like to have four scenarios for the values of Stage 1's two input parameters (monthly payment and annual rate), as follows: Scenario 1 Scenario 2 Scenario 3 Scenario 4 Monthly $ (200.00) $ (300.00) $ (350.00) $ (400.00) payment Annual rate 3.00% 4.00% 4.50% 5.00% Using Excel's Scenario Manager, create a scenario summary to find out the amount of money in retirement account, the total investment amount, as well as the total interest earned at the end of Year your 40.
Expert Answer:
Answer rating: 100% (QA)
Answer Explanation Stage 1 1 Create a new sheet with below data labels and values D12 fe A B 1 ... View the full answer
Related Book For
Fundamentals of corporate finance
ISBN: 978-0470876442
2nd Edition
Authors: Robert Parrino, David S. Kidwell, Thomas W. Bates
Posted Date:
Students also viewed these accounting questions
-
The management of ACME Designs would like to have a better understanding of the behavior of its inspection costs. The company has provided the following data: The management believes that inspection...
-
The owners of the Super Supermarket would like to have a program that computes the monthly gross pay of their employees as well as the employees net pay. The input for this program is an employee ID...
-
1.An investment will give you monthly payments of $474 for 5 years. If your required return is 10.84%, what is it worth today? 2.You will invest $140 per month. If the rate is 8.5%, how much will you...
-
(a) Find the first order fraction transformation where z 1 =,z 2 =0, z 3 =1 is thought to be w 1 =1, w 2 =i, w 3 =-1 each. (b) Find the anchor points of w=(z-1)/(z+1)
-
For what values of and is the line 2x + y = b tangent to the parabola y = ax2 when x = 2?
-
An approach to justifying public sector projects that minimizes revenues while still allowing a business to earn a fair return for its shareholders is a. Revenue requirements. b. Revenue effectivity....
-
Construct a frequency distribution for the data set using eight classes. Include class limits, midpoints, boundaries, frequencies, relative frequencies, and cumulative frequencies. Use the data set,...
-
The file lists the calories and sugar, in grams, in one serving of seven breakfast cereals: a. Compute the covariance. b. Compute the coefficient of correlation. c. Which do you think is more...
-
On March 31, 2024, Susquehanna Insurance purchased an office building for $12,900,000. Based on their relative fair values, one-third of the purchase price was allocated to the land and two-thirds to...
-
The finance director for the City of Green Falls printed the General Fund Revenues and Appropriations Ledgers shown below for the year just ended. The beginning-of-the-year fund balance was $160,160....
-
Assume the best hedge of an asset in your portfolio is $ 2 . 5 m , while the current investment in that asset is $ 1 1 m . Assume you have short selling restrictions and you want to minimise the...
-
How does the AcSB consider benefitcost relationships for segmented reporting?
-
Some scholars have suggested that zakat can be used as a tool for poverty eradication. Discuss.
-
Your Task. Decide whether to respond to the following online posts.24 If you believe you should respond, compose a concise Facebook reply following the guidelines in this chapter. Your instructor may...
-
What is the appropriate method of accounting for a foreign currency gain or loss related to a financial asset classified as held-to-maturity?
-
What is a channel plan and how is it used?
-
How does an Oracle Database use read locks and write locks? What, if any, levels of transaction isolation are available in an Oracle Database? Summarize the backup capabilities of an Oracle...
-
Inexhaustible collections of ONPOs are not required to be capitalized or depreciated, if certain criteria are met. Why is this so, and what accounting and reporting recognition, if any, is required...
-
UNC Bancorp has issued preferred stock with no maturity date. It has a par value of $100 and pays a quarterly dividend of $2.25. If the required rate of return is 8 percent, what is the value of the...
-
Describe the Capital Asset Pricing Model (CAPM) and what it tells us.
-
Present value with multiple cash ows: Jeremy Fenloch borrowed some money from his friend and promised to repay him the amounts of $1,225, $1,350, $1,500, $1,600, and $1,600 over the next five years....
-
Determine the longitudinal tensile strength of the hybrid carbon/aramid/ epoxy composite described in Problem 3.4 and Figure 3.10 of Chapter 3 if the fiber packing array is square with the closest...
-
For the IM-9/8551-7 carbon/epoxy composite rod design of Problem 3.3 in Chapter 3, what would be the increase in the longitudinal tensile strength compared with that of the original 6061-T6 aluminum...
-
Assuming that the failure mode for longitudinal compression of unidirectional E-glass/epoxy with fiber volume fraction \(v_{\mathrm{f}}=0.6\) is a transverse tensile rupture due to Poisson strains,...
Study smarter with the SolutionInn App