After graduation, you decide to join the firm Financial

After graduation, you decide to join the firm Financial Engineers Corp., which specializes in giving financial advice to both individuals and corporations. One of your first clients is a young engineer Katrina Hamilton who has hired Financial Engineers Corp. to help her make decisions about her financial future. Katrina plans to work for 35 years. She currently has credit card debt, and she is about to purchase a house. She also plans to purchase two vehicles during the next 35 years. Katrina’s goal is to maximize the amount of money that she will have in her Individual Retirement Account (IRA) at the end of 35 years and to pay off her credit card debt, her home mortgage, and her two vehicles. In order to provide advice to Katrina, your job is to determine which option will provide Katrina with the most money in her IRA at the end of 35 years. You also should ensure that Katrina has paid off her credit card debt, her mortgage, and both of her vehicles by the time she retires in 35 years.Income

After figuring out her daily living expenses, Katrina calculates that she will have $2,500 in each month during the first year that can go towards the credit card debt, home mortgage, vehicle, and her IRA. She expects that she will receive a 3% raise in salary in each year for the next 35 years, so she will have $2500(1+0.03) = $2,575 in each month during year 2 and $2575(1+0.03) = $2,652.25 in each month during year 3. This pattern continues for all 35 years. Current and Future LiabilitiesKatrina currently has $5,000 in credit card debt, and the credit card company charges her a nominal annual interest rate of 25%, compounded daily. Katrina is in the final stages of borrowing $200,000 to purchase a house. She has the option of choosing betweena 15-year mortgage with a nominal annual interest rate of 5.5%, compounded monthly, ora 30-year mortgage with a nominal annual interest rate of 4%, compounded monthly. For either of these mortgage options, Katrina must make her first payment in month 1.Katrina will also need to purchase a new vehicle in the future. She will purchase a $30,000 vehicle exactly 5 years from now and she will purchase a $45,000 vehicle exactly 20 years from now. She intends to borrow money to finance these vehicles. She will be charged a nominal annual interest rate of 4.9%, compounded monthly, with the loan to be repaid 5 years after purchasing the vehicle.

2IRAKatrina has the option of investing in an IRA. Katrina intends to retire exactly 35 years from now. The IRA will earn a nominal annual interest rate of 4.3%, compounded monthly. If Katrina deposits money into the IRA, she cannot withdraw that money until 35 years from now. The current value of her IRA is $0. You should evaluate 6 different options (3 options for the 15-year mortgage and 3 options for the 30-year mortgage).

Option 1: Katrina chooses the 15-year mortgage. She makes her monthly payment on the mortgage and initially uses the remainder ($2,500 – mortgage payment) to pay off her credit card debt. After her credit card debt has been eliminated, she invests the remainder in her IRA. When she purchases a vehicle, she borrows the entire amount ($30,000 for vehicle 1 and $45,000 for vehicle 2) at the 4.9% rate and makes monthly payments for the vehicle. She continues to invest whatever is left after making her monthly payments (for the mortgage and the vehicle) in her IRA. 

Option 2: Katrina chooses the 15-year mortgage. She makes her monthly payment on the mortgage and initially uses the remainder ($2,500 – mortgage payment) to pay off her credit card debt. After her credit card debt has been eliminated, she uses the remainder every month to pay down the balance on her mortgage. She borrows the entire amount to finance both of her vehicles, and she makes monthly payments on her vehicle loans. After the balance on her mortgage equals 0, she begins to invest in her IRA.

Option 3: Katrina chooses the 15-year mortgage. She makes her monthly payment on the mortgage and initially uses the remainder ($2,500 – mortgage payment) to pay off her credit card debt. After her credit card debt has been eliminated, she invests the remainder in her IRA. When she purchases a vehicle, she borrows the entire amount, but she stops investing in the IRA in month 61 for vehicle 1 and in month 241 for vehicle 2. She uses what she would have deposited in the IRA to pay down the balance on her vehicle loan. After the balance on her vehicle loan equals 0, she resumes investing in her IRA. 

Option 4: Same as option 1, but Katrina chooses the 30-year mortgage.

Option 5: Same as option 2, but Katrina chooses the 30-year mortgage.

Option 6: Same as option 3, but Katrina chooses the 30-year mortgage. 

Use the attached spreadsheet “Project 1.xlsx” to help you with the calculations. Each sheet corresponds to one of the first three options. Copy each sheet for options 4, 5, and 6. You should calculate everything in terms of months. Your calculations should include the value of Katrina’s IRA at the end of 35 years or 420 months. The cells that are in black indicate that no numbers are 3 needed in those cells. However, a cell that is not black does not necessarily mean that a number is needed in that cell. When calculating the monthly balance on the credit card debt (all options), the mortgage (option 2 or 5), and the vehicle loan (option 3 or 6), use the following equations from Chapter 4:????????=????????―1∗????????????????=????????―1+????????―????―????????where is the period (in months), is the effective monthly interest rate, is the ending ????????????????????balance of the loan in the period, is the interest accrued in the period, is the regular monthly ????????????????????payment, and is any additional amount to pay down the debt. For example, if the ending ????????balance on the mortgage in month 30 is $150,000, the effective monthly interest is 0.3%, the regular monthly payment is $800, and Katrina pays an additional $1,000 in month 31 on her mortgage, then and her ending balance in month 31 is ????31=$450????31. (Note: These numbers do not correspond to the =150000+450―800―1000=$148,650real numbers in the problem.)Each vehicle is purchased at the very end of the year. Vehicle 1 is purchased at the end of year 5, and the first payment on the vehicle will occur in month 61. Vehicle 2 is purchased at the end of year 20, and the first payment on the vehicle will occur in month 241. All deposits into the IRA are made at the very end of the month and do not earn any interest until the following month. For example, if the value of the IRA in month 50 is $30,000 and Katrina deposits $1,000 into the IRA in month 51, then the value of the IRA in month 51 equals $30000 where is the effective monthly interest of the IRA.(1+????????)+1000????????

For options 2 and 5 (where Katrina pays off her mortgage more quickly) and for options 3 and 6 (where Katrina pays off her vehicle loans more quickly), the number of months that she makes payments on the mortgage (options 2 and 5) or for the vehicle (options 3 and 6) is less than 15 or 30 years (for the mortgage) and less than 5 years (for the vehicle).