Demographic Information

Benjamin B$.$ Goode, born $6/15/1983,$ married to Karen A$.$ Badd, born July $2,1983.$ The couple has three children $($Carl$,$ born $1/12/2012$; Susie, born $2/15/2013$; and Edward, born $7/1/2014).$ All children go to private school and are in summer camps each year. Clients have no wills drafted. They have no prenuptial agreement.

Ben earns $$176,000$ per year gross plus $10\%$ in an annual bonus, has a $401($k$)$ plan and contributes $10\%$ of his income. The company matches $50\%$ up to $6\%.$ His expected retirement age is $70$ and life expectancy is $95.$

Karen earns $$78,000$ per year as an office manager. She has a retirement plan and contributes $10\%$ each year. The company matches $50\%$ up to $6\%.$ Her expected retirement age is $70$ and life expectancy is $95.$ Benjamin and Karen think they are in the $18\%$ income tax bracket.

Assets and liabilities:

Clients estimate the current value of the house is $$645,000.$ It was purchased on $9/29/2018$ for $$545,000$ with a $30$ year, $3\mathrm{.}75\%$ mortgage of $$495,000$ and $$50,000$ down payment. The homeowner$$s association$$s monthly fee is $$425\mathrm{.}00.$

Ben and Karen have one credit card carrying $$35,800$ balance at $17\%$ and he pays $$250$ a month. They want to pay down to zero in $12$ months.

They have two car payments $1)$ a $2021$ Honda Odyssey EX minivan, excellent condition, $18,756$ miles, blue, $($standard equipment$)$ purchased for $$36,000,$ and financed $$25,000$ at $4\mathrm{.}75\%$ for six years starting on August $1,2021.2)$ a $2022$ BMW $330$i $($base model$),$ excellent condition $($standard equipment$),18,435$ miles, gray, purchased for $$49,000$ and financed $$45,000$ at $2\mathrm{.}75\%$ for seven years starting on $9/1/22.$ The annual cost for insurance is $$2,960(1,250$ Honda, $$1,710$ BMW$).$ The deductible is $$500$ and the coverage is $100/300/50$ for each car.

There is a $$58,000$ balance in Ben$$s $401$k plan and $$28,000$ in Karen$$s$.$ Their joint savings account has a balance of $$25$k$.$ Ben has $$35$k in an IRA and Karen has $$29$k in her IRA. They own a Municipal bond mutual fund worth $$46,000,$ jewelry worth $$15,000,$ and household appliances worth $$32,000.$

Expenses

Food $$925$ per month, dining out $$625$ per month, lunches $($including children$)$ $$300$ per month, gasoline for cars $$250$ per month, tolls of $325,$ car maintenance $$175$ per month, school $($and summer camp$)1,425$ per month, $$7,200$ a year on vacations $($they put it on their BKcredit card each year$),$ water $$115$ per month, electricity $$410$ per month, cell phones $$165$ per month, cable and internet $$140$ per month, monthly streaming service $$55,$ life insurance $$375$ per month $($a $$425$k policy on Ben which pays $$275,$ and a $$250$k policy on Karen which pays $$100),$ homeowner$$s insurance $$1,500$ per year $($$$125$ included in their monthly mortgage payment$),$ monthly personal care $$310,$ monthly medical expenses $$125,$ monthly savings $$550$ in addition to $401$k contributions, monthly donations $$350,$ monthly clothing $$250,$ school supplies $$960$ per year $($once a year$),$ and gardener$/$housekeeping $$350$ per month.

Calculate the following ratios:

Debt Ratio

Current Ratio

Liquidity ratio

Debt payments ratio

Savings ratio

Client Goals:

Evaluate monthly and year expenses, they do not feel like there is enough income.

Provide for college for each of their children. The estimated costs will be about $$35,000$ per year for four years at a four$-$year degree at a state university $($being from out of state$).$

Retire with similar cost of living at age $70$ with a life expectancy of $96$ years.

Provide for each other at the death of the spouse.

Financial Plan

We will be preparing a financial plan for this client that includes, but is not limited to$,$ the following client reports

Net worth statement $($you will need to create amortization tables$)$

Cash Flow statement.

Capital needs analysis for each spouse

Capital needs analysis for retirement capital needs to retire at age $70($both$)$

Capital needs analysis for college costs.

Estate distribution report

Each report will include comments and suggestions for improvement.

BASED ON THE INFORMATION ABOVE PLEASE ANSWER THE FOLLOWING WITH FORMULAS SO I CAN DO THEM ON EXCEL

FINAL PROJECT DESCRIPTION $(400$ points$)$:

Each of the following items are separate calculations. You will end up with three spreadsheets $-$

the original $($Item $1$ below$),$ one for Item $2$ below and one for Item $3$ below. Use formulas when

possible and do not make manual entries of calculation results. For example, if you are to enter

the future value of an amount, be sure to use the FV formula in Excel $($doing the calculation on a

calculator and then entering the results is NOT acceptable$)$

Ben and Karen want to know the true cost of financing college using their retirement

plans $($under the current plan we have done in class$).$

a$.$ OUTPUT: List $($describe$)$ the items you considered for this calculation and show

your calculation in the education tab$).$ Hint: be sure to think outside the box on

topics like opportunity costs.

b$.50$ points

Ben and Karen want to know the earliest they can retire, with