Расчет ежедневного начисления процентов - проблемы с високосными годами

Question

Так что у меня есть интересная проблема - мне нужно рассчитать начисленные проценты на сумму. Легко. Это должно быть сделано ежедневно. Легко. Нужно правильно учитывать високосные годы. Нелегко.

Я перечитал формулу Excel, чтобы преобразовать процентную ставку в год в сложную дневную и недельную ставку , что является хорошим примером для начисления процентов и расчета процентов. У меня есть текущая формула:

=InitialAmount*(1+0.1/365)^(TODAY()-EOMONTH(StartDate,0))-F8

Обратите внимание: дата начала указывается мне как дата в течение месяца, однако проценты начисляются только в конце месяца.

Такая же разница. Однако, когда я попадаю в високосный год, он разбивается на копейки. Мне не нравится, когда у меня нет денег - как я могу правильно рассчитать ежедневные начисляемые проценты за високосные годы?

Спасибо

Изменить: чтобы быть совершенно ясным, мне нужно иметь возможность рассчитывать суммы процентов, которые пересекают как високосные годы, так и не високосные годы. Например, если я начну рассчитывать проценты 21 июля 2015 года, остальная часть 2015 года будет регулярной, 2016 интерес високосного года, и 2017 год вернулся к обычному интересу.

Также обратите внимание, что заявленная процентная ставка указана за год, независимо от количества дней в году. Таким образом, ежедневная процентная ставка будет отличаться для високосных лет.

Так как все еще есть некоторая путаница:

Пример:

1-й день - 31 декабря 2015 г. Начальное значение 100 000,00. Процентная ставка составляет 20% годовых. 100 000,15 = 100 000 *(1+.2/365)^(1/365).

2-й день - 1 января 2016 г. Начальное значение теперь 100 000,15. Процентная ставка по-прежнему 20% годовых. 100000,30 = 100000,15 *(1+0,22/366)^(1/366).

3-й день 2 января. 100000,30 = 100000,15 *(1+0,22/366)^(1/366).

~ 368-й день - 1 января. Теперь множитель вернулся к (1+.2/365)^ (1/365).

Я ищу формулу, которая безупречно переходит в високосные годы и из них при правильном расчете начисления процентов.

(100000 *(1+ .2/365) ^(1/365)) *(1+ .2/366) ^(1/366) - это 1+ 2 день вместе взятых.

Изменить 2: текущее состояние проблемы: решение выглядит примерно так:

=PV*(1+rate/365)^DaysNotInALeapYear*(1+rate/366)^DaysInALeapYear

Где ставка - это годовая ставка.

Answer 1

Я полагаю, что скриншот таблицы ниже расскажет вам, что нужно для этого. Столбец B дает формулу, столбец C - значение. Здесь есть несколько шагов:

1. Определите, сколько дней существует от даты начала до даты окончания.
2. Определите, сколько лет прошло с начала начального года до конца конечного года
3. Определите, сколько дней в эти годы
4. Вычтите 365 * количество лет: разница есть количество високосных лет
5. Определите, сколько дней в году начала и в конце года: это определяет, являются ли они високосным годом
6. Определите количество високосных дней в общем периоде от начальной до конечной даты. Эти начисляются по ставке /366
7. Общее количество дней минус количество високосных дней, начисляемых по курсу /365
8. Теперь у вас есть то, что вам нужно, чтобы вычислить число, после которого вы были

Вполне возможно, что в обоих концах моих вычислений произошла ошибка 1 день - формула Excel =DAYS(stopDate, startDate) возвращает 1 для последовательных дней; Я не уверен, что кредит, который открывается в понедельник и выплачивается во вторник, будет приносить 1 или 2 дня интереса. Но это то, что вы, вероятно, можете понять.

Дайте мне знать, если это имеет смысл!

PS1:

Возможно, вы могли бы сделать это как одно уравнение монстра, но это было бы очень, очень грязно. Некоторое упрощение может быть возможным, если вы не рассчитываете numNormalDays отдельно (B17), а вместо этого вычисляете

=(1+rate/365)^totalDays*(1-rate*365/366)^leapDays

поскольку leapDays<<totalDays , это приближение должно работать (и тогда вам не нужно использовать результат B16 дважды, что удвоит размер вашего уравнения, если вы попытаетесь сделать это в одной строке.

Как вы можете видеть, в приведенном примере разница между этими двумя методами находится в 8-й значащей цифре. Не уверен, что этого достаточно, чтобы потревожить ...

PS2:

Было бы намного лучше спрятать всю эту работу в функции VBA - ее нужно будет правильно проверить только один раз, и тогда ваша электронная таблица сможет использовать ее повсюду. На самом деле он был бы гораздо менее подвержен ошибкам при использовании (поскольку существует множество способов, которыми вставка копий в уравнение монстров может и не получится ... и это будет так сложно устранить).

Функция VBA может выглядеть следующим образом (очень близко к методу, используемому в электронной таблице, некоторые различия в реализации):

Option Explicit

Function compoundInterestLeap(startDate, endDate, rate)
' compute the interest that accrues from the end of the month that includes startDate, to endDate
' taking account of the fact that interest accrues more slowly in leap years (1/366 th per day).
' needs error checking?

Dim startYear, endYear, totalYears, leapYears, totalDays, leapDays, normalDays
Dim missingDaysFirst, missingDaysLast, totalYearDays
Dim yearOneIsLeap, yearNisLeap As Boolean
Dim eom As Date

eom = Application.WorksheetFunction.EoMonth(startDate, 0)

startYear = year(startDate)
endYear = year(endDate)
totalYears = endYear - startYear + 1
With Application.WorksheetFunction
    totalDays = .Days(endDate, eom)
    totalYearDays = .Days(DateSerial(endYear + 1, 1, 1), DateSerial(startYear, 1, 1))
    missingDaysFirst = .Days(eom, DateSerial(startYear, 1, 1))
    missingDaysLast = .Days(endDate, DateSerial(endYear, 12, 31))
End With

leapYears = totalYearDays - totalYears * 365

leapDays = leapYears * 366
If isLeapYear(startYear) Then
    leapDays = leapDays - missingDaysFirst
End If

If isLeapYear(endYear) Then
    leapDays = leapDays - missingDaysLast
End If

normalDays = totalDays - leapDays

compoundInterestLeap = (1 + rate / 365) ^ normalDays * (1 + rate / 366) ^ leapDays

End Function

Function isLeapYear(year)
' return True if the year passed as an argument is a leap year
' no error checking...
If Application.WorksheetFunction.Days(DateSerial(year, 12, 31), DateSerial(year - 1, 12, 31)) = 366 Then
    isLeapYear = True
Else
    isLeapYear = False
End If

End Function

Answer 2

Ладно, это заняло довольно много времени, и решение - полный медведь. Благодарим @Floris за помощь в решении этой проблемы.

Вопрос требует, чтобы все закончилось единой формулой, но я собираюсь разбить все на части, а затем собрать их вместе.

Я собираюсь замять некоторые основы сложного интереса. Для ознакомления с основными понятиями см. Https://www.investopedia.com/terms/c/compoundinterest.asp для концепций и формулу Excel для преобразования годовой процентной ставки в ежедневную и еженедельную ставку для простых примеров математики, стоящей за ней. первенствует.

Начнем с нашей стандартной формулы процента:

FV = PV(1+r/n)^(t*n)

где т в годах. Это означает, что ежедневная рецептура (для нормального года)

FV = PV*(1+r/365)^DaysNotLeap

И наше начисление процентов на високосный год

FV = PV*(1+R/366)^DaysInLeapYears

Нам математически разрешено объединить этих двоих так:

FV= PV*(1+r/365)^DaysNotLeap*(1+R/366)^DaysInLeapYears

Теперь дело в том, чтобы выяснить, сколько дней в високосных годах и сколько дней - нормальные дни между двумя датами. Это довольно сложно, но как только у нас есть одно, у нас есть другое.

Я собираюсь называть их StartDate и EndDate.

Сколько дней между двумя?

DaysDifference=EndDate-StartDate+1
DaysDifference=Days(EndDate,StartDate)+1

Эти две формулы - одно и то же, просто написано немного по-разному. Один использует встроенную функцию дней в Excel, другой - простое вычитание.

Почему +1? Если я начну 2 января и закончу 3 января, у меня будет 2 и 3 января. Но если я вычту даты, я получу только 1. Нужно добавить один обратно, чтобы компенсировать.

Это было просто. Сколько високосных лет между ними, в том числе если мы начинаем високосный год, заканчиваем високосный год или полностью пропускаем високосный год? Мы можем сделать это, рассчитав, сколько дней мы ожидаем между двумя годами и сколько дней у нас на самом деле между этими двумя годами.

DaysInYearsBetween=DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1))

Дни у нас на самом деле есть

Дни, которые мы ожидаем иметь: сначала мы рассчитываем, сколько лет проходит между двумя днями:

YearsBetween=YEAR(enddate)-YEAR(startdate)+1

Тогда мы умножаем на 365

ExpectedDaysBetween=YearsBetween*365

Полное раскрытие всех формул произойдет на последнем этапе.

Так сколько же високосных лет? Давайте вычтем наши два числа:

LeapYearsInvolved = DaysInYearsBetween - ExpectedDaysBetween

Это работает, потому что каждый високосный год добавляет один дополнительный день, поэтому количество дополнительных дней, которое у нас есть, равно количеству високосных лет. Большой!

Нам нужно знать, является ли последний или конечный год високосным. Это можно сделать с помощью пары тестов, тестирование аналогично:

IsStartDateLeap=IF(DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366,TRUE,FALSE)

Это очень ясно и показывает все необходимое. Если вы немного потрясены своим превосходством, я рекомендую выше. Вы можете сократить его до:

IsStartDateLeap=DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366

И пара:

IsEndDateLeap=DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366

Это дает нам довольно простое значение true/false, если наш начальный или конечный год - високосный, который мы можем использовать, чтобы помочь вычислить, где эти дни принадлежат.

Далее нам нужно количество дней в году, которые не использовались ни в начале, ни в конце. Например, если мы начали 27 января, то в этом году 26 дней мы не использовали.

UnusedStartDays=startdate - DATE(YEAR(startdate)-1,12,31)

UnusedEndDays=DATE(YEAR(enddate)+1,1,1)-enddate

Итак, теперь у нас есть все части - давайте свяжем их вместе. Високосные дни - длинная версия:

DaysInLeapYear=LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays)

Leap Days - короткая версия

DaysInLeapYear=LeapYearsInvolved*366-IsStartDateLeap*UnusedStartDays-IsEndDateLeap*UnusedEndDays

Обратите внимание, что в Excel 2010 и более ранних версиях вам может потребоваться преобразовать его в формат (--IsStartDateLeap), чтобы принудительно преобразовать логическое значение в число.

DaysNotLeap=DaysDifference-DaysInLeapYear

Как упоминалось ранее, если мы знаем один, мы знаем оба.

У нас есть все кусочки головоломки. Время собрать их с помощью простой замены. Я не буду подробно останавливаться на деталях замены, просто буду публиковать сообщения на разных этапах.

FV= PV*(1+r/365)^(DaysDifference-DaysInLeapYear)*(1+R/366)^DaysInLeapYears

,

FV= PV*(1+r/365)^(DaysDifference-(LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays)))*(1+R/366)^(LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays))

,

FV= PV*(1+r/365)^(DaysDifference-(LeapYearsInvolved*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^(LeapYearsInvolved*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

,

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DaysInYearsBetween - ExpectedDaysBetween)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DaysInYearsBetween - ExpectedDaysBetween)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

,

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - YearsBetween*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - YearsBetween*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

И мы наконец получаем:

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - (YEAR(enddate)-YEAR(startdate)+1)*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - (YEAR(enddate)-YEAR(startdate)+1)*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

И там мы идем. Как рассчитать ежедневный начисляемый процент между двумя датами, скорректированный на високосные годы.

На вопрос "Разве это не довольно близко друг к другу" - да, они есть. При первоначальном значении в 10 000, в течение 8 лет, с 24 апреля 2008 года по 2 февраля 2016 года, Compounding "должным образом" дает нам значение 14 753,70 долл. США, в то время как сложное "ненадлежащее" значение дает нам 14 757,28 долл. США.

Answer 3

Наконец, я хотел бы предложить вам метод, используемый финансовыми учреждениями. Этот метод сначала подсчитывает количество дней для дней без високосного года и дня високосного года. Затем рассчитывает проценты. Его можно рассчитать по одной формуле или по двум отдельным формулам.

Мое решение имеет две формулы.

Для обычных дней (31-01/2016 - 12-11-2015) = 80 дней (ячейка B589).

Для високосного года = 29 дней (ячейка B590).

Формула для обычного года =(((1+0,055)^(B589/365)-1)* B584)

Формула для високосного года =(((1+0,055)^(B590/366)-1)* B584)

Тогда получите общую сумму 160,55.

Я надеюсь, что вы найдете это полезное решение.

Answer 4

В формуле, которую вы использовали, чего-то не хватает во второй части.

Формула, которую можно использовать для расчета ежедневного начисления процентов ,

= Основная сумма *((1+ годовой международный курс /365)^(инвестиционные годы * 365)))

Теперь самый первый вопрос: какой должна быть формула для високосного года?

Это просто, требуется только одна коррекция вместо 365, используйте 366, поскольку високосный год в феврале имеет 29 дней. Итак, формула

= Основная сумма *((1+ Годовой международный курс /366)^(Инвестиционные годы * 366)))

Теперь позвольте мне внести некоторую ценность в Формулу.

Основная сумма = 10000.

Годовой процент = 10%.

Инвестиционные годы = 5.

NB: для формулы високосного года производят 16 486,09 .

Поскольку мы рассчитываем значение Future, оно может быть рассчитано во время выдачи кредита, как в первый день.

Теперь вернемся к вашей формуле. Во второй части вы написали,

(СЕГОДНЯ ()- КОНМЕСЯЦ (StartDate, 0))

Если просто поставить какое-то значение в этом.

Где Дата начала - 05/01/2017, Формула производит 304. Означает, что вы хотите найти сложный процент за 304 дня на сегодня (т.е. 01/12/2017).

Для этого вы можете использовать эту простую формулу,

= Принцип Amt *(1+Inst. Норма)^ Nu. Дней

Надеюсь, что это поможет вам, в случае, если мое решение отличается, просто добавьте комментарий, и я помогу решить проблему лучше.

Answer 5

Рассматривая вашу проблему с LEAP YEAR, я хотел бы показать вам, как Excel учитывает это при расчете сложного процента.

Проверьте снимок экрана.

Для лучшего понимания я взял ДВА образца, первый для обычного, второй для года LEAP, и я использовал две разные формулы.

Проверьте дату ссуды, имеет ли 2016 год високосный год, составляющий 2017 год.

Рядом с ним Год 2015 в качестве даты займа и Год 2016 года, который является составным (високосный).

В следующей строке вы можете найти 2 аналогичных значения (10 550,00): первое для обычного года, второе для високосного года.

Формула, которую я использовал,

= E542 *(1+0,055)^ ОКРУГЛВНИЗ ((E545-E544)/365,0)

= E542 *(1+0,055) ^ ОКРУГЛВНИЗ ((E545-E544)/366,0)

Примечание: ячейка E542 имеет основную сумму. E545 является датой расчета, а E544 является датой займа.

в последнем ряду вы снова найдете две одинаковые суммы и формула, которую я использовал,

= E542 *(1+5,5%/365)= 365

= E542 *(1+5,5%/366)^ 366

Теперь основной вопрос, как получить вовлечение LEAP ГОД. Для этого я использовал формулу для определения високосного года,

= IF(MOD(ГОД (E545), 4) = 0, "Прыжок", "Обычный")

Таким образом, вывод таков: сначала проверьте, является ли год високосным, или нет, затем примените формулу для подсчета сложного процента. И формула есть ,,

= ЕСЛИ (MOD (ГОД (F545), 4) = 0, E542 * (1+0,055) ^ КРУГЛЫЙ ВОД ((F545-F544)/366,0), E542 * (1+0,055) ^ КРУГЛЫЙ ВОД ((F545-F544))/365,0))

Это лучшее решение, которое я нашел для расчета КИ с учетом года LEAP.

Надеюсь, вы найдете это полезным, в случае, если он снова отличается, просто напишите.