Antes de tomar un préstamo, sería bo calcular todos os pagamentos nel. Isto salvará ao prestatario no futuro de varios problemas e desilusións inesperados cando resulta que o pago excesivo é demasiado grande. As ferramentas de Excel poden axudar neste cálculo. Imos descubrir como calcular os pagamentos de préstamos de renda vitalicia neste programa.
Cálculo do pagamento
Primeiro de todo, debo dicir que hai dous tipos de pagos de crédito:
- Diferenciado;
- Anualidade.
Cun esquema diferenciado, o cliente realiza unha cota mensual igual de pagamentos ao banco sobre o corpo do préstamo máis os pagamentos de xuros. A cantidade de pagamentos de xuros cada mes diminúe a medida que diminúe o corpo do préstamo do que se calculan. Así, tamén se reduce o pagamento mensual total.
Un esquema de renda anual utiliza un enfoque lixeiramente diferente. O cliente efectúa mensualmente o mesmo importe do pagamento total, que consiste en pagamentos do corpo do préstamo e pagamentos de xuros. Inicialmente, os pagamentos de xuros rexístranse no importe total do préstamo, pero a medida que o corpo diminúe, redúcese o interese. Pero o importe total do pagamento permanece inalterado debido ao aumento mensual dos pagamentos do corpo do préstamo. Así, co paso do tempo, diminúe a proporción de xuros no pago total mensual, mentres que a proporción de pago por corpo aumenta. Ao mesmo tempo, o pagamento mensual en si non cambia ao longo de todo o período de préstamo.
Só ao calcular o pago de anualidades, paramos. Ademais, isto é relevante, xa que na actualidade a maioría dos bancos usan este esquema en particular. Tamén é conveniente para os clientes, porque neste caso o importe total do pagamento non cambia, quedando fixado. Os clientes sempre saben canto pagar.
Etapa 1: Cálculo mensual de taxas
Para calcular a taxa mensual cando se usa o réxime de renda vitalicia en Excel hai unha función especial - PMT. Pertence á categoría de operadores financeiros. A fórmula desta función é a seguinte:
= PMT (tipo; nper; ps; bs; tipo)
Como podes ver, a función especificada ten un número bastante grande de argumentos. É certo, os dous últimos non son obrigatorios.
Argumento "Aposta" indica o tipo de interese para un período específico. Se, por exemplo, utilízase a taxa anual, pero o préstamo paga mensualmente, a taxa anual debería dividirse por 12 e usa o resultado como argumento. Se se usa o método de pago trimestral, neste caso a taxa anual debería dividirse en 4 e así por diante
"Kper" Denota o número total de períodos de amortización de préstamos. É dicir, se se toma un préstamo por un ano cun pagamento mensual, considérase o número de períodos 12Se durante dous anos, entón o número de períodos é 24. Se se toma un préstamo por dous anos con pagamento trimestral, entón o número de períodos é igual a 8.
"Ps" indica o valor actual. En palabras simples, este é o importe total do préstamo ao comezo do préstamo, é dicir, o importe que teña prestado, excluíndo os xuros e outros pagamentos adicionais.
"Bs" - este é o valor futuro. Este valor, que será o corpo do préstamo no momento da conclusión do contrato de préstamo. Na maioría dos casos, este argumento é "0", xa que o prestatario ao final do período de préstamo debe pagar totalmente o prestamista. O argumento especificado é opcional. Polo tanto, se cae, considérase cero.
Argumento "Tipo" determina o tempo de cálculo: ao final ou ao comezo do período. No primeiro caso, toma o valor "0", e no segundo - "1". A maioría das entidades bancarias usan exactamente a opción co pagamento ao final do período. Este argumento tamén é opcional e, se o omite, considérase que é cero.
Agora é hora de pasar a un exemplo específico de cálculo da cota mensual mediante a función PMT. Para o cálculo, utilizamos unha táboa cos datos orixinais, onde se indica o tipo de interese do préstamo (12%), importe do préstamo (500.000 rublos) e o prazo do préstamo (24 meses). Neste caso, o pago faise mensual ao final de cada período.
- Seleccione o elemento na folla na que se amosará o resultado do cálculo e faga clic na icona "Inserir función"colocado preto da barra de fórmulas.
- Iníciase a xanela. Mestría de funcións. Na categoría "Financeira" seleccione o nome "PLT" e prema no botón "OK".
- Despois, ábrese a ventá de argumento do operador. PMT.
No campo "Aposta" debe introducir o importe dos intereses do período. Isto pódese facer manualmente simplemente poñendo unha porcentaxe, pero aquí está listado nunha cela separada na folla, polo que damos unha ligazón a ela. Sitúe o cursor no campo e faga clic na cela correspondente. Pero, como recordamos, temos unha taxa de interese anual na táboa e o período de remuneración é igual a un mes. Polo tanto, dividimos a taxa anual, ou mellor, a referencia á cela na que está contido, polo número 12correspondente ao número de meses nun ano. A división realízase directamente no campo da xanela dos argumentos.
No campo "Kper" establecer o período de préstamo. É igual a nós 24 meses. Pode introducir un número no campo 24 manualmente, pero nós, como no caso anterior, proporcionamos unha ligazón á localización deste indicador na táboa orixinal.
No campo "Ps" indique o valor inicial do préstamo. É igual a 500.000 rublos. Do mesmo xeito que nos casos anteriores, indicamos a referencia ao elemento da folla que contén este indicador.
No campo "Bs" indica o importe do préstamo, despois do pago completo. Como recordamos, este valor é case sempre cero. Defina o número neste campo "0". Aínda que este argumento pode omitirse completamente.
No campo "Tipo" indique ao comezo ou ao final do mes o pagamento. Nós, como na maioría dos casos, faise a finais do mes. Polo tanto, configure o número "0". Como no caso do argumento anterior, neste campo non pode introducir nada, entón o programa asumirá por defecto que hai un valor igual a cero.
Despois de introducir todos os datos, faga clic no botón "OK".
- Despois disto, o resultado do cálculo móstrase na cela seleccionada no primeiro parágrafo deste manual. Como podes ver, o valor do pagamento total mensual do préstamo é 23536,74 rublos. Non confundas co signo "-" diante deste importe. Así, Excel sinala que este é un fluxo de caixa, é dicir, unha perda.
- Para calcular o importe total do pagamento durante todo o período de préstamo, tendo en conta o reembolso do corpo do préstamo e os xuros mensuais, basta con multiplicar o importe do pagamento mensual (23536,74 rublos) para o número de meses (24 meses). Como podes ver, o importe total dos pagamentos durante todo o período de préstamo no noso caso ascendeu a 564881.67 rublos.
- Agora podes calcular a cantidade de sobrepago do préstamo. Para iso ten que quitar o importe total dos pagamentos do préstamo, incluídos os intereses e o corpo do préstamo, o importe inicial prestado. Pero recordamos que o primeiro destes valores xa está sinal "-". Polo tanto, no noso caso particular, resulta que precisan ser dobrados. Como podes ver, o importe total do pago excesivo do préstamo durante todo o período foi de 64881,67 rublos.
Lección: Asistente de funcións de Excel
Etapa 2: Detalles do pagamento
E agora, coa axuda doutros operadores, Excel fará detalles de pagamentos mensuais para ver canto pagamos o corpo do préstamo nun mes determinado e canto os intereses. Para estes efectos, estamos a elaborar en Excel unha táboa que iremos enchendo con datos. As filas desta táboa corresponderán ao período correspondente, é dicir, ao mes. Dado que o período de acreditación que temos é 24 mes, entón o número de filas tamén será apropiado. As columnas indican o pagamento do corpo do préstamo, o pagamento dos xuros, o pagamento mensual total, que é a suma das dúas columnas anteriores, así como o importe restante a pagar.
- Para determinar a cantidade de pago no corpo do préstamo use a función OSPLTque só se destina a este propósito. Estableza o cursor na cela, que está na liña "1" e na columna "Pago no corpo do préstamo". Pulsamos o botón "Inserir función".
- Ir a Asistente de funcións. Na categoría "Financeira" marque o nome OSPLT e preme o botón "OK".
- Iníciase a ventá de argumentos do operador OSPLT. Ten a seguinte sintaxe:
= OSPLT (Taxa; Período; Kper; Ps; Bs)Como podes ver, os argumentos desta función coinciden case completamente cos argumentos do operador PMT, só no canto do argumento opcional "Tipo" Engadido o argumento requirido "Período". Indica o número do período de reembolso e, no noso caso particular, o número do mes.
Encha os campos da xanela de funcións familiares OSPLT os mesmos datos que se usaron para a función PMT. Só tendo en conta o feito de que no futuro se utilice a copia da fórmula a través do marcador de recheo, é necesario facer absolutamente todos os enlaces nos campos para que non cambien. Para facelo, ten que poñer un sinal de dólar diante de cada valor das coordenadas vertical e horizontalmente. Pero é máis fácil facelo simplemente seleccionando as coordenadas e premendo a tecla de función. F4. O sinal de dólar colocarase automaticamente nos lugares correctos. Ademais, non esqueza que a taxa anual debe ser dividida en 12.
- Pero quedamos con outro novo argumento que a función non tiña PMT. Este argumento "Período". No campo correspondente puxemos a ligazón á primeira cela da columna. "Período". Este elemento de folla contén un número "1"que indica o número do primeiro mes de crédito. Pero, a diferenza dos campos anteriores, no campo especificado deixamos o vínculo relativo e non facemos unha referencia absoluta.
Despois de introducir todos os datos anteriores, faga clic no botón "OK".
- Despois diso, na cela asignada previamente aparecerá o importe do pagamento do corpo do préstamo no primeiro mes. Ela fará 18536,74 rublos.
- Entón, como mencionado anteriormente, deberiamos copiar esta fórmula ás celas restantes da columna usando o marcador de recheo. Para facelo, configure o cursor no ángulo inferior dereito da cela, que contén a fórmula. O cursor convértese entón nunha cruz, que se denomina marcador de enchemento. Manteña premido o botón esquerdo do rato e arrástrea ata o final da táboa.
- Como resultado, enche todas as celas da columna. Agora temos un calendario mensual de amortización de préstamos. Como se mencionou anteriormente, o importe do pagamento deste artigo aumenta con cada novo período.
- Agora necesitamos facer un cálculo mensual dos pagamentos de xuros. Para estes efectos, usaremos o operador PRPLT. Seleccione a primeira cela baleira na columna. "Pagamento de intereses". Pulsamos o botón "Inserir función".
- Na xanela de inicio Mestría de funcións na categoría "Financeira" fai a selección do nome PRPLT. Fai un clic no botón. "OK".
- Iníciase a ventá dos argumentos da función. PRPLT. A súa sintaxe é a seguinte:
= PRPLT (Taxa; Período; Kper; Ps; Bs)Como podes ver, os argumentos desta función son absolutamente idénticos aos do operador OSPLT. Polo tanto, simplemente introducimos na ventá os mesmos datos que introducimos na xanela do argumento anterior. Non esquezas que a ligazón no campo "Período" debe ser relativo e en todos os outros campos as coordenadas deben reducirse a forma absoluta. Despois faga clic no botón "OK".
- Entón o resultado do cálculo do importe do pagamento dos xuros do préstamo no primeiro mes móstrase na cela correspondente.
- Unha vez aplicado o marcador de recheo, realizamos unha copia da fórmula para os restantes elementos da columna, obtendo así un horario de pagamento mensual para os intereses do préstamo. Como podes ver, como se dixo anteriormente, o valor deste tipo de pago decrece dun mes a outro.
- Agora temos que calcular o pagamento mensual total. Para este cálculo, non debería recorrer a ningún operador, xa que pode usar unha fórmula aritmética sinxela. Dobre o contido das celas do primeiro mes das columnas "Pago no corpo do préstamo" e "Pagamento de intereses". Para facelo, configure o signo "=" na primeira cela de columna baleira "Pagamento mensual total". A continuación, faga clic nos dous elementos anteriores, marcando entre eles o signo "+". Pulsamos a tecla Intro.
- A continuación, empregando o marcador de enchemento, como nos casos anteriores, enche a columna con datos. Como podes ver, ao longo da duración do contrato, o importe total do pagamento mensual, incluído o pagamento do corpo do préstamo e o pagamento de intereses, será 23536,74 rublos. En realidade xa calculamos esta figura con axuda de PMT. Pero neste caso, preséntase con máis claridade, precisamente como o importe do pagamento do corpo do préstamo e dos intereses.
- Agora ten que engadir datos á columna onde se amosará mensualmente o saldo do importe do préstamo que aínda hai que pagar. Na primeira cela da columna "Saldo a pagar" O cálculo será o máis sinxelo. Necesitamos retirar o valor inicial do préstamo, que se mostra na táboa cos datos primarios, o pago do corpo do préstamo no primeiro mes na táboa de cálculo. Pero, dado o feito de que un dos números que xa temos ten un sinal "-", non deben ser retirados, senón dobrados. Fai isto e fai clic no botón. Intro.
- Pero o cálculo do saldo a pagar despois do segundo e posterior mes será algo máis difícil. Para facer isto, necesitamos restar o importe total dos pagamentos do corpo do préstamo para o período anterior desde o corpo do préstamo ao comezo do préstamo. Estableza o signo "=" na segunda cela da columna "Saldo a pagar". A continuación, especifique a ligazón á cela que contén o importe do préstamo inicial. Facémolo absoluto seleccionando e premendo a tecla. F4. A continuación, coloque un sinal "+", xa que o segundo valor será negativo para nós. Despois faga clic no botón "Inserir función".
- Inicia Asistente de funciónsno que ten que pasar á categoría "Matemática". Alí seleccionamos a inscrición "SUMM" e prema no botón "OK".
- Iníciase a fiestra do argumento da función. SUM. O operador especificado serve para resumir os datos nas celas, que necesitamos realizar na columna "Pago no corpo do préstamo". Ten a seguinte sintaxe:
= SUMA (número 1; número2; ...)Os argumentos son referencias a celas que conteñen números. Poñemos o cursor no campo. "Número1". A continuación, mantemos o botón esquerdo do rato e selecciona as dúas primeiras celas da columna na folla. "Pago no corpo do préstamo". Como podes ver, amósase a ligazón no campo. Consta de dúas partes separadas por dous puntos: referencias á primeira cela do intervalo e á última. Para poder copiar a fórmula indicada no futuro por medio dun marcador de recheo, facemos a primeira parte da referencia ao rango absoluto. Selecciona e fai clic na tecla de función. F4. A segunda parte da ligazón queda relativa. Agora, cando se usa o marcador de enchemento, fixarase a primeira cela da franxa e estenderase a medida que se move. Isto é o que necesitamos para cumprir os nosos obxectivos. A continuación, fai clic no botón "OK".
- Así, o resultado do saldo de débeda crediticia despois do segundo mes móstrase na cela. Agora, a partir desta cela, copiamos a fórmula nos elementos baleiros da columna usando o marcador de recheo.
- O cálculo mensual dos saldos dos préstamos realizouse durante todo o período do crédito. Como debería ser, ao final do prazo este importe é cero.
Así, fixemos non só o cálculo do pago do préstamo, senón que organizamos unha especie de calculadora de préstamos. Que operará baixo o réxime de renda vitalicia. Se na táboa de orixe, por exemplo, cambiamos o tamaño do préstamo e a taxa de interese anual, entón na táboa final os datos volveranse a calcular automaticamente. Polo tanto, pode usarse non só unha vez para un caso específico, senón que pode usarse en varias situacións para calcular as opcións de préstamo mediante o réxime de renda vitalícia.
Lección: Funcións financeiras en Excel
Como podes ver, usando Excel na túa casa, podes calcular facilmente o pagamento mensual total do préstamo usando o réxime de renda vitalicia, usando o operador PMT. Ademais, coa axuda de funcións OSPLT e PRPLT Pode calcular a cantidade de pagamentos no corpo do préstamo e sobre os intereses do período especificado. Ao aplicar toda esta equipaxe de funcións en conxunto, é posible crear unha calculadora de préstamos poderosa que se poida usar máis dunha vez para calcular o pagamento de renda vitalicia.
