Блог

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

некоторые детали кредита:

 {loanValue=10000, interest=0.1, termMonths=12};
 

1. Подайте заявку на кредит 6 января, но я уточняю, что хотел бы погасить его в конце месяца.
2. Получите средства 15-го числа (фактически начиная выдачу кредита)
3. Я возвращаю свой первый платеж 28 января ( это указанная дата EoM).

Я легко могу разобраться в:

• Сумма погашения: -PMT((1 0.1)^(1/12)-1,12,10000) $=877,16
• Часть капитала: -PPMT((1 $B$2)^(1/12)-1,1,12,10000) = 797,41 долл.
• Процентная часть`: -IPMT((1 $B$2)^(1/12)-1,1,12,10000) = $79,74

Однако я не уверен, как скорректировать сумму погашения за первые месяцы ( и эффективные расчеты), чтобы поддержать этот начальный неполный месяц?

Ниже показано, как далеко я продвинулся в универсальной функции для этого:

 Private Function createRepaymentSchedule(TotAmnt, anIntRate, nPER, startDate, processDate)
 Dim rs As DAO.Recordset
    Dim RT As Double
    Dim RES As Double
    RT = (1   anIntRate) ^ (1 / 12) - 1
    Dim Sql As String
    Pval = TotAmnt
    For i = 1 To nPER
        cap = -PPmt(RT, i, nPER, TotAmnt)
        intr = -IPmt(RT, i, nPER, TotAmnt)
        Pval = Pval - (cap)
        Debug.Print (i amp; "~" amp; Round(cap, 2) amp; "~" amp; Round(intr, 2) amp; "~" amp; Round(Pval, 2))
    
    Next i
 

Многое из этого зависит от условий кредитования. Приведенный ниже ответ основан на некоторых предположениях (которые я бы предпочел проверить с помощью комментариев заранее).:

1. Непрерывное смешивание
2. Оплата в последний рабочий день месяца после первого платежа считается такой же, как оплата в последний день месяца
3. Дата процесса не используется ни в каких расчетах, поэтому я не могу ее включить.
4. Датой начала является дата фактического предоставления кредита.
5. Датой расторжения кредита является последний день месяца через несколько месяцев после Даты начала

Обычно PMT, PPMT и IPMT основаны на другом предположении, которое почти никогда не соответствует действительности : платежи являются единообразными. Таким образом, каждый период имеет точно такую же продолжительность, вплоть до второго. Тот факт, что один месяц короче другого, а реальные даты платежей приходятся на рабочие дни, обычно не является важным фактором, поэтому PMT делает «достаточно хорошее» приближение. Однако дата вашего первого платежа, наступающая только через половину периода, будет иметь существенное значение — особенно для PPMT и IPMT, так что ваша точка зрения верна. Они не могут быть использованы.

Сначала я попытался ответить на финансовый трюк, чтобы ответить на вопрос: перенесите дату кредита на начало месяца и установите сумму кредита = PV кредита на реальную дату начала (-15 дней). Это работает в финансовом плане, но создает проблемы в плане Принципов, Процентов и Капитала. Вместо того, чтобы настраивать его, я просто сделал то, что вы предложили: создал новый PPMT, который не только основан на вашей дате начала, но и включает ваше требование «последний день недели месяца». В результате получается нечто, что соответствовало бы банковским стандартам.

Это можно было бы встроить в VBA, как вы предлагали, но я решил придерживаться чистого Excel, так как обнаружил, что в последнее время LET является недостаточно используемым подходом, и решение его с помощью LET имело бы свои преимущества. Итак, это решение основано на функции Excel LET и создает динамический массив, который будет отображаться на рабочем листе. Для этого требуется Excel 2016 или Microsoft 365. Если это проблема, скажите мне в комментариях, и я смогу преобразовать это в VBA (но с помощью некоторых действительно разных методов).

Настройка Я помещаю ваши ключевые переменные (TotAmt, anIntRate, nPer, дата начала и дата процесса) в ячейки с B1 по B5. NB: Дата процесса не используется. Результаты помещаются в A8, а заголовки в A7:F7 для обозначения каждой серии значений.

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

Элемент представляет собой простую вертикальную последовательность =SEQUENCE( B3 1,,0 ) . Дата-это динамический массив, основанный на формуле, которая вычисляет последний рабочий день месяца:

 =IF( A8#, WORKDAY( EOMONTH( startDate, A8# - 1 )   1, -1 ),
             startDate )
 

ПРИМЕЧАНИЕ: ваш последний рабочий день-28 января. Возможно, вы находитесь в стране СОЛНЦА.
Существует еще один способ вычисления последнего дня недели, основанный на ДНЕ недели
, который позволит вам перенести его в расписание вашей страны. Если вам
это нужно, попросите об этом в комментариях.

Формулу я ввел в C8 следующую формулу:

 =LET( loanAmt, B1,
      anInt, B2,
      nP, B3,
      startD, B4,
      pmtDates, B8#,
       v, SEQUENCE( nP 1,,0 ),   h, TRANSPOSE( v ),
       pp, SIGN( h ),
       ones, SIGN( TRANSPOSE( pp   1 ) ),
       stagr, (v - h   1) * (v >= h),
       dlyInt, ( 1   anInt ) ^ (1/365 ) - 1,
       fvFactors, ( 1   dlyInt ) ^ ( pmtDates - startD ),
       fvarray, INDEX( fvFactors, stagr, ) * SIGN( stagr ),
       guess1, PMT( ( 1   anInt ) ^ (1/12 ) - 1, nP, loanAmt ),
       rseq1, MMULT( fvarray * ( loanAmt*(1-pp)   guess1*pp), ones),   guess2, guess1 * ( 1   INDEX( rseq1, 13 )/loanAmt ),
       rseq2, MMULT( fvarray * (loanAmt*(1-pp)   guess2*pp), ones ),   guess3, guess2 * ( 1   INDEX( rseq2, 13 )/loanAmt ),
       rseq3, MMULT( fvarray * (loanAmt*(1-pp)   guess3*pp), ones ),   guess4, ROUND( guess3 * ( 1   INDEX( rseq3, 13 )/loanAmt ), 2 ),
       rseq4, MMULT( fvarray * (loanAmt*(1-pp)   guess4*pp), ones ),
       prin, INDEX( rseq4, v 1, ) - INDEX( rseq4, v, ),
       i,  (TRANSPOSE(loanAmt*(1-pp)   guess4*pp) - prin) * --( v  1 > 1 ),
       cflows, TRANSPOSE(loanAmt*(1-pp)   guess4*pp),
       tap, IFERROR( INDEX( cflows, v 1, SEQUENCE( 1, 2 ) ), prin ),
       tapi, IFERROR( INDEX( tap, v 1, SEQUENCE( 1, 3 ) ), i ),
       tapic, IFERROR( INDEX( tapi, v 1, SEQUENCE( 1, 4 ) ), rseq4 ),
       tapic )
 

Как это работает
В данный момент я немного занят, поэтому не могу дать подробное объяснение прямо сейчас, поэтому я дам представление на высоком уровне:

Входные переменные являются прямыми — как уже упоминалось, даты являются вспомогательным столбцом, поэтому pmDates-это диапазон дат платежей, созданных в соответствии с диапазоном последних дней недели месяца, упомянутым выше.

Формирователи массивов — это просто последовательности индексов и матриц, которые будут использоваться при формировании и расчете финансовых показателей. Затем они используются для создания набора финансовых массивов для будущих факторов стоимости, которые будут использоваться для создания всех финансовых результатов.

Конвергенция платежей-это итеративный подход к созданию скорректированной реальной суммы платежа (т. е. замены PMT), которая действительно уравновесит кредит, если он будет выплачен в соответствии с введенными датами PMT. При этом используются итерации, которые начинаются с предположения, а затем корректируют предположение в каждой итерации. Он сходится быстрее, чем Ньютон-Рафсон, и, вероятно, чрезмерно разработан для небольших значений кредита/процента, но может надежно масштабироваться до 7-значных кредитов и двузначных процентных ставок. Конечным результатом является rseq4, который представляет собой график оставшегося капитала, который вы просили.

Prin (PPMT), i (IPMT) и потоки (платежи) рассчитываются в столбцах в столбцах результатов. Построение таблицы результатов затем добавляет их, используя вложенную избыточную индексацию столбцов, чтобы сформировать единую таблицу, называемую tapic (t, способную к множествам в процентах), которая является конечным результатом.

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