Zeitwert des Geldes Tabellen in Excel
Bist du Schüler? Wissen Sie, dass Amazon 6 Monate von Amazon Prime bieten - frei zweitägigen Versand, kostenlose Filme und andere Vorteile - für Studenten? Klick hier um mehr zu erfahren
Praktisch jedes Finanzlehrbuch hat, auf der Rückseite, eine Reihe von Tabellen, die Multiplikatoren enthalten, die leicht verwendet werden können, um gegenwärtige oder zukünftige Werte, ohne die Notwendigkeit für einen Finanzrechner zu berechnen. In den letzten Jahren haben diese Tabellen Weg zum Finanzrechner langsam gegeben, aber sie sind immer noch weit verbreitet, die von einigen Professoren und an einigen professionellen Prüfungen.
Dieses Tutorial zeigt, wie diese Tabellen erstellen mit Excel. Die Tabellen hier erstellt sind viel besser als die Lehrbuch-Tabellen, weil sie ein paar Einschränkungen zu überwinden:
Wie bereits erwähnt, bieten diese Tabellen ein hohes Maß an Flexibilität. Diese Flexibilität wird unter Verwendung von Standard-Excel-Funktionen wie Zeitwert des Geldes Funktionen erreicht. mit zwei Eingängen Datentabellen, Datenvalidierung und die bedingte Formatierung.
Unter Verwendung der TVM Tabellen
Zeitwert des Geldes Tabellen sind sehr einfach zu bedienen, weil sie einen „Faktor“ zur Verfügung stellen, die von einem Barwert, zukünftigen Wert oder Rentenzahlung zu finden, die Antwort multipliziert wird. Also, bewaffnet mit der entsprechenden Tabelle und eine Möglichkeit, sich zu vermehren (beliebige Rechner oder sogar mit Bleistift und Papier), die Sie zu leicht Zeitwert des Geldes Probleme lösen kann.
Das folgende Bild zeigt einen Ausschnitt eines PVIF (Present Value Zins Factor) Tabelle:
In diesem Fall liefert die Tabelle einen Faktor, der durch einen zukünftigen Wert eines Pauschalbetrag Cashflow um multipliziert wird den Barwert zu erhalten.
Lassen Sie uns ein Beispiel an:
Stellen Sie sich vor, dass Sie jetzt $ 5.000 drei Jahre haben müssen und 4% pro Jahr in Ihrem Sparkonto verdienen. Wie viel brauchen Sie heute einzahlen, um Ihr Ziel zu erreichen?
Um dieses Problem zu lösen, multiplizieren wir einfach den zukünftigen Wert ($ 5.000) durch den entsprechenden PVIF Tabellenwert:
Also, schaut die erste Spalte der Tabelle nach unten für den Zeitraum 3 Reihe, und dann über auf die 4% Spalte. Die PVIF ist 0,8890, so lautet die Antwort:
PV = 5,000 x 0,8890 = 4445
Deshalb, wenn Sie $ 4445 heute in einem Sparkonto einzahlen, die jährlich 4% Aufzinsung zahlt, dann werden Sie $ 5.000 in drei Jahren.
Aber was passiert, wenn der Zinssatz 3,5% statt 3% oder 4% ist? Dann müssen Sie interpolieren, da 3,5% nicht in der Tabelle ist. Sie können die Antwort annähern, indem sie die PVIF Tabellenwerte für 3% und 4% im Durchschnitt und dass durchschnittlich für die PVIF verwenden. Der Durchschnitt liegt bei 0,90205 so würden Sie eine Antwort von $ 4,510.25 bekommen. Die richtige Antwort ist jedoch, $ 4,509.71 so Ihre Antwort würde off von etwa 0,54 $ sein. Gar nicht so schlecht, aber die Tische, die wir hier schaffen können leicht den genauen Zinssatz, die Sie benötigen.
Erstellen der Zinsfaktor Tabellen
Der Schlüssel, um die Tabellen zu erstellen ist zu verstehen, dass sie alle auf der Grund Zeitwert des Geldes Formeln basieren. Zum Beispiel kann die PVIF Faktoren aus der obigen Tabelle unter Verwendung von $ 1 für den FV in der Gleichung für Gegenwartswert berechnet:
Setzt man 1 für FV, 3 für N und 0,04 für i wir 0,8890 erhalten. Das ist das gleiche wie die PVIF, die wir ursprünglich vom Tisch gezogen. Da wir diese Tabellen mit Excel erstellen, können wir seine integrierten Funktionen (PV () in diesem Fall) anstelle der mathematischen Formel verwenden.
Zwei-Eingangsdatentabellen
Anstatt einen großen Tisch mit der Funktion PV () die Schaffung wiederholt immer und immer wieder werden wir von Excel mit zwei Eingängen Datentabelle Funktion verwenden. Dies ermöglicht es uns, eine Formel einmal eingeben, und dann wird es automatisch die Tabelle bevöl basierend auf Werte in der linken Spalte und die oberen Zeile der Tabelle. Diese Funktion wird in der Regel für die Sensitivitätsanalyse verwendet. Zum Beispiel könnten wir sehen, wie der aktuelle Wert ändert sich, wenn sowohl der Zinssatz und die Anzahl der Perioden Änderungen. In der Tat ist das, was wir hier tun, mit der Ausnahme, dass der FV 1 $ statt einen anderen Wert. Das Snippet unten zeigt die Formeln, die von oben in der PVIF Tabelle sind:
Beachten Sie, dass die PV () Funktion nur in der oberen linken Ecke der Tabelle verwendet wird. Der Rest der Tabelle wird automatisch ausgefüllt, wenn wir die Daten Tabelle Befehl verwenden. Es wirkt, indem es den Wert von der oberen Reihe und die linke Spalte in die Zellen angegeben (F1 und F2) ersetzt wird. Excel tut dies immer wieder in der Tabelle zu füllen. Tabelle Neuberechnung kann bei großen Tabellen oder komplizierte Formeln langsam sein, so dass man von Excel Berechnungsoptionen auf Automatisch Mit Ausnahme von Datentabellen. Wir brauchen nicht zu verwenden, die Einstellung hier, aber Sie sollten sich bewusst sein, dass es existiert.
Wir werden sehen, wie die Datentabelle unten im Abschnitt erstellen.
Erstellen Sie die Tabelle PVIF
Unsere PVIF Tabelle als Vorlage für jede der drei anderen Tabellen dienen. Wenn wir das richtig funktioniert, können wir einfach das Arbeitsblatt kopieren und dann die Formel ändern, die die Tabelle antreibt.
Öffnen Sie eine neue Arbeitsmappe, und dann ein Arbeitsblatt erstellen, die unten wie das aussieht:
Geben Sie alles genau so, wie gezeigt, mit Ausnahme der folgenden:
- In A10 geben Sie die Formel ein: = PV (F1, F2,0, -1). Das ist die Formel, die unsere Datentabelle fahren. Die 0,9901 im Bild ist einfach das aktuelle Ergebnis der Formel.
- In B10 geben Sie die Formel ein: = B1.
- In C10 geben Sie die Formel ein: = B10 + B $ 2 $. Dies wird „step up“ den Zinssatz. Kopieren Sie diese Formel über zu AE10 (dh 30 Spalten der Zinssätze).
- In A11 geben Sie die Formel ein: = B3.
- In A12 geben Sie die Formel ein: = A11 + $ B $ 4. Kopieren Sie diese Formel nach unten durch A70. Dies wird „step up“ die Periodennummer durch die Anzahl der Einheiten in B4 angegeben.
- Sie nicht die Schattierung in Zeile hinzufügen 10. Wir tun das mit Bedingte Formatierung später.
Bevor die Datentabelle zu schaffen, soll ich die Daten in E1 erklären: F2. Dies ist der Bereich (genauer gesagt, F1 und F2), in dem Excel werden die Werte aus der oberen Reihe und der linken Spalte ersetzen die Zahlen zu erhalten, in die Tabelle einfügen. Sie können es selbst ausprobieren: Geben Sie 4% in der Formel 1 und 3 in F2. Beachten Sie, dass der Wert in A10 wurde auf 0,8890 geändert. Das ist der gleiche Wert, den wir für die PVIF im ursprünglichen Beispiel Problem, das oben verwendet.
So, im Wesentlichen, was in der Datentabelle passiert ist, dass Excel Zahlen in F1 und F2 wird stecken und dann die Formel in A10 neu berechnen. Die Ergebnisse werden in eine Anordnung an der Schnittstelle zwischen der entsprechenden Reihe und Spalte angeordnet werden. Die Tabelle () -Funktion wird das Array in unserem Tabellenbereich anzuzeigen (B11: AE70).
Um die Datentabelle müssen wir schaffen A10 wählen: AE70 und dann auf die Registerkarte Daten zu gehen, klicken Sie auf die Was-wäre-wenn-Analyse-Schaltfläche und dann auf Datentabelle wählen. Sie werden nun das folgende Dialogfeld angezeigt:
Hier können Sie Excel, dass die Zelle F1 sagen ist, wo in den Zahlen von der obersten Zeile der Tabelle (die Zinsen) stecken und dass F2 ist, wo in den Zahlen in der linken Spalte (die Periodenzahlen) stopfen. Bitte beachten Sie, dass die tatsächlichen Zahlen in F1 und F2 überhaupt nicht egal, weil Excel sie ersetzen wird die Tabelle zu erstellen. Auch dies ist eine Zwei-Eingangs-Datentabelle. Sie können auch eine Ein-Eingangsdatentabelle erstellen, die nur die Zeile oder Spalte Eingangszelle durch die Angabe, aber das würde den Zweck hier nicht gerecht wird. Ihr Arbeitsblatt sollte nun so aussehen wie die unten, mit Ausnahme der Schattierung in Zeile 10.
An diesem Punkt ist der PVIF Tisch voll funktionsfähig. Wenn Sie den Wert in B1, zum Beispiel zu ändern, dann in der Tabelle ist die Zinsen zu ändern, und die Zinsfaktoren und neu berechnet werden. Allerdings müssen wir dies ein wenig aufzuräumen, um es funktionsfähig zu machen.
Machen Sie es Fancy Formatierungstechniken
Das Formatieren ist nicht nur für Ihre Tabelle recht zu machen. Es kann auch auf die Funktionalität hinzufügen. In diesem Abschnitt werden wir sehen, wie verschiedene Arten von Formatierung und Datenvalidierungsregeln gelten die TVM Tabellen flexibler und funktionsfähig zu machen.
Ausblenden von Text und Benutzerdefinierte Zahlenformatierung
Lassen Sie uns kümmern sich um ein paar einfache Dinge zuerst. Wir brauchen nicht den Inhalt von E1 zu sehen: F2, so können wir diese Zellen ausblenden, indem Sie die Schriftfarbe auf Weiß setzen.
Auch wir brauchen nicht auf die Zahl in A10 zu sehen. In der Tat, es verwirrt nur Dinge. So werden wir ein eigenes Format zur Anzeige der Text „Periode“ statt das Ergebnis der Formel anzuwenden. Beachten Sie, dass dies nicht die Formel oder das Ergebnis, nur das, was in der Zelle erscheint nicht ändert.
Anwenden von Regeln zur bedingten Formatierung
Bedingte Formatierung ändert das Aussehen einer Zelle oder einen Bereich, wenn bestimmte Bedingungen erfüllt sind. Um die Regeln einzurichten, wählen Sie eine Zelle oder einen Bereich und klicken Sie dann auf die Schaltfläche Bedingte Formatierung auf der Registerkarte Start des Bandes. Wählen Sie Neue Regel aus dem Menü. Wir wollen Regeln erstellen, die auf Formeln basieren, so wählen Sie das letzte Element in der Regel Typenliste (Verwenden Sie eine Formel, um zu bestimmen, welche Zellen zu formatieren). Dies führt zu dem folgenden Dialog:
Sie können sehen, wie die Regeln erstellt werden. Sie müssen Formeln sein, die entweder wahr oder falsch bewerten wird. Verlassen Sie das Dialogfeld, so dass wir die Schaffung neuer Regeln beginnen.
Die erste Regel wird die Beschattung und Grenzen für die obere Reihe der Tabelle erstellen. Wählen A10: AE10 und rufen Sie dann das Dialogfeld oben auf. Wir wollen nur das Format auf die Zellen anzuwenden, wenn sie im „sichtbaren“ Teil der Tabelle sind (das heißt, ist die Säule innerhalb des Bereichs durch die Anzahl der Spalten in B6 angegeben). So wird die Regel sein:
Wir brauchen 1 auf die Anzahl der Spalten hinzufügen, weil wir Spalte A sind inklusive, die nicht spezifiziert ein Teil der 30 Spalten ist. Wenden Sie ein Format durch die Schaltfläche Format klicken und gelten einige Grenzen, Hintergrundschattierung, und eine fette Schrift. Klicken Sie auf OK, um die Formatierungsregel anzuwenden. Um zu testen, zu ändern, B6, sagen wir, 10 und stellen Sie sicher, dass nur A10: K10 dieses Format haben. Wenn Sie B6 bis 15 ändern, dann A10: P10 sollte das Format hat.
Für die zweite Regel wollen wir eine Grenze zum rechten Rand der Spalte A bewerben, aber nur die Zeilen, die in der Tabelle sichtbar sein sollen. Wählen A10: A70 und dann diese Formatierungsregel erstellen:
Diese Regel überprüft, um zu sehen, dass es in Spalte A ist, und dass die Zeilennummer im sichtbaren Bereich. Wenden Sie ein Format mit einer Grenze am rechten Rand nur, und stellen Sie die Schrift zu fett.
Die dritte Regel wird alles außerhalb des sichtbaren Teils der Tabelle verstecken, wie durch die Werte in B5 definiert: B6. Wählen Sie die gesamte Tabelle (A10: AE70) und dann diese Regel verwenden:
In dem Format, stellen Sie die Schriftfarbe auf Weiß. Das wird die Daten erhalten, aber es wird unsichtbar, weil die Schriftfarbe die gleiche wie die Hintergrundfarbe ist.
Die vierte und letzte Regel wird die letzte sichtbare Zeile, sondern nur in sichtbaren Spalten unterstreicht. Die Regel lautet:
Tragen Sie eine Grenze nach unten auf die Schaltfläche Format verwenden. Beachten Sie, dass, wenn einige Ihrer Regeln nicht richtig funktionieren, können Sie jederzeit zurückgehen und sie bearbeiten, indem Regeln aus der bedingten Formatierung Dropdown-verwalten wählen.
Anwenden von Datenvalidierungsregeln
Für den letzten Schliff, wollen wir sicherstellen, dass ein Benutzer nicht Daten, die unerwartet in B1 eingeben: B6. Zum Beispiel wollen wir nicht, ihnen einen negativen Zinssatz in B1 eingeben. Wir können dies tun, indem sie einige Datenvalidierungsregeln auf diese Zellen anwenden.
Wählen Sie B1 und klicken Sie dann auf die Datenüberprüfung Schaltfläche auf der Registerkarte Daten. Dadurch wird das folgende Dialogfeld starten:
Richten Sie ähnliche Regeln für B2: B6 wie folgt:
Das vervollständigt die PVIF Tabelle. Die anderen sind fast so gut gemacht!
Erstellen der Tabelle FVIF
Die FVIF (Future Value Zins Factor) Tabelle ist identisch mit der PVIF Tabelle, mit der Ausnahme, dass sie die Funktion FV () in A10 und anderen Text in A9 verwendet. So kopieren wir einfach das PVIF Arbeitsblatt. Rechtsklick auf das Blattregister für die PVIF Blatt und wählen Sie „Verschieben oder Kopieren“ aus dem Menü. Seien Sie sicher, dass das Erstellen einer Kopie Feld am unteren Rand des Dialogfensters klicken.
Ändern Sie den Text in A9 auf "Future Value von $ 1 Angelegte Heute am Ende von N Perioden (FVIF)" und die Formel in A10 = FV (F1, F2,0, -1). Das ist es. Hier ist ein kleines Stück des FVIF Tisches, so dass Sie sicher sein können, dass Sie korrekt sind:
Erstellen der Tabelle PVIFA
Die PVIFA (Present Value Zinsfaktor Annuity) Tabelle ist nur etwas komplizierter, aber starten, indem Sie eine weitere Kopie der PVIF Tabelle erstellen. Die Komplikationsrate ist, weil wir die Tabelle wollen durch reguläre Renten und Annuitäten zu behandeln.
Beginnen, indem einige Daten in Zeile 7 A7 Zugabe eingeben „Type“ (für die Art der Renten). In B7 werden wir eine weitere Datengültigkeitsregel eingeben. Klicken Sie auf B7 und dann auf die Schaltfläche Datenvalidierung. Dieses Mal wollen wir setzen die zur Liste zulassen und die Souce dann auf „Regular, Due“ (Sie die Anführungszeichen nicht geben, aber das Komma enthalten). Dies wird den Benutzer eine Dropdown-Liste, aus dem sie die Art der Rente wählen.
Für den Text in A9 müssen wir etwas anderen Text in Abhängigkeit von der Art der Rente angeben. Wir werden die folgende Anweisung IF () verwenden:
= IF (B7 = "Regular", "Present Value einer Annuität von $ 1 pro Periode für N Perioden (PVIFA)", "Present Value eines Annuity Aufgrund von $ 1 pro Periode für N Perioden (PVIFAd)")
Schließlich wird in A10 werden wir die Funktion PV () wieder verwenden, aber dieses Mal werden wir FV 0 und PMT auf 1 gesetzt Darüber hinaus müssen wir die Art Argument an die Funktion spezifizieren. Für die regelmäßigen Annuitäten ist dieses Argument 0, aber für Annuitäten ist 1. Die Formel in A10 ist:
Hier ist ein Ausschnitt aus der Tabelle, da es für die regelmäßigen Renten erscheint:
Wenn Sie auf eine Rente wegen (in B7) ändern dann als Referenz, sollten Sie 1.000 in B11 und 1,9901 in B12 erhalten.
Erstellen der Tabelle FVIFA
Um die FVIFA (Future Value Zinsfaktor Annuity) Tabelle zu erstellen, starten Sie durch die PVIFA Tabelle zu kopieren, die wir oben erstellt. Die Tische sind fast identisch, mit Ausnahme des Text in A9 und die Formel in A10.
Für den Text in A9 verwenden Sie die folgende Anweisung IF ():
= IF (B7 = "Regular", "Future Value einer Annuität von $ 1 pro Punkt am Ende von N Perioden (FVIFA)", "Future Value eines Annuity Aufgrund von $ 1 pro Punkt am Ende von N Perioden (FVIFAd) „)
In A10, müssen wir die PV () Funktion FV () wie folgt ändern:
Beachten Sie, dass wir noch etwas andere Formeln brauchen, je nach der Art der Rente, wie oben beschrieben. Ihre FVIFA Tabelle wie die unten aussehen sollte:
Als Referenz, wenn Sie B7 auf eine Rente wegen ändern, sollten Sie 1,0100 in B11 und 2,0301 in B12 erhalten.