Question

Your friend Sue has asked you to help her out as she is developing her financial plan. Help her come up with a plan for her finances and how she can set herself up for financial success!

She has an after tax income of $48,000 and budgets $30,000 for necessary expenses. This leaves $18,000 to spend on debt and savings annually. (Assume all annuity payments are in the form of ordinary annuities.)

Part A: Debt

1. Sue has a current balance of $20,000 on her credit card. She has a minimum monthly payment of $500 and an APR of 17.25% (divide by 12 to get the monthly rate). How many months will it take Sue to pay off her credit card debt?
2. Suppose Sue would like to purchase a new car. She believes she can spend $550 a month on a car. She has been approved for a 4.50% loan (divide by 12 for monthly rate) for 36 months. What is the maximum amount she can spend on a car as not to exceed her $550 a month budget?

Part B: Savings

1. Sue would like to save up for a down payment on a home she hopes to purchase in 5 years. If she wishes to have $20,000 saved up at the end of five years and can earn 3.5% annually in her savings account. If she would like to make equal annual deposits, what amount will her deposits need to be in order to reach her goal?
2. Sue received $20,000 as an inheritance from her uncle. He stipulated that she save this money for her 2 children's college education. She would like to have $50,000 saved up in 10 years. What annual interest must she earn in order to reach this goal (she will make no additional deposits to this account)?

Part C: Offering Advice

1. Calculate the total annual amount of debt and savings payments Sue has planned in the scenarios above.
2. If she has any of her $18,000 remaining after her credit card, auto loan, and savings are made offer advise on how this should be divided (make more than the minimum payments or make additional deposits to savings). Be sure to offer reasons on why she should choose to follow your advice rather than spend the cash.

Answer 1

Please find below table useful to compute desired results: -

A 1 17.25 500 20000 =NPER(B2/12,B3,0.-B4)*12 months 2 Interest 3 Periodic Payments 4 Future Value Number of periods 5 6 7 Periodic savings(PMT) 550 -4.5/100 36 |=FV(B8/12,B9,-B7,0, 1) 8 Interest 9 Months 10 Maximum amount she can spend 11 0.035 5 20000 |=PMT(B12,B13,0,-B14,1) 12 Interest 13 Tenure 14 Future Value 15 Sue need to save 16 20000 50000 17 Present value received 18 Future value required 19 Tenure 10 years RATE(B19,0.-B17,B18,1) 20 She needs to invest in a asset class which gives return of 21. Amount 550/month Debt Credit card 500/month Amount 22 Savings 23 Car 20000 24 Inherited amount(one time) 25 Home goals 3600 P.a

using excel will give you end results as follows: -

A. B A 2 Interest 17.25 Periodic Payments 500.00 3 4 Future Value 20,000.00 55 months Number of periods 5 6 7 Periodic savings(PMT 550.00 4.50% Interest 9 Months 36 10 Maximum amount she can spend 21,235.69 11 B 4% 12 Interest 13 Tenure 5 20,000.00 14 Future Value Sue need to save 3,603.50 15 16 Present value received 20,000.00 17 18 Future value required 50,000.00 10 years 19 Tenure 10% She needs to invest in a asset class which gives return of 20 21 C 22 Savings 23 Car 24 Inherited amount(one time) 25 Home goals Amount Debt Amount 550/month Credit card 500/month 20,000.00 3600 P.a