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