Public Function Cal_intAPL(Loan As Double, BD As Date, ED As Date) As Double
Dim VCR As Variant, result As Double
ReDim VCR(1 To 15, 1 To 3) 'Loan interest rate table ''NEED TO UPDATE NEW LOAN RATE IF ANY
VCR(1, 1) = DateSerial(2014, 5, 1): VCR(1, 2) = 0.11: VCR(1, 3) = 365
VCR(2, 1) = DateSerial(2015, 9, 1): VCR(2, 2) = 0.1: VCR(2, 3) = 365
VCR(3, 1) = DateSerial(2016, 2, 1): VCR(3, 2) = 0.1: VCR(3, 3) = 366
VCR(4, 1) = DateSerial(2016, 3, 1): VCR(4, 2) = 0.1: VCR(4, 3) = 365
VCR(5, 1) = DateSerial(2016, 12, 1): VCR(5, 2) = 0.975: VCR(5, 3) = 365
VCR(6, 1) = DateSerial(2017, 1, 1): VCR(6, 2) = 0.1: VCR(6, 3) = 365
VCR(7, 1) = DateSerial(2017, 3, 1): VCR(7, 2) = 0.0975: VCR(7, 3) = 365
VCR(8, 1) = DateSerial(2018, 2, 1): VCR(8, 2) = 0.095: VCR(8, 3) = 365
VCR(9, 1) = DateSerial(2018, 8, 1): VCR(9, 2) = 0.0935: VCR(9, 3) = 365
VCR(10, 1) = DateSerial(2018, 9, 1): VCR(10, 2) = 0.095: VCR(10, 3) = 365
VCR(11, 1) = DateSerial(2019, 8, 1): VCR(11, 2) = 0.1: VCR(11, 3) = 365
VCR(12, 1) = DateSerial(2020, 2, 1): VCR(12, 2) = 0.1: VCR(12, 3) = 366
VCR(13, 1) = DateSerial(2020, 3, 1): VCR(13, 2) = 0.1: VCR(13, 3) = 365
VCR(14, 1) = DateSerial(2024, 2, 1): VCR(14, 2) = 0.1: VCR(14, 3) = 366
VCR(15, 1) = DateSerial(2024, 3, 1): VCR(15, 2) = 0.1: VCR(15, 3) = 365
'VCR(16, 1) = DateSerial(2028, 2, 1): VCR(16, 2) = 0.1: VCR(16, 3) = 366
'VCR(17, 1) = DateSerial(2028, 3, 1): VCR(17, 2) = 0.1: VCR(17, 3) = 365
result = Loan
For i = 1 To UBound(VCR)
If VCR(i, 1) < BD Or ED < VCR(i, 1) Then 'If loan date > Date and Valuation Date < Date --> no interest accrued
result = result
Else
d = VCR(i, 1) - IIf(BD > VCR(i - 1, 1), BD, VCR(i - 1, 1))
result = result * (1 + VCR(i - 1, 2)) ^ (d / VCR(i - 1, 3))
End If
If i > 1 Then
If ED > VCR(i - 1, 1) And ED < VCR(i, 1) Then
d = ED - IIf(BD > VCR(i - 1, 1), BD, VCR(i - 1, 1))
result = result * (1 + VCR(i, 2)) ^ (d / VCR(i, 3))
ElseIf i = UpperBound And ED >= UB_Date(BD, ED) Then
d = ED - IIf(BD > VCR(i, 1), BD, VCR(i, 1))
result = result * (1 + VCR(i, 2)) ^ (d / VCR(i, 3))
End If
End If
Next i
Cal_intAPL = result
End Function