Valeur temps des tableaux Excel dans l'argent
Êtes-vous un étudiant? Saviez-vous que Amazon offre 6 mois d'Amazon Prime - livraison gratuite de deux jours, des films gratuits et d'autres avantages - aux étudiants? Cliquez ici pour en savoir plus
Pratiquement tous les manuels des finances a, à l'arrière, une série de tableaux qui contiennent des multiplicateurs qui peuvent être utilisés pour calculer facilement les valeurs actuelles ou futures sans la nécessité d'une calculatrice financière. Ces dernières années, ces tableaux ont lentement cédé la place à des calculateurs financiers, mais ils sont encore largement utilisés par certains professeurs et quelques examens professionnels.
Ce tutoriel va vous montrer comment créer ces tables en utilisant Excel. Les tableaux créés ici sont beaucoup mieux que les tables de manuels scolaires, car ils surmonter quelques limitations:
Comme il est indiqué, ces tableaux offrent une grande flexibilité. Cette flexibilité est obtenue en utilisant des fonctionnalités Excel standard telles que la valeur temporelle des fonctions de l'argent. tableaux de données à deux entrées, validation de données et de mise en forme conditionnelle.
En utilisant les tableaux TVM
Valeur temps des tables d'argent sont très faciles à utiliser car ils fournissent un « facteur » qui est multipliée par une valeur actuelle, la valeur future ou le paiement de la rente pour trouver la réponse. Armé de la table appropriée et une façon de multiplier (une calculatrice ou même avec un crayon et du papier), vous pouvez aussi facilement résoudre la valeur temporelle de problèmes d'argent.
L'image ci-dessous montre un extrait d'une table PVIF (facteur d'intérêt Valeur actuelle):
Dans ce cas, la table fournit un facteur qui est multiplié par une valeur future d'un flux de trésorerie d'un montant forfaitaire afin d'obtenir sa valeur actuelle.
Regardons un exemple:
Imaginez que vous devez avoir 5 000 $ trois ans à partir de maintenant et peut gagner 4% par an dans votre compte d'épargne. Combien avez-vous besoin de déposer aujourd'hui pour atteindre votre objectif?
Pour résoudre ce problème, nous multiplions simplement par la valeur de la table de PVIF appropriée la valeur future (5 000 $):
Alors, regardez en bas de la première colonne de la table de la ligne 3 de la période, puis dans la colonne de 4%. Le PVIF est 0,8890 si la réponse est:
PV = 5000 x 0,8890 = 4445
Par conséquent, si vous déposez 4445 $ aujourd'hui dans un compte d'épargne qui rapporte des intérêts de 4% composé annuellement, alors vous aurez 5 000 $ en trois ans.
Mais ce qui se passe si le taux d'intérêt est de 3,5% au lieu de 3% ou 4%? Ensuite, vous devez interpoler parce que 3,5% ne sont pas dans le tableau. Vous pouvez approcher la réponse en faisant la moyenne des valeurs de la table de pVIF pour 3% et 4% et en utilisant cette moyenne pour la PVIF. La moyenne est de 0,90205 donc vous obtiendrez une réponse de 4,510.25 $. La bonne réponse, cependant, est 4,509.71 $ pour que votre réponse serait hors d'environ 0,54 $. Pas trop mal, mais les tables que nous créons ici peuvent facilement avoir le taux d'intérêt exact que vous avez besoin.
Création des tables Facteur d'intérêt
La clé de la création des tables est de comprendre qu'ils sont tous basés sur la valeur temporelle de base de formules d'argent. Par exemple, les facteurs de pVIF du tableau ci-dessus sont calculées en utilisant 1 $ pour la FV dans l'équation de la valeur actuelle:
1 pour FV Le remplacement, 3 N, et 0,04 pour i-nous obtenir 0,8890. C'est la même que celle PVIF que nous avons tiré à l'origine de la table. Puisque nous construisons ces tableaux avec Excel, nous pouvons utiliser ses fonctions intégrées (PV () dans ce cas) au lieu de la formule mathématique.
Tableaux de données à deux entrées
Plutôt que de créer une grande table avec la fonction PV () répétais encore, nous utiliserons la fonction de table de données à deux entrées d'Excel. Cela nous permet d'entrer dans une formule une fois, et il remplira automatiquement la table en fonction des valeurs dans la colonne de gauche et la rangée supérieure de la table. Cette fonction est généralement utilisée pour l'analyse de sensibilité. Par exemple, nous pourrions voir comment la valeur actuelle change lorsque les deux le taux d'intérêt et le nombre de changements de périodes. En fait, c'est-ce que nous faisons ici, sauf que le FV est de 1 $ au lieu d'une autre valeur. L'extrait ci-dessous montre les formules qui sont dans le tableau ci-dessus PVIF de:
Notez que la fonction PV () est utilisée seulement dans le coin supérieur gauche de la table. Le reste de la table est remplie automatiquement lorsque l'on utilise la commande Tableau des données. Il agit en substituant la valeur de la rangée supérieure et la colonne de gauche dans les cellules spécifiées (F1 et F2). Excel fait cela à plusieurs reprises pour remplir le tableau. Tableau recalcul peut être lent pour de grandes tables ou des formules compliquées, donc l'une des options de calcul d'Excel est Automatique À l'exception des tableaux de données. On n'a pas besoin d'utiliser ce paramètre ici, mais vous devez savoir qu'il existe.
Nous verrons comment créer le tableau de données dans la section ci-dessous.
Créer la table PVIF
Notre table PVIF servira de modèle pour chacun des trois autres tables. Une fois que nous obtenons ce travail correctement, nous pouvons simplement copier la feuille de calcul, puis changer la formule qui entraîne la table.
Ouvrez un nouveau classeur et puis créer une feuille de calcul qui ressemble à celui ci-dessous:
Entrez tout exactement comme indiqué, à l'exception de ce qui suit:
- Dans A10 entrer la formule: PV = (F1, F2,0, -1). Telle est la formule qui stimuleront notre table de données. La 0,9901 dans l'image est simplement le résultat courant de la formule.
- Dans B10 entrer la formule: = B1.
- C10 entrez la formule: = B10 + $ B $ 2. Cela « intensifier » le taux d'intérêt. Copiez cette formule dans la AE10 (qui est de 30 colonnes de taux d'intérêt).
- A11 entrez la formule: = B3.
- Dans A12 entrez la formule: = A11 + $ B $ 4. Copiez cette formule vers le bas par A70. Cela « étape » le numéro de la période par le nombre d'unités spécifiées dans B4.
- Ne pas ajouter de l'ombrage dans la ligne 10. Nous allons le faire avec la mise en forme conditionnelle plus tard.
Avant de créer la table de données, je dois expliquer les données dans E1: F2. Ceci est la zone (en particulier, F1 et F2) où Excel remplacera les valeurs de la rangée supérieure et la colonne de gauche pour obtenir les chiffres pour coller dans la table. Vous pouvez essayer vous-même: entrez 4% en F1 et 3 en F2. Notez que la valeur A10 a changé à 0,8890. C'est la même valeur que nous avons utilisé pour la PVIF dans le problème exemple original ci-dessus.
Donc, essentiellement ce qui se passe dans le tableau de données est que Excel bouchera les numéros en F1 et F2, puis recalcule la formule A10. Les résultats seront placés dans un tableau à l'intersection de la rangée et de la colonne appropriée. La fonction tableau () affiche ce tableau dans notre zone de table (B11: AE70).
Pour créer la table de données, nous devons sélectionner A10: AE70 puis passez à l'onglet Données, cliquez sur le bouton What-If analyse, puis choisissez Tableau de données. Vous verrez maintenant la boîte de dialogue suivante:
C'est là que vous dites Excel que cellule F1 est où brancher les numéros de la rangée supérieure de la table (les taux d'intérêt) et que F2 est où brancher les numéros de la colonne de gauche (les numéros d'époque). S'il vous plaît noter que les chiffres réels en F1 et F2 ne comptent pas du tout parce que Excel va les remplacer pour créer la table. Encore une fois, ceci est une table de données à deux entrées. Vous pouvez également créer un tableau de données à une entrée en spécifiant que la cellule d'entrée ligne ou une colonne, mais ne conviendrait pas à l'objectif ici. Votre feuille de calcul devrait ressembler à celui ci-dessous, à l'exception de l'ombrage à la ligne 10.
A ce stade, la table de PVIF est entièrement fonctionnel. Si vous modifiez la valeur dans B1, par exemple, alors les taux d'intérêt dans le tableau changera, et les facteurs d'intérêt seront recalculés ainsi. Cependant, nous devons nettoyer ce un peu pour le rendre plus fonctionnel.
Faites-fantaisie utilisant des techniques de mise en forme
Le formatage est pas seulement pour faire joli votre feuille de calcul. Il peut également ajouter à la fonctionnalité. Dans cette section, nous verrons comment appliquer plusieurs types de règles de validation de mise en forme et des données différentes pour rendre les tables de TVM plus souple et fonctionnelle.
Hiding Texte et mise en forme personnalisée Nombre
Prenons soin d'un couple d'éléments simples en premier. On n'a pas besoin de voir le contenu de E1: F2, donc nous pouvons cacher ces cellules en définissant la couleur de la police au blanc.
, Nous ne devons aussi voir le numéro A10. En fait, il confond les choses simplement. Donc, nous allons appliquer un format personnalisé pour afficher le texte « période » au lieu du résultat de la formule. Notez que cela ne change pas la formule ou le résultat, que ce qui apparaît dans la cellule.
L'application des règles conditionnelles de mise en forme
Mise en forme conditionnelle change l'apparence d'une cellule ou d'une plage lorsque certaines conditions sont remplies. Pour mettre en place les règles, sélectionnez une cellule ou une plage, puis cliquez sur le bouton Mise en forme conditionnelle dans l'onglet Accueil du ruban. Choisissez Nouvelle règle dans le menu. Nous voulons créer des règles qui sont basées sur des formules, afin de choisir le dernier élément dans la liste Type de règle (utiliser une formule pour déterminer les cellules au format). Cela conduit à la boîte de dialogue suivante:
Vous pouvez voir comment les règles sont créées. Ils doivent être des formules qui sera évaluée à Vrai ou faux. La sortie de la boîte de dialogue afin que nous puissions commencer à créer de nouvelles règles.
La première règle crée l'ombrage et les frontières de la rangée supérieure de notre table. Sélectionnez A10: AE10 puis appeler la boîte de dialogue ci-dessus. Nous voulons seulement appliquer le format aux cellules si elles sont dans la partie « visible » de la table (qui est, la colonne est dans la plage spécifiée par le nombre de colonnes B6). Ainsi, la règle sera:
Nous devons ajouter 1 au nombre de colonnes parce que nous intégrons la colonne A, qui ne fait pas partie des 30 colonnes spécifiées. Appliquer un format en cliquant sur le bouton Format et appliquer des bordures, ombrage d'arrière-plan, et caractères gras. Cliquez sur OK pour appliquer la règle de mise en forme. Pour le tester, le changement B6, disons, 10 et assurez-vous que seuls A10: K10 ont ce format. Si vous changez B6 à 15, puis l'A10: P10 doit avoir le format.
Pour la deuxième règle que nous voulons appliquer une bordure sur le bord droit de la colonne A, mais seules les lignes qui sont censées être visibles dans le tableau. Sélectionnez A10: A70 puis créer cette règle de mise en forme:
Cette règle vérifie qu'il est dans la colonne A et que le numéro de ligne est dans la gamme visible. Appliquer un format avec une bordure sur le bord droit seulement, et définir la police en gras.
La troisième règle cachera tout à l'extérieur de la partie visible de la table telle que définie par les valeurs B5: B6. Sélectionnez la table entière (A10: AE70), puis utiliser cette règle:
Dans le format, définissez la couleur de la police au blanc. Cela préservera les données, mais il est invisible parce que la couleur de la police est la même que la couleur d'arrière-plan.
La quatrième et dernière règle soulignera la dernière ligne visible, mais seulement dans les colonnes visibles. La règle est:
Appliquer une bordure au fond en utilisant le bouton Format. Notez que si certains de vos règles ne fonctionnent pas correctement, vous pouvez toujours revenir en arrière et de les modifier en choisissant Gérer les règles dans la liste déroulante Mise en forme conditionnelle.
L'application des règles de validation de données
Pour la touche finale, nous voulons nous assurer que l'utilisateur ne peut pas entrer des données qui est inattendu dans B1: B6. Par exemple, nous ne voulons pas qu'ils entrent dans un taux d'intérêt négatif dans B1. Nous pouvons le faire en appliquant certaines règles de validation des données à ces cellules.
Sélectionnez B1 puis cliquez sur le bouton de validation de données sur l'onglet Données. Cela permet de lancer la boîte de dialogue suivante:
Mettre en place des règles similaires pour B2: B6 comme suit:
Voilà qui termine la table PVIF. Les autres sont presque fait aussi bien!
Création de la table FVIF
Le tableau FVIF (valeur future facteur d'intérêt) est identique à la table de PVIF, sauf qu'il utilise la fonction FV () dans A10 et texte différent dans A9. Nous allons donc simplement copier la feuille de PVIF. cliquez droit sur l'onglet de la feuille pour la feuille de PVIF et choisissez « Déplacer ou Copier » dans le menu. Assurez-vous de cliquer sur la case Créer une copie au bas de la boîte de dialogue.
Modifier le texte A9 à « valeur future de 1 $ Investi Aujourd'hui, à la fin des périodes N (FVIF) » et la formule A10 = FV (F1, F2,0, -1). C'est tout. Voici un petit morceau de la table FVIF afin que vous pouvez être sûr que le vôtre est correct:
Création de la table PVIFA
Le tableau PVIFA (facteur d'intérêt Valeur actuelle Rente) est seulement un peu plus compliqué, mais commencez par créer une autre copie de la table PVIF. La complication est parce que nous voulons que la table pour gérer les rentes régulières et rentes dues.
Commencez par ajouter des données dans la ligne 7. Dans A7 entrer « Type » (pour le type de rente). En B7, nous entrerons dans une autre règle de validation des données. Cliquez B7 puis sur le bouton de validation de données. Cette fois, nous voulons mettre l'Allow à la liste, puis le Souce à « régulier, en raison » (ne tapez pas les guillemets, mais faire la virgule). Cela permettra à l'utilisateur une liste déroulante à partir de laquelle ils peuvent choisir le type de rente.
Pour le texte A9, nous devons spécifier un texte légèrement différent selon le type de rente. Nous utiliserons l'instruction IF () suivant:
= IF (B7 = « normal », « Valeur actualisée d'une rente de 1 $ par période pour les périodes N (PVIFA) », « Valeur actualisée d'une rente En raison de 1 $ par période pour les périodes N (PVIFAd) »)
Enfin, A10, nous utiliserons à nouveau la fonction PV (), mais cette fois nous allons mettre FV à 0 et VPM à 1. De plus, il faut préciser l'argument de type à la fonction. Pour les rentes régulières cet argument est 0, mais il est pour les rentes dues 1. La formule A10 est:
Voici un extrait de la table tel qu'il apparaît pour les rentes régulières:
Si vous passez à une rente due (en B7), puis, à titre de référence, vous devriez obtenir 1.000 à B11 et 1,9901 en vitamine B12.
Création de la table FVIFA
Pour créer la table FVIFA (valeur future du facteur d'intérêt Rente), commencez par copier la table PVIFA que nous avons créé ci-dessus. Les tables sont presque identiques, à l'exception du texte A9 et la formule A10.
Pour le texte A9 utiliser l'instruction IF () suivant:
= IF (B7 = « normal », « valeur future d'une rente de 1 $ par période à la fin des périodes N (FVIFA) », "valeur future d'une Rente En raison de 1 $ par période à la fin des périodes N (FVIFAd) « )
Dans A10, nous devons changer la fonction PV () à FV () comme suit:
Notez que nous avons encore besoin de formules légèrement différentes, selon le type de rente comme décrit ci-dessus. Votre table FVIFA devrait ressembler à celui ci-dessous:
Pour référence, si vous changez B7 à une rente due vous devriez obtenir 1,0100 B11 et B12 2,0301 en.