You are a strategist for a coffee shop company that is contemplating about opening a new store near your university. The total cost for opening a shop is $500,000. The store will open on 1/1/2020, and is expected to operate for 5 years. After 5 years, the company expects to sell the shop for $600,000.

After some research, you estimate 720,000 people will visit the university each semester (8000 students and staff each day for 90 days). For each 100 people that visit, 5 people buy coffee. Each customer spends, on average, $4 dollars per visit. Cost per customer visit is $2 for the shop (staff salary, buying coffee beans, etc.).

The company has decided to finance this project by borrowing $300,000 from the bank. The

APR of the loan is 4.75% and requires quarterly payments. The loan will be amortized with a

fixed amount over 5 years. The company would like you to construct a cash flow table that

records expected net income per quarter.

Question 1.

1. Calculate the quarterly loan payment using the annuity formula and check if you get the same answer by using excel. NOTE: PMT is the excel function.

2. Fill in the amortization schedule provided in the excel tab (Q1b)

3. After 3 year (12 payments), the interest rate has gone down. The company decides to refinance the loan. How much money does the company need to borrow to refinance the loan? (i.e. what is the ending balance after 12 payments?). The terms of the new loan are as follows

1. The APR is 2.75%
2. Equal payments each quarter
3. Maturity: 2 years

4. What is the quarterly payment for this loan?

5. Complete the amortization table in excel (tab Q1e)

6. Calculate the quarterly profits and fill in the missing information in tab Q1f