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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.