Cálculo de anualidades Pay en Excel

Antes de tomar o préstamo, sería bo calcular todos os pagamentos nel. Gardará o mutuários no futuro a partir de varios problemas e decepcións inesperadas cando resulta que o exceso de pagamento é demasiado grande. A axuda deste cálculo pode as ferramentas de programa de Excel. Descubriremos como calcular os pagamentos de anualidades do préstamo neste programa.

Cálculo de pagamento

Primeiro de todo, hai que dicir que hai dous tipos de pagamentos de crédito:

• Diferenciado;
• Anualidade.

Cun esquema diferenciado, o cliente trae unha parte mensual igual de pagamentos sobre o corpo dun préstamo máis pagos ao interese. A magnitude dos pagamentos de xuros cada mes diminúe, a medida que se reduce o corpo do préstamo do que se calculan. Así, tamén se reduce o pagamento mensual global.

Ann Ty St Scheme usa un enfoque un pouco diferente. O cliente fai unha única cantidade do pagamento total mensual, que consiste en pagamentos sobre o corpo dun préstamo e pago de interese. Inicialmente, as contribucións de interese están numeradas en todo o importe do préstamo, pero a medida que o corpo diminúe, o interese é reducido e o interese de acumulación. Pero a cantidade total de pagamento permanece inalterada debido a un aumento mensual da cantidade de pagamentos polo corpo do préstamo. Así, co paso do tempo, a proporción de interese no pagamento mensual total cae e crece o peso da proporción do corpo. Ao mesmo tempo, o pagamento mensual xeral en si non está cambiando ao longo do prazo de crédito.

Só sobre o cálculo do pagamento da anualidade, pararemos. Especialmente, isto é relevante, xa que a maioría dos bancos usan este esquema particular. É conveniente para os clientes, porque neste caso a cantidade total de pagamento non cambia, permanece fixada. Os clientes sempre saben o que ten que pagar.

Paso 1: Cálculo de cotización mensual

Para calcular unha contribución mensual ao usar un circuíto de anualidades en Excele, hai unha función especial: PPT. Refírese á categoría de operadores financeiros. A fórmula desta característica é a seguinte:

= Ppt (taxa; kper; ps; bs; tipo)

Como vemos, a función especificada ten unha gran cantidade de argumentos. Verdade, os dous últimos deles non son obrigatorios.

O argumento "taxa" indica unha taxa de porcentaxe por un período específico. Se, por exemplo, úsase unha taxa anual, pero o pagamento do préstamo está feito mensual, entón a taxa anual debe dividirse en 12 e o resultado úsase como argumento. Se se aplica un tipo de pagamento trimestral, neste caso a aposta anual debe dividirse en 4, etc.

"CPER" significa o número total de períodos de pagamentos de préstamos. É dicir, se o préstamo é tomado por un ano co pagamento mensual, entón o número de períodos considérase 12, se dous anos, entón o número de períodos - 24. Se o préstamo é tomado por dous anos con pagamento trimestral, entón O número de períodos é 8.

"PS" indica o valor actual no momento. Falando con palabras sinxelas, este é o importe total do préstamo ao comezo dos préstamos, é dicir, a cantidade que está prestado, excluíndo os intereses e outros pagamentos adicionais.

"BS" é un custo futuro. Este valor será un organismo de préstamo no momento da conclusión do contrato de préstamo. Na maioría dos casos, este argumento é "0", xa que o mutuários ao final do período de crédito debe establecerse plenamente co valor. O argumento especificado non é obrigatorio. Polo tanto, se é descendente, considérase cero.

O argumento "tipo" determina o tempo de cálculo: ao final ou ao comezo do período. No primeiro caso, leva o valor "0", e no segundo - "1". A maioría das institucións bancarias utilizan exactamente a opción con pagamento ao final do período. Este argumento tamén é opcional, e se é omitido, crese que é cero.

Agora é hora de pasar a un exemplo específico de calcular unha contribución mensual usando a función pl. Para calcular, usamos unha táboa cos datos de orixe, onde se indica a taxa de interese do préstamo (12%), o valor do préstamo (500.000 rublos) eo período de préstamo (24 meses). Ao mesmo tempo, o pagamento faise mensualmente ao final de cada período.

1. Seleccione o elemento na folla no que se mostrará o resultado do resultado e faga clic na icona "Inserir función", situada preto da Fórmula Fila.



2. Lanzouse a xanela Wizard Wizard. Na categoría "Financial" asignar o nome "PLT" e premer no botón "Aceptar".



3. Despois diso, abre a xanela de argumentos do operador de pl.

   No campo "Prezo", debes introducir o valor porcentual do período. Isto pódese facer manualmente, simplemente poñendo unha porcentaxe, pero está indicada nunha célula separada na folla, polo que imos darlle unha ligazón a ela. Instala o cursor no campo e, a continuación, fai clic na cela correspondente. Pero, como recordamos, temos a taxa de interese anual na nosa mesa e o período de pagamento é igual ao mes. Polo tanto, dividimos a aposta anual, e máis ben unha ligazón á célula na que está contida polo número 12, correspondente ao número de meses do ano. A división execútase directamente no campo da xanela do argumento.

   No campo CPER, o préstamo está configurado. É igual a 24 meses. Pode aplicar no campo número 24 manualmente, pero nós, como no caso anterior, especifique unha ligazón á localización deste indicador na táboa de orixe.

   No campo "PS" indica o valor do préstamo inicial. É igual a 500.000 rublos. Como nos casos anteriores, especificamos unha ligazón ao elemento LEAF, que contén este indicador.

   No campo "BS" indica a magnitude do préstamo, despois do seu pagamento completo. A medida que recordas, este valor é case sempre cero. Instalar neste campo o número "0". Aínda que se pode omitir este argumento.

   No campo "Tipo", especificamos ao comezo ou ao final do mes de pago. Nós, como na maioría dos casos, é producido a finais de mes. Polo tanto, fixamos o número "0". Como no caso do argumento anterior, é posible introducir calquera cousa a este campo, entón o programa predeterminado suporá que é cero igual a el.

   Despois de introducir todos os datos, prema o botón "Aceptar".



4. Despois diso, na célula que asignamos no primeiro parágrafo deste manual, aparece o resultado do cálculo. Como podes ver, a magnitude do pagamento xeral mensual do préstamo é de 23536,74 rublos. Deixe que non confunda o sinal "-" antes deste importe. Así, o exilio indica que este é o fluxo de diñeiro, é dicir, unha perda.



5. Para calcular a cantidade total de pagamento durante todo o período de préstamo, tendo en conta o reembolso do órgano dun préstamo e interese mensual, en vez de multiplicar o importe do pagamento mensual (23536.74 rublos) durante o número de meses (24 meses) ). Como podes ver, a cantidade total de pagamentos por todo o período de préstamo no noso caso foi de 564881,67 rublos.



6. Agora podes calcular a cantidade de pagamento de pagamento de préstamo. Para iso, é necesario sacar a cantidade total de pagamentos do préstamo, incluídos os intereses eo organismo de préstamo, o importe inicial reclamado. Pero recordamos que o primeiro destes valores xa co signo "-". Polo tanto, en específicamente, o noso caso resulta que deben ser dobrados. Como vemos, o exceso de pagamento total do préstamo durante todo o período foi de 64881,67 rublos.

Lección: Máster de Funcións en Excel

Etapa 2: Detalles do pagamento

E agora, coa axuda doutros operadores de Excel, facemos un detalle mensual de pagamentos para ver o que nun mes específico pagamos a través do corpo do préstamo e canto é a cantidade de interese. Para estes fins, ferreiros na mesa de exilio, que encheremos os datos. As liñas desta táboa serán responsables do período correspondente, é dicir, o mes. Dado que o período de préstamos é de 24 meses, o número de filas tamén será apropiado. As columnas indicaron un organismo de préstamo, pagamentos de xuros, un pagamento mensual total, que é a suma das dúas columnas anteriores, así como a cantidade restante a pagar.

1. Para determinar a cantidade de pagamento polo corpo do préstamo, use a función OSP, que está destinada a estes fins. Establecemos o cursor na célula, que está situado na liña "1" e na columna "Pago polo corpo do préstamo". Fai clic no botón "Función de pasta".



2. Vaia ao mestre das funcións. Na categoría "Financial", notamos o nome "OSPLT" e prema no botón "Aceptar".



3. Comezan os argumentos dos argumentos do operador OSP. Ten a seguinte sintaxe:

   = Ospult (taxa; período; kper; ps; bs)

   Como podemos ver, os argumentos desta característica coinciden case por completo cos argumentos do operador PLT, só no canto do argumento opcional "tipo" engadiu un argumento obrigatorio "período". Indica o número do período de pagamento e, no noso caso particular sobre o número do mes.

   Encha os argumentos dos argumentos da función OSR xa familiarizado cos mesmos datos, que se usaban para a función pl. Acaba de dar o feito de que no futuro, a copia dunha fórmula empregarase a través dun marcador de recheo, cómpre facer todas as ligazóns nos campos absolutos para que non cambien. Isto require poñer un sinal de dólar antes de cada valor de coordenadas verticais e horizontais. Pero é máis doado facelo, simplemente seleccionando as coordenadas e premendo na tecla FUND FUND. O sinal de dólar colocarase automaticamente nos lugares correctos. Tampouco esquecemos que a aposta anual debe dividirse en 12.



4. Pero temos outro novo argumento, que non foi da función pl. Este argumento "período". No campo apropiado, establece unha referencia á primeira cela da columna "Período". Este elemento da folla contén o número "1", que denota o número do primeiro mes de préstamos. Pero a diferenza dos campos anteriores, deixamos o enlace relativo no campo especificado e non facemos absoluto.

   Despois de que se introduzan todos os datos sobre os que falamos anteriormente, prema o botón "Aceptar".



5. Despois diso, na célula, que anteriormente asignamos, aparecerá a cantidade de pagamento polo corpo do préstamo para o primeiro mes. Será 18536,74 rublos.



6. Entón, como se mencionou anteriormente, debemos copiar esta fórmula ás celas das columnas restantes usando un marcador de recheo. Para iso, axusta o cursor á esquina inferior dereita da cela, que contén a fórmula. O cursor convértese nunha cruz, que se chama un recheo. Fai clic no botón esquerdo do rato e tiralo ata o final da táboa.



7. Como resultado, todas as columnas celulares están cheas. Agora temos un gráfico de pagar un préstamo mensualmente. Como se mencionou anteriormente, a cantidade de pagamento neste artigo aumenta con cada novo período.



8. Agora necesitamos facer un cálculo mensual de pagamento por interese. Para estes fins, imos usar o operador PRT. Asignamos a primeira célula baleira na columna "Pagamento porcentaxe". Fai clic no botón "Función de pasta".



9. Nas funcións do Máster de Funcións na categoría "Finanzas", nós producimos os nomes da NAMP. Realice un clic no botón "Aceptar".



10. Iníciase a fiestra de argumentos da función TRP. A súa sintaxe parece así:

    = PRT (taxa; período; CPU; PS; BS)

    Como podemos ver, os argumentos desta función son absolutamente idénticos a elementos similares do operador OSP. Polo tanto, simplemente introduza os mesmos datos na xanela que introducimos na xanela anterior dos argumentos. Non esquecemos que a referencia no campo "período" debe ser relativa e, en todos os outros campos, as coordenadas deben ser traídas a forma absoluta. Despois diso, fai clic no botón "Aceptar".



11. A continuación, o resultado do cálculo da cantidade de pagamento por interese por un préstamo para o primeiro mes móstrase na cela correspondente.



12. Aplicando o marcador de recheo, fai copiar a fórmula nos elementos restantes da columna, deste xeito recibir un horario mensual para porcentaxes para o préstamo. Como podemos ver, como se dixo anteriormente, de mes a mes o valor deste tipo de pagamento diminúe.



13. Agora temos que calcular o pagamento mensual global. Para este cálculo, non se debe recorrer a ningún operador, xa que pode usar a fórmula aritmética simple. Dobramos os contidos das células do primeiro mes das columnas "Pagamento polo corpo do préstamo" e "interese total". Para facelo, configure o sinal "=" na primeira cela baleira da columna "Pagamento mensual total". A continuación, faga clic nos dous elementos anteriores establecendo o cartel "+" entre eles. Fai clic na tecla Intro.



14. A continuación, usando o marcador de recheo, como nos casos anteriores, enche a columna de datos. Como podemos ver, ao longo de toda a acción do contrato, o importe do pagamento mensual total, que inclúe o pagamento do organismo do préstamo e o pagamento de intereses, será de 23536,74 rublos. En realidade, xa calculamos este indicador antes de usar PPT. Pero neste caso preséntase máis claramente, precisamente como a cantidade de pagamento do corpo do préstamo e do interese.



15. Agora cómpre engadir datos á columna, onde se amosa o saldo do importe do préstamo mensualmente, que aínda está obrigado a pagar. Na primeira célula da columna "equilibrio a pagar" o cálculo será o máis sinxelo. Necesitamos ser eliminados da magnitude do préstamo inicial, que se especifica na táboa con datos primarios, o pagamento do organismo do préstamo para o primeiro mes na táboa calculada. Pero, dado o feito de que un dos números que xa imos co signo "-", entón non deben ser eliminados, senón para dobrar. Facémolo e fai clic no botón Intro.



16. Pero o cálculo do equilibrio a pagar despois do segundo e posterior meses será un pouco máis complicado. Para iso, necesitamos sacar do corpo do préstamo ao comezo do préstamo o importe total dos pagos polo corpo do préstamo para o período anterior. Instala o signo "=" na segunda cela da columna "Palace a pagar". A continuación, especifica unha ligazón á cela, que contén o importe do préstamo inicial. Facémolo absoluto, destacando e presionando a tecla F4. Entón poñemos o sinal "+", xa que temos o segundo significado e tan negativo. Despois diso, fai clic no botón "Inserir función".



17. O mestre das funcións é lanzado, no que ten que pasar á categoría "Matemática". Alí reservamos a inscrición "sumas" e prema o botón "Aceptar".



18. A xanela de argumentos comeza os argumentos da función. O operador especificado serve para resumir os datos nas celas que necesitamos para realizar no "Pagamento pola columna do organismo de préstamo". Ten a seguinte sintaxe:

    = Sums (número1; número2; ...)

    Como argumentos, referencias a células nas que se contén os números. Fixamos o cursor no campo "número1". A continuación, pin o botón esquerdo do rato e seleccione as dúas primeiras celas da columna do corpo de crédito na folla. No campo, como vemos, apareceu unha ligazón ao rango. Consta de dúas partes separadas polo colon: referencias á primeira gama do rango e no último. Para poder copiar a fórmula especificada no futuro por un recheo, realizamos a primeira ligazón ao rango absoluto. Resaltámolo e faga clic na tecla F4 función. A segunda parte da referencia e deixa parente. Agora, ao usar un marcador de recheo, fixarase o primeiro rango do rango e este último estirará a medida que se mova cara a abaixo. Isto é necesario para nós cumprir os obxectivos. A continuación, fai clic no botón "Aceptar".



19. Así, o resultado do saldo de débeda de crédito despois do segundo mes é descargado na cela. Agora, comezando con esta cela, facemos copiando a fórmula en elementos de columna baleira usando un marcador de recheo.



20. O cálculo mensual dos restos para pagar o préstamo está feito por todo o período de crédito. Como debería ser, ao final do prazo, este importe é cero.

Así, simplemente non calculamos o pagamento do préstamo, senón que organizamos unha especie de calculadora de crédito. Que actuará nun esquema de anualidades. Se na táboa de orixe, por exemplo, cambie o importe do préstamo e da taxa de interese anual, entón na táboa final haberá recálculo automático de datos. Polo tanto, pódese usar non só unha vez por un caso particular, senón a aplicar en varias situacións para calcular as opcións de crédito nun esquema de anualidades.

Lección: Funcións financeiras en Excel

Como podes ver, usando o programa de Excel na casa, pode calcular facilmente o pagamento do préstamo mensual global no esquema de anualidades, utilizando o operador PL para estes fins. Ademais, coa axuda das funcións OSR e do PRT, é posible calcular a cantidade de pagamentos polo organismo do préstamo e porcentaxes para o período especificado. Aplicando todas estas funcións de equipaxe xuntas, é posible crear unha poderosa calculadora de crédito que se pode usar máis dunha vez para calcular o pagamento da anualidade.