nper: Total number of periods for which compounding needs to be done. Now rate and nper
should be on the same scale. If interest rate is monthly, then nper should be in months. If
interest rate is quarterly, then nper should be in quarter. If interest rate is annual, then nper
should be in years.
pv : This is the initial principal and it has to be specified in -ve. (Note, I have already
discussed significance of +ve and -ve in many previous tips on Financial Functions.)
The formula used in below picture for Monthly
=FV(B1/12,B3*12,0,-B2)
The formula used in below picture for Quarterly
=FV(F1/4,F3*4,0,-F2)
The formula used in below picture for Yearly
=FV(J1,J3,0,-J2)
The Compounded Balance Calculator can be downloaded from
http://eforexcel.com/wp/wp-content/uploads/2014/12/Compounded-Interest-
Calculator.xlsx
63. Financial Function – Calculate Effective Interest
You are applying for a loan and an interest rate has been quoted. The interest rate which is
quoted is called "Nominal Interest Rate". They will quote Nominal Interest Rate in yearly
terms. Hence, if they quote 12% interest for a loan, this is yearly figure. Now, you generally
pay EMIs every month. They simply say that you need to pay 1% monthly interest which
has been derived by annual interest rate / 12 which 12%/12=1% in this case.
But actually interest rate of 1% (nominal monthly interest rate) is compounded every
month, hence your effective interest rate per year becomes higher. But lending financial
institutions doesn't quote this higher rate as it will make your loan cost look higher.
To calculate Effective Interest Rate , Excel has provided a function called EFFECT.
