Question

In: Finance

Create an Excel spreadsheet that projects your savings balances every year until retirement, and the income you can generate in retirement from your savings.

Create an Excel spreadsheet that projects your savings balances every year until retirement, and the income you can generate in retirement from your savings.

Include columns for 1. years; 2. savings balance; 3. investment returns; 4. annual new savings (investment) from your salary. Note that each year's balance equals the prior year's balance plus investment returns, plus additional savings. See below:

Interest Rate5.00%

Annual Savings6,000.00





YearBalanceReturnsAnnual Savings
2020500.0025.006,000.00
20216,525.00326.256,000.00
202212,851.25642.566,000.00
::

[Retirement Year]770,734.57





Retirement Annuity50,137.39

Solutions

Expert Solution

In order to plan this retirement project , we do the following operations,

The Retirement chart is constructed in excel as per the required coloums

Years

Savings Balance / BALANCE

Investment returns

Annual New Savings (Investment)

Closing Balance

The Balance in the opening was 500 and the returns are calculated on a 5% apr as has been given

Investment returns

Formula =B3*$B$1

Annual Savings is fixed at 6000

Closing Balance

Closing balance is given as the sum of all the components

=B3+C3+D3

The Retirement plan is as follows

Return

5%

Years

Savings Balance / BALANCE

Investment returns

Annual New Savings (Investment)

Closing Balance

2020

500

25

6000

6525

2021

6525

326.25

6000

12851.25

2022

12851.25

642.5625

6000

19493.8125

2023

19493.8125

974.690625

6000

26468.5031

2024

26468.50313

1323.425156

6000

33791.9283

2025

33791.92828

1689.596414

6000

41481.5247

2026

41481.5247

2074.076235

6000

49555.6009

2027

49555.60093

2477.780047

6000

58033.381

2028

58033.38098

2901.669049

6000

66935.05

2029

66935.05003

3346.752501

6000

76281.8025

2030

76281.80253

3814.090126

6000

86095.8927

2031

86095.89265

4304.794633

6000

96400.6873

2032

96400.68729

4820.034364

6000

107220.722

2033

107220.7216

5361.036082

6000

118581.758

2034

118581.7577

5929.087887

6000

130510.846

The target retirement balance of 770,734.57 was calculated in the Retirement year 2060 as follows

2058

649450.0134

32472.50067

6000

687922.514

2059

687922.5141

34396.12571

6000

728318.64

2060

728318.6398

36415.93199

6000

770734.572

Excel

Hence the retirement plan is constructed with the excel as follows

jeff jeffy answered 2 years ago
Next > < Previous

Related Solutions

Barry and Samantha Harris – Retirement Savings You must work on your own spreadsheet with Excel...
Barry and Samantha Harris – Retirement Savings You must work on your own spreadsheet with Excel skills or concepts. Barry and Samantha are starting to take their retirement planning seriously. They are both 45 and plan to retire in 20 years at the age of 65. They expect to live 15 years in retirement (a life expectancy of 80). Between their 401k accounts they currently have $66,000 in retirement savings. They currently have a combined income of $80,000 per year...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm that is in the 35% tax bracket forecasts that it can retain $4 million of new earnings plans to raise new capital in the following proportions: 60% from 30-year bonds with a flotation cost of 4% of face value. Their current bonds are selling at a price of 91...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company is evaluating the purchase of a machine to improve product quality and output levels. The new machine would cost $1.6 million and would be depreciated for tax purposes using the straight-line method over an estimated six-year life to its expected salvage value of $100,000. The new machine would require...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have interest expense of $2,000,000 and 1,000,000 common shares outstanding, whereas the second would have interest expense of $3,800,000 but only 750,000 shares outstanding. The company is in the 35% tax bracket. Part 1: Construct the...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm had the following abbreviated income statement for 2020 and abbreviated balance sheets at the end of 2020 and 2021. 2020 Sales $600,000 Less Cost of goods sold 320,000 Gross Profit 280,000 Less Operating expenses 190,000 Less Depreciation 30,000 Operating Income (EBIT) 60,000 Less Interest expense 20,000 Earnings Before Taxes...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have $25 million of bonds at 8% interest and 1,000,000 common shares outstanding, whereas the second would have $47.5 million of bonds at 8% interest and only 750,000 shares outstanding. The company is in the 35%...
Your task is to create the 2021 Budget in an Excel Spreadsheet.  You only need to prepare...
Your task is to create the 2021 Budget in an Excel Spreadsheet.  You only need to prepare an Income Statement Assume: The company will sell 16,000 bicycles at an average price of $2,500 each Cost of Labor is 20% of Sales Cost of Materials is 10% of Sales Variable Overhead is 6% of Sales Fixed Manufacturing Overhead is $4,000,000 Office Salaries are 5,000,000 Office Deprecation Expense is $500,000 Lease Expense of Corporate Office is $2,000,000 Advertising is $1,000,000 Income Taxes are...
Based on the number of years until your retirement, calculate the monthly savings required to build...
Based on the number of years until your retirement, calculate the monthly savings required to build a portfolio of $500,000. Assume you are starting with zero savings, can earn 6 percent a year, and that you start saving on the first of each month. What if you could earn 9 percent a year but wanted to save $750,000 - what would you need to deposit into savings monthly? My age is 48 and I will retire at 67.
Calculations must be done in Excel – You must create your own spreadsheet do not copy...
Calculations must be done in Excel – You must create your own spreadsheet do not copy and paste someone else’s. Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three year contract. The new machine will cost $1.9 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $120,000. The contract will not continue beyond three years and...
Suppose you start saving for retirement by depositing $4,000 EVERY YEAR into your retirement account. If...
Suppose you start saving for retirement by depositing $4,000 EVERY YEAR into your retirement account. If your annual return is 8%, how much will you have in 45 years? How much would you have if all deposits were made on the FIRST of the year (as opposed to the last day of the year)? (please solve by hand and not excel)
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT