Les 100 formules Excel/ série 8: fonctions pour la finance

Dans ce tutoriel formules Excel traite les fonctions Excel pour la finance

Fonction financière – Calculer les intérêts composés

Dans le cadre de nos cours de Mathématiques dans notre enfance, nous avions appris le Compounded
Intérêt. La fameuse formule dont on se souvient est
Solde composé = Principal x (1+taux)^N
C’est un travail assez facile à faire dans Excel. La formule à utiliser est FV.
L’aide d’Excel décrit FV comme “Renvoie la valeur future d’un investissement sur la base de données périodiques,
des paiements constants et un taux d’intérêt constant ».
La syntaxe de FV est FV(rate,nper,pmt,[pv],[type])
Vous n’avez besoin que de 3 éléments d’information pour le solde composé.
taux : taux d’intérêt sur lequel la capitalisation doit être effectuée

nper : nombre total de périodes pour lesquelles la capitalisation doit être effectuée. Maintenant taux et nper
doit être à la même échelle. Si le taux d’intérêt est mensuel, alors nper doit être exprimé en mois. Si
le taux d’intérêt est trimestriel, alors nper doit être trimestriel. Si le taux d’intérêt est annuel, alors nper
devrait être en années.
pv : il s’agit du principal initial et il doit être spécifié dans -ve. (Attention, j’ai déjà
discuté de la signification de +ve et -ve dans de nombreux conseils précédents sur les fonctions financières.)
La formule utilisée dans l’image ci-dessous pour Mensuel
=VF(B1/12,B312,0,-B2) La formule utilisée dans l’image ci-dessous pour trimestriel =FV(F1/4,F34,0,-F2)
La formule utilisée dans l’image ci-dessous pour Annuel
=FV(J1,J3,0,-J2)

Le calculateur de solde composé peut être téléchargé à partir de

http://eforexcel.com/wp/wp-content/uploads/2014/12/Compounded-Interest-Calculator.xlsx

Fonction financière – Calculer l’intérêt effectif
Vous demandez un prêt et un taux d’intérêt a été proposé. Le taux d’intérêt qui est coté est appelé “Taux d’intérêt nominal”. Ils citeront le taux d’intérêt nominal chaque année termes. Par conséquent, s’ils citent 12% d’intérêt pour un prêt, il s’agit d’un chiffre annuel. Maintenant, vous généralement
payer les EMI chaque mois. Ils disent simplement que vous devez payer 1 % d’intérêts mensuels, ce qui a été dérivé du taux d’intérêt annuel / 12 dont 12%/12=1% dans ce cas.
Mais en fait, le taux d’intérêt de 1 % (taux d’intérêt mensuel nominal) est composé chaque mois, votre taux d’intérêt effectif annuel devient donc plus élevé. Mais les prêts financiers.
Les institutions ne citent pas ce taux plus élevé car cela fera paraître le coût de votre prêt plus élevé.
Pour calculer le taux d’intérêt effectif, Excel a fourni une fonction appelée EFFECT.

Excel décrit EFFET – Renvoie le taux d’intérêt annuel effectif, compte tenu du taux nominal.
taux d’intérêt annuel et le nombre de périodes de capitalisation par an.
La syntaxe de EFFECT est EFFECT(nominal_rate, npery).
Taux nominal – Taux d’intérêt annuel
npery – Périodes de capitalisation dans une année. Pour les paiements mensuels, il est de 12. Pour les paiements trimestriels
paiements, c’est 4.
Dans l’image ci-dessous, le taux d’intérêt effectif est de 12,68 % pour un paiement mensuel. Ceci peut être une petite différence pendant un an ou deux, mais si vous prenez une hypothèque sur le logement, c’est-à-dire pour
20 ans, ça fait une sacrée différence.
La formule utilisée est =EFFET(B1,B2)
Maintenant, si vous faites un investissement et effectuez des paiements mensuels, vous obtiendrez le rendement annuel de 12,68 % contre 12 % si vous effectuez un paiement annuel.
Le calculateur de taux d’intérêt effectif peut être téléchargé sur
http://eforexcel.com/wp/wp-content/uploads/2014/12/Effective-InterestCalculator.xlsx

Abréger les prénoms

Si vous avez des noms donnés comme – Smith Johnson
Liz Lotte
Christy diadème Lewisk
John
Et vous devez produire des abréviations ou des acronymes pour eux comme ci-dessous dans toutes les capitales
Smith Johnson – SJ
Liz lotte – LT
Christy diadème Lewisk – CTL
Jean-J
Ensuite, vous pouvez utiliser la formule suivante pour la même chose jusqu’à 3 mots dans le nom –

=UPPER(TRIM(LEFT(A1,1)&MID(A1,FIND(” “,A1&”
“)+1,1)&MID(A1,FIND(““,SUBSTITUTE(A1&” “,” “,”“,2))+1,1)))

Explication de la formule

1. LEFT(A1,1) – Extrait la première lettre du prénom
2. MID(A1,CHERCHE(” “,A1&” “)+1,1) –
   TROUVER(” “,A1&” “) – Rechercher trouve le premier espace dans le nom donné pour localiser le début du
   deuxième nom. ” ” a été concaténé à la fin de A1 de sorte que s’il n’y a que le prénom, FIND ne donnera pas d’erreur car il trouvera toujours les blancs. +1 a été ajouté pour démarrer le MID
   position à partir de laquelle commence le deuxième prénom.
3. MOYEN(A1,TROUVER(““,SUBSTITUER(A1&” “,” “,”“,2))+1,1))
   SUBSTITUTE(A1&” “,” “,”*”,2) remplacera le deuxième blanc par un *, nous pouvons donc trouver le position de * pour localiser le début du nom de famille. Comme au 2 ci-dessus, un double espace ” ” a été ajouté
   dans A1 pour que FIND trouve toujours le deuxième espace. +1 a été ajouté pour démarrer le MID
   position à partir de laquelle le nom de famille commence.
4. TRIM supprimera tous les blancs insérés à cause de 2 ou 3.
5. UPPER convertira la chaîne en majuscules.
   Remarque – Si vous n’utilisez pas la concaténation d’un espace simple et d’un espace double comme dans 2 et 3, alors le bloc IFERROR peut être utilisé. Dans ce cas, la formule deviendrait –

=SUPER(COUPER(GAUCHE(A1,1)&SIERREUR(MILIEU(A1,TROUVER(“
“,A1)+1,1),””)&SIERREUR(MID(A1,TROUVER(““,SUBSTITUTION(A1,” “,”“,2))+1,1),””)))

Remarque – Cette technique peut être utilisée pour étendre jusqu’à plusieurs mots. Seul le changement sera en dernier
bloc où vous pouvez remplacer 2 par 3, 4,5 et ainsi de suite dans
IFERROR(MID(A1,FIND(““,SUBSTITUTE(A1,” “,”“,2))+1,1),””) pour les 4ème, 5ème, 6ème mots et
les concaténer…. Par conséquent, pour un maximum de 6 mots, la formule deviendrait
=SUPER(COUPER(GAUCHE(A1,1)&SIERREUR(MILIEU(A1,TROUVER(“
“,A1)+1,1),””)&SIERREUR(MID(A1,TROUVER(““,SUBSTITUTION(A1,” “,”“,2))+1,1),””)
&SIERREUR(MILIEU(A1,TROUVER(““,REMPLACEMENT(A1,” “,”“,3))+1,1),””)&SIERREUR(MID(A1,TROUVER(““,SUBSTITUT(A1,” “,”“,4))+1,1), “”)
&SIERREUR(MID(A1,TROUVER(““,SUBSTITUTION(A1,” “,”“,5))+1,1),””)))

Obtenir le nom de colonne pour un numéro de colonne

Supposons que vous ayez un nombre dans A1 et que vous souhaitiez obtenir le nom de la colonne pour cela.
Donc, si A1=1, vous voulez “A”
Donc, si A1 =26, vous voulez “Z”
Par conséquent, si A1=27, vous voulez “AA” et ainsi de suite.
La formule pour dériver le nom de la colonne serait –
=SUBSTITUER(ADRESSE(1,A1,4),1,””)

Obtenir la plage de colonnes pour un numéro de colonne

Supposons que vous ayez un nombre dans A1 et que vous souhaitiez obtenir la plage de colonnes pour cela.
Donc, si A1=1, vous voulez “A:A”
Donc, si A1 =26, vous voulez “Z:Z”
Par conséquent, si A1=27, vous voulez “AA:AA” et ainsi de suite.
La formule pour dériver la plage de colonnes serait –
=SUBSTITUER(ADRESSE(1,A1,4)&”:”&ADRESSE(1,A1,4),1,””)

Trouver le nième plus grand nombre lorsqu’il y a doublons

Vous connaissez la fonction LARGE qui permet de trouver la nième plus grande valeur. Par conséquent, si vous avez un série comme ci-dessous –

Et vous donnez =LARGE(A1:A10,3), vous obtenez la réponse comme 18
Maintenant, si nous avons une série comme ci-dessous.

Maintenant, vous donnez =LARGE(A1:A10,3) et maintenant le résultat est 24. La raison en est que la grande fonction donne la nième plus grande valeur dans un tableau trié. Par conséquent, la fonction LARGE triera le tableau ci-dessus
comme {24,24,24,22,22,18,18,9} et le 3ème plus grand est 24.
Mais en fait, vous voulez l’unique 3ème plus grand qui est 18 comme réponse.
La formule pour un tel cas serait

=LARGE(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10)<>0,$A$2:$A$10),3)

Extraire la date et l’heure de l’horodatage de la date

Supposons que vous ayez une valeur d’horodatage de date dans la cellule A1
A1 = 14/06/15 22h15
Et vous voulez en extraire la date et l’heure.
Pour extraire la date, utilisez la formule suivante et formatez la cellule de résultat en tant que date
= INT(A1)
Pour extraire l’heure, utilisez la formule suivante et formatez la cellule de résultat en tant qu’heure
= MOD(A1,1)

Convertir un nombre en années et mois

Supposons que vous ayez reçu un nombre dans la cellule A1, disons 26, et que vous souhaitiez l’afficher sous la forme 2 Années et 4 mois, vous pouvez utiliser la formule suivante –
=INT(A1/12)&” Années et “&MOD(A1,12)&” Mois”
Maintenant, un utilisateur peut devenir plus exigeant et il peut dire que si le mois est inférieur à 12, alors les années ne doivent pas être affichées. Par exemple, il pourrait dire que 8 doit être converti en 8
Mois et il ne doit pas être affiché comme 0 ans et 8 mois.
Dans ce cas, la formule serait –
=IF(INT(A1/12)>0,INT(A1/12)&” Années et “,””)&MOD(A1,12)&” Mois”
Désormais, 8 sera affiché sous la forme de 8 mois uniquement et non sous la forme de 0 ans et 8 mois.
Maintenant, l’utilisateur peut demander plus. Il peut dire que lorsque je donne 12, cela s’affiche sous la forme 1 ans et 0 mois et il veut simplement voir 1 ans seulement. Et pour 36 ans, il veut voir seulement 3 ans pas 3 ans 0
Mois. Dans ce cas, la formule devra être modifiée davantage. Maintenant, la formule devient –
=SI(INT(A1/12)>0,INT(A1/12)&” Années “,””)&SI(MOD(A1,12)=0,””,MOD(A1,12)&”
Mois”)
Maintenant, un utilisateur peut venir et peut demander une dernière chose. Il peut dire que si c’est 1 an ou 1
Mois, il ne doit pas être affiché sous forme d’années ou de mois car 1 n’est pas au pluriel. Par conséquent, 25 devrait être affiché comme 2 ans et 1 mois et non comme 2 ans et 1 mois. Par conséquent, 18 ne devrait pas être
affiché comme 1 an et 6 mois mais comme 1 an et 6 mois. De même, 13 devrait être affiché comme 1 an et 1 mois et non comme 1 an et 1 mois.
=SI(INT(A1/12)>0,INT(A1/12)&” Année”&SI(INT(A1/12)>1,”s”,””)&” et
“,””)&MOD(A1,12)&” Mois”&SI(MOD(A1,12)>1,”s”,””)