Calculando a composição de juros diários - problemas com anos bissextos

Question

Portanto, tenho um problema interessante - preciso calcular os juros compostos em um valor. Fácil. Precisa ser feito diariamente. Fácil. É preciso fatorar corretamente em anos bissextos. Díficil.

Li sobre Fórmula Excel para converter taxa de juros por ano para composição de taxas diárias e semanais , que é uma boa cartilha sobre juros e juros calculados. Eu tenho a fórmula atual:

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

Por favor, note-Start Date é dado a mim como uma data dentro do mês, no entanto, o juro só começa a calcular no final do mês.

Mesma diferença. No entanto, quando eu bato um ano bissexto, ele quebra por centavos. Eu não gosto de gastar centavos - como posso ter os juros diários compostos adequadamente para os anos bissextos?

Obrigado

Editar: para ser completamente claro, preciso descobrir valores de juros que cruzem anos bissextos e não bissexto. Por exemplo, se começar a calcular juros no dia 21 de julho de 2015, o restante de 2015 será regular juros, 2016 é o interesse do ano bissexto e 2017 está de volta aos juros regulares.

Observe também que a taxa de juros declarada é por ano, independentemente do número de dias do ano. Portanto, a taxa de juros diária será diferente nos anos bissextos.

Como ainda há alguma confusão:

Exemplo:

1º dia é 31 de dezembro de 2015. O valor inicial é 100.000,00. Taxa de juros é de 20% ao ano. 100.000,15 = 100.000 * (1 + 0,2 / 365) ^ (1/365).

2º dia é 1º de janeiro de 2016. O valor inicial é agora 100.000,15. A taxa de juros ainda é de 20% ao ano. 100000,30 = 100.000,15 * (1 + 0,2 / 366) ^ (1/366).

O terceiro dia é 2 de janeiro. 100000.30 = 100.000.15 * (1 + .2 / 366) ^ (1/366).

~ 368º dia é 1º de janeiro. O multiplicador está agora de volta para (1 + .2 / 365) ^ (1/365).

Estou à procura de uma fórmula que salte perfeitamente de e para anos bissextos ao calcular adequadamente o interesse de composição.

(100000 * (1 + .2 / 365) ^ (1/365)) * (1 + .2 / 366) ^ (1/366) - é o 1º + o 2º dia combinados.

Editar 2: o estado atual do problema: a solução é algo como isto:

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

Onde taxa é a taxa anual.

    

Answer 1

Tudo bem, isso levou bastante tempo, e a solução é um urso completo. Agradecemos a @Floris por me ajudar a resolver isso.

A questão exige que tudo acabe em uma única fórmula, mas vou dividir as partes e depois reuni-las novamente.

Vou passar algumas informações básicas sobre juros compostos. Para primers, veja link para os conceitos, e fórmula do Excel para converter a taxa de juros por ano para capitalizar as taxas diárias e semanais para exemplos simples da matemática por trás dele no excel.

Começaremos com nossa fórmula de interesse padrão:

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

, onde t é em anos. Isso significa que a composição diária (por um ano normal) é

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

E o nosso interesse de composição para um ano bissexto é

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

Estamos matematicamente autorizados a juntar os dois assim:

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

Agora, o truque é descobrir quantos dias são em anos bissextos e quantos dias são dias normais entre duas datas. Isto é bastante complicado, mas uma vez que temos um, temos o outro.

Vou chamá-los de StartDate e EndDate.

Quantos dias tem entre os dois?

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

Essas duas fórmulas são a mesma coisa, apenas escritas de maneira um pouco diferente. Um usa a função interna do Excel, o outro é a subtração simples.

Por que o +1? Se eu começar em 2 de janeiro e terminar em 3 de janeiro, tenho o segundo e o terceiro. Mas se eu subtrair as datas, eu só recebo 1. Preciso adicionar um de volta para compensar.

Isso foi fácil. Quantos anos bissextos existem entre os dois, incluindo se começarmos em um ano bissexto, terminar em um ano bissexto ou pular completamente um ano bissexto? Podemos fazer isso calculando quantos dias esperamos entre os dois anos versus quantos dias realmente temos entre os dois anos.

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

Dias que realmente temos

Dias que esperamos ter: primeiro calculamos quantos anos há entre os dois dias:

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

Então, nós multiplicamos por 365

ExpectedDaysBetween=YearsBetween*365

A expansão completa de todas as fórmulas ocorrerá na etapa final.

Então, quantos anos bissextos estão envolvidos? Vamos subtrair nossos dois números:

LeapYearsInvolved = DaysInYearsBetween - ExpectedDaysBetween

Isso funciona porque cada ano bissexto adiciona um dia extra, então o número de dias extras que temos é o número de anos bissextos envolvidos. Ótimo!

Precisamos saber se um ano final ou final é um ano bissexto. Isso pode ser feito com um par de testes, testando de maneira semelhante:

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

Isso está sendo muito explícito e mostrando tudo o que é necessário. Se você é um pouco shakey com o seu excel, eu recomendo o acima. Você pode encurtá-lo para:

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

E o par:

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

Isso nos dá um verdadeiro / falso razoavelmente simples se nosso ano inicial ou final for salto, o que podemos usar para ajudar a calcular onde esses dias pertencem.

Em seguida, precisamos do número de dias do ano não usados em nossas datas de início e fim. Por exemplo, se começamos em 27 de janeiro, há 26 dias naquele ano que não utilizamos.

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

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

Então agora temos todas as peças - vamos juntá-las. Leap days - Long version:

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

Dias bissextos - versão resumida

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

Por favor, note no Excel 2010 e anteriores você pode precisar transformá-lo no formato de (--IsStartDateLeap) para forçar a conversão de um booleano para um número.

DaysNotLeap=DaysDifference-DaysInLeapYear

Como mencionado anteriormente, se conhecemos um, conhecemos ambos.

Temos todas as peças do quebra-cabeça. Hora de colocá-los juntos via simples substituição. Não vou repassar todos os detalhes da substituição, apenas postando em vários estágios.

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)))

E finalmente conseguimos:

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)))

E lá vamos nós. Como calcular os juros compostos diários entre duas datas, ajustados para anos bissextos.

Para a pergunta "Não é muito parecido" - sim, eles são. Em um valor inicial de 10.000, mais de 8 anos de 24 de abril de 2008 a 2 de fevereiro de 2016, o Composto "corretamente" nos dá um valor de US $ 14.753,70, enquanto que a composição "indevida" nos dá um valor de US $ 14.757,28.

    

Answer 2

Acredito que a captura de tela abaixo de uma planilha informará o que é necessário para fazer isso. Coluna B dá a fórmula, coluna C dá o valor. Existem vários passos aqui:

1. Determine quantos dias há desde a data de início até a data de término.
2. Determine quantos anos existem desde o início do ano inicial até o final do ano final
3. Determine quantos dias há nesses anos
4. Subtrair 365 * número de anos: a diferença é o número de anos bissextos
5. Determine quantos dias há no ano inicial e final: isso determina se eles são um ano bissexto
6. Determine quantos dias bissextos existem no período total, da data de início até a data final. Estes acumulam a taxa / 366
7. O número total de dias menos o número de dias bissextos acumulados à taxa / 365
8. Agora você tem o que precisa para calcular o número que estava depois

É possível que haja um erro de 1 dia em cada um dos meus cálculos - a fórmula do Excel =DAYS(stopDate, startDate) retorna 1 para dias consecutivos; Não tenho certeza se um empréstimo que é aberto na segunda-feira e pago na terça-feira deve incorrer em 1 ou 2 dias de juros. Mas isso é algo que você provavelmente descobrirá.

Deixe-me saber se isso faz sentido!

PS1:

Você provavelmente poderia fazer isso como uma única equação de monstro, mas seria muito, muito confuso. Alguma simplificação pode ser possível se você não calcular numNormalDays separadamente (B17), mas sim computar

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

desde leapDays<<totalDays , essa aproximação deve funcionar (e então você não precisa usar o resultado de B16 duas vezes, o que duplicaria o tamanho de sua equação se você tentasse fazê-lo em uma única linha.

Como você pode ver, no exemplo dado a diferença entre os dois métodos está no 8º dígito significativo. Não tenho certeza se isso é o suficiente para ter conipções sobre ...

PS2:

Seria muito melhor para ocultar todo esse trabalho em uma função VBA - só precisaria ser validado adequadamente uma vez e, em seguida, sua planilha pode usá-lo em todos os lugares. Na verdade, seria muito menos propenso a erros de uso (já que há muitas maneiras de copiar e colar uma equação monstruosa e pode dar errado ... e seria muito difícil solucionar problemas).

A função VBA pode ter esta aparência (muito próxima do método usado na planilha, algumas diferenças na implementação):

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 3

Por fim, gostaria de sugerir o método usado pelas instituições financeiras. Este método conta primeiro o número de dias para o ano Non Leap e dias do ano bissexto . Então calcula o interesse. Ele pode ser calculado com uma única fórmula ou usando duas fórmulas separadas.

Minha solução tem duas fórmulas.

Para os dias Regulares (31-01 / 2016 - 12-11-2015) = 80 dias (Célula B589) .

Para o ano bissexto dias = 29 dias (célula B590) .

Fórmula para o Ano Regular = (((1 + 0,055) ^ (B589 / 365) -1) * B584)

Fórmula para o ano bissexto = (((1 + 0,055) ^ (B590 / 366) -1) * B584)

Em seguida, obtenha o valor total, 160,55 .

Espero que você considere esta uma solução útil.

    

Answer 4

A fórmula que você usou tem algo faltando na parte 2.

A fórmula que pode ser usada para calcular juros compostos diários é

= Valor Principal * ((1 + Taxa Int. Anual / 365) ^ (Anos de Investimento * 365)))

Agora, a primeira questão é qual deve ser a fórmula para o ano bissexto ?

É simples, apenas uma correção é necessária, em vez de 365, use 366, já que o ano bissexto tem 29 dias em fevereiro. Então a fórmula é,

= Valor Principal * ((1 + Taxa Int. Anual / 366) ^ (Anos de Investimento * 366)))

Agora, deixe-me colocar algum valor na fórmula.

Valor principal = 10000.

Interesse anual = 10%.

Anos de investimento = 5.

NB: Para a fórmula do ano bissexto, produzir 16,486.09 .

Como estamos calculando o valor Futuro para que possa ser calculado no momento do Empréstimo, como no Dia 1.

Agora retornando à sua fórmula. Na segunda parte você escreveu ,

(TODAY () - EOMONTH (StartDate, 0))

Se simplesmente colocar algum valor nisso.

Onde Data de início é 05/01/2017 , a fórmula está produzindo 304. Significa que você quer encontrar Juros compostos de 304 dias hoje (ou seja, 01/12/2017) .

Para isso você pode usar esta fórmula simples,

= Princípio Amt * (1 + taxa Inst.) ^ Nu. De dias

Espero que isso ajude você, no caso de a minha Solução diferir apenas de soltar um Comentário, ajudará a corrigir o problema de uma maneira melhor.

    

Answer 5

Considerando o seu problema com o LEAP YEAR, gostaria de mostrar a você como o Excel o considera ao calcular juros compostos.

Verifique a captura da tela.

Paraummelhorentendimento,eutomeiDOISamostras,primeiroéparaRegularsegundoparaoanoLEAP,eeuuseiduasfórmulasdiferentes.

Verifiqueadatadoempréstimo,Oanode2016foioanobissexto,ovencimentonoanode2017.

Próximoaele,oanode2015comodatadeempréstimo&Anodecomposiçãodoanode2016(éoanobissexto).

Napróximalinha,vocêpodeencontrar2valoressemelhantes(10,550.00)primeiroparaosegundoanoregularparaoanobissexto.

Afórmulaqueuseié

=E542*(1+0,055)^ROUNDDOWN((E545-E544)/365,0)

=E542*(1+0,055)^ROUNDDOWN((E545-E544)/366,0)

NB:OcelularE542temvalorPrincipal.E545éadatadecálculo&E544éDatadeEmpréstimodada.

naúltimalinha,vocêencontraráduasquantidadessemelhantes&afórmulaqueuseié

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

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

Agora,aquestãoprincipalécomoenvolveroLEAPYEAR.Paraissoeuuseiafórmulaparadeterminaroanobissextoé,

=SE(MOD(ANO(E545),4)=0,"Leap", "Regular")

Portanto, a conclusão é , primeiro teste se o ano é Leap ou não, então aplique a fórmula para contar juros compostos. E a fórmula é,

= SE (MOD (ANO (F545), 4) = 0, E542 * (1 + 0,055) ^ ROUNDDOWN ((F545-F544) / 366,0), E542 * (1 + 0,055) ^ ROUNDDOWN (( F545-F544) / 365,0))

Esta é a melhor solução que encontrei para calcular o IC considerando o ano do LEAP.

Espero que você ache útil, no caso de se diferir apenas escrever.