VIEWS: 6 PAGES: 20 POSTED ON: 11/22/2011 Public Domain
#1 Beginning Balance (PV) $20,000 Ending Balance (FV): Regular Deposit (PMT) $100 Periods Per Year 12 Years 40 Annual Interest Rate 10.00% Ending Balance (FV) Periods (NPER) 480 Rate Per Period (RATE) 0.83333% Beginning (1)/End(0) 1 Use the FV function to determine how much an investment will be worth at the end of a period of time if you make regular, constant deposits at a constant interest rate. If you currently have $20,000 in the bank, and plan to deposit an additional $100 at the beginning (1) of each month (12 periods per year) for 40 years at an annual interest rate of 10.00%, how much will you have at the end of the 40 years? $1,711,691.29 vestment will be worth at the end eposits at a constant interest rate. n to deposit an additional $100 at ear) for 40 years at an annual t the end of the 40 years? Beginning Balance (PV) $20,000 Regular Payment (PMT) Regular Deposit (PMT) $100 Periods Per Year 12 Years 40 Annual Interest Rate 10.00% Ending Balance (FV) Periods (NPER) 480 Rate Per Period (RATE) 0.83333% Beginning (1)/End(0) 1 Use the PMT function to determine the regular payment on a loan, or the amount you must "pay" (deposit) in a savings account to reach a desired savings goal. In this example, we want to find the payment on a $50,000 30- year loan. Payments will be made monthly (12 per year) and the annual interest rate is 12.00%. Payments are at the end of the month (0) and the balance (amount still owed) at the end of the 30 years will be $0. ($0.34) yment on a 30- r year) and the e end of the the end of the Beginning Balance (PV) $5,000 Regular Deposit (PMT) Regular Deposit (PMT) Periods Per Year 12 Years 20 Annual Interest Rate 8.00% Ending Balance (FV) $ 100,000 Periods (NPER) 240 Rate Per Period (RATE) 0.66667% Beginning (1)/End(0) 1 Use the PMT function to determine the regular payment on a loan, or the amount you must "pay" (deposit) in a savings account to reach a desired savings goal. In this example, we want to determine how much we will have to save monthly (12 per year) if we start with $5,000 and the money is invested at an annual rate of 8.00% for 20 years. Our goal is $100,000 and we will make our deposits at the beginnin g of the month. ($127.10) yment on a we will have to 0 years. Our the beginnin g Beginning Balance (PV) Beginning Balance Regular Deposit (PMT) $100,000 Periods Per Year 1 Years 20 Annual Interest Rate 6.00% Ending Balance (FV) 0.00% Periods (NPER) 20 Rate Per Period (RATE) 6.00000% Beginning (1)/End(0) 1 Use the PV function to determine how much a series of future payments would be worth to you today. This is the function that a lottery winner must use to decide whether to take a series of future payments or a lump sum (present value). In this example, we have won a $2,000,000 lottery which will be paid off at $100,000 per year for 20 years. If we could earn 6.00% on a lump sum and withdraw $100,000 per year, how much money would we have to start out with to exactly duplicate the lottery's performance (i.e., get $100,000 per year for 20 years and have exactly $0 left at the end)? This shows us that we would need $1,215,811.65 invested at 6.00% to make this happen. If the lottery offers us more than $1,215,811.65, we should take it, invest the money at 6.00%, withdraw $100,000 each year, and we will STILL have money left over at the end. $1,215,811.65 ies of future e function that a e a series of future y which will be ould earn 6.00% how much money ate the lottery's ars and have would need pen. If the lottery e it, invest the nd we will STILL Beginning Balance (PV) Beginning Balance Regular Deposit (PMT) $ 300 Periods Per Year 12 Years 20 Annual Interest Rate 6.00% Ending Balance (FV) $ 1,000,000 Periods (NPER) 240 Rate Per Period (RATE) 0.50000% Beginning (1)/End(0) 1 Use the PV function to determine how much you would have to start out with to reach a specific goal. In this example, we have a goal of $1,000,000 in 20 years. We will receive a return of 6.00% per year. We will save $300 at the beginning (1) of each month (12 per year) for 20 years. How much would we have to begin with to reach our goal? ($260,012.54) would have 20 years. We ve $300 at Beginning Balance (PV) Amount we can borrow Regular Deposit (PMT) $ 500 Periods Per Year 12 Years 5 Annual Interest Rate 6.00% Ending Balance (FV) $ - Periods (NPER) 60 Rate Per Period (RATE) 0.50000% Beginning (1)/End(0) 0 Use the PV function to determine how much can afford to borrow if you know how much you can afford to make in payments. In this example, we know we can afford payments of $500. The interest rate on the loan will be 6.00% per year, and we will make monthly payments at the END of each month. The loan will be paid back over a period of 5 years, and the balance owed at that time will be $0. How much can we afford to borrow? $25,862.78 uch can afford to borrow if ake in payments.. payments of $500. The r year, and we will make onth. The loan will be paid ance owed at that time will Beginning Balance (PV) $ 20,000 Rate Per Period Regular Deposit (PMT) $ 200 Annual Rate Periods Per Year 12 Years 25 Annual Interest Rate Ending Balance (FV) $ 500,000 Periods (NPER) 300 Rate Per Period (RATE) Beginning (1)/End(0) 0 Use the RATE function to determine the interest rate that will be required to reach a given investment goal, or to determine the rate that you will pay on a loan. This returns the rate per period! You must multiply the rate per period by the number of periods per year to get the annual interest rate! In this example, we are starting with a balance of $20,000 in our savings account. We can afford to deposit $200 at the end (0) of each month. Our goal is to have $500,000 in the account at the end of 25 years. What annual rate do we have to receive to reach this goal? 0.8281% 9.9376% ate that will be required to reach a hat you will pay on a loan. NOTE: ply the rate per period by the erest rate! $20,000 in our savings account. We month. Our goal is to have $500,000 l rate do we have to receive to Beginning Balance (PV) $ 50,000 Rate per period (RATE) Regular Deposit (PMT) $ 300 Annual rate Periods Per Year 12 Years 30 Annual Interest Rate Ending Balance (FV) $ - Periods (NPER) 360 Rate Per Period (RATE) Beginning (1)/End(0) 0 Use the RATE function to determine the rate of interest that you will be paying on a loan. The result will be the RATE PER PERIOD, not the annual rate, so to get the annual rate, you must multiply by the periods per year! In this example, we want to find the rate on a $50,000 30-year loan. Payments will be made monthly (12 per year) and the monthly payments are $300. Payments are at the end of the month (0) and the balance (amount still owed) at the end of the 30 years will be $0. 0.5006% 6.0070% erest that you E PER PERIOD, must multiply year end of the the end of the Beginning Balance (PV) $ 100,000 Periods Regular Deposit (PMT) $ 500 Years Periods Per Year 12 Years Annual Interest Rate 10.00% Ending Balance (FV) $ 1,000,000 Periods (NPER) Rate Per Period (RATE) 0.83333% Beginning (1)/End(0) 0 Use the NPER function to determine how many periods it will take to reach an investmen goal when making regular constant payments at a regular interest rate. In this example, we are starting with $100,000 in our account. We will deposit $500 into the account at the end (0) of every month (12 per year). We are earning interest at an annual rate of 10.00%. Our goal is to have $1,000,000. How long will it take? 227.85 18.99 eriods it will take to reach an investment regular interest rate. our account. We will deposit $500 into year). We are earning interest at an 000. How long will it take? Beginning Balance (PV) $ 100,000 Periods Regular Deposit (PMT) $ 800 Years Periods Per Year 12 Years Annual Interest Rate 5.50% Ending Balance (FV) $ - Periods (NPER) Rate Per Period (RATE) 0.45833% Beginning (1)/End(0) 0 Use the NPER function to determine how many periods it will take to pay back a loan whe making regular constant payments at a regular interest rate. In this example, we are borrowing $100,000. Our payments are $800 at the end every month (12 per year). Our annual interest rate is 5.50%. The ending balance of the loan will be $0. How long will it take? 186.05 15.50 riods it will take to pay back a loan when erest rate. payments are $800 at the end (0) of e is 5.50%. The ending balance of the