You work for the CFO of a coffee franchise that is contemplating about open a new store near Drexel university. The total cost of opening a shop is $500,000. The store will open on the 1/1/2020.

You have decided to finance this project by borrowing $300,000 from a bank. The APR of this loan is 4.75%. The loan requires quarterly payment. The loan will be amortized with a fixed payment amount over 5 years.

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 three year (12 Payments), the interest rate has gone down a lot. 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 is 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)

(And fill in the blank, thanks!)

Loan characteristics (New Loan) Amount borrowed Interest rate (Quoted) Loan period in years Number of payment per year Loan Start date Number of payments Interest rate (actual per period) Payment per period Fill in 2.75% 2 4 2020/1/1 Fill in Fill in Fill in Date Beginning blance Total Payment Interest paid Principal Paid Ending Balance Pmt number 13 14 15 16 17 18 19 20 2023/3/1 2023/6/1 2023/9/1 2023/12/1 2024/3/1 2024/6/1 2024/9/1 2024/12/1