Question

Make sure you show the fomula and dont forget to do G&H a lot of answeres were not complete **

Please do this assignment in Excel using TVM formulas. I want to see your formulas. (It would be a good idea to check your work with a financial calculator also to make sure you didn't make mistakes and to also get more practice with the calculator). Turn in on excel
Answer the following:
A. John just bought a $33,000 car. He put $5,000 down and financed the rest. He got a 5 year loan and his payments are $550 at the end of each month. What is the interest rate on John's loan? B. Leticia wants to know how much her tuition will be next year if the inflation rate for tuition is 8% and it tuition currently costs $15,000. What would we estimate Leticia's tuition would cost next year?
C. Leticia wants to know how much she will have to put away in the bank today to have enough money to pay her tuition next year. How much would she need to set aside if her bank would pay her 1.2% interest on her money?
D. What would Leticia's tuition cost in 2 years?
E. Barry inherited $47,000 from an Uncle recently. He wants to invest it for his retirement. He estimates he could earn about 7% on his money after fees. He wants to keep the money invested for at least 30 years. What amount will his money grow to in 30 years?
F. What if Barry could reduces his fees by 1% how much would he earn over 30 years then?
G. How much would your payments be if you are buying a $500,000 home with 20% down and an interest rate of 4% for a 30 year loan.
H. What is your 28% Ratio given the following information:
You want to buy a home costing $350,000 with 3% down with a 3.7% rate on a 30 year loan.
Your Property Mortgage Insurance will cost $45 per month
Your Home Owners Insurance will cost $140 per month
Your taxes will $500 per month

Answer 1

A. The interest rate on John's loan is approximately 8.61%. B. Leticia's tuition will cost an estimated $16,200 next year. C. Leticia would need to set aside approximately $16,026.78 in the bank today to pay her tuition next year.

Step-by-step explanation:

A. To find the interest rate on John's loan, we can use the TVM (Time Value of Money) formula:

PV = FV / (1 + r)^n

Where:

• PV = Present Value (loan amount)
• FV = Future Value (car price minus down payment)
• r = interest rate
• n = number of periods

In this case, PV = $28,000 (loan amount), FV = $28,000 (car price minus down payment), and n = 60 (5 years * 12 months).

Plugging these values into the formula, we have:

28000 = 28000 / (1 + r/12)^(5 * 12)

By rearranging the formula and solving for r, we find that the interest rate is approximately 8.61%.

B. To estimate Leticia's tuition cost next year, we can use the formula:

FV = PV * (1 + inflation rate)

Where:

• FV = Future Value (tuition cost next year)
• PV = Present Value (current tuition cost)
• Inflation rate = 8%

Plugging in the values, we have:

FV = $15,000 * (1 + 0.08) = $16,200

Therefore, we estimate that Leticia's tuition will cost $16,200 next year.

C. To find how much Leticia needs to set aside in the bank today, we can use the TVM formula:

PV = FV / (1 + r)^n

Where:

• PV = Present Value (amount to set aside)
• FV = Future Value (tuition cost next year)
• r = interest rate
• n = number of periods

Plugging in the values, we have:

PV = $16,200 / (1 + 0.012) = $16,026.78

Therefore, Leticia would need to set aside approximately $16,026.78 in the bank today.

D. To find Leticia's tuition cost in 2 years, we can use the formula:

FV = PV * (1 + inflation rate)^n

Where:

• FV = Future Value (tuition cost in 2 years)
• PV = Present Value (current tuition cost)
• Inflation rate = 8%
• n = number of years

Plugging in the values, we have:

FV = $15,000 * (1 + 0.08)^2 = $16,704

Therefore, Leticia's tuition cost in 2 years would be approximately $16,704.

E. To calculate Barry's retirement fund in 30 years with an interest rate of 7%, we can use the TVM formula:

FV = PV * (1 + r)^n

Where:

• FV = Future Value (retirement fund)
• PV = Present Value (initial amount)
• r = interest rate
• n = number of years

Plugging in the values, we have:

FV = $47,000 * (1 + 0.07)^30 = $294,144.83

Therefore, Barry's retirement fund would grow to approximately $294,144.83 in 30 years.

F. If Barry reduces his fees by 1%, the new interest rate would be 6%. Using the same formula as before, we have:

FV = $47,000 * (1 + 0.06)^30 = $241,366.42

Therefore, if Barry reduces his fees by 1%, his retirement fund would grow to approximately $241,366.42 in 30 years.

G. To calculate the monthly payments for a $500,000 home with 20% down, an interest rate of 4%, and a 30-year loan, we can use the TVM formula:

PMT = PV * r / (1 - (1 + r)^(-n))

Where:

• PMT = Monthly payment
• PV = Present Value (loan amount minus down payment)
• r = interest rate per period
• n = number of periods

Plugging in the values, we have:

PMT = ($500,000 - $100,000) * 0.04 / (1 - (1 + 0.04)^(-30)) = $1,888.81

Therefore, the monthly payments for the home would be approximately $1,888.81.

H. To calculate the 28% Ratio, we need to consider the monthly housing expense and monthly income:

Monthly housing expense = Mortgage payment + Property Mortgage Insurance + Home Owners Insurance + Taxes

Monthly income = $350,000 * 0.28

28% Ratio = Monthly housing expense / Monthly income

Plugging in the values, we have:

Monthly housing expense = $1,153 (mortgage payment) + $45 (Property Mortgage Insurance) + $140 (Home Owners Insurance) + $500 (Taxes) = $1,838

Monthly income = $350,000 * 0.28 = $98,000

28% Ratio = $1,838 / $98,000 = 0.01878

Therefore, the 28% Ratio is approximately 0.01878.