1. Colin is trying to decide whether he should make his IRA contribution at the beginning of the year or at the end of the year. He wants to save $5,000 per year for 25 years in his IRA (a retirement account) that can earn 7% per year. What would be the difference in his account value if he made the payments at the beginning of each year rather than at the end? (1 point)

2. Brandon buys a piece of equipment for $15,000. He pays $5,000 for upgrades in year 1 and the equipment generates $2,000 in cash flow for year 1. In year 2 the equipment generates $8,000, year 3 it generates $4,000, but Brandon sells it for $6,000 but also pays a $500 commission. Brandon’s required rate of return is 8%. What is the NPV? (1 point)

3. Suppose you are planning to buy a car. Complete the following tasks. (1 point)

a. Search online (dealer’s website, Craigslist, etc) for a car you would like to buy. Write down the following information about that car.

- Brand
- Model
- Year
- Color
- Condition
- Mileage (ignore if new)
- Price

b. Suppose you are buying with full financing. Search online (bank’s website, dealer’s website, etc) for the most recent auto loan interest rate. Write down that information.

c. Suppose you plan to repay $500 each month for the auto loan you get from Part b. How long will it take for you to repay the loan.

4. Suppose that you consider some mortgage options. The price of home is $200k. (1 point)

a. Calculate your monthly payments for each option:

- Option A: 20% down payment at 15-year fixed APR of 4%
- Option B: 15% down payment at 30-year fixed APR of 4.5%
- Option C: 10% down payment at 30-year fixed APR of 6%

b. For which option are you going to repay the least total amount? In other words, which mortgage will cost you the least so that you are going to apply for it? (Hint: Suppose for a loan you repay $10 every month for 12 months, total amount you repay is $120.)

5. You are 22 now and would like to retire at age of 67. After you retire, you assume you will live for another 20 years until 87 years old. Suppose you want to withdraw $5,000 at the beginning of each month from your retirement plan after you retire, and the monthly return of your retirement plan is 0.5%.

a. In order to support your monthly withdrawal after retirement, how much do you need to accumulate when you are 67? (0.5 points)

b. In order to accumulate what you need (the number you get in part 1), how much do you need to save at the end of each month from now? (0.5 points)

6.

Peter wants to establish a college fund for his son, who is 4 years old. Peter plans on a private college that costs, in today’s dollars, $30,000 annually. He expects college expenses to increase at a rate of 6%. He thinks that the money can be invested at 7.5%. What fixed amount does Peter need to contribute each month in order to have saved the full cost of all four years of his son’s college education by the time his son begins his freshman year of college 14 years later at age of 18? (1 point)

Instructions:

- Use the
**Excel financial functions**and model each scenario below in Excel. - Answer each question clearly below in the word file. You do not need to include all the details of your operations in the Excel. Only include the information (i.e. the calculation results) that helps you answer each question.
- Submit both excel file (with your detailed modeling) and word file (with your answer/explanation to each question).
- Check the attached file for all the requirements and questions.

0 comments