Ignore the bonus part. This is all the information we were given.

PART I. (50 points) Anna worked as an assistant at a real estate management company. She made $30,000 per year but wished to expand her earning potential by going back to school. She saved $40,000, which included gifts she received from relatives for her new adventure. Anna enrolled full time in school while continuing to work at her previous job half time through her school years. Anna graduated from business school four years later, in May, having spent her entire $40,000, with grants and scholarships covering the remainder of her costs. Her first job out of college began on June 1st of that same year. She worked as an analyst for a builder, making $50,000 per year. That December, after working for the builder for 7 months, she received a modest bonus of $3,000. During the following year, her salary was raised to $55,000 and her year-end bonus was $8,000. During the third year, her salary was $62,000 and her year-end bonus was $10,000. Use a blank workbook. Label your first sheet PARTI Calculate Anna's cash flows from year 0 through year 12, including salaries, bonuses, taxes, and amounts received. Also calculate Anna's likely salary had she continued as an assistant. After the stated amounts above, assume that Anna's prior salary and her new salary would grow with inflation, which is expected to be 3% per year. HINT: in several years, Anna's quoted annual salary will need to be modified to reflect mid-year changes. Use the tax tables below: Single-Taxable income 2019 Individual Income Tax Rates 10 percent 12 percent 22 percent 24 percent 32 percent 35 percent 37 percent 0 to $9,700 $9,701 to $39,475 $39,476 to $84,200 $84,201 to $160,725 $160,726 to $204,100 $204,101 to $510,300 $510,301 and up 1. Ignoring the time value of money, calculate the time it will take for Anna to realize a return on her investment. Express your answer in years and months, rounded down to the nearest month. 2. Calculate the net present value of Anna's cash flow through the 12th year, using the inflation rate as the PV rate. Place these answers in a new sheet called Summary PART II. (50 points) After some time, and success in her new position, Anna has amassed $1,000,000. She would like to place this money in a fund or a CD and analyzes the following three funds: 12b-1 Fee Expense ratio Frond-end load Back-end load fees Mutual Fund 0.00% 0.50% 4% Mutual Fund 0.00% 0.50% 4% Mutual Fund 0.75% 0.50% 0% 0% 0% 1% She has identified a CD which yields 6%, with no fees, and would like to know, of the structures shown above, what return she must achieve on the funds in order to beat the CD. In a new sheet called PART II and display results in the Summary sheet. 1. Calculate the returns which must be achieved by these three funds in order to make them a better investment than the CD. Assume a 2 year time horizon. 2. If Anna decides to invest the money for the purchase of a new home in 5 years, would this change her choice? PART III. (EXTRA CREDIT 25 Points) After 2 years, Anna decides it is time to purchase her first home, using the 1,000,000 as her down payment. She elects to leave any interest or capital gains in the fund she chose as cash investment. If real estate has risen steadily in the last few years at an average growth rate of 7%, would Anna be better off using all of her money to invest in the new home? Place calculations for this part in a sheet called "PART III" and place your answer in the Summary sheet. 1. You are calculating the NPV of her choice to return to school, but this must include the opportunity cost. Create a row (or column, if you store your calculations in columns) which represents her current expectations of salary for her assistant job. This will start with 30,000 in year 0 and grow at the rate of inflation through year 12. 2. The NPV will be performed on the difference between your data points for years 1 through 12 for the following: New salary (both through school and after graduation) for years 1 through 12 MINUS the amount she dips into her savings for years 1 through 5 (8 semesters starting in year 1 and ending when she graduates in year 5 total 40,000) MINUS her baseline salary from item 1 above. HINT: year O is NOT included in the calculation of NPV. 3. To find the point at which she recovers her 40,000 you must find the point at which the CUMULATIVE values for the row (or column) from item 2 above where the cumulative equals 40,000 and then round down to the nearest month. You should display this in years and months. PART I. (50 points) Anna worked as an assistant at a real estate management company. She made $30,000 per year but wished to expand her earning potential by going back to school. She saved $40,000, which included gifts she received from relatives for her new adventure. Anna enrolled full time in school while continuing to work at her previous job half time through her school years. Anna graduated from business school four years later, in May, having spent her entire $40,000, with grants and scholarships covering the remainder of her costs. Her first job out of college began on June 1st of that same year. She worked as an analyst for a builder, making $50,000 per year. That December, after working for the builder for 7 months, she received a modest bonus of $3,000. During the following year, her salary was raised to $55,000 and her year-end bonus was $8,000. During the third year, her salary was $62,000 and her year-end bonus was $10,000. Use a blank workbook. Label your first sheet PARTI Calculate Anna's cash flows from year 0 through year 12, including salaries, bonuses, taxes, and amounts received. Also calculate Anna's likely salary had she continued as an assistant. After the stated amounts above, assume that Anna's prior salary and her new salary would grow with inflation, which is expected to be 3% per year. HINT: in several years, Anna's quoted annual salary will need to be modified to reflect mid-year changes. Use the tax tables below: Single-Taxable income 2019 Individual Income Tax Rates 10 percent 12 percent 22 percent 24 percent 32 percent 35 percent 37 percent 0 to $9,700 $9,701 to $39,475 $39,476 to $84,200 $84,201 to $160,725 $160,726 to $204,100 $204,101 to $510,300 $510,301 and up 1. Ignoring the time value of money, calculate the time it will take for Anna to realize a return on her investment. Express your answer in years and months, rounded down to the nearest month. 2. Calculate the net present value of Anna's cash flow through the 12th year, using the inflation rate as the PV rate. Place these answers in a new sheet called Summary PART II. (50 points) After some time, and success in her new position, Anna has amassed $1,000,000. She would like to place this money in a fund or a CD and analyzes the following three funds: 12b-1 Fee Expense ratio Frond-end load Back-end load fees Mutual Fund 0.00% 0.50% 4% Mutual Fund 0.00% 0.50% 4% Mutual Fund 0.75% 0.50% 0% 0% 0% 1% She has identified a CD which yields 6%, with no fees, and would like to know, of the structures shown above, what return she must achieve on the funds in order to beat the CD. In a new sheet called PART II and display results in the Summary sheet. 1. Calculate the returns which must be achieved by these three funds in order to make them a better investment than the CD. Assume a 2 year time horizon. 2. If Anna decides to invest the money for the purchase of a new home in 5 years, would this change her choice? PART III. (EXTRA CREDIT 25 Points) After 2 years, Anna decides it is time to purchase her first home, using the 1,000,000 as her down payment. She elects to leave any interest or capital gains in the fund she chose as cash investment. If real estate has risen steadily in the last few years at an average growth rate of 7%, would Anna be better off using all of her money to invest in the new home? Place calculations for this part in a sheet called "PART III" and place your answer in the Summary sheet. 1. You are calculating the NPV of her choice to return to school, but this must include the opportunity cost. Create a row (or column, if you store your calculations in columns) which represents her current expectations of salary for her assistant job. This will start with 30,000 in year 0 and grow at the rate of inflation through year 12. 2. The NPV will be performed on the difference between your data points for years 1 through 12 for the following: New salary (both through school and after graduation) for years 1 through 12 MINUS the amount she dips into her savings for years 1 through 5 (8 semesters starting in year 1 and ending when she graduates in year 5 total 40,000) MINUS her baseline salary from item 1 above. HINT: year O is NOT included in the calculation of NPV. 3. To find the point at which she recovers her 40,000 you must find the point at which the CUMULATIVE values for the row (or column) from item 2 above where the cumulative equals 40,000 and then round down to the nearest month. You should display this in years and months