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:

[TABLE="width: 312"]
<tbody>[TR]
[TD]Annual Percentage Rate:
[/TD]
[TD]The Annual Percentage Rate for the loan term is fixed at 2.4900%
[/TD]
[/TR]
</tbody>[/TABLE]


The details are as follows:

[TABLE="width: 312"]
<tbody>[TR]
[TD]Amount of Credit
[/TD]
[TD="align: right"]115,000.00
[/TD]
[/TR]
[TR]
[TD]Add: Establishment Fee
[/TD]
[TD="align: right"]320.00
[/TD]
[/TR]
[TR]
[TD]Total Amount of Credit
[/TD]
[TD="align: right"]115,320.00
[/TD]
[/TR]
</tbody>[/TABLE]


Schedule of Payments:

[TABLE="width: 416"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Repayment No.
[/TD]
[TD]Repayment Date
[/TD]
[TD]Repayment
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]06/12/2015
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]06/01/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]06/02/2016
[/TD]
[TD="align: right"]21,540.16
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]06/03/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]06/04/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]06/05/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]06/06/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]06/07/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]06/08/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]06/09/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]06/10/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD="align: right"]06/11/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD="align: right"]06/12/2016
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]14
[/TD]
[TD="align: right"]06/01/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]15
[/TD]
[TD="align: right"]06/02/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]16
[/TD]
[TD="align: right"]06/03/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]17
[/TD]
[TD="align: right"]06/04/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]18
[/TD]
[TD="align: right"]06/05/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]19
[/TD]
[TD="align: right"]06/06/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]20
[/TD]
[TD="align: right"]06/07/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]21
[/TD]
[TD="align: right"]06/08/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]22
[/TD]
[TD="align: right"]06/09/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]23
[/TD]
[TD="align: right"]06/10/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]24
[/TD]
[TD="align: right"]06/11/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]25
[/TD]
[TD="align: right"]06/12/2017
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]26
[/TD]
[TD="align: right"]06/01/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]27
[/TD]
[TD="align: right"]06/02/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]28
[/TD]
[TD="align: right"]06/03/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]29
[/TD]
[TD="align: right"]06/04/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]30
[/TD]
[TD="align: right"]06/05/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]31
[/TD]
[TD="align: right"]06/06/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]32
[/TD]
[TD="align: right"]06/07/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]33
[/TD]
[TD="align: right"]06/08/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]34
[/TD]
[TD="align: right"]06/09/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]35
[/TD]
[TD="align: right"]06/10/2018
[/TD]
[TD="align: right"]2,790.16
[/TD]
[/TR]
[TR]
[TD="align: right"]36
[/TD]
[TD="align: right"]06/11/2018
[/TD]
[TD="align: right"]2,756.17
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total
[/TD]
[TD="align: right"]119,161.77
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 516"]
<tbody>[TR]
[TD]No. of Repayments
[/TD]
[TD]Repayment Amounts
[/TD]
[TD]First Due Date of Payment
[/TD]
[TD]Last Due Date of Payment
[/TD]
[/TR]
[TR]
[TD]2 Monthly Repayments
[/TD]
[TD="align: right"]2,790.16
[/TD]
[TD="align: right"]06/12/2015
[/TD]
[TD="align: right"]06/01/2006
[/TD]
[/TR]
[TR]
[TD]1 Monthly Repayment
[/TD]
[TD="align: right"]21,540.16
[/TD]
[TD="align: right"]06/02/2016
[/TD]
[TD="align: right"]06/02/2016
[/TD]
[/TR]
[TR]
[TD]33 Monthly Repayments
[/TD]
[TD="align: right"]2,790.16
[/TD]
[TD="align: right"]06/03/2016
[/TD]
[TD="align: right"]06/11/2018
[/TD]
[/TR]
</tbody>[/TABLE]