# Spreadsheet examples - Briar Cliff University by linzhengnd

VIEWS: 6 PAGES: 20

• pg 1
```									#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

```
To top