Answered step by step
Verified Expert Solution
Link Copied!

Question

00
1 Approved Answer

i do not understand this example problem, where do i even start to get an answer ? 2 In cell D19, by using cell references,

i do not understand this example problem, where do i even start to get an answer ?
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
2 In cell D19, by using cell references, calculate the future value of the money market account after one year. Use the Excel FV function. Maka absolute references to the account balance in cell D18 and the relevant growth rate from the Glven Data section. Note: The output of the function you typed in this cell is expected as a positive number. 3 In cell E19, by using cell references, calculate the future value of the government bond mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell E18 and the relevant growth rate from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. 4 In cell F19, by using cell references, calculate the future value of the large capital mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell F18 and the relevant growth rate from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. 6 In cell G19, by using cell references, calculate the future value of the small capital mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell G18 and the relevant growth rate from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. In cell H19, by using cell references, calculate the future value of the real estate trust fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell H18 and the relevant growth rate from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. 7 In cell range D20:H38, by using cell references, calculate the future values of each of the five accounts at the end of each year for years 2 through 20. Copy the contents from cell range D19:H19 down the columns to row 38. In cell 118, by using cell references, calculate the current value of the total portfolio. Use the Excel SUM function. 9 In cell range 119:138, by using cell references, calculate the future values of the total portfolio at the end of each year for the next 20 years. Copy the function cell 118 down the columns to row 38. 10 In cell H40, enter the number of years it will take for Rachel and Richard to reach their goal. Do not use the equal sign when entering a numeric value. Future value of a portfolio. Rachel and Richard want to know when their current portfolio will be sufficient for them to retire. They have the following balances in their portfolio: Money market account: $37,000 Government bond mutual fund: $140,000 Large capital mutual fund: $107.000 Small capital mutual fund: $71,000 Real estate trust fund: $87,000 Rachel and Richard believe they need at least $2,000,000 to retire. They assume the money market account grows at 2.5% annually, the government bond mutual fund grows at 5.5% annually, the large capital mutual fund grows at 9.5% annually, the small capital mutual fund grows at 12.0% annually, and the real estate trust fund grows at 4.0% annually. Using a spreadsheet, calculate the end-of-year balance for the portfolio with the assumption that no more funds will be deposited into any of these accounts. How long will it be until they reach the $2,000,000 goal? Given Data Money market account Government bond mutual fund Large capital mutual fund Small capital mutual fund Real estate trust fund Annual growth rate 2.5% 5.5% 9.5% 12.0% 4.0% Solution Portfolio Total Year 0 Money Market $37,000.00 Government Bonds $140,000.00 Large Capital $107,000.00 Surall Capital $71,000.00 Real Estate $87,000.00 2 3 4 5 6 3-1 + Money market account Government bond mutual fund Large capital mutual fund Small capital mutual fund Real estate trust fund Annual growth rate 2.5% 5.5% 9.5% 12.0% 4.0% Solution Portfolio Total Money Market $37,000.00 Government Bonds $140,000.00 Large Capital $107,000.00 Small Capital $71,000.00 Real Estate $87,000.00 Year 0 1 2 3 4 S 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 How long will it be until Rachel and Richard reach the $2,000,000 goal to retire? lyears

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Finance questions