Financial Functions for Excel
Financial Functions for Excel
1) To calculate the rate of a four year $8,000 loan with monthly payments of $200
0.77%
RATE (48 – 200,8000) (nper = 48, pmt – 200, pv = 8000, fv = 0, type = 0,)
2) To calculate the payment for a loan on constant payments and a constant interest rate
a) Monthly payment on a $10,000 loan at an annual interest rate of 8%, paid off in 10 months
($1,037.03)
PMT (0.08/12, 10, 10000)
PMT (rate = 8%, nper = 10 mo , pv = 10000 , fv = 0 , type = 0)
b) Same loan if payments are due at the beginning of the period, the payment is
($1,030.16)
PMT (0.08/12,10,10000,0,1)
PMT (rate = 8% , nper = 10mo, pv = 10000, fv =0, type = 1)
c) You loan someone $5,000 at 12% interest for 5 months. Their monthly payments are
$1030.20
PMT (0.12/12,5,-5000)
PMT (rate = 12%, nper=5mo, pv=5000, fv=0, type=0)
d) You want to save $50,000 in 18 years. Assume a 6% annual interest rate, how much would you have to put away each month
($129.08)
PMT (0.06/12, 18*12, 0, 50000)
PMT (rate = 6%, nper = 216mo, pv = 0, fv = 50000, type = 0)
The financial function FV returns the value of an investment based on periodic, constant payments and a constant interest rate –cash out is negative, cash in is positive
You deposit $1000 in a savings account earning 6% annual interest compounded monthly. You plan to deposit $100 at the beginning of each month for the next 12 months. How much money will you have at the end of 12 months?
$2301.40
FV (0.06 / 12, 12, -100, -1000, 1)
FV ( rate = 6%, nper = 12, pmt = -100, pv = -1000, type = 1)
Where: Rate = 6% / 12; # of months = 12 ; monthly payments = $100; initial deposit = $1000, Type = 1 meaning that payments are made at the beginning of each month
