Can someone help me solve this problem?
In this assigriment, you will be estimating how much you will need to save monthy to fund your retireinent. There are five steps to tigure this out. Each step is covered on its own worksheet (tabs below). First, you will need to determine how much income you need per month to fund your ratirement. Second, you will estimate how much taxes you wiil have to pay per month. Third, you will estimate how much income you will need per year after you adjust for inflation. Fourth, you will estimate your total retirement need. Finally, you will estimate the amount you must invest monthly to meet your total retirement need. This excercise can actually be used as your retirement plan. The more detalled your values are the more accurate your projections will be. It is my hope that you use this to reach your retirement goals. Important items: 1) You will need to input values for the red cells. Values for the yellow celts are optional, Partial credit will only be given IF all required (red) cells are completedi. If you fall to input values for ANY of the red cells, you will not get credit for this assignment. 2) You are not required to use excel functions to complete the assignment, but you can if you want. 3) I created notes for some of the cells to help you determine what values to use if you aren't too sure. 4) You are welcome to work with others on this, but values should be unique to you, i will be checking for plagiarism. 5) The worksheet is protected, so you will not be able to click on certain cells within it. I did this to prevent accidental deletion of vital data and to make the submission consistently formatted. 6) Save your work often, 7) Submit the completed workbook in the submission within our Canvas course. Piease click on the "ExpensesToday" tab to besin. Any questions? Please email me at mikemc@nmsu.edu For this worksheet, please list all current monthly expenses that you expect to have whon you start retirement below. We wili use these expenses to determine our monthly incomeneed. Values should be in todiy's dollars. We will adjuat. them for infation later. I have listed some examples of expenses that may apply to you. Please use the empty spaces to add any other expenses that you may have. The more detalted you are, the more useful this will be, At a minimum, you must input non-zero values for the first 13 expense items unless note says otherwise. Please use actual expense amounts where you cin. Use estimated values in note if you are unsure of the oxpense amount. 2 Home/renter's insurance premum 3 Property taxar 4 Electric bill 5 Gas/Water/Waste bill 6 Internet bill 7. Cell/Telephone bill 8 TV/cable bill 9 Food cost 10 Car payment 11 Fuel cost 12. Car insurance premiums 13 Health insurance premiums 14 Emergency fund 15 Fun money 16 Travel fund 1718192021222324252627282930 Total monthily expenses In the previous worlicheet we calculared the amount of income we would need to cover our projected expenses if we retired today. Most likely, that income will be taxed, so we will want to make sure that we include the amount we will have to pay in taxes. For simplicity, lets use the marginal tax bracket that our last dollar of income would be taxed at using the tax table below. This will overestimate the amount we will pay, but it is better to be overfunded than underfunded when it comes to retirement. Use the total amount of expenses from the previous worksheet to identify. the marginal rate. Multiply the marginal tax rate and the total monthly expense amount to calculate monthly taxes. Add monthly taxes to total monthly expenses to get total monthly need. This value is the total amount we need to fund retirement in today's dollars. Now that we know thow much we need to cevire next month, we canforecast what wa wall need per morth starting at the time we actually want to retire. Inffation is going to errode our future buying power and increase the amount of cash will need when we retire. Accordingly, we can adjust today'a need for inflation by caleulating the future value of ouf need using average montly inflation as our compounding rate. We will use the average montly rate of infiation for the Last 120 months ( 10 yeara) as ouf compounding rate. Hiatorical infiation values were downioaded from hrips://data.bls. Eoy for the period of 1957 to 2022 using series id: CUUR00005A0L1E on 11/28/2022. Answer the questions below. FV is the value of interest. It is the amount of income we will need the first month of. retirement, adjusted for infiation. We need this value to determine the total amount of money we need to fund our retirement. How old, in years, are you todayl What age do you want to retirel How many years do you have until you retirel Mistorical Monthly Infiation Date We have one last step to go. Wa need to figure out how much we need to invest each month to fund our retinemant. We should have a lot of time to to do thls. That means compounding will work in our favor. We can also make more agresslve Investments since we will have more time to racover in the event wa lose a ton of valus. An example of mere risky investments would be stocks, so why not use the 55.500, a collection of stocks from 500 different companies, to estimate our returns during the pre-retirement period? The 58.500 has averaged an 9.01%6 annual return for the period of Ianuary of 2000 through December 2021 (source: https:/loses.stern.nyu.edu/4adamodar/New.Home_Page/dotafle/histretsP.html accessed on 11/28/22) NOTEI we do NOT need to account for inflation in this atep because we accounted for it when we calculated the smount of income needed for the first month of retirement. Like the problems in the previous workshests, we will use time-value-of-money to solve this problem. Our variable of interest in this problem is PMT. In this assignment, you will be estimating how much you will need to save monthy to fund your retirement, There are five steps to figure this out. Each step is covered on its own worksheet (tabs below). First, you will need to determine how much income you need per month to fund your retirement. Second, you will estimate how much taxes you will have to pay per month. Third, you will estimate how much income you will need per year after you adjust for inflation. Fourth, you will estimate your total retirement need. Finally, you will estimate the amount you must invest monthly to meet your total retirement need. This excercise can actually be used as your retirement plan. The more detalled your values are the more accurate your projections will be. It is my hope that you use this to reach your retirement goals. Important items: 1) You will need to input values for the red cells. Values for the yellow cells are optional. Partial credit will only be given IF all required (red) cells are completed. If you fall to input values for ANY of the red cells, you will not get credit for this assignment. 2) You are not required to use excel functions to complete the assignment, but you can if you want. 3) I created notes for some of the cells to help you determine what values to use if you aren't too sure. 4) You are welcome to work with others on this, but values should be unique to you. I will be checking for plagiarism. 5) The worksheet is protected, so you will not be able to click on certain cells within it. I did this to prevent accidental deletion of vital data and to make the submission consistently formatted. 6) Save your work often. 7) Submit the completed workbook in the submission within our Canvas course. For this worksheet, please list all current monthly expenses that you expect to have when you start retirement below. We will use these expenses to determine our monthly income need. Values should be in todays dollars. We will adjust them for inflation later. I have listed some examples of expenses that may apply to you. Please use the empty spaces to add any other expenses that you may have. The more detailed you are, the more useful this will be. At a minimum, items unless note says otherwise. Please use actual expense f you are unsure of the expense amount. In the previous worksheet we calculated the amount of income we would need to cover our projected expenses if we retired today. Most likely, that income will be taxed, so we will want to make sure that we include the amount we will have to pay in taxes. For simplicity, lets use the marginal tax bracket that our last dollar of income would be taxed at. using the tax table below. This will overestimate the amount we will pay, but it is better to be overfunded than underfunded when it comes to retirement. Use the total amount of expenses from the previous worksheet to identify the marginal rate. Multiply the marginal tax rate and the total monthly expense amount to calculate monthly taxes. Add monthly taxes to total monthly expenses to get total monthly need. This value is the total amount we need to fund retirement in today's dollars. Now that we know how much we need to retire next month, we can forecast what we will need per month starting at the time we actually want to retire. Inflation is going to errode our future buying power and increase the amount of cash will need when we retire. Accordingly, we can adjust today's need for inflation by calculating the future value of our need using average montly inflation as our compounding rate, We will use the average montly rate of inflation for the last 120 months (10 years) as our compounding rate. Historical inflation values were downloaded from https://data.bls.gov for the period of 1957 to 2022 using series id: CUUROOOOSAOL1E on 11/28/2022. Answer the questions below. FV is the value of interest. It is the amount of income we will need the first month of retirement, adjusted for inflation. We need this value to determine the total amount of money we need to fund our retirement. How old, in years, are you today? What age do you want to retire? How many years do you have until you retire? Financial Calculator inputs N=I=PV=PMT=FV= Historical Monthly inflation Data At this point we know how much income we will need to fund our first month of retirement. Its time to determine how much we will need to fund our entire retirement. First we need to estimate our life expectancy. There are many resources out there, but we are going to use the one on the Social Security website. Please visit https://www.ssa.gov/oact/population/longevity.html and answer the two questions to get your life expectancy. There will be four life expectancies given. Use the largest one for your projection. It is better to overfund, than underfund. We will use the life expectancy to determine how many months of retirement we need to fund. Once in retirement, we will have a large sum of money that we will have saved. We are going to solve for that amount in this step. That amount will accrue interest over the period we are in retirement. Each month a small portion will be pulled out to cover that month's income needs, but the rest of the funds will accrue interest. The interest accrued depends on how aggressively we invest. Once we retire we will want to invest conservatively because we cannot risk Iosing a ton of value. A conservative investment would be US T-bonds. US T-bonds have averaged 5.30% annual returns for the period of January of 2000 through December 2021 (source: https://pages.stern.nyu.edu/ adamodar/New_Home_Page/datafile/histretSP.html accessed on 11/28/22). We will use this value to estimate monthly returns. Inflation will continue in retirement, 50 we will have to adjust our monthly return accordingly. Once we do that we are ready to calculate how much we need in total to fund our retirement
