Question

In: Finance

Answer the following in Excel. A. Your friend is planning to buy a car and has...

Answer the following in Excel.

A. Your friend is planning to buy a car and has decided to contribute $300 to an investment account at the beginning of each month for the next 5 years. What will the value of this investment account be at the end of the 5years if your friend can earn a compound return of 8% per year compounded monthly?

B. At the end of year 5, your friend will purchase a car with a price of $40,000 using the final balance on the savings account and a car loan for the balance of the car's price. The loan will have monthly payments for 6 years due at the end of the month. The interest rate on the loan will be 4% compounded monthly. What would the monthly payments your friend will need to make on the loan?

Solutions

Expert Solution

Sol:

A.

Monthly payment (PMT) = $300

Period (NPER) = 5 years, Monthly = 5 * 12 = 60

Interest rate = 8%, Compounded monthly = 8 / 12 = 0.6667%

To determine future value of this investment we have to use FV function in excel:

PMT

-300

NPER

60

Rate

0.6667%

Future value

$22,190.01

Therefore future value of the investment will be $22,190.01

B.

Loan present value (PV) = 40,000 - 22,190.01 = $17,809.99

Period (NPER) = 6 years, Monthly = 6 * 12 = 72

Interest rate = 4%, Compounded monthly = 4 / 12 = 0.3333%

To determine monthly payments on loan we have to use PMT function in excel:

Loan PV

$17,809.99

NPER

72

Rate

0.3333%

Monthly payment

$278.64

Therefore monthly payments on loan will be $278.64

Working


Related Solutions

Please answer in EXCEL Geoffrey decides not to buy the car mentioned earlier. Instead, he is...
Please answer in EXCEL Geoffrey decides not to buy the car mentioned earlier. Instead, he is now considering a food delivery service "You, bars, meats" that his friend Gillian has recently started. Gillian has agreed that for a single payment of $78,000 today to help her launch her business, she will provide all the delivery services that Geoffrey needs for his business for the next 5 years. Geoffrey is considering borrowing the full amount from his business account. Suppose that...
Solve using excel 2. You give a loan to your friend to buy equipment for his...
Solve using excel 2. You give a loan to your friend to buy equipment for his business. The friend puts $4000 of their own money as a down payment, you lend them $12,000. The equipment is $16,000 total. You will charge your friend 4.0% Interest every year and will collect monthly payments for 60 months. Inflation is 1.7% per year, constant for 5 years. a. What is the monthly payment you will collect? b. What is the total amount of...
You are planning to buy a car. The retail price of the car is $40,000. Fox...
You are planning to buy a car. The retail price of the car is $40,000. Fox Auto is making you the following offer: You pay $4,000 down and then $1,000 a month for next 36 months (3 years). The APR is 4.8 percent (compounded monthly). This offer is equivalent to a $​[___] off the retail price (when paid in cash today). Select an answer and submit. For keyboard navigation, use the up/down arrow keys to select an answer. a $2,534...
You are planning to buy a new car. The cost of the car is $50,000. You...
You are planning to buy a new car. The cost of the car is $50,000. You have been offered two payment plans: • A 10 percent discount on the sales price of the car, followed by 60 monthly payments financed at 9 percent per year. • No discount on the sales price of the car, followed by 60 monthly payments financed at 2 percent per year. If you believe your annual cost of capital is 9 percent, which payment plan...
You buy an insurance policy on your car. The contract represents that, if the car has...
You buy an insurance policy on your car. The contract represents that, if the car has incurs more than $500 worth of damage, the insurance company will pay for the amount of the bill in excess of that amount. Which statement below is most accurate? A. You are the issuer of a put option, and the insurance company is the purchaser of that put option. B. You are the buyer of a call option, and the insurance company is the...
You hear that your friend has been in a car accident and needs a blood transfusion;...
You hear that your friend has been in a car accident and needs a blood transfusion; you want to donate blood. Your friend has type AB+ blood and you have A+ blood. Can your blood be given to your friend? Explain the reason for your answer.
Answer the following questions and use Excel to show your work. A student has an important...
Answer the following questions and use Excel to show your work. A student has an important exam coming up and is contemplating not studying for the exam in order to attend a party with his friends. The student must earn a minimum score of 70% on the exam in order to successfully maintain his desired GPA. Suppose the student knows in advance that the exam will consist of 20 multiple-choice questions with 4 possible answers for each question. Answer questions...
Jody is planning to buy a car for $20,000, putting 20% down in cash, with the...
Jody is planning to buy a car for $20,000, putting 20% down in cash, with the rest going towards a loan. The bank tells her that the interest rate on the loan will be 8% per year, which is then compounded monthly, for a three-year loan. What is Jody’s monthly payment going to be?
In order to buy a car, you borrow $31,500 from a friend at 9%/year compounded monthly...
In order to buy a car, you borrow $31,500 from a friend at 9%/year compounded monthly for 4 years. You plan to repay the loan with 48 equal monthly payments. How much interest is in the 23rd payment? $enter a dollar amount
In order to buy a car, you borrow $19500 from a friend at 6%/year compounded monthly...
In order to buy a car, you borrow $19500 from a friend at 6%/year compounded monthly for 4 years. You plan to repay the loan with 48 equal monthly payments. a. How much are the monthly payments? b. How much interest in in the 23rd payment? c. What is the remaining balance after the 37th payment?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT