In a management buyout situation, the financial model is used to assess the cash flows generated by the business to cover the loan interest and repayments, and the sums involved can be substantial. It is often the case that the first 2 years are calculated on a monthly basis, switching to quarterly for the next 3 years or so.

Set up a model to show the following:

a. A loan of 1,000,000, drawn down on day 1 of the forecast period.

b. The loan is to be repaid over 5 years, payable monthly.

c. Interest is calculated at 8% per annum, payable monthly (for simplicity, use the opening balance times the rate).

d. The model should show the first two years of interest and repayments at the monthly level, the final 3 years at quarterly.
Prepare a report to show the management the repayment and interest schedule. The management would like to be able to see the monthly figures for the final three years as an option.