Na construção de modelos financeiros em Excel, um dos desafios mais comuns é o cálculo das depreciações de investimentos em ativos fixos tangíveis. Não me refiro ao cálculo das depreciações do investimento inicial, que são normalmente simples de calcular, mas ao cálculo das depreciações de todo o investimento, incluindo o de substituição. Com as fórmulas OFFSET ou INDEX esta tarefa pode ser muito facilitada.

Para ilustrar o problema, suponha que um determinado projeto requer um investimento inicial 100.000 euros. Além disso, será necessário realizar diversos investimentos de substituição nos anos seguintes conforme demonstra a tabela abaixo: 

Suponha ainda que a vida útil dos ativos é de 4 anos, o que corresponde a uma taxa fixa de depreciação anual de 25%. Se o objetivo for analisar este projeto a 15 anos, como calcular as depreciações do investimento inicial e de substituição?

A solução mais usual não é de todo a mais eficaz

A solução mais comum consiste em criar duas tabelas de depreciação para cada um destes dois tipos de investimento.

No Excel, utilizando uma multiplicação da taxa de depreciação pelo valor do investimento inicial para o primeiro ano e arrastando a fórmula para a direita até preencher os quatro anos de vida útil teríamos as depreciações para cada um dos períodos.

Mas, como lidar com as depreciações do investimento de substituição? E tendo em conta que os montantes de investimento são diferentes a cada ano, como conceber uma única fórmula que resolva o problema sem correr o risco de considerar colunas anteriores ao ano inicial (que devolvem o erro #REF!)?

A solução OFFSET

Esta fórmula, que no Excel em português, tem o lindo nome de DESLOCAMENTO, quando inserida dentro de uma fórmula SUM (SOMA em português), permite somar os valores de um intervalo de células dinâmico.

No nosso caso, pretendemos somar os montantes de investimento dos últimos quatro anos para evitarmos calcular depreciações de ativos já integralmente depreciados. E simultaneamente procuramos uma fórmula que possa ser aplicada do ano 1 ao ano 15.

Assim, a solução seria multiplicar a taxa de depreciação por OFFSET cujo ponto de partida esteja na linha do investimento inicial referente ao ano imediatamente anterior e para uma área correspondente a duas linhas de altura (para incluir também o investimento de substituição) e para as quatro últimas colunas.

Algo do género:

Esta solução permite calcular a totalidade das depreciações com uma única fórmula que pode ser a mesma para todos os 15 anos.

Tem ainda a vantagem de permitir a alteração dos parâmetros caso os pressupostos do modelo se alterem.

A solução INDEX

A fórmula INDEX (ÍNDICE em português) é tipicamente usada para devolver um valor dentro de um intervalo de células, posicionado numa determinada linha e coluna. Mas quando usada dentro de uma fórmula SUM permite também a utilização de intervalos de células como argumento.

É precisamente o que procuramos para este tipo de situações.

Assim, vejamos como somar o intervalo definido por INDEX para as duas linhas onde constam os montantes de investimento inicial e de substituição para os quatro anos imediatamente anteriores ao ano corrente:

A fórmula INDEX tem ainda a vantagem de ser mais rápida enquanto não é volátil (como a OFFSET), o que facilita a hierarquia interna de precedência de cálculos do Excel. Isto pode ser importante para o desempenho de modelos de grande dimensão.

No ficheiro em anexo (ver abaixo) poderá analisar com mais detalhe o funcionamento destas duas soluções de cálculo.