HomeworkLib
Question

Generate Retirement planning in Excel. Need to generate values in 'Accumulation Phase' and the CORRECT answers are provided in "Post Retirement' table but need to generate those values.

Mark and Cindy are 35 years old with a combined income of $175,000 a year. They have not saved anything for retirement. Based on their retirement plans they are anticipating that they will need $3,000,000 to retire comfortably at 65. Assume investments are made at the end of the period. 1) How much do they need to save annually to reach their $3 M goal using constant dollar approach? 2) They want enough for 30 years after retirement how much should they withdraw? 3) Complete all for schedules for the constant and nominal dollar approach for both the accumulation phase and drawdown phase.(235,254) Requirement: Use financial functions where applicable Savings Goal Annual Contribution Years to Retirement Real Rate Inflation Rate Nominal Rate 3,000,000 30 6.73% 4.00% 11.00% ent starting at Year 31 235,254 Accumulation Phase Constant Dollar Approach Accumulation Phase-Nominal Dollar Approach Year nning Balance Earnin Contribution Ending Balance Year inning Balance Earnin Contribution Ending Balance 10 1013 14 15 16 17 18 19 13 14 16 17 19 21 21 23 25 26 27 28 29 3,000,000 9,730,193 Post Retirement Drawdown- Constant Value Approach Post Retirement Drawdown-Nominal Value Approach Ending Balance Year 31 32 Beginning Balance Year inning Balance Earn Ending Balance 3,000,000 S 2,966,669 S 2,931,095 $ 2,893,126 S 2,852,601S 2,809,349 $ 2,763,186 S 2,713,916 201,923 IS 199,680 S 197,285 S 194,730 $ (235,254) S (235,254)S 2,966,669 2,931,095 2,893,126 2,852,601 2,809,349 2.763,186 2,713,916 2,661,329 31 10,006,970 10,282,452 10,555,226 10,823,672 10,823,672 1,190,604 (928,333)11,085,943 11,339,930 11,583,236 11,583,236 1,274,156 (1,044,249)11,813,144 9,730,193 1,070,321 93,543)$ 10,006,970 1,100,767 (825,285) 10,282,452 $1,131,070 $ (858,296) $ 36 37 38 189,091 S 185,984S 182,667 S 235,254) S (235,254)$ (235,254) S 235,254) S (235,254)$ 10,555,226 $ 1,161,075 (892,628) $ 467) $ 1,004,085) S 11,085,943 $ 1,219,454$ 11,339,930 1,247 3710,006,970$1,100,767 $(825,285) $ 10,282,452 $ 1,131,070 $ (858,296) 10,5S5,226 10,555,226 1,161,075 (892,628) S 10,823,672$ 1,190,604$ 11,085,9431,219,454 $ (965,467) 11,339,9301,247,392 (1,004,085) $ 11,583,236 $ 1,274,156 (1,044,249) S 11,813,144 1,299,446$ (1,086,019) 12,026,5711,322,923 (1,129,460) $ 12,220,034 $ 1,344,204 $ (1,174,638) $ 12,389,6001,362,856 (1,221,623) 12,530,832 $ 1,378,392 (1,270,488) S 12,638,735 S 1,390,261 S (1321,308) 5 12,707,688 1,397,846 (1,374,160) 12,731,374 $ 1,400,451 (1,429,127) S 12,702,6981,397,297 (1,486,292) 12,613,7041,387,507 (1,545,743)$ 12,455,468 $ 1,370,101 (1,607,573)S 12,217,9961,343,980 (1,671,876) $ 11,890,1001,307,911 (1,738,751) $ 11,459,260 $ 1,260,519 $ (1,808,301 S 10,911,477 1,200,262 (1,880,633) 10,231,106 $ 1,125,422 (1,955,858) S 2.931,095 10,282,452 2,931,09S S 2,893,126 $ 2,852,601$ 197,285 $ 2,852,601 10,823,672 11,085,943 11,339,930 11,583,236 11,813,144 192,002 $ 189,091 $ (928,333) 51 2,763,186 2,713,916 $ 2,661,329 S 182,667 $ 12.220,034 12,389,600 12,530,832 12,638,735 12,707,688 2,545,299S 167,015 $(235,254) S 162,422 $ 2,413,124 2,413,124 S 2,179,591 2,179,591 $ (235,254)S 12,702,698 12,613,704 140,743 134,382 $ 127,592 $ 120,346$ 112,612 S 104,357 $ 1,895,657 $ 1,787,995 S 1,673,087 S (235,254) S 235,254) S 12,217,996 11,890,100 11,459,260 10,911,477 1,673,087 1,419,547 1,419,547 S (235,254) $ 9,400,670 1,034,074$ (2,034,093) $ 8,400,651 924,072$ (2,115,456) $ 7,209,266 $793,019 (2,200,075) S 5,802,210 $ 638,243$ (2,288,078) 4,152,375$ 456,761 $ (2,379,601) $ 2,229,536 $ 245,249 (2,474,785) S 5,802,210 4,152,375 426,936$ 220,418 S 14,836 $

business   finance   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1
A B C D E F G H I J K L M N
2 Constant Dollar Nominal
3 Savings Goal $3,000,000 $9,730,192.53 =D3*((1+D7)^D5)
4 Annual Contribution $33,330.97
5 Years to retirement 30
6 Real rate 6.73%
7 Inflation rate 4%
8 Nominal rate 11%
9 Payment starting at Year 31 -235254
10
11 Accumulation Phase - Constant Dollar Approach Accumulation Phase - Nominal Dollar Approach
12 Year Beginning Balance Earnings Contribution Ending Balance Year Beginning Balance Earnings Contribution Ending Balance
13 1 0 0 $33,330.97 $33,330.97 1 0 0 $34,664.21 $34,664.21
14 2 $33,330.97 $2,243.43 $33,330.97 $68,905.36 2 $34,664.21 $3,813.06 $36,050.77 $74,528.04
15 3 $68,905.36 $4,637.86 $33,330.97 $106,874.19 3 $74,528.04 $8,198.08 $37,492.80 $120,218.93
16 4 $106,874.19 $7,193.46 $33,330.97 $147,398.61 4 $120,218.93 $13,224.08 $38,992.52 $172,435.53
17 5 $147,398.61 $9,921.06 $33,330.97 $190,650.64 5 $172,435.53 $18,967.91 $40,552.22 $231,955.66
18 6 $190,650.64 $12,832.25 $33,330.97 $236,813.86 6 $231,955.66 $25,515.12 $42,174.31 $299,645.09
19 7 $236,813.86 $15,939.39 $33,330.97 $286,084.23 7 $299,645.09 $32,960.96 $43,861.28 $376,467.32
20 8 $286,084.23 $19,255.67 $33,330.97 $338,670.86 8 $376,467.32 $41,411.41 $45,615.73 $463,494.46
21 9 $338,670.86 $22,795.15 $33,330.97 $394,796.98 9 $463,494.46 $50,984.39 $47,440.36 $561,919.21
22 10 $394,796.98 $26,572.87 $33,330.97 $454,700.82 10 $561,919.21 $61,811.11 $49,337.97 $673,068.29
23 11 $454,700.82 $30,604.86 $33,330.97 $518,636.65 11 $673,068.29 $74,037.51 $51,311.49 $798,417.30
24 12 $518,636.65 $34,908.24 $33,330.97 $586,875.86 12 $798,417.30 $87,825.90 $53,363.95 $939,607.15
25 13 $586,875.86 $39,501.26 $33,330.97 $659,708.08 13 $939,607.15 $103,356.79 $55,498.51 $1,098,462.45
26 14 $659,708.08 $44,403.43 $33,330.97 $737,442.48 14 $1,098,462.45 $120,830.87 $57,718.45 $1,277,011.77
27 15 $737,442.48 $49,635.55 $33,330.97 $820,409.00 15 $1,277,011.77 $140,471.29 $60,027.19 $1,477,510.25
28 16 $820,409.00 $55,219.84 $33,330.97 $908,959.80 16 $1,477,510.25 $162,526.13 $62,428.28 $1,702,464.66
29 17 $908,959.80 $61,179.99 $33,330.97 $1,003,470.75 17 $1,702,464.66 $187,271.11 $64,925.41 $1,954,661.18
30 18 $1,003,470.75 $67,541.30 $33,330.97 $1,104,343.02 18 $1,954,661.18 $215,012.73 $67,522.42 $2,237,196.33
31 19 $1,104,343.02 $74,330.78 $33,330.97 $1,212,004.77 19 $2,237,196.33 $246,091.60 $70,223.32 $2,553,511.25
32 20 $1,212,004.77 $81,577.24 $33,330.97 $1,326,912.98 20 $2,553,511.25 $280,886.24 $73,032.25 $2,907,429.74
33 21 $1,326,912.98 $89,311.45 $33,330.97 $1,449,555.40 21 $2,907,429.74 $319,817.27 $75,953.54 $3,303,200.55
34 22 $1,449,555.40 $97,566.23 $33,330.97 $1,580,452.59 22 $3,303,200.55 $363,352.06 $78,991.68 $3,745,544.30
35 23 $1,580,452.59 $106,376.62 $33,330.97 $1,720,160.18 23 $3,745,544.30 $412,009.87 $82,151.35 $4,239,705.52
36 24 $1,720,160.18 $115,780.01 $33,330.97 $1,869,271.15 24 $4,239,705.52 $466,367.61 $85,437.41 $4,791,510.54
37 25 $1,869,271.15 $125,816.33 $33,330.97 $2,028,418.45 25 $4,791,510.54 $527,066.16 $88,854.90 $5,407,431.60
38 26 $2,028,418.45 $136,528.16 $33,330.97 $2,198,277.58 26 $5,407,431.60 $594,817.48 $92,409.10 $6,094,658.17
39 27 $2,198,277.58 $147,960.99 $33,330.97 $2,379,569.54 27 $6,094,658.17 $670,412.40 $96,105.46 $6,861,176.03
40 28 $2,379,569.54 $160,163.33 $33,330.97 $2,573,063.84 28 $6,861,176.03 $754,729.36 $99,949.68 $7,715,855.08
41 29 $2,573,063.84 $173,186.99 $33,330.97 $2,779,581.80 29 $7,715,855.08 $848,744.06 $103,947.67 $8,668,546.81
42 30 $2,779,581.80 $187,087.24 $33,330.97 $3,000,000.00 30 $8,668,546.81 $953,540.15 $108,105.58 $9,730,192.53
43 =G41 =D42*$D$6 =$D$4 =D42+E42+F42 30 =M41 =J42*$D$8 =$D$4*((1+$D$7)^(I42)) =J42+K42+L42
44
0 0
Add a comment
Know the answer?
Add Answer to:
Generate Retirement planning in Excel. Need to generate values in 'Accumulation Phase' and the CORRECT answers...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • As of the end of 2009, Amelia and Adam have a joint investment account of $400,000...

    As of the end of 2009, Amelia and Adam have a joint investment account of $400,000 in assets. They are 50 years old and plan to retire in 15 years. They expect to live for another 20 years after they retire. They have a household annual income of $200,000 of which they spend $101,000. They would like to save enough money so that they can maintain the same consumption level of 101,000 a year. The inflation rate is 4%, and...

  • Need to generate Amortization Table in Excel for the following problem. The correct answers are given...

    Need to generate Amortization Table in Excel for the following problem. The correct answers are given but they need to be generated using Financial Functions. Marley has been saving and is soon planning to buy a house. The maximum amount she wants to mortgage is $220,000 and is considering either a 20 or 30 year mortgage. Assume payment is made at beginning of the period 1) What will her payment be for a 20 or 30 year mortgage. Assume a...

  • I need to use the excel functions to find the correct answers. The first picture has...

    I need to use the excel functions to find the correct answers. The first picture has the correct numerical values. Please solve the rest, and show me how you got those answers. A F G 1 B с D E Xercise Cycles Company sells exercise equipment to gyms on account. At year end, the following accounts receivable were uncollected. 2 3 Required: 4 1) Calculate the number of days unpaid, USING THE EXCEL DAYS FUNCTION (fx). 5 6 Number of...

  • what are the correct answers? sorry for the long post, just need some help :) Please...

    what are the correct answers? sorry for the long post, just need some help :) Please use the following information to answer Questions 7-9: At the end of January, the unadjusted trial balance of Windsor, Inc. included the following accounts: Debit Credit $500.000 Sales (80%o represent credit sales) Accounts Receivable Allowance for Doubtful Accounts $340,000 $800 7. Windsor uses the balance sheet approach in estimating uncollectible accounts expense, and aging the accounts receivable indicates the estimated uncollectible portion to be...

  • I am in desperate need of help with this project. Apparently my answers are all wrong....

    I am in desperate need of help with this project. Apparently my answers are all wrong. 3:02 PM Thu Feb 27 4796% AC 321 Intl - FA19 - Comprehensive Project Insert Draw Formulas Data Review View 8 Q o Calibri (Body) 12 B U Home B ^ A = 1 EE tx ã į 19 2 3 4 All work must be completed using this Excel spreadsheet. All journal extries, financial statements, etc. must be prepared in clean, proper form....

  • I need this answered in Microsoft Excel please GBCoffee Budget Project The GBCoffee Company roasts and...

    I need this answered in Microsoft Excel please GBCoffee Budget Project The GBCoffee Company roasts and sells high quality certified sustainable coffee beans. The final one pound bags of roasted whole coffee beans has two direct materials – coffee beans and packaging. GBCoffee is preparing budgets for the fourth quarter ending December 31, 2019. For each requirement below prepare budgets by month for October, November, and December, and a total budget for the quarter. 1. The previous year’s sales for...

  • I need Summary of this Paper i dont need long summary i need What methodology they used , what is the purpose of this...

    I need Summary of this Paper i dont need long summary i need What methodology they used , what is the purpose of this paper and some conclusions and contributes of this paper. I need this for my Finishing Project so i need this ASAP please ( IN 1-2-3 HOURS PLEASE !!!) SPECIAL ARTICLES tole of Monetary Policy C Rangarajan What should be the objectives of monetary policy? Does the objective of price stability conflict with the goal of achieving...

  • I need Summary of this Paper i dont need long summary i need What methodology they used , what is the purpose of this p...

    I need Summary of this Paper i dont need long summary i need What methodology they used , what is the purpose of this paper and some conclusions and contributes of this paper. I need this for my Finishing Project so i need this ASAP please ( IN 1-2-3 HOURS PLEASE !!!) Budgetary Policy and Economic Growth Errol D'Souza The share of capital expenditures in government expenditures has been slipping and the tax reforms have not yet improved the income...

  • How can we assess whether a project is a success or a failure? This case presents...

    How can we assess whether a project is a success or a failure? This case presents two phases of a large business transformation project involving the implementation of an ERP system with the aim of creating an integrated company. The case illustrates some of the challenges associated with integration. It also presents the obstacles facing companies that undertake projects involving large information technology projects. Bombardier and Its Environment Joseph-Armand Bombardier was 15 years old when he built his first snowmobile...

  • Read the Article posted below, then answer the following questions: Mergers & acquisitions are a major...

    Read the Article posted below, then answer the following questions: Mergers & acquisitions are a major form of corporate diversification strategy, identify and discuss the top three reasons why most (50-60%) of acquisitions fail to create shareholder value. What are the five major components of “CEMEX Way” and why has this approach been so successful in post-acquisition integration? In your opinion, what can other companies learn from the “CEMEX Way” as a benchmark for acquisition management? Article: CEMEX: Globalization "The...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT