Docstoc

Spreadsheet examples - Briar Cliff University

Document Sample
Spreadsheet examples - Briar Cliff University Powered By Docstoc
					#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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:11/22/2011
language:English
pages:20