Compound interest rate with leap year

[truc.tu]

Hi everyone, I'm having this problem with compound interest for leap year, specifically only for February in leap year that the interest rate will be divided by 366 instead of 365 like any other months. Adding to that problem is the interest rate will be adjusted randomly. The table below will demonstrate it better.

Date (mm/dd/yyyy)	Interest rate	Days
05/01/2014	11%	365
09/01/2015	10%	365
02/01/2016	10%	365
03/01/2016	10%	366
12/01/2016	9.75%	365
01/01/2017	10%	365
03/01/2017	9.75%	365
02/01/2018	9.5%	365
08/01/2018	9.35%	365
09/01/2018	9.5%	365
08/01/2019	10%	365
02/01/2020	10%	365
03/01/2020	10%	366

Here's the code guys:

Public Function Cal_intAPL(Loan As Double, BD As Date, ED As Date) As Double 'BD as beginning date, ED is Valuation date




Dim VCR As Variant, result As Double

ReDim VCR(1 To 13, 1 To 3) 'Loan interest rate table

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.0975: 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



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



End If

If i > 1 Then

If ED > VCR(i - 1, 1) And ED <= VCR(i, 1) Then

If BD > VCR(i - 1, 1) Then

d = ED - BD
result = result * (1 + VCR(i - 1, 2)) ^ (d / VCR(i - 1, 3))

ElseIf BD < VCR(i - 1, 1) Then

d1 = VCR(i - 1, 1) - BD
result = result * (1 + VCR(i - 2, 2)) ^ (d1 / VCR(i - 2, 3))
d = ED - VCR(i - 1, 1)
result = result * (1 + VCR(i - 1, 2)) ^ (d / VCR(i - 1, 3))

End If

ElseIf i = UBound(VCR) And ED > VCR(i, 1) Then

If BD > VCR(i, 1) Then

d = ED - BD
result = result * (1 + VCR(i, 2)) ^ (d / VCR(i, 3))

ElseIf BD < VCR(i, 1) Then

d1 = VCR(i, 1) - BD
result = result * (1 + VCR(i - 1, 2)) ^ (d1 / VCR(i - 1, 3))
d = ED - VCR(i, 1)
result = result * (1 + VCR(i, 2)) ^ (d / VCR(i, 3))

End If


End If

End If


Next i






Cal_intAPL = result






End Function

Thank you guys in advance

[macropod]

Whether it's a leap year is of no consequence if the interest is calculated & compounded monthly or annually.

[truc.tu]

I'm sorry, could you explain it a bit clearer? And also could you see if there's a mistake in my logic or codes in macro? Thank you so much.

d = ED - BD 'calculate the numbers of days between 2 dates
result = result * (1 + VCR(i - 1, 2)) ^ (d / VCR(i - 1, 3)) 'this is just daily compound interest formula

[Paul_Hossler]

There are some questions

1. Does this mean that from 5/1/2014 to 9/1/2015 minus one day the rate is 11% per year
and from 9/1/2015 to 12/1/2016 minus one day the rate is 10% per year?

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.0975: VCR(5, 3) = 365

That is only the dates when the rate changes are shown? (Ignoring February)

2. The daily rate for all months EXCEPT February is based on a 365 day year, and the daily rate for February is based on a 366 day year for BOTH leap years and non-leap years?

3. In general there might be a partial month in the beginning, some full months, and a partial month at the end

4. I took a quick look and changed the setup. I didn't try to calculate anything yet. Because of the possible February 366 complication(#2 above) , you might have to do it month-by-month

Option Explicit


Sub test()
    MsgBox Cal_intAPL(1000, #1/15/2015#, #5/15/2019#)
End Sub




Public Function Cal_intAPL(Loan As Double, BD As Date, ED As Date) As Variant 'BD is beginning date, ED is end date
    Dim VCR(1 To 9, 1 To 2) As Variant 'Loan interest rate table '
    Dim numDays(1 To 12) As Long
    Dim numDaysBegin As Long, numDaysEnd As Long
    Dim begYear As Long, begMonth As Long, begDay As Long
    Dim endYear As Long, endMonth As Long, endDay As Long
    
    
    Dim i As Long, d1 As Long, d2 As Long
    Dim Result As Double


    'from 5/1/2014 to 9/1/2015 minus one day the annual interest rate is 11%
    VCR(1, 1) = DateSerial(2014, 5, 1): VCR(1, 2) = 0.11
    VCR(2, 1) = DateSerial(2015, 9, 1): VCR(2, 2) = 0.1
    VCR(3, 1) = DateSerial(2016, 12, 1): VCR(3, 2) = 0.0975
    VCR(4, 1) = DateSerial(2017, 1, 1): VCR(4, 2) = 0.1
    VCR(5, 1) = DateSerial(2017, 3, 1): VCR(5, 2) = 0.0975
    VCR(6, 1) = DateSerial(2018, 2, 1): VCR(6, 2) = 0.095
    VCR(7, 1) = DateSerial(2018, 8, 1): VCR(7, 2) = 0.0935
    VCR(8, 1) = DateSerial(2018, 9, 1): VCR(8, 2) = 0.095
    VCR(9, 1) = DateSerial(2019, 8, 1): VCR(9, 2) = 0.1


    'days in a each month
    numDays(1) = 31
    numDays(2) = 28
    numDays(3) = 31
    numDays(4) = 30
    numDays(5) = 31
    numDays(6) = 30
    numDays(7) = 31
    numDays(8) = 31
    numDays(9) = 30
    numDays(10) = 31
    numDays(11) = 30
    numDays(12) = 31




    'is ending date before beginning date
    If ED < BD Then
        Cal_intAPL = CVErr(xlErrNA)
        Exit Function
    End If


    'is ending date same as beginning date
    If ED = BD Then
        Cal_intAPL = Loan
        Exit Function
    End If


    'is beginning date past rates table or is ending date before rates table
    If BD > VCR(UBound(VCR, 1), 1) Then
        Cal_intAPL = CVErr(xlErrNA)
        Exit Function
    End If
    If ED < VCR(LBound(VCR, 1), 1) Then
        Cal_intAPL = CVErr(xlErrNA)
        Exit Function
    End If
    
    begYear = Year(BD)
    begMonth = Month(BD)
    begDay = Day(BD)
    endYear = Year(ED)
    endMonth = Month(ED)
    endDay = Day(ED)
    
    
    'BD might be middle of month, so how many days
    numDaysBegin = DateSerial(begYear, begMonth + 1, 0) - BD
    'ED might be middle of month, so how many days
    numDaysEnd = ED - DateSerial(endYear, endMonth, 1)
    
    
    
    
    
    Stop
    
End Function

[truc.tu]

Hi Paul. Thank you for your reply, these are my answers:

1. Does this mean that from 5/1/2014 to 9/1/2015 minus one day the rate is 11% per year
and from 9/1/2015 to 12/1/2016 minus one day the rate is 10% per year?
Yes, you're correct.

That is only the dates when the rate changes are shown? (Ignoring February)
Yes sir. February's interest rate only relies on the previous interest rate date.


2. The daily rate for all months EXCEPT February is based on a 365 day year, and the daily rate for February is based on a 366 day year for BOTH leap years and non-leap years?
Only February in leap year has the daily rate based on a 366 day year. The rest of the months within leap year will have the daily rate based on a 365 day year

3. In general there might be a partial month in the beginning, some full months, and a partial month at the end
Yes sir. The BD (Beginning Date) and ED (End Date) can be at anytime but of course ED must be greater than BD.

4. I took a quick look and changed the setup. I didn't try to calculate anything yet. Because of the possible February 366 complication(#2 above) , you might have to do it month-by-month
My strategy is to separate the period of time into 3 parts when the period of time contains leap year(s). What I was trying to do is to stop the compound interest rate by the end of 01/31/[Leap Year], continue to calculate it from 02/01/[Leap Year] to 02/29/[Leap Year] and finally from 03/01/[Leap Year] to End Date

Thank you Paul!

[Paul_Hossler]

Still thinking / working on it

Takes a while to be careful

[Paul_Hossler]

This may not be perfect, but I didn't have any data to test against

Option Explicit


Dim VCR(1 To 9, 1 To 2) As Variant 'Loan interest rate table '
Dim numDays(1 To 12) As Long


Sub test()
    MsgBox Cal_intAPL(1000, #1/1/2015#, #12/1/2019#)
End Sub




Public Function Cal_intAPL(Loan As Double, BD As Date, ED As Date) As Variant 'BD is beginning date, ED is end date
    Dim numDaysBegin As Long, numDaysEnd As Long
    
    Dim begYear As Long, begMonth As Long, begDay As Long
    Dim endYear As Long, endMonth As Long, endDay As Long
    Dim curYear As Long, curMonth As Long, curDay As Long
    Dim curDate As Date
    
    Dim i As Long
    Dim Result As Double


    'from 5/1/2014 to 9/1/2015 minus one day the annual interest rate is 11%
    VCR(1, 1) = DateSerial(2014, 5, 1): VCR(1, 2) = 0.11
    VCR(2, 1) = DateSerial(2015, 9, 1): VCR(2, 2) = 0.1
    VCR(3, 1) = DateSerial(2016, 12, 1): VCR(3, 2) = 0.0975
    VCR(4, 1) = DateSerial(2017, 1, 1): VCR(4, 2) = 0.1
    VCR(5, 1) = DateSerial(2017, 3, 1): VCR(5, 2) = 0.0975
    VCR(6, 1) = DateSerial(2018, 2, 1): VCR(6, 2) = 0.095
    VCR(7, 1) = DateSerial(2018, 8, 1): VCR(7, 2) = 0.0935
    VCR(8, 1) = DateSerial(2018, 9, 1): VCR(8, 2) = 0.095
    VCR(9, 1) = DateSerial(2019, 8, 1): VCR(9, 2) = 0.1


    'days in a each month
    numDays(1) = 31
    numDays(2) = 28
    numDays(3) = 31
    numDays(4) = 30
    numDays(5) = 31
    numDays(6) = 30
    numDays(7) = 31
    numDays(8) = 31
    numDays(9) = 30
    numDays(10) = 31
    numDays(11) = 30
    numDays(12) = 31
    
    begYear = Year(BD)
    begMonth = Month(BD)
    begDay = Day(BD)
    endYear = Year(ED)
    endMonth = Month(ED)
    endDay = Day(ED)
    
    
    'BD might be middle of month, so how many days
    If begDay = 1 Then
        numDaysBegin = 0
    Else
        numDaysBegin = DateSerial(begYear, begMonth + 1, 0) - BD
    End If
    
    'ED might be middle of month, so how many days
    numDaysEnd = ED - DateSerial(endYear, endMonth, 1)
    
    Result = Loan
    
    'do partial month at beginning if necessary
    If numDaysBegin > 0 Then
        Result = Result * (1# + DailyRate(BD)) ^ numDaysBegin
        curDay = 1
        curMonth = begMonth + 1
        If curMonth = 13 Then
            curYear = begYear + 1
            curMonth = 1
        Else
            curYear = begYear
        End If
    
    Else
        curDay = 1
        curMonth = begMonth
        curYear = begYear
    End If
    
    'do whole months in the middle
    Do While DateSerial(curYear, curMonth, 1) < DateSerial(endYear, endMonth, 0)
        curDate = DateSerial(curYear, curMonth, 1)
        
        If curMonth = 2 And IsLeapYear(curYear) Then
            Result = Result * (1# + DailyRate(curDate)) ^ (numDays(curMonth) + 1)
        Else
            Result = Result * (1# + DailyRate(curDate)) ^ numDays(curMonth)
        End If


        curMonth = curMonth + 1
        If curMonth = 13 Then
            curYear = curYear + 1
            curMonth = 1
        End If
    Loop
    
    'do partial month at end if necessary
    If numDaysEnd > 0 Then
        Result = Result * (1# + DailyRate(ED)) ^ numDaysEnd
    End If
    
    Cal_intAPL = Result
End Function


'http://www.cpearson.com/Excel/DateTimeVBA.htm#LeapYear
Private Function IsLeapYear(Y As Long)
    IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function


Private Function DailyRate(D As Date) As Double
    Dim i As Long
    Dim RateForPeriod As Double
    
    If D <= VCR(LBound(VCR, 1), 1) Then
        RateForPeriod = VCR(LBound(VCR, 1), 2)
    ElseIf D >= VCR(UBound(VCR, 1), 1) Then
        RateForPeriod = VCR(UBound(VCR, 1), 2)
    Else
        For i = LBound(VCR, 1) To UBound(VCR, 1) - 1
            If VCR(i, 1) <= D And D < VCR(i + 1, 1) Then
                RateForPeriod = VCR(i, 2)
                Exit For
            End If
        Next i
    End If
    
    'Only February in leap year has the daily rate based on a 366 day year. The rest of the months within leap year will have the daily rate based on a 365 day year
    If Month(D) = 2 And IsLeapYear(Year(D)) Then
        DailyRate = RateForPeriod / 366#
    Else
        DailyRate = RateForPeriod / 365#
    End If
End Function

[truc.tu]

Thank you Paul for your reply but the calculation is far off when I compare it with my manually calculation by breaking the period of time into smaller parts. I have tried a setup for my calculation and so far they did well as long as the End Date's Year is the same as Upper bound's year.

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

[Paul_Hossler]

Thank you Paul for your reply but the calculation is far off when I compare it with my manually calculation by breaking the period of time into smaller parts. I have tried a setup for my calculation and so far they did well as long as the End Date's Year is the same as Upper bound's year.

Post a workbook with your manual calculations and I'll check

[Paul_Hossler]

Don't know if you're still interested in this, but I wanted to finish

I made a spread sheet with manual calculations and the result of my function below and they seem to agree

The Red is a manual calculation and the Green is my function

Option Explicit


Dim VCR(1 To 9, 1 To 2) As Variant 'Loan interest rate table '


Public Function Cal_intAPL(Loan As Double, BD As Date, ED As Date) As Variant 'BD is beginning date, ED is end date
    Dim numDaysBegin As Long, numDaysEnd As Long
    
    Dim begYear As Long, begMonth As Long, begDay As Long
    Dim endYear As Long, endMonth As Long, endDay As Long
    Dim curYear As Long, curMonth As Long, curDay As Long
    Dim curDate As Date
    
    Dim i As Long
    Dim Result As Double


    'from 5/1/2014 to 9/1/2015 minus one day the annual interest rate is 11%
    VCR(1, 1) = DateSerial(2014, 5, 1): VCR(1, 2) = 0.11
    VCR(2, 1) = DateSerial(2015, 9, 1): VCR(2, 2) = 0.1
    VCR(3, 1) = DateSerial(2016, 12, 1): VCR(3, 2) = 0.0975
    VCR(4, 1) = DateSerial(2017, 1, 1): VCR(4, 2) = 0.1
    VCR(5, 1) = DateSerial(2017, 3, 1): VCR(5, 2) = 0.0975
    VCR(6, 1) = DateSerial(2018, 2, 1): VCR(6, 2) = 0.095
    VCR(7, 1) = DateSerial(2018, 8, 1): VCR(7, 2) = 0.0935
    VCR(8, 1) = DateSerial(2018, 9, 1): VCR(8, 2) = 0.095
    VCR(9, 1) = DateSerial(2019, 8, 1): VCR(9, 2) = 0.1


   
    begYear = Year(BD)
    begMonth = Month(BD)
    begDay = Day(BD)
    endYear = Year(ED)
    endMonth = Month(ED)
    endDay = Day(ED)
    
    If BD = ED Then
        Cal_intAPL = Loan
        Exit Function
    End If
    
    'BD might be middle of month, so how many days
    If begDay = 1 Then
        numDaysBegin = 0
    Else
        numDaysBegin = DateSerial(begYear, begMonth + 1, 0) - BD + 1
        begDay = 1
        begMonth = begMonth + 1
        If begMonth = 13 Then
            begMonth = 1
            begYear = begYear + 1
        End If
    End If
    'beg = first day of next month
    
    'ED might be middle of month, so how many days
    If endDay = NumDays(ED) Then
        numDaysEnd = 0
    Else
        numDaysEnd = ED - DateSerial(endYear, endMonth, 1) + 1
        endMonth = endMonth - 1
        If endMonth = 0 Then
            endMonth = 12
            endYear = endYear - 1
        End If
        endDay = NumDays(DateSerial(endYear, endMonth, 1))
    End If
    'end = last day of previous month
    
    Result = Loan
    
    'do partial month at beginning if necessary
    If numDaysBegin > 0 Then
        Result = Result * DailyRate(BD) ^ numDaysBegin
    End If
    
    curYear = begYear
    curMonth = begMonth
    curDay = begDay
    
    curDate = DateSerial(curYear, curMonth, curDay)
    
    'do whole months in the middle
    Do While curDate <= DateSerial(endYear, endMonth, endDay)
        curDate = DateSerial(curYear, curMonth, 1)
        
        Result = Result * DailyRate(curDate) ^ NumDays(curDate)


        curMonth = curMonth + 1
        If curMonth = 13 Then
            curYear = curYear + 1
            curMonth = 1
        End If
    
        curDate = DateSerial(curYear, curMonth, 1)
    
    Loop
    
    'do partial month at end if necessary
    If numDaysEnd > 0 Then
        Result = Result * DailyRate(ED) ^ numDaysEnd
    End If
    
    Cal_intAPL = Result
End Function


'http://www.cpearson.com/Excel/DateTimeVBA.htm#LeapYear
Function IsLeapYear(Y As Long)
    IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function


Function DailyRate(D As Date) As Double
    Dim i As Long
    Dim RateForPeriod As Double
    
    If D <= VCR(LBound(VCR, 1), 1) Then
        RateForPeriod = VCR(LBound(VCR, 1), 2)
    ElseIf D >= VCR(UBound(VCR, 1), 1) Then
        RateForPeriod = VCR(UBound(VCR, 1), 2)
    Else
        For i = LBound(VCR, 1) To UBound(VCR, 1) - 1
            If VCR(i, 1) <= D And D < VCR(i + 1, 1) Then
                RateForPeriod = VCR(i, 2)
                Exit For
            End If
        Next i
    End If
    
    'Only February in leap year has the daily rate based on a 366 day year. The rest of the months within leap year will have the daily rate based on a 365 day year
    If Month(D) = 2 And IsLeapYear(Year(D)) Then
        DailyRate = 1# + (RateForPeriod / 366#)
    Else
        DailyRate = 1# + (RateForPeriod / 365#)
    End If
End Function




Function NumDays(D As Date) As Long
    NumDays = Day(DateSerial(Year(D), Month(D) + 1, 0))
End Function

[truc.tu]

Hello Paul!

Sorry for the late reply since it was our holiday. I have looked at your excel file and I have put together a file of the requirements. Mine works well with Leap Years, however when it comes to non Leap Years, my macro got it wrong, you can see it from the diff cell.

Thank you for your help Paul!

[Paul_Hossler]

I don't think your calculations are correct

I made a month-by-month calculation, based on:

1. the number of days in the partial beginning and ending months
2. the number of days in the month
3. Leap year February annual rate effective at that time / 366, otherwise / 365

Capture.JPG


Col I is the step by step, Col J is my function result for the start in row A2 to the end date in Col B

The only change I made to the previous version of my function was to pass the rates table as a parameter instead of hard coding it

[truc.tu]

Hello Paul

From the calculation, I can see that your formula is

FV=PV(1+r/365)^[number of days]

While my problem is using the formula of

FV=PV(1+r)^[number of days/365]

Even though I don't think that there will be much of a different but right now the differences between the two is quite big.

Thank you for your help Paul and I'm looking forward for your reply. In the mean time, I'll try to modify your macro into the second formula.

[truc.tu]

Hello Paul,

Although I have managed to find out the solution for my problem by changing the dailyrate formula, I have encountered something else. For example, if the range between begin date and end date is within a month, the calculation would be wrong.

Thank you so much for the help! I hope you didn't have any sleepless nights because of this.

[Paul_Hossler]

In my spread sheet, I compute the Daily rate, based on the number of days in the month (28, 30, 31) and if it's a February leap year (28, 29) from the appropriate annual rate


Capture1.JPG


Then it's

Current month $ = Previous month$ X (1 + DailyRate) ^ NumDays


Capture.JPG


You formula

FV=PV(1+r)^[number of days/365]


Just using Excel's FV() function, your formula doesn't compound daily if that's what you want, it appears to end up as annual
Since the rates change over time (possibly mid-month) and you wanted leap year February treated special, I think that a Daily compounding is more accurate

Capture.JPG

For example, if the range between begin date and end date is within a month, the calculation would be wrong.

Mine has the advantage of handling less than a full month

[truc.tu]

Dear Paul,

Thank you for your help! I have checked it again and you're absolutely correct. I will mark the post as solved now.

[SamT]

IsLeapYear = If ((Year(Date) Mod 4 = 0) And (Year(Date) Mod 100 <> 0)) OR (Year(Date) Mod 400 = 0)

Number of days used for APR Compounded Daily = Depends on local regulations; can be 360 or 365 or 365/366; Except during prorated months; then it depends of local regulations.

Monthly Interest due is calculated by total debt on day payment is due. Generally, in USA, Interest due is BalancexAPRx30/360 regardless of num days in month or year. Late Payment fees obviate the need for daily interest rates.

Daily Interest

DPR :=: From APR and num days in regulated year,
ND :=: num days since last transaction
Bal :=: Balance after last transaction

IntDue = Bal*((1+DPR)^ND)
Balance = Balance+IntDue (+ Late Fees, generally applied at the due date)

The effective date of any rate change depends on local regulations, in any case, for ND above, the effective date should be considered a Transaction date.