10 fórmulas y funciones contables de Excel (con ejemplos)

Los contables pueden utilizar Excel para almacenar y manipular datos financieros y automatizar cálculos repetitivos. El uso de fórmulas útiles en Excel puede ayudarle a adquirir o mejorar sus conocimientos de contabilidad.

En este artículo, explicamos la diferencia entre fórmulas y funciones y enumeramos 10 fórmulas útiles para los contables.

Fórmulas frente a funciones

Microsoft Excel permite utilizar tanto fórmulas como funciones para resolver cálculos. Mediante una fórmula, un usuario puede definir un cálculo como, por ejemplo, =B2+B3+B4 para sumar los valores de las celdas B2, B3 y B4. Excel incluye una amplia gama de funciones, así como fórmulas incorporadas. Las funciones SUM y AVERAGE son ejemplos de funciones.

Relacionado: Fórmulas de Excel: Qué son y cómo usarlas

Las mejores fórmulas contables de Excel

Estas son algunas de las formas más útiles para que los contables utilicen las fórmulas y funciones de Excel:

1. Amortización de activos

Los contables pueden calcular la depreciación de activos para encontrar la cantidad de valor que un activo pierde con el tiempo. Excel ofrece cinco métodos para calcular la depreciación de activos:

DB(coste, salvamento, vida, periodo, mes): El método de depreciación de saldo decreciente puede utilizarse para reducir el valor de un activo' en un porcentaje fijo. El valor del mes es opcional.
SLN(coste, salvamento, vida): El cálculo de la depreciación lineal se puede utilizar para demostrar que el valor de un activo disminuye en un periodo de tiempo fijo a lo largo de su vida.
DDB(coste, salvamento, vida, periodo, factor): La depreciación de doble saldo decreciente es otro método de Excel que puede duplicar la tasa de depreciación lineal. El valor del factor es opcional.
VDB(coste, salvamento, vida, periodo de inicio, periodo de finalización, factor, sin cambio): El balance de disminución de la variable puede mostrar la depreciación durante un período especificado por el usuario. Los valores del factor y de la ausencia de cambio son opcionales.
SYD(costo, salvamento, vida, por): La suma de años' dígitos de depreciación también puede mostrar el valor decreciente de un artículo.

Ejemplo: Julio es un contable cuyo cliente quiere comprar un barco por 5.000 dólares. El cliente quiere saber cuánto se depreciará el barco en los primeros cinco años de su vida. Julio decide calcular la depreciación del barco. El valor de salvamento (o valor residual) de la embarcación es de 350 $, el valor después de la depreciación de la embarcación. La vida útil estimada del barco que el cliente quiere comprar es de 15 años. Julio utiliza la fórmula =DB(5000, 350, 15, 5) para encontrar que la depreciación del barco es de $399.45.

2. Tipo de interés anual

Puedes utilizar la función TASA en Excel para calcular el tipo de interés anual de un préstamo.

Fórmula: RATE(nper, pmt, pv, fv, type, guess)

Nper: Es el número de periodos de pago.
Pmt: Se trata de la cantidad que se paga por un préstamo cada año.
Pv: Esto representa el valor actual del préstamo'.
Fv: Puede incluir opcionalmente el saldo del préstamo' después de la última cuota.
Tipo: Este argumento opcional se refiere a la fecha de vencimiento del pago de un préstamo. Se puede utilizar un uno para el comienzo del período de pago y un cero para el final.
Adivinar: Este valor opcional representa la suposición del usuario de Excel para la tasa. Este valor es por defecto el 10%.

Ejemplo: La clienta de Misha&apos quiere saber cuál es el tipo de interés anual de un préstamo de 7.000 $ que paga con 350 $ al mes durante dos años. Misha utiliza la función =RATE(24, 350, -7.000)*12 para determinar que el tipo de interés anual del préstamo es del 18,16%.

3. Interés compuesto

El interés compuesto es la inclusión de los intereses en la suma principal de un préstamo. A veces se describe como interés ganado sobre interés. Puedes utilizar una fórmula de interés compuesto en Excel para encontrar el valor futuro de una inversión.

Fórmula: p * (1+r)^n

P: Se trata del importe principal del préstamo
R: Este es el tipo de interés del préstamo.
N: Este es el periodo de inversión del préstamo.

Ejemplo: Si Jackie pide un préstamo personal de 500 $ que se pagará al 8% en dos años, podría utilizar Excel para calcular su interés compuesto. Utilizando la fórmula =500*(1+8%)^2, podría encontrar que su interés compuesto sobre el préstamo sería de 583,20 $.

4. Pagos de la hipoteca

Los contables pueden utilizar la función PMT para determinar el coste del pago de una hipoteca.

Fórmula: PMT(tasa, nper, pv, fv, tipo)

Valora: Este es el tipo de interés del préstamo.
Nper: Nper es el número de pagos del préstamo que se deben realizar.
PV: Se trata del importe principal del préstamo.
FV: Este valor representa el saldo de caja después del último plazo. Este valor es opcional. Si no se incluye un valor fv, se pone por defecto a cero.
Tipo: Representa cuándo vence el pago del préstamo. Este valor también es opcional. Un valor de uno representa el comienzo del período de pago y un valor de cero representa el final del período de pago.

Ejemplo: La clienta de Marjorie'quiere comprar una casa con un préstamo hipotecario de 100.000 dólares a 30 años que deberá devolver mensualmente. El tipo de interés del préstamo es del 7%. Marjorie utiliza la fórmula =PMT(7%/12, 360, -100000) para averiguar que su cliente deberá un pago mensual de 665,30 $.

5. Valor futuro de las inversiones

Los contables pueden utilizar la función de valor futuro para encontrar el valor de una inversión en el futuro.

Fórmula: FV(tasa, nper, pmt, pv, tipo)

Tasa: Esto representa el tipo de interés por período.
Nper: Nper es el número de períodos de pago.
Pmt: Es el valor del pago realizado en cada periodo.
Pv: Este valor opcional es el valor de los pagos futuros en la actualidad.
Tipo: Este parámetro opcional muestra cuándo vence un pago. Puede utilizar el cero para el final de un periodo de pago y el uno para el principio.

Ejemplo: El cliente de Nina quiere ahorrar dinero para el pago inicial de una casa en tres años. Su cliente tiene 7.000 dólares en una cuenta de ahorros que otorga un 3% de interés al año. El cliente planea añadir 3.000 dólares a la cuenta durante el próximo año. Nina utiliza la fórmula =FV(3%/12, 36, -3000, -7000) en Excel para mostrar a su cliente que pueden ahorrar 120.520,04 dólares para el pago inicial.

Relacionado: Cómo incluir las habilidades de Excel en su currículum

6. Tipo de interés anual efectivo

Los contables pueden utilizar la fórmula EFFECT para determinar el tipo de interés anual efectivo de un préstamo.

Fórmula: EFFECT(nominal_rate, npery)

tasa_nominal: Es el tipo de interés nominal o el tipo de interés antes de la inflación.
Npery: Es el número de periodos anuales de capitalización o el número de veces por periodo de pago que se añaden los intereses a un préstamo.

Ejemplo: El cliente de un contable quiere saber el coste real de un préstamo a 30 años con intereses y comisiones. El tipo de interés nominal del préstamo es del 8%. El contable utiliza la función =EFECTO(8%, 30*12) para encontrar que el tipo de interés anual efectivo del préstamo es del 8,33%.

7. Interés de pago del préstamo fijo

Puedes calcular la parte de los intereses de un préstamo que es fija utilizando la función IPMT en Excel.

Fórmula: IPMT(tasa, per, nper, pv, fv, tipo)

Calificación: Este es el tipo de interés del préstamo.
Por: Este es el periodo para los intereses.
Nper: Nper es el número de pagos del préstamo.
PV: Esta es la cantidad principal del préstamo.
FV: Este valor representa el saldo de caja después del último plazo. Este valor es opcional.
Tipo: Representa la fecha de vencimiento del pago del préstamo. Este valor también es opcional.

Ejemplo: Una empresa pide a un contable que encuentre la parte de los intereses de un préstamo de 15.000 dólares a cinco años con un interés del 5% que la empresa paga mensualmente. El contable puede utilizar la función =IPMT(5%/12, 1, 5*12, -15.000) para encontrar que el 6% de cada pago mensual son intereses.

8. Flujos de caja

Puede utilizar la función MIRR en Excel para calcular los flujos de caja de una empresa. Son las siglas de la tasa interna de rendimiento modificada.

Función: MIRR(flujos_de_efectivo, tasa_de_financiación, tasa_de_reinversión)

Flujos de caja: El primer parámetro se refiere a las celdas que contienen flujos de caja.
Tarifa_financiera: Esta es la tasa de rendimiento (o la tasa de descuento), mostrada como un porcentaje.
Tasa_de_reinversión: Es un porcentaje que representa el tipo de interés recibido por los flujos de caja reinvertidos.

Ejemplo: Los valores del flujo de caja trimestral de una empresa en una tabla de Excel son una inversión de 5.000 dólares y unos ingresos de 6.700, 8.000 y 7.000 dólares para cada mes del trimestre. Su tasa de rendimiento requerida es del 15%. Recibe un tipo de interés del 10% sobre los flujos de caja reinvertidos. Un contable de la empresa introduce =MIRR(A13:A15, 15%, 10%) para obtener una tasa interna de rendimiento modificada del 70%.

9. Tasa interna de rendimiento

Los contables pueden utilizar la función TIR para calcular la tasa interna de rendimiento de los flujos de caja.

Función: TIR(valores, suposición)

Flujos_de_efectivo: Es una referencia a las celdas que contienen los flujos de caja de una empresa. Por ejemplo, (D6:D11).
Adivina: Este parámetro opcional es la estimación del usuario para la tasa interna de retorno.

Ejemplo: Los flujos de caja anuales de una empresa están referenciados en la columna F de una hoja de cálculo de Excel. Un contable puede utilizar la fórmula TIR(F5:F17) para encontrar la tasa interna de rendimiento anual de la empresa.

Relacionado: Cómo calcular el VAN en Excel

10. Interés acumulado

Los contables pueden utilizar la función ACCRINT para calcular los intereses acumulados durante un periodo de tiempo para un valor, como un bono.

Función: ACCRINT(id, fd, sd, rate, par, freq, basis, calc)

id: Esta es la fecha de emisión de la garantía.
fd: Esto representa la primera fecha de interés del valor.
sd: Esta es la fecha de liquidación del valor.
tasa: Esto representa el tipo de interés del valor'.
par: Este es el valor nominal del título, o su valor facial.
freq: Este es el número de pagos por año.
base: Este parámetro opcional controla el modo en que Excel cuenta los días utilizando esta función.
calc: Este parámetro opcional controla cómo calcula Excel el método.

Ejemplo: Una empresa quiere calcular los intereses que ha devengado por un bono con un valor nominal de 6.000 $. La fecha de emisión es el 05/01/17. Esta fue también la fecha en que comenzaron los intereses. Su fecha de liquidación es el 05/01/19. El tipo de interés del bono es del 6%. Los intereses del bono se pagan trimestralmente. Un contable utiliza la función =ACCRINT(05/01/17, 05/01/17, 05/01/19, 6%, 6.000, 4) para hallar los intereses devengados por la empresa sobre el bono.