Personal Finance Using Discrete Dynamical Systems This project is an individual effort, but you may discuss your final answers. You should, however, do your own Excel work and prepare your own report. The report should be professional quality, and include appropriate supporting graphs and tables from Excel. Problem Statements: PART 1.4 You wish to buy a new house for $250,000. A well-known financial guru recommends your total house payment (principal, interest, and PMI) be no more than 25% of your take-home pay. Since your monthly take home pay is $5400, you have allocated at most $1350 a month for your house payment. You have the following options for financing: Loan Down Points Interest Monthly Term Payment Rate PMI A $10,000 0 3.140% $130 30 year, fixed B $50,000 1.609 2.875% SO 30 year, fixed C $50,000 2.625% $0 15 year, fixed Discount points are paid at the time of closing to obtain a lower interest rate. One point equals one percent of the loan amount and typically lowers the interest rate by about 0.25 percent. This is a one-time payment made when you take out the loan. For example, if you were borrowing $100,000, a typical offer would be to let you pay 1% of the loan amount ($1,000) up front in order to lower your interest rate by 0.25%. For Loan A, the lender charges an additional $130 every month for private mortgage insurance (PMI). This payment does not pay down your remaining balance - you are merely buying insurance to make sure the bank gets its money if you default on your loan. However, that extra monthly charge does increase the total cost of the loan. Use a dynamical system to compare the alternatives, choose a loan, and establish your exact monthly payment. In your report, discuss the pros and cons of each loan and include the Styles For Loan A, the lender charges an additional $130 every month for private mortgage insurance (PMI). This payment does not pay down your remaining balance - you are merely buying insurance to make sure the bank gets its money if you default on your loan. However, that extra monthly charge does increase the total cost of the loan Use a dynamical system to compare the alternatives, choose a loan, and establish your exact monthly payment. In your report, discuss the pros and cons of each loan and include the rationale for your choice of loan. Be sure to address not only the monthly payment, but the total cost of purchasing your home: purchase price, points paid, PMI, and total interest paid. PART 1.B For the Loan option of your choice, what is the effect of paying an additional $200 per month toward the principal? How much will this reduce the total amount of money paid on the house? How long will it take to pay off the mortgage? PART 2-Suppose, starting at age 25, you put $6000 in a Roth IRA each year until you are 65 (no contribution in the year you turn 65). If you invest in mutual funds, you can (historically) average 11% return each year. How much money would you have in the account at age 6S? How much could you withdraw per year (starting at age 65) if you wanted the money to last until you were 100 years old? Paragraph Styles Expectations Your submission will be two files, an Excel file (to show your technical skills) and a written report . O For your Excel file: You must interpret the problem statements and implement discrete dynamical systems to solve the problems in Excel. o "Hard-coding" values in your formulas makes it much more difficult to re-use your spreadsheet. Use cell references (preferably,"named cells") to make it easy to check out different options. O Double-check your iteration equations. Use the IF() function to make your formulas general enough that you don't have to make any changes "by hand" if you change the inputs. For the home loan: o (1) show the status (principal balance, interest accrued, payment, additional payment, and PMI paid) for each month; (2) use a formula to calculate the total amount paid (remember to include down, closing, and PMI payments!). For the Roth IRA: o (1) calculate the amount available at the beginning of the year that you turn 65 (before any withdrawals); o (2) use the Solver add-in to determine the maximum annual withdrawal amount you can take and still have some money in your IRA the year you turn 100; o (3) remember to leave your remaining IRA funds invested at 11% (even after age 65); and (4) cedo your analysis with at least three other rates of return (say, 6%, 8%, and 12%), with each rate of return" case on a separate Excel worksheet (so you you take Subtitle Title Font F Paragraph Tuse me guver du veterime maxTTITUT WICTIUTJWar IT OUTIL Styles you can take and still have some money in your IRA the year you turn 100; o (3) remember to leave your remaining IRA funds invested at 11% (even after age 65); and o (4) redo your analysis with at least three other rates of return (say, 6%, 8%, and 12%), with each "rate of return" case on a separate Excel worksheet (so you should have at least four worksheets for the IRA part of your workbook). Write a professional quality report (3-5 pages, including graphs) that discusses your findings: . For the home loan analysis: o Compare the total costs of the three options. o Choose one of the options, and show what happens if you pay an additional $200 towards the principal each month. Compare total cost with that of the original payment plan. For the IRA analysis: o How much have you accumulated by age 65 (just before starting your withdrawals)? o What is the largest amount you can withdraw each year and still have a positive balance after you age 100 distribution? . O What impact does the rate of return have on your retirement withdrawals? Discuss how well you could live (e.g., what things could you do) with the different resulting amounts of withdrawal. Communication skills count! Your submission should be free of grammar and spelling errors, and clearly communicate your findings. Incorporating supporting graphs directly in your document (and explaining them) will make your report easier to understand. Personal Finance Using Discrete Dynamical Systems This project is an individual effort, but you may discuss your final answers. You should, however, do your own Excel work and prepare your own report. The report should be professional quality, and include appropriate supporting graphs and tables from Excel. Problem Statements: PART 1.4 You wish to buy a new house for $250,000. A well-known financial guru recommends your total house payment (principal, interest, and PMI) be no more than 25% of your take-home pay. Since your monthly take home pay is $5400, you have allocated at most $1350 a month for your house payment. You have the following options for financing: Loan Down Points Interest Monthly Term Payment Rate PMI A $10,000 0 3.140% $130 30 year, fixed B $50,000 1.609 2.875% SO 30 year, fixed C $50,000 2.625% $0 15 year, fixed Discount points are paid at the time of closing to obtain a lower interest rate. One point equals one percent of the loan amount and typically lowers the interest rate by about 0.25 percent. This is a one-time payment made when you take out the loan. For example, if you were borrowing $100,000, a typical offer would be to let you pay 1% of the loan amount ($1,000) up front in order to lower your interest rate by 0.25%. For Loan A, the lender charges an additional $130 every month for private mortgage insurance (PMI). This payment does not pay down your remaining balance - you are merely buying insurance to make sure the bank gets its money if you default on your loan. However, that extra monthly charge does increase the total cost of the loan. Use a dynamical system to compare the alternatives, choose a loan, and establish your exact monthly payment. In your report, discuss the pros and cons of each loan and include the Styles For Loan A, the lender charges an additional $130 every month for private mortgage insurance (PMI). This payment does not pay down your remaining balance - you are merely buying insurance to make sure the bank gets its money if you default on your loan. However, that extra monthly charge does increase the total cost of the loan Use a dynamical system to compare the alternatives, choose a loan, and establish your exact monthly payment. In your report, discuss the pros and cons of each loan and include the rationale for your choice of loan. Be sure to address not only the monthly payment, but the total cost of purchasing your home: purchase price, points paid, PMI, and total interest paid. PART 1.B For the Loan option of your choice, what is the effect of paying an additional $200 per month toward the principal? How much will this reduce the total amount of money paid on the house? How long will it take to pay off the mortgage? PART 2-Suppose, starting at age 25, you put $6000 in a Roth IRA each year until you are 65 (no contribution in the year you turn 65). If you invest in mutual funds, you can (historically) average 11% return each year. How much money would you have in the account at age 6S? How much could you withdraw per year (starting at age 65) if you wanted the money to last until you were 100 years old? Paragraph Styles Expectations Your submission will be two files, an Excel file (to show your technical skills) and a written report . O For your Excel file: You must interpret the problem statements and implement discrete dynamical systems to solve the problems in Excel. o "Hard-coding" values in your formulas makes it much more difficult to re-use your spreadsheet. Use cell references (preferably,"named cells") to make it easy to check out different options. O Double-check your iteration equations. Use the IF() function to make your formulas general enough that you don't have to make any changes "by hand" if you change the inputs. For the home loan: o (1) show the status (principal balance, interest accrued, payment, additional payment, and PMI paid) for each month; (2) use a formula to calculate the total amount paid (remember to include down, closing, and PMI payments!). For the Roth IRA: o (1) calculate the amount available at the beginning of the year that you turn 65 (before any withdrawals); o (2) use the Solver add-in to determine the maximum annual withdrawal amount you can take and still have some money in your IRA the year you turn 100; o (3) remember to leave your remaining IRA funds invested at 11% (even after age 65); and (4) cedo your analysis with at least three other rates of return (say, 6%, 8%, and 12%), with each rate of return" case on a separate Excel worksheet (so you you take Subtitle Title Font F Paragraph Tuse me guver du veterime maxTTITUT WICTIUTJWar IT OUTIL Styles you can take and still have some money in your IRA the year you turn 100; o (3) remember to leave your remaining IRA funds invested at 11% (even after age 65); and o (4) redo your analysis with at least three other rates of return (say, 6%, 8%, and 12%), with each "rate of return" case on a separate Excel worksheet (so you should have at least four worksheets for the IRA part of your workbook). Write a professional quality report (3-5 pages, including graphs) that discusses your findings: . For the home loan analysis: o Compare the total costs of the three options. o Choose one of the options, and show what happens if you pay an additional $200 towards the principal each month. Compare total cost with that of the original payment plan. For the IRA analysis: o How much have you accumulated by age 65 (just before starting your withdrawals)? o What is the largest amount you can withdraw each year and still have a positive balance after you age 100 distribution? . O What impact does the rate of return have on your retirement withdrawals? Discuss how well you could live (e.g., what things could you do) with the different resulting amounts of withdrawal. Communication skills count! Your submission should be free of grammar and spelling errors, and clearly communicate your findings. Incorporating supporting graphs directly in your document (and explaining them) will make your report easier to understand