PDA

View Full Version : [SOLVED] Compound interest rate with leap year



truc.tu
01-20-2020, 09:39 PM
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
01-20-2020, 10:58 PM
Whether it's a leap year is of no consequence if the interest is calculated & compounded monthly or annually.

truc.tu
01-20-2020, 11:25 PM
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
01-21-2020, 12:13 AM
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
01-21-2020, 12:35 AM
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
01-21-2020, 05:38 PM
Still thinking / working on it

Takes a while to be careful

Paul_Hossler
01-21-2020, 06:53 PM
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
01-22-2020, 12:49 AM
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
01-22-2020, 06:40 AM
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
01-24-2020, 01:20 PM
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
01-29-2020, 07:48 PM
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
01-29-2020, 09:00 PM
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

25895


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
02-02-2020, 07:40 PM
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
02-03-2020, 03:31 AM
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
02-03-2020, 07:07 AM
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


25911


Then it's

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


25912


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

25913



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
02-03-2020, 07:15 PM
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
02-04-2020, 03:02 AM
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.