Расчет средней процентной ставки с использованием диапазона входных данных в Excel

Question

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

В таблице просто указаны даты начала и окончания установленной процентной ставки за период в десять лет. Процентная ставка может меняться так же часто, как четыре раза в календарном году, а в некоторые годы вообще не. За десятилетний период 22 записи. Мне нужно рассчитать среднюю процентную ставку по каждому из десяти календарных лет в периоде.

Пример таблицы курсов валют:

Поэтому в приведенной выше таблице (в формате DD/MM/YYYY ) для достижения средней процентной ставки за 2008 год я рассчитываю следующее:

• С 1 января 2008 года по 07 июля 2008 года - базовая ставка: 6,00% - ставка за период 3,10%
• С 8 июля 2008 г. по 3 ноября 2008 г. - базовая ставка: 6,25% - ставка за период 2,04%
• С 4 ноября 2008 года по 31 декабря 2008 года - базовая ставка: 7,00% - ставка за период 1,13%

Средняя ставка за год 6,27%.

Есть ли формула, которую я мог бы использовать для расчета в Excel?

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

Answer 1

Я дал ответ, который, по сути, делает свое дело.

Думаю, что я опубликую это здесь на случай, если это подойдет кому-то еще

Я добавил новый столбец для каждого года и над каждым столбцом ввел дату начала и дату окончания года (в данном случае начиная с 1 августа и заканчивая 31 июля следующего года).

Оттуда я использовал вариант следующей формулы в каждой ячейке:

D5 =MAX(MIN($B5,D$2)-MAX($A5,D$1),0)/(D$2-D$1+1)*$C5

Который может быть разбит как:

• Дата окончания (но не позднее конца года)

Минус:

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

Деленное на:

• Количество дней в периоде (в процентах от года)

Умножается на:

• Процентная ставка за период

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

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

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