Jake took a 5-year $200,000 loan from MQ bank. Jake needs to make 60 monthly repayment with an amount of $3,800. The loan repayment will be paid by the end of each month. This package has an annual fee of $200. The package fee is paid on 30 June of each year during the following five-year period. The first one will be paid on 30 June 2020. This loan will be fully repaid by the end of 5 years. Jack built up an Excel spreadsheet and used Goal Seek to find the implied annual nominal rate of interest payable monthly (i.e., j12) charged by MQ bank for this 5-year loan. He firstly set a dummy variable in cell B7 for the annual nominal rate of interest payable monthly (i.e., j12). He then converted the dummy the annual nominal rate of interest payable monthly to effective yearly rate and effective half yearly rate to calculate the present value of all cash flows in B12. Finally, he used Goal Seek to set cell B12 to value 200000 by changing cell B7. a) Give the Excel cell formula Jack would use to calculate the value of cell B8.

Select one:

a. =(1+B8/12)^12-1

b. =(1+B7/12)^12-1

c. =(1+B8/12)^6-1

d. =(1+B7/12)^6-1

b) Give the Excel cell formula Jack would use to calculate the value of cell B11.

Select one:

a. =ABS(PV(B8,B2,B5))*(1+B9)

b. =ABS(PV(B9,B3,B6))*(1+B9)

c. =ABS(PV(B9,B3,B6))*(1+B10)

d. =ABS(PV(B8,B2,B5))*(1+B8)

B $200,000.00 5 12 $ 3,800.00 $ 200.00 1 Principal 2 Loan term (year) 3 Payment per year 4 Monthly Repayment 5 Annual 6 7 Annual interest rate (312) 8 Effective yearly rate 9 Effective half yearly rate 10 Present value of monthly payment 11 Present value of annual fees 12 Present value of all cash flows 5.46% 5.60% 2.76% $199,124.77 $ 875.23 $200,000.00