Personal Finance Advice

Project 1: Personal Finance Advice

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 Liabilities
Katrina 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.

IRA
Katrina 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.
Options for Analysis
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 in 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.
Calculation Suggestions
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 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:
I_n=B_(n-1)*i_m
B_n=B_(n-1)+I_n-A-X_n
where n is the period (in months), i_m is the effective monthly interest rate, B_n is the ending balance of the loan in period n, I_n is the interest accrued in period n, A is the regular monthly payment, and X_n 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 I_31=$450 and her ending balance in month 31 is B_31=150000+450-800-1000=$148,650. (Note: These numbers do not correspond to the real 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(1+i_m )+1000 where i_m is the effective monthly interest of the IRA.
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).
Deliverable
You should evaluate each of these options and write a professional memorandum (memo) outlining Katrina’s options and advising her on which option she should pursue. See the attached Word document “Writing a professional memo.docx” for expectations and guidelines on writing a professional memo.
You should upload your memo via Canvas. You can also upload your Excel sheet. Your grade will be docked by 10 points for each day that you submit the memo after the due date.
The memo should contain 8-9 paragraphs. The first paragraph should be an executive summary that summarizes your analysis and provides your final recommendation. Each paragraph after the executive summary should analyze an individual option, and you should briefly explain the option and discuss that option’s advantages and disadvantages. You should explain how much will be in the IRA at the end of 35 years for each option. You should not go into details about the calculations in the memo, but you should explain how Katrina should spend her money for each option.
The final one or two paragraphs should briefly compare the options and explain the option that you recommend. You should try to provide some intuition for why that option is the best. You can also mention other things that Katrina might want to consider that are not part of the calculations in these two paragraphs.
It would be really good to include a figure in the memo which has number of months on the x-axis and value in the IRA on the y-axis. Plot a line for each option showing how the value of the IRA increases over the 35 years.