Por lo que parece, no estás pidiendo una fórmula única sino cómo hacer el análisis... Y que conste que te estás centrando en lo que no debes. Deberías centrarte en cuánto cuesta tener tu coche durante ese periodo de tiempo no el total de su patrimonio.
Fórmulas: No estoy seguro de que entiendas bien las tuercas y los pernos de las finanzas detrás de tu pregunta, (puede que sólo seas un profesional y realmente quieras una ecuación consolidada para hacer esto de una sola vez). Así que, a riesgo de especificar demasiado, pecaré de empezar por el principio. Cualquier análisis de préstamos financieros se basa en 5 elementos: (1) número de períodos, (2) valor actual, (3) valor futuro, (4) pagos y (5) tipo de interés.
En el software de hoja de cálculo se suelen denominar NPER, PV, FV, PMT y Rate. Cada uno de ellos tiene su propia función de Excel/google docs donde se puede calcular uno como función de los otros 4. Utilizaré esas funciones en lo sucesivo y te ahorraré las ecuaciones "matemáticas reales".
Disposición: Si yo intentara resolver tu problema, empezaría por configurar la hoja de cálculo con la columna A como "Periodo". Pondría esta etiqueta en la celda A2 y luego empezaría por la celda A3 como "0" y llegaría hasta "N". Los préstamos a 5 años te darán el valor de compra más alto con los pagos más bajos, así que n=60 meses... pero también dijiste 48 meses así que haz lo que quieras.
Luego, establecería dos tablas una al lado de la otra con 7 columnas cada una. (Sí, siete.) Empezando por la C2, etiquetaría las celdas/columnas como: "Tasa", "Valor del coche", "Saldo del préstamo", "Pago", "Pagado a intereses", "Principal" y "Capital acumulado". A continuación, seleccione y copie las celdas C2:I2 como el siguiente conjunto de encabezados de columna comenzando en K2. (Yo suelo saltarme una columna para dejar espacio porque soy así de obsesivo compulsivo :) )
Números: Ahora tienes que establecer tu conjunto inicial de números para cada mesa. Haremos el coche más antiguo en la tabla de la izquierda y el más nuevo en la de la derecha. Digamos que el tipo de interés es del 5% TAE. Ponlo en la celda C1 (no C3). A continuación, en la celda C3 escriba =C $1/12. Car Value $ 12.000 en la celda D3. A continuación, escribe "Pago inicial" en la celda E1 y pon el 10% en la celda D1. Y por último, en la celda E3 pon la fórmula =D3*(1-D$1).
Esto debería dejarle con un valor para el primer mes en las columnas de Tasa, Valor del Auto y Saldo del Préstamo. Ahora selecciona C1:E3 y pégalos en la tabla de la derecha. Lo único que tendrás que cambiar es el "Valor del coche" a 20.000 dólares.
Como comprobación, debería tener 0,0042 / 12.000 / 10.800 a la izquierda y luego 0,0042 / 20.000 / 18.000 a la derecha.
Otra vez las fórmulas: Aquí es donde las hojas de cálculo se vuelven increíbles. Si configuramos las fórmulas adecuadas, puedes copiarlas y pegarlas y hacer este complicadísimo análisis muy rápidamente.
Pago La fórmula de Excel para el pago es =PMT(Tipo, NPER, PV, FV). FV es normalmente cero. Así, en la celda F3, escribe la fórmula =PMT(C3, 60, E3, 0). Obviamente, si realmente estás haciendo un préstamo a 48 meses (4 años), tendrás que cambiar el 60 por el 48. Deberías poder copiar el resultado de la celda F3 a N3 y la fórmula se actualizará sola.
Para los 60 meses, estoy mostrando que el préstamo de 12K del coche/10.8K tiene un pmt de $203.81. El préstamo de 20K/18K tiene un pmt de 339,68.
Interés La forma más fácil de calcular el interés es como =E3*C3. Es decir, (Saldo pendiente del préstamo) x (Tipo de interés periódico). Ponga esto en la celda G4, ya que en realidad no debe ningún interés en el período 0.
Principal Si usted paga PMT cada mes y X va a los intereses, entonces la cantidad al principal es "PMT - X". Así que en H4 escriba =-F3 - G3. El "menos" delante de F3 es porque la función PMT de Excel devuelve una cantidad negativa. Si quieres, puedes escribir "=-PMT(...)" para la fórmula que está en F3. Es tu decisión.
Obtengo 159 por la cantidad a principal en el periodo 1.
Patrimonio acumulado Como mencioné en el comentario, tu "Equity" proviene de tu Loan-to-Value inicial y de los pagos de capital acumulados. Así que la fórmula en esta celda debe reflejar eso. Hay una variedad de maneras de hacer esto... la más fácil es simplemente comparar el valor esperado de tu coche con el saldo de tu préstamo cada vez.
En la celda I3, escriba =(D3-E3). Ese es tu capital inicial en el coche antes de hacer cualquier pago. Copia esa celda y pégala en I4. Verás que se actualiza a =(D4-E3) automáticamente. (En este momento es cero... esas celdas están vacías, pero estamos llegando a ellas) Lo importante es que, como señaló JB King, tu patrimonio es una función del capital acumulado Y del patrimonio, que se deprecia. Este enfoque maneja ambos.
Terminar las fórmulas de copiar y pegar Sé que esto es largo, pero ya casi hemos terminado.
Tasa // Período 1 En la celda C4 escriba =C3.
Pago // Período 1 En la celda F4 escriba =F3.
Saldo del préstamo // Periodo 1 En la celda E4 tipo =E3-H4. El saldo de tu préstamo al final del periodo se reduce por el capital que has pagado. Obtengo 10.641.
Valor del coche // Periodo 1 Esto variará en función de cómo quiera gestionar la depreciación. Si lo ignoras, estás cometiendo un gran error y no merece la pena hacer todo este análisis... simplemente compra el coche más bonito y sigue con tu vida. Pero tampoco tienes que conseguirlo con precisión científica. Ve a algún sitio como edmunds.com y busca un valor aproximado. Yo estoy usando un 4% de depreciación por año para el coche viejo (12K) y un 7% para el coche más nuevo. Sin embargo, me he sacado esos datos del culo, así que averigua cuál es la mejor cifra aproximada.
En G1 escriba "Depreciación" y luego ponga 4% en H1. En O1 escribe "Depreciación" y luego el 7% en P1. Ahora, en la celda D4, pon la fórmula =D3 * (1-(H$1/12)).
Pegar fórmulas para completar la tabla
Como comprobación, tu fila 4 debería decir 1 / .0042 / 11.960 / 10.641 / 203,81 / 45 / 159 / 1.319. Si es así, usted es grande. Copia las celdas C4:I4 y pégalas en K4:Q4.
Estos se actualizarán para ser .0042 / 19.883 / 17.735 / 339,68 / 75 / 265 / 2.148.
Si lo tienes, copia C4:Q4 y pégalo en C5:C63. Has construido una tabla de amortización completa para tus dos préstamos hipotéticos. Enhorabuena.
Cómo tomar la decisión No te voy a decir lo que tienes que decidir, pero te daré una mejor idea de lo que tienes que mirar. Personalmente, tomaría la decisión basándome en el coste total de la propiedad durante ese periodo de tiempo, más un poco de "factor x" por el coche que realmente me gustara.
Mira en el periodo 24, en las columnas I y Q. Estas son tus "acciones" en cada coche. Si construyes la hoja usando mis números inventados, entonces obtienes "Equidad del auto viejo" como 4,276. "Equidad del coche nuevo" es de 6.046. Si sólo te fijas en la mayor parte de la equidad, podrías tomar una mala decisión financiera.
El valor real que debes considerar es el coste de poseer el coche (no necesariamente operarlo) durante ese tiempo... Coste total = (Patrimonio final) - (Pago x 24) - (Efectivo inicial).
Para tu "viejo" coche, son (4.276) - (203,81 * 24) - (1.200) = -1.815,75
Para el coche "nuevo", son (6.046) - (339,68 * 24) - (2.000) = -4.106,07.
¿Es bueno o malo? Tú decides. Hay fórmulas de Excel como "CUMPRINC" que pueden consolidar algunas de las mecánicas de la tabla, pero supuse que si estás aquí preguntando te habrías quedado con alguna de ellas.
Aquí está la hoja de cálculo: https://docs.google.com/spreadsheet/ccc?key=0Ah0weE0QX65vdHpCNVpwUzlfYjlTY2VrNllXOS1CWUE#gid=1
