Calculate interest on loan given at various date with moving libor rate

[sanram]

Hi All

Looking forward for your advice and help in calculating interest on loan given at various date. Details as below

a) I have below data as loan disbursement date during the period

<tbody>
[TD="class: xl65"] Party
[/TD]
[TD="class: xl65, width: 87"] Date of Loan [/TD]
[TD="class: xl65, width: 89"] Loan Amount [/TD]

[TD="class: xl65"]A[/TD]
[TD="class: xl67"]3-Jan-2016[/TD]
[TD="class: xl69"] $ 1,500.00[/TD]

[TD="class: xl65"]B[/TD]
[TD="class: xl67"]3-Jan-2016[/TD]
[TD="class: xl69"] $ 200.00[/TD]

[TD="class: xl65"]C[/TD]
[TD="class: xl67"]3-Jan-2016[/TD]
[TD="class: xl69"] $ 300.00[/TD]

[TD="class: xl65"]A[/TD]
[TD="class: xl67"]16-Jan-2016[/TD]
[TD="class: xl69"] $ 1,500.00[/TD]

[TD="class: xl65"]B[/TD]
[TD="class: xl67"]20-Jan-2016[/TD]
[TD="class: xl69"] $ 2,000.00[/TD]

[TD="class: xl65"]C[/TD]
[TD="class: xl66, align: right"]15 Dec-2016[/TD]
[TD="class: xl69"] $ 1,000.00[/TD]

[TD="class: xl65"]C[/TD]
[TD="class: xl68"]21 Dec-2016[/TD]
[TD="class: xl69"] $ 5,000.00[/TD]

[TD="class: xl65"]B[/TD]
[TD="class: xl68"]28 Dec-2016[/TD]
[TD="class: xl69"] $ 2,000.00[/TD]

[TD="class: xl65"]A[/TD]
[TD="class: xl68"]28-Feb-2017[/TD]
[TD="class: xl69"] $ 3,000.00[/TD]

[TD="class: xl65"]B[/TD]
[TD="class: xl68"]22-Mar-2017[/TD]
[TD="class: xl69"] $ 1,500.00[/TD]

[TD="class: xl65"]C[/TD]
[TD="class: xl68"]29-Mar-2017[/TD]
[TD="class: xl69"] $ 500.00[/TD]

[TD="class: xl65"]A[/TD]
[TD="class: xl68"]19-Apr-2017[/TD]
[TD="class: xl69"] $ 2,000.00[/TD]

[TD="class: xl65"]A[/TD]
[TD="class: xl68"]15-May-2017[/TD]
[TD="class: xl69"] $ 1,500.00[/TD]

</tbody>

b) I have Libor closing rate month wise as below

<tbody>
[TD="class: xl66"]Month / LIBOR rate[/TD]
[TD="class: xl67"]Year[/TD]
[TD="class: xl67, width: 83"]Average[/TD]

[TD="class: xl75, width: 200"]January[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]0.856 %[/TD]

[TD="class: xl75, width: 200"]February[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]0.870 %[/TD]

[TD="class: xl75, width: 200"]March[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]0.901 %[/TD]

[TD="class: xl75, width: 200"]April[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl77, width: 83"]0.903 %[/TD]

[TD="class: xl75, width: 200"]May[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl77, width: 83"]0.933 %[/TD]

[TD="class: xl75, width: 200"]June[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]0.936 %[/TD]

[TD="class: xl75, width: 200"]July[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]0.999 %[/TD]

[TD="class: xl75, width: 200"]August[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl77, width: 83"]1.198 %[/TD]

[TD="class: xl75, width: 200"]September[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl77, width: 83"]1.247 %[/TD]

[TD="class: xl75, width: 200"]October[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]1.259 %[/TD]

[TD="class: xl75, width: 200"]November[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]1.269 %[/TD]

[TD="class: xl75, width: 200"]December[/TD]
[TD="class: xl76, align: right"]2016[/TD]
[TD="class: xl78, width: 83"]1.306 %[/TD]

[TD="class: xl79, width: 200"]January[/TD]
[TD="class: xl80, align: right"]2017[/TD]
[TD="class: xl82, width: 83"]1.340 %[/TD]

[TD="class: xl79, width: 200"]February[/TD]
[TD="class: xl80, align: right"]2017[/TD]
[TD="class: xl82, width: 83"]1.351 %[/TD]

[TD="class: xl79, width: 200"]March[/TD]
[TD="class: xl80, align: right"]2017[/TD]
[TD="class: xl82, width: 83"]1.426 %[/TD]

[TD="class: xl79, width: 200"]April[/TD]
[TD="class: xl80, align: right"]2017[/TD]
[TD="class: xl82, width: 83"]1.417 %[/TD]

[TD="class: xl79, width: 200"]May[/TD]
[TD="class: xl80, align: right"]2017[/TD]
[TD="class: xl82, width: 83"]1.425 %[/TD]

</tbody>

Requirement : Calculate month-wise Interest due from the date of loan till May 2017 applying 5%+Libor Rate(given in above table)

Ex: Party A has give loan on 3rd Jan 2016 for $1500 so have to calculate interest due till May 2017 , month-wise using the constant interest rate 5% + Libor rate as per table above(variable)

Kindly guide how to map the Libor interest rate from the date of loan , month-wise so that the calculation is simple and meaningful.

Thank You