Question

John and Mary, who make $50,000 per year, calculated their average tax rate at 15 percent. They contribute 12 percent of their income to charity and pay themselves 10 percent of their income. They have a fixed 6%, $100,000 mortgage with 25 years to maturity, a 3 years $20,000 auto-loan at a fixed 7% rate, and a $10,000, 10 years to maturity and 3% fixed rate college loan. In addition, utilities and property taxes were $2,270 per year, food was $6,000, insurance was $1,500, and other expenses were $5,430.

John and Mary would like you to help them understand where they are financially. You have John and Mary’s balance sheet and income statements, which were prepared earlier. They ask for help to calculate each of the key liquidity, debt, and savings ratios (see Chapter 2). Using the data and calculations, comment on how well they are doing. You need to carefully explain and design your analysis. What can and should they be doing to improve

Answer 1

Monthly mortgage payment is calculated using PMT function in Excel as :

rate = 6% / 12 (converting annual rate into monthly rate)

nper = 25 * 12 (25 year mortgage with 12 monthly payments each year)

pv = 100,000 (mortgage amount)

PMT is calculated to be $644.30. This is the monthly payment

Monthly auto loan payment is calculated using PMT function in Excel as :

rate = 7% / 12 (converting annual rate into monthly rate)

nper = 3 * 12 (3 year mortgage with 12 monthly payments each year)

pv = 20,000 (mortgage amount)

PMT is calculated to be $617.54. This is the monthly payment

Monthly college loan payment is calculated using PMT function in Excel as :

rate = 3% / 12 (converting annual rate into monthly rate)

nper = 10 * 12 (10 year mortgage with 12 monthly payments each year)

pv = 10,000 (mortgage amount)

PMT is calculated to be $96.56. This is the monthly payment

Annual Income Statement is below :

Description	Amount
Annual income	50,000.00
Utilities and Property Taxes	2,270.00
Food	6,000.00
Insurance	1,500.00
Other expenses	5,430.00
Mortgage Payment	7,731.60
Auto loan payment	7,410.48
College loan payment	1,158.72
Paid to self	5,000.00
Charity	6,000.00
Tax	7,500.00
Net Income	(0.80)

They are just able to breakeven. After all the expenses and payments, the after-tax net income is almost zero.

.

Savings Ratio (0.80/50,000)	(0.00)
Debt Ratio (16,380/50,000)	0.33