Please solve this using Excel Functions required for each question.

• Part a and Part b: you can treat the implied rate as the real borrowing cost. You should include the annual fees as cash outflow in your calculation.
• Part b: use one chart to demonstrate the payments from two packages.
• Part c, Part d and Part e: similar to part a and part b, you can use the goal seek to find the implied rate. For these parts, assume that Jack is still under the original 5-year $200,000 loan from MQ bank with 60 monthly repayment of $3,800 (you should also include the annual fees). You need to treat the Jake's own investment of $200,000 as the cash outflow at time 0.
• Part e: all losses will be covered, if Jack chooses to purchase the insurance.

This includes listing relevant cells with the relevant Excel functions. Information are given below. All numbers should be numerical. Today is 1 January 2020. Jake just used $400,000 ($200,000 is from Jake’s own investment and $200,000 is raised by taking a 5-year loan) to purchase a café franchise. To operate this business, Jake needs to pay rent, maintenance costs, labor costs and loan repayments.

• 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.

• Rent will be paid by the end of each quarter with an amount of $12,000. • Labour cost will be paid by the end of each month with an amount of $10,000.

• Maintenance cost will be paid by the end of each half year with an amount of $15,000.

• Jake predicts that this café franchise initially can have monthly revenue of $24,000. Assume that Jake can obtain this amount by the end of each month. Jake forecasts that this revenue amount will increase at the rate of y% p.a. The revenue increase will only happen at the beginning of each year. For example, this café franchise initially has monthly revenue of $24,000 in 2020. Then the revenue amount will become $24,000(1+y%) per month in 2021 and $24, 000(1 + y%)² per month in 2022. Jake assumes the value of y is the same as the Australian CPI rate for 2019. You need to use Fact Set to find the Australian CPI rate for 2019. (Australian CPI rate for 2019 is 1.6% p.a.). This is = y%

• The franchise has a term of 5 years. If Jake chooses not to renew the franchise, he needs to terminate this business by 31 December 2024.

a). Use Goal Seek to find the implied annual nominal rate of interest payable monthly (i.e., j₁₂) charged by MQ bank for this 5-year loan. Label this sheet as “Part a”.

b). Jake has also been offered an option to switch his loan to a new package. Here are the details of this package.

• Under this new package, Jake will make 60 monthly repayments at the end of each month.

• Jake can have a one year interest-only-period at the beginning of the loan. Jake’s repayments will be interest-only¹ for the first year (i.e., first 12 payments will be interest-only payments), followed by payments of principal plus interest for the following 4 years.

¹ Interest-only repayment means your repayments only cover the interest on the amount you have borrowed, during the interest-only period. For example, if you borrow $1,000 through a five-year mortgage on 1 January 2020 with a one year interest-only period at j12 = 6% during the first year (1 January 2020–31 December 2020), your monthly repayment is $1, 000 × 6%/12 = $5 per month. On 1 January 2021, you need to use the remaining four years to repay the borrowed $1,000. The present value on 1 January 2021 of all payments in the remaining four years should be equal to $1,000.

• During the interest-only period, the interest rate will be j₁₂ = 6% which means Jake needs to repay 6%/12 x 200000 by the end of each month.

• Under this new package, Jake needs to pay $4,600 by the end of each month after the interest-only period.

• This package has an annual fee of $400. The package fee is paid by the end of January of each year during the following five year period (from 1 January 2020 to 31 December 2024). Use Goal Seek to find the implied annual nominal rate of interest payable monthly (i.e., j₁₂) (j₁₂=effective monthly rate) charged by MQ bank for this new loan package. Use a bar or column chart to compare the loan repayment amounts (including annual fees) of the original package and the new package over 5 years. Plot 60 monthly payments for both the original package and the new package. Should Jake switch to this package? Label this sheet as Part b.

c). If Jake chooses not to renew the franchise, use the given information regarding costs and revenue to calculate Jake’s effective yearly return rate (j₁) over this five-year investment period. Label this sheet as “Part c”. (j1=Effective Yearly Return Rate)

d). Jake predicts incidents, such as, theft and fire, might happen in next five years which cause a loss to his café. Assume that Jake chooses not to renew the franchise, re-calculate Jake’s effective yearly return rate (j₁) over this five year investment period by considering following scenarios. Label this sheet as “Part d”.

• Scenario 1: an incident happens in July 2023. Jake’s café loses all revenue of that month. Assume that all the other costs remain same.

• Scenario 2: an incident happens in January 2024. Jake’s café loses 50% of revenue from January 2024 to February 2024. Assume that all other costs remain same. 1 Interest-only repayment means your repayments only cover the interest on the amount you have borrowed, during the interest-only period. For example, if you borrow $1,000 through a five-year mortgage on 1 January 2020 with a one year interest-only period at j₁₂ = 6% during the first year (1 January 2020–31 December 2020), your monthly repayment is $1, 000 × 6%/12 = $5 per month. On 1 January 2021, you need to use the remaining four years to repay the borrowed $1,000. The present value on 1 January 2021 of all payments in the remaining four years should be equal to $1,000.

e). Jake can purchase insurance to avoid the potential loss from the incident. Assume that Jake needs to pay $370 by the end of each month for the insurance premium to purchase this insurance product. Based on the scenarios from part d, should Jake purchase this insurance product? Label this sheet as “Part e”.

Answer rating: 100% (QA)

Part a The implied annual nominal rate of interest payable monthly ie j12 charged by MQ bank for this 5year loan is 539 To find the implied annual nominal rate of interest payable monthly ie j12 charg... View the full answer