Creating an Amortization Table with a balloon payment in between the fixed monthly instalments

[Philip28]

This is a Hire purchase with fixed monthly Repayments.

I have tried to create an Amortization schedule on this (showing the monthly interest and principal repayment portions) but have not yet succeeded in doing so. This is because there is a balloon payment sandwiched in between the fixed monthly payments. I believe this is a very commonly encountered Hire Purchase type and would like to ask whether someone could help create an amortization table for this type of loan?
I have uploaded the details onto this Excel workbook. The details are also shown below:

Annual Percentage Rate:

The Annual Percentage Rate for the loan term is fixed at 2.4900%

<tbody>
</tbody>

The details are as follows:

Amount of Credit	115,000.00
Add: Establishment Fee	320.00
Total Amount of Credit	115,320.00

<tbody>
</tbody>

Schedule of Payments:

Repayment No.	Repayment Date	Repayment
1	06/12/2015	2,790.16
2	06/01/2016	2,790.16
3	06/02/2016	21,540.16
4	06/03/2016	2,790.16
5	06/04/2016	2,790.16
6	06/05/2016	2,790.16
7	06/06/2016	2,790.16
8	06/07/2016	2,790.16
9	06/08/2016	2,790.16
10	06/09/2016	2,790.16
11	06/10/2016	2,790.16
12	06/11/2016	2,790.16
13	06/12/2016	2,790.16
14	06/01/2017	2,790.16
15	06/02/2017	2,790.16
16	06/03/2017	2,790.16
17	06/04/2017	2,790.16
18	06/05/2017	2,790.16
19	06/06/2017	2,790.16
20	06/07/2017	2,790.16
21	06/08/2017	2,790.16
22	06/09/2017	2,790.16
23	06/10/2017	2,790.16
24	06/11/2017	2,790.16
25	06/12/2017	2,790.16
26	06/01/2018	2,790.16
27	06/02/2018	2,790.16
28	06/03/2018	2,790.16
29	06/04/2018	2,790.16
30	06/05/2018	2,790.16
31	06/06/2018	2,790.16
32	06/07/2018	2,790.16
33	06/08/2018	2,790.16
34	06/09/2018	2,790.16
35	06/10/2018	2,790.16
36	06/11/2018	2,756.17
	Total	119,161.77

<tbody>
</tbody>

No. of Repayments	Repayment Amounts	First Due Date of Payment	Last Due Date of Payment
2 Monthly Repayments	2,790.16	06/12/2015	06/01/2006
1 Monthly Repayment	21,540.16	06/02/2016	06/02/2016
33 Monthly Repayments	2,790.16	06/03/2016	06/11/2018

<tbody>
</tbody>