Solveur Excel et optimisation : Combien investir pour la retraite

J’ai déjà publié sur ce blogue un article concernant le add-in Excel “Solveur” (voir article: Excel: Résoudre un cas de maximisation grâce au solveur). Cet add-in Excel permet notamment de résoudre des problèmes d’optimisation. Dans le présent article, j’utilise le solveur pour répondre à une question que plusieurs se posent: Combien dois-je investir aujourd’hui, si je souhaite obtenir 100,000$ par année à la retraite? Nous aurions pu utiliser d’autres méthodes de calculs que celle présentée dans cet article mais l’objectif était d’illustrer un autre cas d’optimisation avec le solveur d’Excel, tout en répondant à une question d’intérêt.

Structure du fichier Excel

Dans un premier temps, il faut bien préparer le fichier Excel à répondre à la question. Il faut notamment déterminer le nombre d’années que l’on prévoit à la retraite et le nombre d’années que l’on prévoit vivre une fois à la retraite (1ère colonne de données dans l’image ci-bas). Dans notre exemple, nous avons supposé une cotisation annuelle sur 40 ans et une retraite de 20 ans. Il faut également déterminer la contribution que l’on souhaite faire à chaque année. Est-ce que celle-ci sera fixe? Est-ce que celle-ci évoluera au fil du temps? Dans notre exemple ci-bas, nous avons déterminé que cette contribution allait augmenter de 500$ par année. Il ne faut pas oublier de préciser les retraits qui seront faits annuellement (habituellement, ces retraits seront faits seulement lors de la retraite). Il faut enfin préciser le taux de rendement annuel attendu. Ce taux de rendement peut être fixe ou varier au fil du temps et il peut s’avérer intéressant de tester plusieurs hypothèses.

Problème d’optimisation avec le solveur d’Excel

Notre problème d’optimisation consiste à trouver la valeur de la cellule D12 qui permettra d’obtenir un solde de 0 à la cellule G71. Il faut également que le solde annuel soit toujours positif (cellules G12 à G71).

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

Solution finale de notre cas d’optimisation avec le solveur d’Excel

Le “Solver” nous indique qu’il faudrait que l’on contribue aujourd’hui 1,388$ et que l’on augmente cette cotisation de 500$ par année, pendant 40 ans (aux taux de rendements indiqués), si l’on souhaite retirer 100,000$ par année, pendant 20 ans, à la retraite.

Et vous? Dans quels contextes utilisez-vous le “Solver”?

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour couvrir plusieurs aspects avancés de la modélisation financière, qui sont indispensables afin de créer des modèles financiers dynamiques et flexibles, suivez notre formation Excel – Modélisation financière niveau 2.

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 2 :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.