Utilisation du Solveur dans Microsoft Excel pour la planification financière
■■ Puis-je utiliser Solver pour vérifier l’exactitude de la fonction Excel VPM ou pour déterminer les versements hypothécaires pour un taux d’intérêt variable?
■■ Puis-je utiliser Solver pour déterminer combien d’argent je dois épargner pour la retraite?
| Le solveur peut être un outil puissant pour analyser les problèmes de planification financière. Dans bon nombre de ces types de problèmes, une quantité telle que le solde impayé d’un prêt ou le montant d’argent nécessaire à la retraite change avec le temps. Prenons par exemple une situation dans laquelle vous empruntez de l’argent. Étant donné que seule la partie sans intérêt de chaque paiement mensuel réduit le solde du prêt impayé, vous savez que l’équation suivante (équation 1) est vraie:
(Solde du prêt impayé à la fin de la période t) = (Solde du prêt impayé au début de la période t) – [(Paiement du mois t) – (Intérêt du mois t payé)] Supposons maintenant que vous épargniez pour la retraite. Jusqu’à votre retraite, vous déposez au début de chaque période (disons des années égales) une somme d’argent dans votre compte de retraite, et pendant l’année, votre fonds de retraite est investi et reçoit un rendement d’un certain pourcentage. Pendant la retraite, vous retirez de l’argent au début de chaque année et votre caisse de retraite reçoit toujours un retour sur investissement. Vous savez que l’équation suivante (équation 2) décrit la relation entre les contributions, les retraits et le rendement: (Épargne-retraite à la fin de l’année t + 1) = (Épargne-retraite à la fin de l’année t + cotisation de retraite au début de l’année t + 1 – Année t + 1 retrait de retraite) * (Retour sur investissement gagné au cours de l’année t + 1) La combinaison de relations de base telles que celles-ci avec Solver vous permet de répondre à une myriade d’inter-problèmes de planification financière. |
|
Puis-je utiliser Solver pour vérifier l’exactitude de la fonction Excel VPM ou pour déterminer les paiements hypothécaires pour un taux d’intérêt variable?
Rappelez-vous qu’ à l’article 9, «Plus de fonctions financières Excel», vous avez trouvé que le paiement mensuel (en supposant que les paiements ont lieu à la fin d’un mois) sur un prêt de 10 mois de 8 000 $ à un taux d’intérêt annuel de 10% était de 1 037,03 $ . Auriez-vous pu utiliser Solver pour déterminer votre paiement mensuel? Vous trouverez la réponse dans la feuille de calcul PMT By Solver du fichier , qui est illustré à la figure ci-dessous.
FIGURE 1 :Il s’agit du modèle de solveur pour calculer le paiement mensuel d’un prêt.
La clé de ce modèle est d’utiliser l’équation 1 (présentée plus haut dans le chapitre) pour suivre le solde de départ mensuel. La cellule cible du solveur est de minimiser le paiement mensuel. La cellule qui change est le paiement mensuel. La seule contrainte est que le solde final du mois 10 est égal à 0.
Entrez le solde de départ dans la cellule B5. Vous pouvez saisir un paiement mensuel d’essai dans la cellule C5, puis copier le paiement mensuel dans la plage C6: C14. Parce que vous supposez que les paiements ont lieu à la fin de chaque mois, des intérêts sont engagés sur le solde au début du mois. Le taux d’intérêt mensuel (la cellule C1 est intitulée Taux) est calculé en D1 en divisant le taux annuel de 0,08 par 12. Les intérêts payés chaque mois sont calculés en copiant la formule de taux * B5 de la cellule D5 à D6: D14. Chaque mois, cette formule calcule l’intérêt comme .006666 * (solde d’ouverture du mois). En copiant la formule (B5– (Paiement – D5)) de la cellule E5 à E6: E14, vous utilisez l’équation 1 pour calculer le solde de fin de chaque mois. Étant donné que (Solde de début du mois t + 1) = (Solde de fin du mois t), le solde de début de chaque mois est calculé en copiant la formule = E5 de la cellule B6 à B7: B14.
Vous êtes maintenant prêt à utiliser Solver pour déterminer le paiement mensuel. Pour voir comment vous avez configuré le
Dans la boîte de dialogue Paramètres du solveur, consultez la figure ci-dessous.
FIGURE 2 : La boîte de dialogue Paramètres du solveur est configurée pour déterminer les versements hypothécaires.
L’objectif est de minimiser le paiement mensuel (cellule C5). Notez que la cellule qui change est la même que la cellule cible. La seule contrainte est que le solde de fin du mois 10 doit être égal à 0. L’ajout de cette contrainte garantit le remboursement du prêt. Après avoir choisi le moteur Simplex LP et sélectionné l’option de variables non négatives, le solveur calcule un paiement de 1 037,03 $, qui correspond au montant calculé par la fonction Excel NPM.
Ce modèle est linéaire car la cellule cible est égale à la cellule changeante et la contrainte est créée en ajoutant des multiples de cellules changeantes.
Il convient de mentionner que lorsque les modèles Solver impliquent de très grands ou très petits nombres, Solver pense parfois que les modèles linéaires ne sont pas linéaires. Pour éviter ce problème, il est recommandé de sélectionner Utiliser la mise à l’échelle automatique dans la boîte de dialogue Options. Cela devrait garantir que Solver reconnaît correctement les modèles linéaires comme étant linéaires.
Puis-je utiliser Solver pour déterminer combien d’argent je dois épargner pour la retraite?
En utilisant l’équation 2 (présentée plus haut dans le chapitre ), vous pouvez facilement déterminer combien d’argent une personne doit épargner pour sa retraite. Voici un exemple.
Vous planifiez votre retraite et au début de cette année et au cours des 39 prochaines années, vous verserez de l’argent à votre caisse de retraite. Chaque année, vous prévoyez d’augmenter votre cotisation de retraite de 500 $. Lorsque vous prendrez votre retraite dans 40 ans, vous prévoyez de retirer (au début de chaque année) 100 000 $ par année pendant 20 ans. Vous faites les hypothèses suivantes sur les rendements de votre portefeuille de placements de retraite:
■■ Au cours des 20 premières années de votre investissement, les investissements gagneront 10% par an.
■■ Pendant toutes les autres années, vos investissements gagneront 5% par an.
Vous supposez que toutes les contributions et tous les retraits ont lieu au début de l’année. Compte tenu de ces hypothèses, quel est le moins d’argent que vous pouvez cotiser cette année et qui a encore suffisamment d’argent pour effectuer vos retraits de retraite?
Vous pouvez trouver la solution à cette question sur la feuille de calcul Retire dans le fichier, illustré à la figure ci-dessous. Notez que de nombreuses lignes sont masquées dans le modèle.
Cette feuille de travail suit simplement votre solde de retraite au cours de chacune des 60 prochaines années. Chaque année, vous gagnez le taux d’intérêt indiqué sur le solde de la retraite. Commencez par saisir une valeur d’essai pour le paiement de l’année 1 dans la cellule C6. La copie de la formule C6 + 500 de la cellule C7 à C8: C45 garantit que la cotisation de retraite augmente de 500 $ par an au cours des années 2 à 40. Inscrivez dans la colonne D le rendement présumé de vos investissements pour chacune des 60 prochaines années. Dans les cellules E46: E65, entrez la valeur annuelle Retrait de 100 000 $ pour les années 41 à 60. Copie de la formule (B6 + C6 – E6) * (1 + D6) de F6 à F7: F65 utilise l’équation 2 pour calculer le solde du compte de retraite de fin d’année. Copie de la formule = F6 de la cellule B7 vers B8: B65 calcule le solde de départ pour les années 2 à 60. Bien sûr, le solde initial de l’année 1 est 0. Notez que la valeur 6.8704E-07 dans la cellule F65 est d’environ 0, avec le différence le résultat d’une erreur d’arrondi.
FIGURE 3: Voici les données de planification de la retraite qui peuvent être configurées pour l’analyse avec Solver.
La boîte de dialogue Paramètres du solveur pour ce modèle est illustrée à la figure ci-dessous. Vous souhaitez minimiser votre contribution à la première année (cellule C6). La cellule changeante est également votre contribution pour l’année 1 (cellule C6). Assurez-vous de ne jamais manquer d’argent pendant votre retraite en ajoutant la contrainte F46: F65> = 0 pour que le solde de fin des années 41 à 60 ne soit pas négatif.
FIGURE 4 : La boîte de dialogue Paramètres du solveur est configurée pour le problème de retrait.
Après avoir choisi le moteur Simplex LP et sélectionné Rendre les variables non contraintes non négatives dans la boîte de dialogue Paramètres du solveur, cliquez sur Résoudre dans la boîte de dialogue Paramètres du solveur; vous constatez que la contribution de la première année devrait être de 1 387,87 $.
Ce modèle est linéaire car la cellule cible est égale à la cellule changeante et la contrainte est créée en ajoutant des multiples de cellules changeantes. Notez que le retour sur investissement n’étant pas le même chaque année, il n’existe aucun moyen simple d’utiliser les fonctions financières d’Excel pour résoudre ce problème. Le solveur fournit un cadre général qui peut être utilisé pour analyser les problèmes de planification financière lorsque les taux hypothécaires ou les rendements des investissements ne sont pas constants.