Notas de Clase Docente: Julián Higuita Gómez

MODULO I FUNCIONES FINANCIERAS:

Módulo III: Excel Administrativo y Financiero para el análisis de información

TABLAS DINAMICAS

Creación de tablas dinámicas para el análisis de bases de datos.

Las tablas dinámicas es una de las herramientas más útiles que tiene Excel, ya que esta permite
procesar grandes cantidades de información almacenada en filas y columnas y de esta manera
manipular la información para construir informes acordes a nuestras necesidades.
Para crear una tabla dinámica es necesario contar con los siguientes elementos:
• Tener una base de datos.
• Que la base de datos no tenga celdas combinadas.
• Que la base de datos tenga títulos o rótulos de columna.
• Que la base de datos no tenga espacios discontinuos es decir que no tenga columnas
vacías.

Una vez se verifica esto procedemos a crear la tabla dinámica de la siguiente manera:

1. Nos ubicamos en la parte superior (la de los títulos de columnas) y en la barra de

herramientas seleccionamos la opción insertar - tabla dinámica.

Como podemos observar, Excel nos permite seleccionar la opción “Tablas dinámicas” la cual son
formatos predeterminados.

2. Una vez seleccionamos, Excel mostrara en un cuadro de dialogo, el rango que se tomara
para construir la base de datos y a su vez nos da la opción de decidir dónde queremos que
se crear la tabla si en una hoja nueva o en una hoja existente. Seleccionaremos hoja nueva
y damos aceptar.
 Notas de Clase Docente: Julián Higuita Gómez

3. Una vez allí, observaremos como Excel nos muestra en la parte derecha un panel
denominado “Campos de tabla dinámica”, en este aparecerá el nombre de cada título o
rotulo de las columnas de la base de datos los cuales denominaremos como CAMPOS.
También observaremos 4 cuadrantes en los cuales podremos ubicar los campos para crear
los informes que deseamos. Los cuadrantes son:

FILTROS: Este permite crear una especie de filtro del informe que limitara la información en su
máxima jerarquía en el informe creado.

FILAS: Son los datos/Campos que aparecerán en la parte izquierda de la tabla dinámica.

COLUMNAS: Son los datos/Campos que parecerán en la parte superior de la tabla dinámica.

VALORES: Son los Valores que se mostraran como números de manera resumida según las
condiciones de los campos en los cuadrantes FILAS/COLUMNAS. Por lo general en este cuadrante
se colocan los datos/campos con valores numéricos que pueden resumirse como, suma,
promedio, Min, Max o recuento, si el campo que se coloca allí es alfanumérico los valores se
resumirán como recuento.

En el anterior ejemplo, observaremos que en el cuadrante FILTRO hemos arrastrado el campo año
el cual esta los años 2011, 2012,2013. El cual modificara todo el informe con la información
correspondiente a esos años.
 Notas de Clase Docente: Julián Higuita Gómez

En el cuadrante COLUMNAS, Hemos asignado el campo DIA el cual se ven los días de la semana en
la parte superior de la tabla

En el Cuadrante FILAS, Hemos asignado el campo PRODUCTO, el cual observamos en la parte

izquierda de la tabla.

En el cuadrante VALORES, Hemos asignado el campo CANTIDAD, el cual agrupa por suma, el total
de unidades por producto, día y AÑO

PROPIEDADES.

Una de las principales propiedades y funciones que tiene las tablas dinámicas es que una vez
construida la tabla dinámica, Excel nos puede proporcionar un detalle en una hoja nueva que
compone el número del campo VALORES, dando doble clic en el valor.

Ejemplo si damos doble clic en la celda B5 (Fila Producto camisetas / día lunes), nos aparecerá el
siguiente detalle en una hoja nueva.

.
 Notas de Clase Docente: Julián Higuita Gómez

Campos Calculados
Los campos calculados son funciones básicas (Suma, resta, Multiplicación y división) que permite
realizar Excel en CAMPOS únicamente numéricos, también dichas operaciones pueden darse entre
varios CAMPOS.
Para generar un campo calculado realizaremos los siguientes pasos:
Ejemplo, queremos saber el precio unitario de los productos, ya que la base de datos contiene
campos de CANTIDAD y VALOR VENTA.
1. Nos ubicamos en la tabla dinámica y esto hará que se active un “Menú especial” en la
parte de la barra de herramientas de color Rosado, y seleccionamos la opción Campos,
Elementos y Conjuntos/CAMPO CALCULADO.

2. Una vez hecho esto nos aparecerá un cuadro que nos indicara el NOMBRE del CAMPO
CALCULADO, que en nuestro ejercicio será “Precio Unitario”, en el campo FORMULA
colocaremos la función que nos dé como resultado el precio unitario que resulta de dividir
el campo VALOR VENTA con el campo CANTIDAD, estos campos se seleccionan dando
doble clic en la opción CAMPOS, o simplemente seleccionando el campo y dando en el
botón INSERTAR CAMPO y por ultimo damos aceptar.
 Notas de Clase Docente: Julián Higuita Gómez

3. Como resultado nos aparecerá en el panel derecho de la tabla dinámica un nuevo campo
con el nombre que hemos dado “Precio Unitario” en el cuadrante de Valores.
 Notas de Clase Docente: Julián Higuita Gómez

Tablas Dinámicas: herramientas Mostrar valores como % Participación de…

Excel también permite en las tablas dinámicas hacer operaciones para analizar % de
participación, para ello realizaremos lo siguiente:

Para mostrar valores en una tabla dinámica por % de Participación se debe hacer lo
siguiente:

1. Una vez se tenga la tabla dinámica, debe situarse en la parte de VALORES

(Recuerde que en el campo de valores permite hacer operaciones como CONTAR
para campos alfanuméricos y numéricos en este caso debe ser siempre un campo
Numérico).
2. Una vez situados allí, damos clic derecho y seleccionamos la opción MOSTRAR
VALORES COMO y la opción de % que se desee realizar, en este caso
seleccionaremos % del total de Columnas el cual nos mostrara la participación que
tuvo cada producto en cada año.
 Notas de Clase Docente: Julián Higuita Gómez

Formato condicional

En tablas dinámicas también podemos utilizar los formatos condicionales tales como,
barras de datos, semáforos, etc.

Para ello retomaremos el ejemplo anterior.

Supongamos que queremos implementar unas barras de participación que nos indique de
manera más grafica cuales productos tienen mayor participación en cada año.

Para ello haremos lo siguiente:

1. Con la tabla dinámica anterior, nos vamos por la opción, de la barra de

herramientas INICIO – Formato Condicional - Administrador de reglas.

2. Luego en el cuadro, presionamos la opción NUEVA REGLA y nos aparece el

siguiente cuadro, donde seleccionaremos la opción 3 en el campo donde
aplicaremos la regla y en el estilo de formato podremos escoger entre, iconos,
 Notas de Clase Docente: Julián Higuita Gómez

semáforos y barra de datos, que para el ejercicio seleccionaremos este último y

daremos aceptar.

3. Luego nos devolverá al cuadro de administrador de reglas y daremos aceptar.

 Notas de Clase Docente: Julián Higuita Gómez

Segmentación de datos y Conexiones de Segmentación de Datos.

Esta opción permite manejar de manera simultánea varias tablas dinámicas con un filtro
que unifique un criterio común, por ejemplo varias tablas o gráficos dinámicos que tenga
en su estructura el campo MES, entonces al aplicar el filtro de la segmentación de datos
este hará que la o las tablas dinámicas cambien según el criterio dado.

Para crear un cuadro de segmentación de datos se debe hacer lo siguiente:

1. Tener al menos una o varias tablas dinámicas en el LIBRO, puede ser

independientemente la hoja distinta.
2. Nos ubicamos en la tabla de dinámica y seleccionamos en el menú “Especial” ( el
que está en la cinta de opciones de color rosado) o menú de tabla dinámica y allí
seleccionamos la opción “OPCIONES” la opción INSERTAR SEGMENTACION DE
DATOS.

3. Una vez allí escogemos el campo que será el filtro en común con las tablas
dinámicas.
4. Para habilitar la conexión de varias tablas dinámicas debemos seleccionar el
cuadro de segmentación de tablas dinámicas, luego en el Menú “especial” ( de
Color Morado en la cinta de opciones), seleccionamos la opción conexiones de
tabla dinámica.
 Notas de Clase Docente: Julián Higuita Gómez

5. Una vez allí nos aparecerá un cuadro que nos permitirá seleccionar las tablas
dinámicas que estén en el libro a las cuales queremos conectar al cuadro de
segmentación de datos y damos aceptar.

Gráficos Dinámicos
Excel permite realizar gráficos dinámicos e interactivos que van ligados a las tablas dinámicas
creadas, esto resulta muy útil cuando se quiere representar gráficamente un informe.

Para crear gráficos dinámicos puede hacerse de dos maneras:

a. La primera consiste en una vez se tiene la base de datos, nos ubicamos en la opción
insertar – Grafico Dinámico – grafico dinámico.
 Notas de Clase Docente: Julián Higuita Gómez

Al dar clic nos aparecerá un submenú similar al de cuando se crea la tabla dinámica (véase en
cómo crear una tabla dinámica) y a la vez que agregamos campos, Excel construye una tabla
dinámica y un gráfico que por defecto es un gráfico en barras. El tipo de grafico puede cambiarse
de la manera tradicional por la opción de cambiar tipo de grafico (véase en la lección gráficos).

b. La otra opción consiste en una vez se tiene la tabla dinámica o el informe, nos ubicaremos
en la tabla dinámica y nos iremos por la opción insertar – GRAFICOS RECOMENDADOS y
nos aparecerá el siguiente cuadro que nos permitirá insertar el grafico que deseamos para
representar la información de la tabla dinámica. Para este ejercicio seleccionaremos
columna Agrupada.
 Notas de Clase Docente: Julián Higuita Gómez

Por ultimo daremos aceptar y nos aparecerá el siguiente grafico que resumen las ventas por año
de cada uno de los vendedores.

En lo particular, recomendamos la segunda opción ya que este permite desde un informe o tabla
ya creada insertar el grafico que deseamos y escoger de una vez su tipo de gráfico, en cambio la
opción a. Sugiere realizar la creación desde el origen de base de datos el cual genera un gráfico
por defecto según la parametrización de Excel.

Nota: Los gráficos dinámicos pueden diseñarse, agregar formatos y otros elementos de acuerdo a
lo visto en la lección de gráficos.

ACTUALIZAR UNA TABLA O GRAFICO DINAMICO.

Si queremos actualizar una tabla dinámica o un gráfico dinámico al cual le hemos agregado o
modificado información en la base de datos debemos realizar lo siguiente.

• Una primera opción es cuando solo modificamos información y su rango de datos no

crece, bastará con irnos por la opción de la barra de herramientas. DATOS – Actualizar
todo – Actualizar Todo

• La segunda opción es si la base de datos se le adiciona información nueva y queremos

incluirla en nuestra tabla, debemos hacer lo siguiente:
 Notas de Clase Docente: Julián Higuita Gómez

1. Nos ubicaremos en la tabla dinámica y en el “menú especial” de la tabla dinámica

seleccionaremos en la barra de herramientas la opción ANALIZAR, Cambiar Origen de
Datos.

2. Una vez allí nos aparece un cuadro donde nos lleva a la base de datos y nos muestra el
rango actual que está tomando la tabla dinámica. Una vez allí lo que hacemos es ampliar
el rango seleccionando nuevamente y damos aceptar.
 Notas de Clase Docente: Julián Higuita Gómez

DISEÑO DE INFORMES.

Cuando realizamos una tabla dinámica, y adicionamos más de dos campos en el cuadrante FILAS,
Excel trae un formato por defecto que es el siguiente.

Como podrán observar en el cuadrante FILAS se colocó el CAMPO AÑO Y PRODUCTO, por lo tanto
Excel agrupa estos dos campos en una sola columna mostrando el año y los productos de ese año
y luego pasando al otro año.

Excel tiene varios tipos de informes que sugiere una mejor presentación de los campos ubicados
en el cuadrante FILAS. Por ejemplo:

Si queremos que Excel separe el campo AÑO y PRODUCTO en columnas independientes debemos
realizar lo siguiente:

1. Nos ubicaremos en la tabla dinámica y en el “menú especial” de la tabla dinámica

seleccionaremos en la barra de herramientas la opción DISEÑO - Diseño de informe –
Mostrar en formato tabular.
 Notas de Clase Docente: Julián Higuita Gómez

2. Al dar clic acá Excel nos separa en columnas diferentes los CAMPOS AÑO y PRODUCTO.
Quedando de la siguiente manera, también nos damos cuenta en la imagen anterior que
Excel cuenta con 3 tipos de diseño de informes (los 3 Primeros).

3. Por ultimo si queremos rellenar los espacios en Blanco que se observan en la fila año lo
podemos hacer nuevamente por el anterior menú del punto 1 y seleccionaremos la opción
“Repetir todas las Etiquetas de los elementos” , lo que hará que el informe nos quede
así:
 Notas de Clase Docente: Julián Higuita Gómez

Agrupación de datos por rangos de fechas

Excel proporciona ciertas funciones adicionales a las anteriores vista que permite
simplificar el trabajo incluso cuando se requiere consolidar información de forma semanal,
quincenal, mes, trimestral, anual, etc. Supongamos que necesitamos realizar un informe
semanal de las unidades de cierto producto para ello debemos recurrir a la base de datos
y verificar que contemos con un campo dentro de la base de datos que contenga el campo
en formato de fecha.

Una vez verificado procedemos a insertar la tabla dinámica con los respectivos campos.

Los campos dentro la tabla dinámica serían los siguientes.

Obsérvese que el campo fecha

está en el campo Filas el cual
nos permitirá hacer la
agrupación deseada, la cual
para efectos de este ejercicio es
de manera quincenal.
 Notas de Clase Docente: Julián Higuita Gómez

Una vez hecho esto nos ubicamos en los datos de la fecha y en el menú especial de tabla
dinámica ubicado en la cinta de opciones marcamos la pestaña ANALIZAR – la opción
AGRUPAR SELECCIÓN

Una vez presionamos ahí en el cuadro que nos aparece llamado agrupar observaremos
opciones que nos permite seleccionar la forma que deseamos agrupar incluso dándonos la
opción de seleccionar la fecha de inicio y de terminación, para efectos del ejercicio
seleccionaremos la opción días y escribiremos en la opción de número de días 7 para
hacer la agrupación semanal y presionamos aceptar.

NOTA: esta opción permite hacer varias

agrupaciones como días y meses, trimestre y años
de forma simultánea según las necesidades del
usuario. Un ejemplo seria si queremos ver el
número de unidades por año y trimestre
respectivamente por lo cual deberíamos marcar
tanto la opción de trimestre como de años.
 Notas de Clase Docente: Julián Higuita Gómez

Una vez hecho esto el resultado es el siguiente en el cual observaremos como Excel hace
la respectiva agrupación semanal indicándonos la fecha de inicio y la fecha final de cada
semana agrupada en 7 días.

Mostrar páginas de Filtro de Informes

Ciertamente esta opción resulta ser demasiado útil cuando se pretende tener un informe
predeterminado en Excel en una tabla dinámica y que este se encuentre segmentando por
un filtro ya sea una sucursal, país, ciudad, mes, etc y que este informe se cree en una hoja
totalmente independiente.

Para ejemplificar tomaremos el ejercicio anterior en el cual queremos ver las ventas
semanales por producto pero que esta se encuentre segmentadas por el campo
OPERARIO es decir el resultado sería que por cada hoja independiente en el libro de Excel
tendremos la producción semanal por producto de cada operario.

Para ello debemos tener indispensablemente dentro de los campos de la tabla dinámica el
campo FILTRO con el campo que queremos realizar nuestros informes, que en este caso es
operario.
 Notas de Clase Docente: Julián Higuita Gómez

Obsérvese que en el campo filtro tenemos el campo operario del cual se desprende varios
operarios que sería cada uno de nuestros informes por cada hoja. Una vez hecho esto nos
ubicamos en el menú especial de la tabla dinámica de la cinta de opciones en la pestaña
ANALIZAR – Tabla Dinámica – Opciones y en opciones presionamos la flecha para que
desplegué un menú donde seleccionaremos la opción Mostrar páginas de Filtros de
Informes...

Una vez marcamos esta opciones nos aparece un cuadro con la opción de seleccionar los
campos que están en el campo FILTRO de la tabla dinámica para realizar nuestro informe
deseado. Para este caso marcaremos la opción OPERARIO

Una vez presionamos aceptar observaremos como Excel nos crea una hoja independiente
incluso nombrada con el respectivo nombre de cada operario y como en cada hoja Excel
nos filtra por cada operario en la tabla dinámica mostrando solo los valores
correspondientes al OPERARIO.
 Notas de Clase Docente: Julián Higuita Gómez

GRAFICO DE LINEAS DE TENDENCIA

Los gráficos de líneas de tendencia permiten analizar de manera gráfica el

comportamiento de una serie de datos predecir el comportamiento de estas a futuro.

Para crear este tipo de grafico tenemos los siguientes datos:

1. Seleccionamos los datos desde la celda A3 hasta G5 (TODA LA TABLA) e insertamos

el grafico de línea.

2. Una vez tenemos el grafico seleccionamos una de las líneas, damos clic y
seleccionamos la opción AGREGAR LINEA DE TENDENCIA.

3. En este sub Menú debemos seleccionamos (Para este caso) la opción Lineal,
Marcamos la opción Presentar Ecuación en el gráfico y la opción Presentar R
Cuadrado en el gráfico, esta última opción nos permite analizar que si este valor se
acerca más a 1 es porque la ecuación que nos aparece está más ajustada a los
datos que están en el gráfico y será más confiable para hacer la proyección.
Recuerden dar formato a la línea de tendencia como grosor y color.
 Notas de Clase Docente: Julián Higuita Gómez

En este caso vemos que la ecuación y el R cuadrado es un dato viable para realizar la
proyección de ventas para los próximos periodos. Para ello solamente debemos utilizar la
ecuación y reemplazar la X por el periodo que se va proyectar en este caso tenemos 6
datos para el periodo siguiente la x valdría 7 y así sucesivamente los siguientes periodos.

FUNCIONES FINANCIERAS FUNDAMENTALES

Tipos o Clases de Interés

INTERÉS SIMPLE:

Es un tipo de interés que siempre se calcula sobre el capital inicial sin la capitalización de
los intereses, es decir que los intereses generados no se incluyen en el cálculo futuro de
los intereses, permaneciendo el capital fijo.

SIMPLE VENCIDO

SIMPLE ANTICIPADO

INTERÉS COMPUESTO:

Es aquel interés que se cobra por un crédito y al ser liquidado se acumula al capital
(Capitalización el interés), por lo que en la siguiente liquidación de intereses, el interés
anterior forma parte del capital o base del cálculo del nuevo interés.

COMPUESTO VENCIDO

COMPUESTO ANTICIPADO
 Notas de Clase Docente: Julián Higuita Gómez

TASAS DE INTERÉS

TASA NOMINAL: Es la tasa de interés que generalmente se refiere a una tasa anual y que
es fraccionada según el número de capitalizaciones.

TASA EFECTIVA: La tasa efectiva es aquella a la que efectivamente está colocado el

capital. La capitalización del interés en determinado número de veces por año, da lugar a
una tasa efectiva mayor que la nominal.

CONSIDERACIONES IMPORTANTES

La siguiente grafica representa lo que es el flujo del dinero en el tiempo, es decir lo que
sucede cuando hacemos un préstamo, hacemos una inversión, pagamos un crédito, o
queremos recibir un rendimiento futuro de una inversión.

Flujo del Dinero en el Tiempo

VA

Pago/Cuota
VF

Una Flecha hacia arriba indica entrada de dinero, como Pago de intereses o rendimientos,
pago de inversión o total después de un ahorro o cuando hacemos un préstamo, de modo
contrario las flechas hacia abajo indicaran una salida de dinero o cuando realizamos un
pago total o parcial.

Adicionalmente a partir de este momento, en los ejercicios tendremos en cuenta la

siguiente nomenclatura o abreviación de palabras que hacen referencia a:
 Notas de Clase Docente: Julián Higuita Gómez

DESCRIPCION NOMENCLATURA
Valor Futuro VF
Valor Actual VA
Tasa de Interes I
Numero de Periodos NPER
Pago o Cuota PAGO

Debido a nuestro sistema financiero actual, nos centraremos en el tipo de interés

compuesto, para las cuales Excel nos provee una serie de funciones para realizar los
respectivos Cálculos.

NOTA: En las siguientes funciones siempre utilizaremos un signo menos (-) antes de la
función {=-Función ()}, para que el resultado sea positivo, menos en la función TASA, en la
cual uno de los valores debe ir Con un signo menos.
Notas de Clase Docente: Julián Higuita Gómez
 Notas de Clase Docente: Julián Higuita Gómez

Ejemplo

Juan Decide ir de viaje con su familia a Panamá el cual le cuesta $ 8.000.000, el viaje está
programado para dentro de 18 Meses y una Cooperativa le ofrece un Producto financiero
el cual le genera un 1.1% Mensual, Cuanto deberá depositar hoy Juan para obtener el
dinero al final de los 18 meses.

Para este ejercicio nos están preguntando de cuánto debe ser el valor a invertir es decir el
Valor actual. Para esto Utilizaremos la función VA de la siguiente forma:

1. Ubicamos los valores.

Abreviatura Dato Descripcion

VF $ 8.000.000,0 Dato Dado
Tasa 1,10% Dato Dado
Nper 18 Dato Dado

2. Aplicamos la Formula VA que solicitan los siguientes campos que nos piden o que
podemos asignar por medio del asistente de fórmulas así:

Recuerden utilizar el menos (-) antes de la función para que el resultado sea positivo. El
valor devuelto de la función es $ 6.570.058 por lo quiere decir que si Juan invierte ese
dinero en la cooperativa en 18 meses tendrá el dinero requerido para su viaje.
 Notas de Clase Docente: Julián Higuita Gómez

Ejercicio 2.

María hace un crédito por $30.000.000 para comprar un Carro a una tasa del 1.2% en el
Banco XXX, María espera pagar el crédito en un plazo no mayor a 60 meses.

De cuanto seria la cuota mensual?

1. Asignamos los datos.

Abreviatura Dato Descripcion

VA $ 30.000.000 Dato Dado
Tasa 1,2% Dato Dado
Nper 60 Dato Dado
2. Como nos pregunta de cuanto es la Cuota mensual, debemos utilizar la función
PAGO. Recuerden usar la función precedida por un signo menos para que el
resultado sea positivo.
Aplicamos la formula y nos apoyamos en el asistente de funciones para asignar los
campos requeridos.
 Notas de Clase Docente: Julián Higuita Gómez

Tablas de Amortización por Cuota Fija.

Las tablas de amortización nos mostrará el plan de pagos que se tiene por ejemplo de un
crédito, esta nos permitirá saber en determinado momento cual es el pago tanto de
intereses como de Capital. Para ello utilizaremos las funciones de tabla de amortización
que son PAGOPRIN y PAGOINT.

Ejemplo.

Volviendo al ejemplo de María que va adquirir un crédito para la compra de un vehículo.

Construiremos el respectivo plan de pagos.

1. Asignaremos los datos.

2. Para el cálculo del respectivo plan de pagos es indispensable tener la cuota mensual
ya que es un valor fijo que se pagará y de ahí ira una parte para pagar intereses y otra
parte pagar Capital. La función para esta es PAGO, que al aplicarla el resultado es
$ 704.284 Mensual.
3. Luego asignaremos los siguientes rótulos en las columnas de la siguiente manera:

En la columna Periodo Se coloraran los periodos desde el 0 porque es el momento donde

me prestan y aún no he comenzado a pagar la primera cuota, y este ira hasta el número
de periodos total del ejercicio, en este caso es 60.
 Notas de Clase Docente: Julián Higuita Gómez

4. Luego Utilizaremos las siguientes funciones a partir del periodo 1, para completar los
campos de cada columna.
a) En el periodo 1. En la columna Cuota ira siempre el valor de hallado con la función
pago. Y siempre será el mismo por ser un sistema de amortización por cuota Fija.

b) En el Campo INTERESES Utilizaremos la Función PAGOINT, (utilizando el (-) antes de la

función. Los campos que solicitan son:

Tasa: La cual es de 1.2% que debemos de fijar pues este no va variar en el tiempo.
Periodo: El periodo en este caso es 1, ya que es el momento donde vamos a pagar, este es
indiferente a NPeriodos. Como la formula la vamos arrastrar para los demás periodos este
no va fijo.
NPER. Corresponde al total de cuotas o periodos en los cuales se va pagar el crédito, en
este caso serían 60 y se fijan dentro de la formula.
VA. Equivale al valor o monto inicial que serían $ 30.000.000
VF. N/A
TIPO. Siempre 0 por ser al final del periodo o porque la tasa es Mensual vencida.
El valor devuelto en este caso es de $360.000.

c) En el campo ABONO A CAPITAL utilizaremos la función PAGOPRIN( utilizando el (-)

antes de la función. Los campos que solicitan son:

Tasa: La cual es de 1.2% que debemos de fijar pues este no va variar en el tiempo.
Periodo: El periodo en este caso es 1, ya que es el momento donde vamos a pagar, este es
indiferente a NPeriodos. Como la formula la vamos arrastrar para los demás periodos este
no va fijo.
NPER. Corresponde al total de cuotas o periodos en los cuales se va pagar el crédito, en
este caso serían 60 y se fijan dentro de la formula.
VA. Equivale al valor o monto inicial que serían $ 30.000.000
VF. N/A
TIPO. Siempre 0 por ser al final del periodo o porque la tasa es Mensual vencida.
El valor devuelto en este caso es de $ 344.284.

NOTA: Otra manera de hallar el abono a capital es simplemente restando el Valor de la

Cuota menos el valor de INTERESES, ya que como la cuota siempre es fija, por diferencia
puedo hallar el valor del ABONO a CAPITAL o de los INTERESES, usando PAGOPRIN o
PAGOINT.
 Notas de Clase Docente: Julián Higuita Gómez

d) En el campo SALDO, haremos la resta entre el Saldo del periodo anterior (periodo 0)
menos el abono a capital del periodo actual (Periodo 1). Es decir los $ 30.000.000 - $
344.284, para un valor de $ 29.655.716 como saldo del periodo 1.

5. Por Ultimo Basta con seleccionar las formulas desde la cuota hasta el saldo y luego
arrastrar hasta el final (periodo 60). El último periodo SIEMPRE el SALDO DEBE SER
IGUAL A CERO.
 Notas de Clase Docente: Julián Higuita Gómez

Ya una vez calculado la tabla basta con hacer la sumatoria de toda la fila de intereses para
calcular cuánto se pagará demás por el vehículo, que según este ejercicio los intereses
demás pagados suman $ 12.257.062.

Función Buscar Objetivo

Buscar Objetivo es una de las herramientas más potentes de Excel, la cual permite hallar
un valor deseado por medio de la combinación de otras como resultado de función u
operación algebraica.

Para ilustrar su funcionamiento, pondremos el siguiente caso:

Tomemos como ejemplo el caso de Maria con el vehículo en el cual la entidad financiera le
dice que su cuota mensual es de $ 704.284 por 60 meses. Maria siente que se quedara un
poco apretada en cuanto su gastos mensuales y según sus finanzas personales ve viable
solo pagar $ 620.000 mensual, la financiera le dice que puede extender su plazo para que
su cuota mensual disminuya hasta el valor que ella espera.

Como se hace:

1. Colocamos los datos iniciales y la formula PAGO de tal manera que el resultado sea
el que se muestra a continuación.

2. El ejercicio consiste en cambiar el plazo (NPER) para que el valor de la cuota

disminuya a $ 620.000. Entonces activamos la herramienta BUSCAR OBJETIVO en la
siguiente dirección:
En la Barra de herramientas seleccionamos DATOS- Análisis de Hipótesis – Buscar
Objetivo.
 Notas de Clase Docente: Julián Higuita Gómez

3. En el cuadro que nos Aparece 3 Campos:

Definir La Celda: En este campo SIEMPRE ira la celda que tiene la formula, en este caso la
celda que tiene la función Pago es la C6.

Con el Valor: Acá debemos digitar el dato que esperamos, en este caso esperamos que la
formula arroje un valor de $ 620.000 por lo tanto este es el valor que digitaremos.

Cambiando la Celda: En este campo seleccionamos el dato que es nuestra variable o el

dato que queremos saber, en este caso es cuánto debe ser el plazo o NPER que está en la
Celda C5.

NOTA. Esta Celda no debe estar vacía, para nuestro ejercicio siempre colocaremos 1.

El Valor que arroja es que si queremos pagar una cuota de $ 620.000 por los $
30.000.0000 y a la Misma tasa de 1.2% mensual, el periodo es de 72.85 Meses. Casi 73
Meses.
 Notas de Clase Docente: Julián Higuita Gómez

Tabla de Amortización con cuota fija y abonos extras.

Este tipo de simulación considera que dentro del plan de pagos la persona hará abonos
extras con el fin de bajar su cuota mensual, Por ejemplo con el caso de Maria ella espera
dar abonos adicionales cada 6 Meses con el fin que su cuota disminuya a $ 650.000
considerando el mismo plazo de los 60 Meses, De cuánto debe ser dicha Cuota?

1. Para resolver este caso debemos utilizar la tabla de amortización anterior y nos
apoyaremos con la herramienta BUSCAR OBJETIVO. Entre la columna Abono
capital y Saldo Insertaremos una columna la cual nombraremos Abono extra, el
cual se visualizará así:

Nota: los abonos extra siempre son abonos que se hacen directamente al capital y no a
pago intereses.

2. Una vez los encabezados usamos las mismas formulas de la tabla de amortización
anterior con la diferencia que en la columna SALDO, le restaremos también el
abono extra (SALDO = SALDO PERIODO ANTERIOR – ABONO CAPITAL – ABONO
EXTRA).
 Notas de Clase Docente: Julián Higuita Gómez

3. Ahora vincularemos cada 6 periodos (6, 12, 18, 24, etc.) el valor de la celda abono
Extra (C7), luego calculemos la cuota extra, para ello utilizaremos la herramienta
buscar objetivo, recordemos que en la CELDA Abono Extra (C7) coloremos 1, para
que BUSCAR OBJETIVO funcione, una vez esto colocaremos lo siguiente en los
campos requeridos.

Definir La Celda: En este campo SIEMPRE ira la celda que tiene la formula o Celda
Objetivo, en este caso nuestro Objetivo va ser que la celda SALDO del periodo 60 (F69) sea
igual a 0, debido a que en una tabla de amortización la última celda (SALDO FINAL) debe
ser 0.

Con el Valor: Acá debemos digitar el dato que esperamos, en este caso esperamos el
último valor de la tabla de amortización sea 0.

Cambiando la Celda: En este campo seleccionamos el dato que es nuestra variable o el

dato que queremos saber, en este caso es cuánto debe ser la cuota extra que está en la
celda C6

El resultado arrojado es que cada 6 meses Maria debe hacer un abono adicional de
$ 325.706.

Nota: Recuerden que todos los datos en la tabla de amortización debe estar vinculados
para que Excel los vincule en sus datos al hacer el cálculo con la herramienta buscar
objetivo.
 Notas de Clase Docente: Julián Higuita Gómez

Administrador de Escenarios.
Esta herramienta de Excel permite hacer varios planteamientos dentro de una misma
función y con este hallar varios escenarios simplemente modificando uno o varios
elementos que lo conforman.

Ejemplo,

Maria va donde un asesor financiero a cotizar cuanto pagará mensualmente un crédito de

$ 30.000.000 para comprar un carro el asesor le plantea las siguientes opciones.

CRITERIOS Escenario 1 Escenario 2 Escenario 3

Tasa 0.89% Mensual 1.01% Mensual 1.45% Mensual
Nper 24 Meses 48 Meses 60 Meses
Con la ayuda del Administrador de escenarios podremos calcular estos escenarios sin la
necesidad de hacerlo 1 a 1.

1. Planteamos el problema para el primer escenario. Ayudándonos de las funciones

en Excel que en este caso utilizaríamos la función PAGO.

2. Activamos el administrador de escenarios en la siguiente ruta.

• En la Barra de herramientas seleccionamos DATOS- Análisis de Hipótesis –
Administrador de Escenarios.
 Notas de Clase Docente: Julián Higuita Gómez

3. En el primer Pantallazo del administrador de escenarios Presionamos Agregar en el

cual observaremos los siguientes Campos.

Nombre del Escenario: en este Campo Colocaremos el escenario Inicial que es según
nuestra tabla el ESCENARIO 1.

Celdas Cambiantes: Seleccionamos las celdas que en nuestro planteamiento inicial

cambiaran, que sería la tasa de interés (C4) y el Plazo (C5).

Al presionar Aceptar nos aparece el siguiente Cuadro donde ingresaremos la

información de cada escenario, para el caso del ESCENARIO 1 el valor de C5 es los
periodos que son 24 y la celda C4 que es la tasa agregamos 0.89%

Si queremos agregar otro escenario presionamos AGREGAR y de esta manera

agregamos los otros dos escenarios iniciales.
 Notas de Clase Docente: Julián Higuita Gómez

4. Una vez agregado todos los 3 escenarios Damos Aceptar y esto nos llevará al
pantallazo inicial donde nos mostrará todos los escenarios ingresados.

5. Para finalizar y mostrar todos los escenarios en un informe resumen, presionamos

la opción RESUMEN y en nos aparece un cuadro RESUMEN DEL ESCENARIO al cual
seleccionaremos la opción RESUMEN y en la opción de CELDAS de RESULTADO,
seleccionaremos siempre la celda que tiene la formula (PAGO Celda C6) resultante
de cambiar las variables del escenario (TASA Y NPER), y damos clic en aceptar.

6. Una vez hecho esto nos aparecerá una hoja nueva llamada “Resumen del
Escenario” que resumen todos los 3 escenarios y su resultado Así:
 Notas de Clase Docente: Julián Higuita Gómez

7. Por Ultimo Cambiamos los nombres de la Columna celdas cambiante y resultado

por Periodo, Tasa y Pago, de tal manera damos mejor presentación a nuestro
informe de escenarios así:

TABLA DE DATOS

Esta herramienta permite calcular varios resultados mediante una tabla de datos.
Ejemplo si quisiéramos una tabla que nos muestre las posibles cuotas que se pagaría
por el crédito de los $ 30.000.000 variando la tasa y el número de periodos sin tener
que repetir una y otra vez la función pago, esta herramienta nos permite hacer este
cálculo así:
Supongamos que Maria quiere ver en un cuadro resumen un comportamiento de la
cuota a diferentes tasas de intereses según el tiempo así:
 Notas de Clase Docente: Julián Higuita Gómez

1. El objetivo es llenar la tabla teniendo en cuenta cada uno de estos criterios, para
esto utilizaremos la herramienta Tabla de Datos, pero primero debemos plantear
el ejercicio usando las funciones financieras, que en este caso es PAGO.

Observen que se ha utilizado los datos iniciales de la Fila (Nper = 24) y Columna (Tasa =
0.8%) y en la celda C6 hemos realizado la función PAGO para hallar la cuota.

2. Ahora en la Celda C9 que esta de Color Naranja referenciaremos la celda que

tiene la función Pago tal cual se muestra en la imagen.
3. Luego Seleccionamos la tabla desde la celda C9 hasta L18 y activar la herramienta
Tabla de datos así.
• En la Barra de herramientas seleccionamos DATOS- Análisis de Hipótesis – tabla
de Datos.
 Notas de Clase Docente: Julián Higuita Gómez

Después de activar la opción TABLA DE DATOS, nos debe aparecer el siguiente

pantallazo:

En el cuadro de tabla de datos nos aparecen los siguientes campos:

• Celda de Entrada (Fila): en este campo se selecciona la CELDA que está dentro del
cuadro 1 a la cual hace referencia la tabla. Como pueden observar en la FILA está
ubicado los meses o periodos, esto quiere decir que este primer campo de la
herramienta hace referencia a NPER ósea la Celda C5

• Celda de Columna (Columna): en este campo se selecciona la CELDA que está

dentro del cuadro 1 a la cual hace referencia la tabla. Como pueden observar en
las COLUMNAS está ubicado las tasas de Interés, esto quiere decir que este
Segundo campo de la herramienta hace referencia a TASA ósea la Celda C4.
 Notas de Clase Docente: Julián Higuita Gómez

Presionamos aceptar. Y nos aparece el siguiente Cuadro, Recuerden dar formato a los
datos de la tabla.

4. Por Último y más por presentación Agregaremos un Mapa de calor por medio de
los formatos condicionales así:
• Seleccionamos de la tabla, SOLO LOS VALORES CALCULADOS (Desde Celda D10
hasta la L18).
• Luego en la Opción de la barra de herramientas presionamos INICIO – FORMATO
CONDICIONAL – ESCALAS DE CALOR y seleccionamos la segunda opción asi:

De Esta Manera podemos ver que los valores más rojos son las cuotas más altas y las
verdes las más Bajas de tal manera que su color varía dependiendo que tan alta o baja sea
la cuota.