Question

In: Finance

This assignment is to be completed in Excel. When completed, submit the exercise by the due...

This assignment is to be completed in Excel. When completed, submit the exercise by the due date in Blackboard (BB) and Attach a copy of the excel spreadsheet.

Case Narrative:

Susan Smith is age 45 and plans to retire in 15 years (at age 60). She has retirement savings in a mutual fund account, which has a current balance of $250,000 (Susan does not plan to add any additional money to this account).  Also, Susan opened a 401K retirement account with her new employer and will contribute $20,000 per year into her 401K until retirement.  

  • Assuming that Susan’s mutual fund account grows at an annual rate of 7.5% how much money will Susan have in her mutual fund account at age 60?  
  • Assuming that Susan’s 401K account grows at an annual rate of 9% per year, how much money will Susan have in her 401K account at age 60?
  • What is the total investment balance of Susan’s retirement account at the age of 60?
  • At retirement, Susan plans take the investment balance from her mutual fund account and the balance from her 401K account and combine them into an IRA account. To minimize risk, her IRA account will invest in more conservative securities.  As a result, Susan anticipates her annual IRA returns to be about 5.5% during retirement.   While in retirement, Susan plans to withdraw $90,000 per year from her IRA account over the next 32 years.  Is this possible? Explain why or why not?

Solutions

Expert Solution

1 Amount in mutual fund at age 60 $739,719.34
2 Amount in 401K at age 60 $587,218.32
3 Total investment balance $1,326,937.66
4 Amount that can be withdrawn in retirement ($89,031.13)
Hence she cannot withdraw $90000 per year

WORKINGS


Related Solutions

This assignment is to be completed in Excel. When completed, submit the exercise by the due...
This assignment is to be completed in Excel. When completed, submit the exercise by the due date in Blackboard (BB) and Attach a copy of the excel spreadsheet. Case Narrative: Ann E. Belle is age 42 and plans to retire in 25 years (at age 67). She has retirement savings in a mutual fund account, which has a current balance of $100,000 (Ann does not plan to add any additional money to this account). Also, Ann opened a 401K retirement...
Mini case #1 - Retirement Planning This assignment is to be completed in Excel. When completed,...
Mini case #1 - Retirement Planning This assignment is to be completed in Excel. When completed, submit the exercise by the due date in Blackboard (BB) and Attach a copy of the excel spreadsheet. Case Narrative: Ann E. Belle is age 45 and plans to retire in 20 years (at age 65). She has retirement savings in a mutual fund account, which has a current balance of $150,000 (Ann does not plan to add any additional money to this account)....
Mini case #1 - Retirement Planning This assignment is to be completed in Excel. When completed,...
Mini case #1 - Retirement Planning This assignment is to be completed in Excel. When completed, submit the exercise by the due date in Blackboard (BB) and Attach a copy of the excel spreadsheet. Case Narrative: Ann E. Belle is age 45 and plans to retire in 20 years (at age 65). She has retirement savings in a mutual fund account, which has a current balance of $150,000 (Ann does not plan to add any additional money to this account)....
Exercise 1) Complete the following Excel Assignment and submit it to dropbox: Sales increase: 22% Cost...
Exercise 1) Complete the following Excel Assignment and submit it to dropbox: Sales increase: 22% Cost of Sales: 33% Ending Inventory Needs: Q1, 2021 Expected Sales: $696,000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year    Q1 2021 Sales in 2019 $407,000 $455,000 $505,000 $778,000 $2,145,000 Expected Sales 2020 xxx xxx xxx xxx xxx xxx Cost of Sales xxx   xxx xxx xxx xxx xxx Beginning Inventory xxx xxx xxx xxx xxx xxx Ending Inventory xxx xxx xxx xxx xxx...
For this assignment, please submit the answers to the following questions, as well as an Excel...
For this assignment, please submit the answers to the following questions, as well as an Excel spreadsheet which documents the work you did. Do poets die young? According to William Butler Yeats, “She is the Gaelic muse, for she gives inspiration to those she persecutes. The Gaelic poets die young, for she is restless, and will not let them remain long on earth.” One study designed to investigate this issue examined the age at death for writers from different cultures...
Please use Excel to solve the assignment and submit as an excel spreadsheet. Bethesda Mining Company...
Please use Excel to solve the assignment and submit as an excel spreadsheet. Bethesda Mining Company Based on a Mini Case presented in the textbook Ross, S.A., R.W. Westerfield and J. Jaffe, Corporate Finance, McGraw Hill/Irwin. Bethesda Mining is a midsized coal mining company with 20 mines located in Ohio, Pennsylvania, West Virginia and Kentucky. The company operates deep mines as well as strip mines. Most of the coal mined is sold under contract, with excess production sold on the...
Using Excel to Plot a Histogram This exercise is to be completed using Microsoft Excel and...
Using Excel to Plot a Histogram This exercise is to be completed using Microsoft Excel and will be turned in on Thursday, September 20th in class. You are to follow the steps we used in class on 9-13-2018 to construct the histogram for the data consisting of the ages of the 50 most powerful women in the world from 2012 (Source: Page 39 of textbook via Forbes Magazine). Your work should have columns for: 1. the raw data; 2. the...
Assignment 1 Note: submit the following 1- "Excel file" include data and graph and analysis "please...
Assignment 1 Note: submit the following 1- "Excel file" include data and graph and analysis "please do not use anything rather than excel" 2- a functional code that performs all the required operations in 1 & part A 1-Collect set of data from the web. About coronavirus evolution in different countries considering other factors like median age of populations, health care systems etc. you are required to perform statistical analysis of this data by calculating the different statistical metrics (like...
Submission 2 - due Saturday June 23 before 5pm - You must submit your completed Bank...
Submission 2 - due Saturday June 23 before 5pm - You must submit your completed Bank Reconciliation, the journal entries to bring the cash balance to the correct balance, and the Bank Reconciliation formulas tab completed. Your file must be named correctly – Please Show equations The following information should be used to prepare a bank reconciliation for SWARS Sales and Consulting as of March 31. Prepare the reconciliation on the Bank Reconciliation tab. On March 31, 2018, SWARS Sales...
Excel Task 1 & 2 (due week 6): Please submit both tasks together on one pdf...
Excel Task 1 & 2 (due week 6): Please submit both tasks together on one pdf document. Do not forget to highlight your answers. Q. Purchasing Department cost drivers, simple regression analysis Fashion Flair operates a chain of 10 retail department stores. Each department store makes its own purchasing decisions. Barry Lee, assistant to the president of Fashion Flair, is interested in better understanding the drivers of Purchasing Department costs. For many years, Fashion Flair has allocated Purchasing Department costs...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT