"=days360(start_date,end_date)" 30 days 1/02/00 1/03/00 "=PMT(rate,nper,pv,fv,type)" fv and type are optional arguments "Purchase a house with a mortgage amount of $250,000 and a flat rate mortgage of" 30 years at 10%. "-$2,193.93" "=PMT(0.1/12,30*12,250000)" "Because you are making monthly payments, you have to convert the interest rate and " term to months. 0.1 rate 30 nper (term) "250,000" pv (amount of loan) "-$2,193.93" "=PMT(A40/12,C40*12,E40)" "You want to save $12,000 in 8 years at an interest rate of 6% p.a." How much should I save per month? $97.70 "=PMT(0.06/12,12*8,,-12000)" The extra comma means there is no fv. How much money can I borrow? "=PV(rate,nper,pmt,fv,type)" fv and type are optional arguments "If I can afford car payments of $300 a month at 9% interest for 4 years," how much can I afford to borrow? "$12,055.43" "=PV(0.09/12,4*12,-300)" "Buy an insurance policy worth $25,000. The policy earns an annual " interest rate of 8% and returns to you a $250 monthly payment for the next 15 years. Is this a good investment? "$26,160.15" "=PV(0.08/12,15*12,-250)" How Many Payments Do I Have To Make? The NPER function tells you how many payments you have to make on a given loan. "=NPER(rate,pmt,pv,fv,type)" fv and type are optional arguments "You are offered a loan of $15,000 at an annual interest rate of 15%." Monthly payments are $557.51. How long will it take to pay off the loan? 33.00058347 "=NPER(0.15/12,-557.51,15000)" It would take 33 months. What's the Interest Rate I Need? "=RATE(nper,pmt,pv,fv,type,guess)" fv and type and guess are optional arguments "First Dealer - A five year, $40,000 loan with monthly payments of $899" "Second Dealer - A five year, $40,000 loan at 13% p.a." Which is better? Fisrt Dealer 1.0379% monthly interest rate "=RATE(5*12,-899,40000)" 12 months 12.4549% p.a. How much interest did I pay during a single period? "=IPMT(rate,per,nper,pv,fv,type)" fv and type are optional arguments How much intrest paid during the third year of a five year loan? "One loan payment per year, at the start of the year." "Annual interest rate for this $100,000 loan is 12%." "$7,138.84" "=IPMT(0.12,3,5,-100000,,1)" "Interest paid during the last month of a $50,000 five year loan." 12 end-of-month payments each year @ 10% p.a. $8.78 "=IPMT(0.1/12,60,5*12,-50000)" How much Principal paid during a single period? "Should you ever want to pay off a loan early, figure how much you paid on the principal." "Then subtract that amount from the original loan, and the remainder is what's still due." "=PPMT(rate,per,nper,pv,fv,type)" fv and type are optional arguments "IPMT and PPMT use the same arguments. When the per argument is the same," "one will show the interest paid for that period, the other shows the paid principal." Add them together and you get the total loan payment for that period. How much principal paid during the third year of a 5 year loan? One loan payment at the start of the year. "$100,000 @12% p.a." Principal "$17,629.89" "=PPMT(0.12,3,5,-100000,,1)" Interest "$7,138.84" "=IPMT(0.12,3,5,-100000,,1)" "$24,768.73" Total loan payment for 3rd year Amortization Table Loan Amount " $100,000.00 " Interest Rate 12% # of Years 5 Year Interest Principal Balance 1 $0.00 "$24,768.73" " $75,231.27 " 2 "$9,027.75" "$15,740.97" " $59,490.30 " 3 "$7,138.84" "$17,629.89" " $41,860.41 " 4 "$5,023.25" "$19,745.48" " $22,114.93 " 5 "$2,653.79" "$22,114.93" $0.00 "=FV(rate,nper,pmt,pv,type)" pv and type are optional arguments This type of investment where you make equal monthly deposits into an account for a fixed perios of time. $100 per month for 8 years @6% pa "$12,282.85" "=FV(0.06/12,8*12,-100)" The monthly interest rate is 0.06/12. The number of deposits over 8 tears is 8 x 12 = 96. "If account already holds $5,000..." "$20,353.57" "=FV(0.06/12,8*12,-100,-5000)" "=NPV(rate,value1,value2,...)" Value2... are optional Value1 is a cell range that contains the series of cash flows - the money you pay out and the money you get back over the life of the investment. Value2 is the optional argument. It is used as payment and income like value1. The NPV calculation is for future cash flows. "If your cash flow occurs at the beginning of the first period, the first value must be" "added to the NPV result, not included in the values argument." Project A Project B Project C Year 1 "-$50,000" "-$50,000" "-$50,000" Year 2 "$6,000" "$10,000" $0 Year 3 "$7,000" "$10,000" $0 Year 4 "$8,000" "$10,000" $0 Year 5 "$9,000" "$10,000" $0 Year 6 "$10,000" "$10,000" $0 Year 7 "$15,000" "$10,000" $0 Year 8 "$40,000" "$25,000" "$120,000" NPV "$1,319.32" $532.76 $891.40