Please answer question 1 & 2 with screenshot of what excel looks like/ what formulas are

Background You received recently a $100,000 inheritance from your great uncle Wilberforce. You and your spouse are concerned with what to do with the money. Salary You and your spouse's salaries amount to $80,000 , and you confidently expect that total to grow at 15% per year. You have decided to keep a tight budget and peg your family expenditures to a fixed percent, tentatively set at 75%, of your salaries. Of course, since your salaries would grow, so will these expenditures. Note that these are based on gross salaries not net after taxes. Assume that you invest your savings at the end of every year in the Mutual Fund Investment described below. Taxes The Congress has just passed a new tax bill, one with a flat-tax rate. Under this law, a couple, such as you and your spouse, have a deduction of $15,000, and the combined state and federal tax rate is 35% for all income above this base of $15,000. Also a capital gains tax has been instituted: 40% of all capital gains are taxed as regular income. Tax shelter investment You have the option of investing any amount of your funds in a real estate venture. One advantage of this investment is that it will incur accounting losses (tax losses) due to depreciation in each of the next 5 years while simultaneously returning a small non-taxable cash payment. At the end of the 5 years the property will be sold, you will share in the profits, and you will pay capital gain taxes on these profits. Calculate the Capital Gains Tax for Year 5 as the Capital Gains (Selling price - buying price) multiplied by 40% and then multiplied by the Income Tax Rate of 35%. For each $1000 invested in the project, the following are the various factors: Annual tax loss Annual cash payment to you Amount returned at end of Year 5 $200 $40 (non-taxable) $1800 Mutual Fund Investment The only other investment you are considering is a money market fund. This fund pays interest at a rate of 14% per year and this interest income is taxable. You can invest any amount in this fund at anytime. For convenience, assume the interest is paid in any year on the balance at the beginning of that year (the end of the prior year). The $100,000 inheritance has been tentatively placed in the money market fund but can be withdrawn immediately if needed. You and your spouse wish to build a personal financial model that will allow you to see how your wealth (market fund balance) will grow by the end of 5 years. You wish to use this model to decide how much to invest in the tax shelter investment, and to examine how sensitive your plan is to some of the assumptions made. Questions Use your model to answer the following questions: 1. How much should you invest in the tax shelter plan? 2. Suppose your salary growth rate is only 10%. How does this affect your wealth at the end of five years? Explain this result. 3. Increase each of the tax shelter investment parameter by 10%. (Do each separately). Which has the greatest impact on your five-year wealth? 4. What is the maximum percentage of your salaries that you can consume (spend) without running into debt (so that your net savings each year is never negative)? 5. What would the yield on the mutual fund have to be for you to be indifferent between investing fully in the real estate investment and investing nothing in it? You must present a Microsoft Excel worksheet as a deliverable and submit it in Blackboard. No other documents must be submitted. Use one Sheet for each response (you may copy your sheet several times to answer each question). No two questions should be answered in the same sheet. You must produce any graphs that help explain your answers. Your Excel should be formatted as explained in class: Decision Variables Exogenous Parameters Other variables Financial Results K Background You received recently a $100,000 inheritance from your great uncle Wilberforce. You and your spouse are concerned with what to do with the money. Salary You and your spouse's salaries amount to $80,000 , and you confidently expect that total to grow at 15% per year. You have decided to keep a tight budget and peg your family expenditures to a fixed percent, tentatively set at 75%, of your salaries. Of course, since your salaries would grow, so will these expenditures. Note that these are based on gross salaries not net after taxes. Assume that you invest your savings at the end of every year in the Mutual Fund Investment described below. Taxes The Congress has just passed a new tax bill, one with a flat-tax rate. Under this law, a couple, such as you and your spouse, have a deduction of $15,000, and the combined state and federal tax rate is 35% for all income above this base of $15,000. Also a capital gains tax has been instituted: 40% of all capital gains are taxed as regular income. Tax shelter investment You have the option of investing any amount of your funds in a real estate venture. One advantage of this investment is that it will incur accounting losses (tax losses) due to depreciation in each of the next 5 years while simultaneously returning a small non-taxable cash payment. At the end of the 5 years the property will be sold, you will share in the profits, and you will pay capital gain taxes on these profits. Calculate the Capital Gains Tax for Year 5 as the Capital Gains (Selling price - buying price) multiplied by 40% and then multiplied by the Income Tax Rate of 35%. For each $1000 invested in the project, the following are the various factors: Annual tax loss Annual cash payment to you Amount returned at end of Year 5 $200 $40 (non-taxable) $1800 Mutual Fund Investment The only other investment you are considering is a money market fund. This fund pays interest at a rate of 14% per year and this interest income is taxable. You can invest any amount in this fund at anytime. For convenience, assume the interest is paid in any year on the balance at the beginning of that year (the end of the prior year). The $100,000 inheritance has been tentatively placed in the money market fund but can be withdrawn immediately if needed. You and your spouse wish to build a personal financial model that will allow you to see how your wealth (market fund balance) will grow by the end of 5 years. You wish to use this model to decide how much to invest in the tax shelter investment, and to examine how sensitive your plan is to some of the assumptions made. Questions Use your model to answer the following questions: 1. How much should you invest in the tax shelter plan? 2. Suppose your salary growth rate is only 10%. How does this affect your wealth at the end of five years? Explain this result. 3. Increase each of the tax shelter investment parameter by 10%. (Do each separately). Which has the greatest impact on your five-year wealth? 4. What is the maximum percentage of your salaries that you can consume (spend) without running into debt (so that your net savings each year is never negative)? 5. What would the yield on the mutual fund have to be for you to be indifferent between investing fully in the real estate investment and investing nothing in it? You must present a Microsoft Excel worksheet as a deliverable and submit it in Blackboard. No other documents must be submitted. Use one Sheet for each response (you may copy your sheet several times to answer each question). No two questions should be answered in the same sheet. You must produce any graphs that help explain your answers. Your Excel should be formatted as explained in class: Decision Variables Exogenous Parameters Other variables Financial Results K