попытка выполнить плавный ежемесячный прогноз на несколько лет для изменения процентных ставок с использованием годовых входных данных по процентным ставкам.
Другими словами, у меня есть процентная ставка на 2022 год (10%), 2023 год (15%), 2024 год (12%), но я хочу прогнозировать ее на ежемесячной основе плавно, вместо того, чтобы иметь 0,8% (=POWER((1+0,1),(1/12))-1) на январь 22 г. - декабрь 22 г., а затем изменить ее на 1,17% на январь 23 г. Я хотел бы сгладить значения, чтобы учесть изменение стоимости (что-то вроде 0,8% в январе 22 г., 0,85% в феврале 22 г. и т. д.).
Есть ли гуру математики, который может помочь сделать этот ежемесячный прогноз точным?
Спасибо!
Майки
решение1
Итак, вы хотите, чтобы процент в январе был некоторой (правильной для этого) суммой, а затем февраль повышается на некоторую сумму и вдвое больше для марта и так далее до 11 раз больше для декабря, так что процент плавно увеличивался на эту дополнительную сумму в месяц и все еще составлял правильный общий годовой процент. Возможно, процент за январь составляет $4.50 и повышается на доллар в месяц до декабря, когда он составит $15.50.
Конечно, это предполагает, что процентная ставка начисляется ежегодно, а не ежемесячно или ежедневно. Немного сложнее, если не ежегодно. (Кстати, это называется "простой процент", и с появлением компьютеров, которые могут выполнять все расчеты, это практически уходит.)
В любом случае, как вы думаете, как это будет выглядеть в математической задаче? Ну, у вас будет сумма "x" в январе, затем "x+$1" в феврале и так далее. Сложите их в течение года, и у вас будет 12 ежемесячных x плюс 1+2+3+...+9+10+11 = 66. Итак, 12x + 66, и это будет равно годовому проценту. Скажем, $120. Выполните алгебраические действия, и вы получите x = $4.50. Таким образом, ежемесячно у вас будет $4.50, $5.50, $6.50...$13.50, $14.50, $15.50.
Но это не «идеально», не так ли? Я имею в виду, эти $4.50, почему они? Почему не $2.00 с большим увеличением каждый месяц? В идеале это должно быть $0 начальных процентов и все «подъемы» — но просто вложив $0 за x, вы не получите никаких процентов в январе. Допустим, в январе есть некая мифическая сумма «подъема», затем в феврале вдвое больше, и так до декабря, когда она будет в 12 раз больше. Тогда все будет гладко, с начальной суммой, равной сумме, которая увеличивается каждый месяц. Тогда ИДЕАЛЬНО гладко!
Итак, снова займемся арифметикой, но теперь 1+2+3+...+10+11+12 (12 значений, а не 11 сверху). Это равно 78. Итак, нужно распределить 78 «шишек», одну в январе, две в феврале и так далее до 12 в декабре. Ах, теперь все так очевидно, да?
В примере со 120 долларами это будет 120/78 долларов = 20/13 долларов = около 1,54 доллара за каждое «повышение».
Все, что вам нужно сделать, это подставить общий годовой процент в это разделение. Если это $10 000 по простой ставке 10% за год, это будет $1000 процентов, которые нужно распределить следующим образом: $1000/78 = около $12,82 в месяц. Таким образом, 2022 год будет $12,82 за январь, $25,64 за февраль, $38,46 за март и так далее. Начиная с $12,82 и увеличиваясь на $12,82 каждый месяц. В общем-то, определение «плавно». Можно будет добавить 4 цента, но пенни тут и там каждые три месяца — это легко и не имеет особого значения для плавности.
Число «78» одинаково для каждого года, поэтому просто возьмите основную сумму за год, простую процентную ставку за год, умножьте их и разделите на 78. Поскольку это Excel, вы можете просто умножить это вычисление на «номер месяца» для этого месяца.
Например, скажем, в столбце A указана годовая основная сумма (рассчитанная или введенная вручную, как вы сочтете нужным), в столбце B указана процентная ставка (как и основная сумма, как вы решите, чтобы она там отображалась), а в столбцах C–N указаны месяцы, и каждая строка — это новый год. У вас либо есть заголовки для каждого столбца месяца, которые являются фактическими датами, например, 1/1/"год" и отформатированы, например, как "ммм", поэтому они отображаются как "Янв", "Фев" и т. д., так что вы можете использовать, =MONTH(C1)и он вернет 1, которую вы используете в качестве множителя этого столбца, ИЛИ у вас могут быть текстовые заголовки, которые вы преобразуете в формуле любым из десяти способов в дату и используете MONTH()ее. В любом случае, или другим способом, вы получите множители от 1 до 12. Таким образом, каждая ячейка под месяцами будет иметь что-то вроде:
=((Principal * Interest Rate) / 78) * MONTH(Heading)
Поскольку основная сумма долга и процентная ставка указаны в A1 и B1, а «Заголовок» — это C1–N1, это будет выглядеть примерно так:
=((A1 * B1) / 78) * Month(N1)
для ячейки C2, затем скопируйте и вставьте его во все двенадцать месяцев и каждую строку, которую вы используете. Только это, написано один раз, затем вставлено один раз.
Если у вас есть Spillфункциональность, вы можете просто написать одну формулу, и она будет распределена по всем необходимым ячейкам: (допустим, у вас есть три года, поэтому используются строки 2, 3 и 4)
=((A2:A4 * B2:B4) / 78) * SEQUENCE(1,12)
поскольку, если у вас есть Spill, у вас также есть SEQUENCE()функция и вы знаете, что используете все месяцы, поэтому генерация последовательности от 1 до 12 так же хороша, как и "гладкая" MONTH()штука с заголовками. Напишите ее, нажмите Enter, и все 36 ячеек заполнятся.
Добавьте a, ROUND()чтобы помочь поймать лишние или меньшие центы, и сделайте так, чтобы последняя ячейка (декабрь) фактически вычитала сумму из остальных 11 ячеек в строке из процентов, которые вы вычисляете, и все проценты будут суммироваться. Не увлекайтесь... это только приблизит все к "правильному", а "правильное" вплоть до цента не является целью: цель - гладкость, поэтому округление окончательного результата для каждого месяца более точно, чем "правильный" способ.
Длинный ответ звучит намного хуже, чем есть на самом деле. Это просто формула выше, или та, которую вам нужно вставить во все требуемые ячейки. Скорректируйте столбцы, которые вы действительно используете, и расположение основного долга и процентной ставки за год, и все готово.