Using Excel for the following problems build data sections for inputting the necessary information needed to solve the questions asked below and solve the problems. All amounts and percentages are to be taken out two decimal places. Please clearly identify your answers by placing a box around it and add a comment next to it when needed. Your work needs to be shown (data section and formulas used), a simple number or answer without support will receive no credit).

Problems 1, 2 and 3 are to be done on one worksheet. Problems 4, 5 and 6 are each to be done on their own separate worksheets. You must show your work and data sections to receive credit.

Problem 1

Determine the monthly payment on an 7 year, $77,700, 7.77%, personal loan.

(10 Points)

Problem 2

Determine the future value of an annuity of $9,750 per year, which is invested at a rate of 5.67% interest annually, for the next 8 years. (10 Points)

Problem 3

How much of a loan can be taken (that is find the PV of an annuity), if such a loan will be paid off in eight (8) years, carries an annual interest rate of 8.88% and will have monthly payments of $888. (10 Points)

Problem 4

Back in the year 2000, George W. Bush wanted to save enough money for his brother (Jeb) to be able to build a new pool in the White House. (He assumed his brother would be elected President after his second term wrapped up. Who could have foreseen the rise of Barak Obama and then Donald Trump back then?) He put in a lump sum to save when he first got into office 20 years ago. Today the initial investment has successfully grown to $1,191,500.

1. If the interest rate which this investment was held in returned 5.85% each year, how much did Mr. Bush invest initially (find the PV of a lump sum)? (10 Points)

2. If the current $1,191,500 is to be given away to the poor in equal monthly payments over the next 13 years, exhausting all the money in the account (meaning the FV will be zero), how much would each (monthly) payment be, (find the PMT), assuming the annuity pays out from an account returning 6.00% annually? (15 Points)

Problem 5

Mr. Stack wants to retire in the lap of luxury in 9.5 years. He can invest $4,000 at the beginning of every month (watch the Type here) for the next 9.5 years. He expects interest rates to stay at 3.70% annually on the stocks and bonds in which he invests. He figures that he will need $1,000,000 to achieve his dream. In addition to the amount he will be investing he has already saved $320,000 (which also receives the same 3.70% return annually).

1. Please identify how much Mr. Stack can expect to have in savings in 9.5 years, based on the combination of his current savings and his monthly annuity. You must show your work to receive full credit. (10 points)

2. Will he be able to attain his goal, based on your work in number 1? (I sure hope so.) (4 Points)

3. (6 Points) Please state whether

1. his investment will be over or short of the $1,000,000 goal and

2. the dollar amount his investment will be over or short of his goal.

Problem 6

You wish to buy a new motorboat, but your monthly funds are limited. You have the following three boat offers to consider:

Boat 1 Boat 2 Boat 3

Cost $24,000 $20,000 $17,000

Interest Rate (Annual) 4.50% 6.25% 8.00%

Term 6.00 years 5.50 years 5.00 years

The boats offer different options, but your main decision factor is you wish to purchase the boat with the cheapest monthly payments.

1. How much will the monthly payments be for each plan? (Please show your work, a data table for each, including their PMT) (15 points).

2. Which boat will you choose based on the monthly payment (and your financial position)? (4 points)

3. Which option (6 points, 3 points each)

1. pays the most interest over the life of the loan and
2. how much is the total amount of that interest?