Calculando a taxa de juros média usando um intervalo de entradas de data no excel

Question

Preciso calcular a taxa de juros média para um ano civil, com base em uma tabela de taxas de câmbio que são alteradas em várias datas.

A tabela mostra simplesmente a data de início e de término da taxa de juros definida, por um período de dez anos. A taxa de juros pode mudar até quatro vezes em um ano civil e, em alguns anos, em nenhum momento. Em um período de dez anos, há 22 entradas. Preciso calcular a taxa média de juros de cada um dos dez anos civis do período.

Exemplo de tabela de taxas de câmbio:

Portanto,natabelaacima(DD/MM/YYYYformat),paraatingirataxadejurosmédiade2008,eucalculooseguinte:

• 01dejaneirode2008a07dejulhode2008-TaxaBase:6,00%-TaxaporPeríodo3,10%
• 08Jul2008to03Nov2008-TaxaBase:6,25%-TaxaporPeríodo2,04%
• 04denovembrode2008a31dedezembrode2008-TaxaBase:7,00%-TaxadePeríodo1,13%

TaxaMédiaparaoano6,27%.

ExisteumafórmulaqueeupoderiausarparacalcularissonoExcel?

Meumaiordesafioatéagorafoiaplicarqualquerfórmulaparaefetivamente"arrastar para baixo" para produzir os resultados corretos para cada ano civil.

    

Answer 1

Eu produzi uma resposta que essencialmente faz o truque.

Pensei em publicá-lo aqui, caso isso apareça para outra pessoa.

Euadicioneiumanovacolunaparacadaanoe,acimadecadacoluna,insiraadatadeinícioedetérminodoano(nessecaso,de1deagostoaté31dejulhodoanoseguinte).

Delá,useiumavariantedaseguintefórmulaemcadacélula:

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

Quepodeserdivididoem:

• Adatafinal(masnãodepoisdofinaldoano)

Menos:

• Adatadeinício(masnãoantesdoiníciodoano)
• Limitadaparanãosermenorquezero(comoquandoafórmulaécopiadaparaoutrosperíodos,elaproduziráumresultadonegativo)

Divididopor:

• Onúmerodediasnoperíodo(paraproduzirumaporcentagemdoano)

Multiplicadopor:

• Ataxadejurosdoperíodo

Emboraissoprecisassedeumatabelamaior,elaproduziuumataxadejurosmédiabastanteprecisaparacadaperíodo,conformemostradonostotais.Pareciaumpoucodesajeitada,mascomoamesarealémuitomaior,afórmulapoderiaserfacilmentearrastadaporumgrandenúmerodeperíodoseentradas.

ElepodetersidomaisprecisousandoafórmulaYEARFRAC,mastiveproblemasparaproduzirumresultado"zero" para os períodos futuros, em que nenhum resultado é esperado.

Tenho certeza de que há uma resposta mais clara e precisa, que eu estaria muito interessada em aprender, mas isso pode ser útil para alguém.