# Formula for Calculating Interest Payments

Document Sample

```					1)                PMT means periodic payment (same amount each period)
PMT function calculates the period payment for a loan (For the Borrower or the
Lender). The Amount of each PMT must be the same and the time between each
2)                                   PMT must be the same.
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
3)                              coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For
4)         the Lender the PV is negative, the PMT is positive, and the FV is positive.
Be consistent with your unit of time! If you are calculating monthly payment, you
need monthly interest rate and total number of months! (The period can be monthly,
5)                            quarterly, yearly or any other length).
=PMT(rate = period rate, nper = total number of periods, pv means amount
invested or lent out today, fv means amount received after all the periods have
elapsed or amount paid after all the periods have elapsed, type refers to the PMT:
6)                PMT at end of period = 0, PMT at beginning of period = 1)
=FV(rate = period rate, nper = total number of periods, pmt means periodic
payment, pv means amount invested or lent out today, type refers to the PMT: PMT
7)                    at end of period = 0, PMT at beginning of period = 1)

Borrower Point of View: At an Annual Interest Rate of 6.50% the monthly
1                               PMT paid = \$0.00
Price of Car                  34,799.00 Annual Interest Rate             6.50%
Down Payment                  10,000.00 Monthly Interest Rate
Loan Amount                              Years for Loan                       5
PMT end of period                        Total Months
Monthly Payment                     0.00 Periods per Year                    12
PMT begin of period                      Type, 0 = End, 1 = Beg               1

Lender Point of view: At an Annual Interest Rate of 6.50% the monthly
Price of Car                  34,799.00 Annual Interest Rate             6.50%
Down Payment                  10,000.00 Monthly Interest Rate            0.54%
Loan Amount                  -24,799.00 Years for Loan                        5
Monthly Payment                         Total Months                         60
Monthly Payment                  485.22 Periods per Year                     12

At an Annual Interest Rate of 5.25% and a balloon payment of \$5,000.00 at
the end of 36 months, the monthly PMT = (\$1,225.21) - Borrower's Point of
3                                   View.
Price of Car                50,000.00 Annual Interest Rate         5.25%
Down Payment                  5,000.00 Monthly Interest Rate       0.44%
Loan Amount                 45,000.00 Years for Loan                    3
Balloon Payment              -5,000.00 Total Months                    36
Monthly Payment                        Periods per Year                12

At an Annual Interest Rate of 8.50% and no payments during the first year,
4               the PMT = (\$67,328.25) - Borrower's Point of View.
Loan Amount                1,000,000.00 Annual Interest Rate       8.50%
Years payment is
put off                                1 Period Interest Rate      2.13%
FV after 1 year                          Years for Loan                 6
Period Payment              Total Periods      24
Period Payment   -67,328.25 Periods per Year    4   check
1087748
1)                PMT means periodic payment (same amount each period)
PMT function calculates the period payment for a loan (For the Borrower or the
Lender). The Amount of each PMT must be the same and the time between each
2)                                   PMT must be the same.
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
3)                              coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For
4)         the Lender the PV is negative, the PMT is positive, and the FV is positive.
Be consistent with your unit of time! If you are calculating monthly payment, you
need monthly interest rate and total number of months! (The period can be monthly,
5)                            quarterly, yearly or any other length).
=PMT(rate = period rate, nper = total number of periods, pv means amount
invested or lent out today, fv means amount received after all the periods have
elapsed or amount paid after all the periods have elapsed, type refers to the PMT:
6)                PMT at end of period = 0, PMT at beginning of period = 1)
=FV(rate = period rate, nper = total number of periods, pmt means periodic
payment, pv means amount invested or lent out today, type refers to the PMT: PMT
7)                    at end of period = 0, PMT at beginning of period = 1)

Borrower Point of View: At an Annual Interest Rate of 6.50% the monthly
1                             PMT paid = (\$485.22)
Price of Car                  34,799.00   Annual Interest Rate           6.50%
Down Payment                  10,000.00   Monthly Interest Rate          0.54%
Loan Amount                   24,799.00   Years for Loan                      5
PMT end of period               -485.22   Total Months                       60
Monthly Payment                 -485.22   Periods per Year                   12
PMT begin of period             -482.61   Type, 0 = End, 1 = Beg              1

Lender Point of view: At an Annual Interest Rate of 6.50% the monthly
Price of Car                  34,799.00   Annual Interest Rate           6.50%
Down Payment                  10,000.00   Monthly Interest Rate          0.54%
Loan Amount                  -24,799.00   Years for Loan                      5
Monthly Payment                  485.22   Total Months                       60
Monthly Payment                  485.22   Periods per Year                   12

At an Annual Interest Rate of 5.25% and a balloon payment of \$5,000.00 at
the end of 36 months, the monthly PMT = (\$1,225.21) - Borrower's Point of
3                                   View.
Price of Car                50,000.00 Annual Interest Rate         5.25%
Down Payment                  5,000.00 Monthly Interest Rate       0.44%
Loan Amount                 45,000.00 Years for Loan                    3
Balloon Payment              -5,000.00 Total Months                    36
Monthly Payment              -1,225.21 Periods per Year                12

At an Annual Interest Rate of 8.50% and no payments during the first year,
4               the PMT = (\$67,328.25) - Borrower's Point of View.
Loan Amount                1,000,000.00 Annual Interest Rate       8.50%
Years payment is
put off                            1.00 Period Interest Rate       2.13%
FV after 1 year            1,087,747.96 Years for Loan                  6
Period Payment   -67,328.25 Total Periods      24
Period Payment   -67,328.25 Periods per Year    4   check
1087748
=RATE(nper = total number of periods, pmt means periodic
payment, pv means amount invested or lent out today, fv means
amount received after all the periods have elapsed or amount
paid after all the periods have elapsed, type refers to the pmt:
1)     pmt at end of period = 0, pmt at beginning of period = 1)
Be consistent with your unit of time! If you are calculating
monthly payment, you need monthly interest rate and total
number of months! (The period can be monthly, quarterly, yearly
2)                         or any other length).
3)          Remember, RATE returns the period rate!!!!!!
Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                 Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the
FV is negative. For the Lender the PV is negative, the PMT is
5)                 positive, and the FV is positive.

If you pay points, then you do not get to use all the
cash you borrowed. Therefore, the Rate is really
based on the cash you receive, not the face value of
the loan.
Item   House       Annual Rate                     5.00%
Price   \$200,000 Monthly Rate                     .00417
Down           \$0 Years                                15
Loan    \$200,000 Months                               180
Points        0.02 Periods Per Year                    12
Type (0 = End, 1 =
PMT      -1,581.59 Begin)                               0
Rate                                                   0.053081
=RATE(nper = total number of periods, pmt means periodic
payment, pv means amount invested or lent out today, fv means
amount received after all the periods have elapsed or amount
paid after all the periods have elapsed, type refers to the pmt:
1)     pmt at end of period = 0, pmt at beginning of period = 1)
Be consistent with your unit of time! If you are calculating
monthly payment, you need monthly interest rate and total
number of months! (The period can be monthly, quarterly, yearly
2)                         or any other length).
3)          Remember, RATE returns the period rate!!!!!!
Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                 Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the
FV is negative. For the Lender the PV is negative, the PMT is
5)                 positive, and the FV is positive.

If you pay points, then you do not get to use all the
cash you borrowed. Therefore, the Rate is really
based on the cash you receive, not the face value of
the loan.
Item   House       Annual Rate                     5.00%
Price   \$200,000 Monthly Rate                     .00417
Down           \$0 Years                                15
Loan    \$200,000 Months                               180
Points        0.02 Periods Per Year                    12
Type (0 = End, 1 =
PMT      -1,581.59 Begin)                               0
Rate                        0.053081                   0.053081
Effective Interest Rate is always higher than APR or Nominal Rate when the compounding periods per year
1)                      are greater than 1. Why? Because you are earning interest on interest.

=EFFECT(nominal_rate means APR or Nominal Rate (APR = NOMINAL RATE = period interest rate *
2)     number of compounding periods in 1 year), npery means "number of compounding periods in 1 year")
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is
3)                                                    positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is
4)                             negative, the PMT is positive, and the FV is positive.

Annual Interest Rate =
1 APR = Nominal Rate                                 0.085      check
Period Rate                                                              100 Amount put in bank on day 1
Periods per Year                                       12                    =FV(C7,C8,,-E7)
Effective Annual Rate                                                        =(1+C7)^C8*E7
Effective Annual Rate                                                        =E7*(1+C10)
Effective Interest Rate is always higher than APR or Nominal Rate when the compounding periods per year
1)                      are greater than 1. Why? Because you are earning interest on interest.

=EFFECT(nominal_rate means APR or Nominal Rate (APR = NOMINAL RATE = period interest rate *
2)     number of compounding periods in 1 year), npery means "number of compounding periods in 1 year")
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is
3)                                                    positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is
4)                             negative, the PMT is positive, and the FV is positive.

Annual Interest Rate =
1 APR = Nominal Rate                               0.085        check
Period Rate                                  0.007083                   100   Amount put in bank on day 1
Periods per Year                                    12          108.8390906   =FV(C7,C8,,-E7)
Effective Annual Rate         0.08839090589263550000            108.8390906   =(1+C7)^C8*E7
Effective Annual Rate         0.08839090589263550000            108.8390906   =E7*(1+C10)
APR = Annual Percentage Rate = NOMINAL RATE = period interest rate * number of
1)          compounding periods in 1 year. This is the rate usually calculated by the bank.
Effective Interest Rate is always higher than APR or Nominal Rate when the compounding
periods per year are greater than . Why? Because you are earning interest on interest - The
Effective Interest Rate tells you in percentage terms what the rate really is (You could multiply it
2)                            plus 1 by the principal and get Future Value)

3)      =EFFECT(APR or Nominal Rate, npery means "number of compounding periods in 1 year")

4) =NOMINAL(Effective Interest Rate), npery means "number of compounding periods in 1 year")
=FV(rate = period rate, nper = total number of periods, pmt means periodic payment, pv means
amount invested or lent out today, type refers to the PMT: PMT at end of period = 0, PMT at
5)                                      beginning of period = 1)
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the
6)                                          wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender
7)                  the PV is negative, the PMT is positive, and the FV is positive.
Be consistent with your unit of time! If you are calculating monthly payment, you need monthly
interest rate and total number of months! (The period can be monthly, quarterly, yearly or any
8)                                            other length).

Savings Plan that compounds interest 365 times a year, but you put money in 12
1                                   times a year.
Monthly PMT =                                        -250
x = years                                              25
n for account is =                                    365
APR = i =                                            0.08
Type= 0 or 1 ==>                                        0
check
Solve for EAR first =                                                                  0.0832776
n for PMT =                                              12
Then from EAR, find APR (i) ==>
Then from APR (i), find period Rate ==>
Solve for Future Value =

2                                   MoneyTreeLoaning will:
Allow you to write a check that has a date 25 days in the future for \$250 and will
give you \$200 today (they cash check in 25 days).
What is the APR and EAR?
Days in Future =                                         25
Check Amount =                                          250               FV
You get Today =                                         200               PV
25 day rate is =
Days in Year =                                          365
# of 25 day periods in 1 year =
APR =
EAR =                                      <== correct because math formula does not truncate to an inte

EAR =                                      <== Incorrect because the EFFECT function truncates npery to a

Excel Help:
Npery is truncated to an integer.
If either argument is nonnumeric, EFFECT returns the #VALUE! error value.
If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error value.
EFFECT is calculated as follows:
oes not truncate to an integer

nction truncates npery to an integer
APR = Annual Percentage Rate = NOMINAL RATE = period interest rate * number of
1)          compounding periods in 1 year. This is the rate usually calculated by the bank.
Effective Interest Rate is always higher than APR or Nominal Rate when the compounding
periods per year are greater than . Why? Because you are earning interest on interest - The
Effective Interest Rate tells you in percentage terms what the rate really is (You could multiply it
2)                            plus 1 by the principal and get Future Value)

3)      =EFFECT(APR or Nominal Rate, npery means "number of compounding periods in 1 year")

4) =NOMINAL(Effective Interest Rate), npery means "number of compounding periods in 1 year")
=FV(rate = period rate, nper = total number of periods, pmt means periodic payment, pv means
amount invested or lent out today, type refers to the PMT: PMT at end of period = 0, PMT at
5)                                      beginning of period = 1)
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the
6)                                          wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender
7)                  the PV is negative, the PMT is positive, and the FV is positive.
Be consistent with your unit of time! If you are calculating monthly payment, you need monthly
interest rate and total number of months! (The period can be monthly, quarterly, yearly or any
8)                                            other length).

Savings Plan that compounds interest 365 times a year, but you put money in 12
1                                   times a year.
Monthly PMT =                                        -250
x = years                                              25
n for account is =                                    365
APR = i =                                            0.08
Type= 0 or 1 ==>                                        0
check
Solve for EAR first =                        0.083277572                               0.0832776
n for PMT =                                            12
Then from EAR, find APR (i) ==>              0.080258436
Then from APR (i), find period Rate ==>      0.006688203
Solve for Future Value =                     \$238,757.59

If we have a Savings Plan that compounds interest 365 times a year, but we put
\$250.00 in only 12 times a year, the Future Value would be \$238,757.59.

2                                 MoneyTreeLoaning will:
Allow you to write a check that has a date 25 days in the future for \$250 and will
give you \$200 today (they cash check in 25 days).
What is the APR and EAR?
Days in Future =                                        25
Check Amount =                                         250               FV
You get Today =                                        200               PV
25 day rate is =                                      0.25
Days in Year =                                         365
# of 25 day periods in 1 year =        14.60
APR =                                365.00%
EAR =                               2499.48% <== correct because math formula does not truncate to an inte

EAR =                                #REF!    <== Incorrect because the EFFECT function truncates npery to a

Excel Help:
Npery is truncated to an integer.
If either argument is nonnumeric, EFFECT returns the #VALUE! error value.
If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error value.
EFFECT is calculated as follows:
oes not truncate to an integer

nction truncates npery to an integer
NPER function calculates the = total number of periods = Years*Number of
compounding periods per year. Example 30 year loan compounded 12
1)                   times a year ==> 12*30 = 360 = Total periods.
=NPER(rate = period Rate, pmt means periodic payment, pv means
amount invested or lent out today, fv means amount received after all the
periods have elapsed or amount paid after all the periods have elapsed,
type refers to the pmt: pmt at end of period = 0, pmt at beginning of period =
2)                                           1)
Be consistent with your unit of time! If you are calculating monthly payment,
you need monthly interest rate and total number of months! (The period can
3)                  be monthly, quarterly, yearly or any other length).
Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                       Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is
negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.

How long to pay off your credit Card if you pay only the
minimum PMT required?
Balance = PV =                  2,000.00
APR = i =                         18.00%               Type
n=                                     12                       0
Minimum Monthly PMT =               41.00
n*x = NPER function =
x = n*x/n = years
Words:
NPER function calculates the = total number of periods = Years*Number of
compounding periods per year. Example 30 year loan compounded 12
1)                   times a year ==> 12*30 = 360 = Total periods.
=NPER(rate = period Rate, pmt means periodic payment, pv means
amount invested or lent out today, fv means amount received after all the
periods have elapsed or amount paid after all the periods have elapsed,
type refers to the pmt: pmt at end of period = 0, pmt at beginning of period =
2)                                           1)
Be consistent with your unit of time! If you are calculating monthly payment,
you need monthly interest rate and total number of months! (The period can
3)                  be monthly, quarterly, yearly or any other length).
Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                       Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is
negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.

How long to pay off your credit Card if you pay only the
minimum PMT required?
Balance = PV =                   2,000.00
APR = i =                          18.00%             Type
n=                                       12                    0
Minimum Monthly PMT =                 41.00
n*x = NPER function =          88.367992
x = n*x/n = years              7.3639994
It will take 7.36399935665702 years to pay off the credit
card if we make only the minimum payment each
Words:                                                 period.
260aa84a-bb10-4c7e-8b55-cbd93bfe6d73.xls - Loan Analysis

1)                                                PMT means periodic payment (same amount each period)
PMT function calculates the period payment for a loan (For the Borrower or the Lender). The Amount of each PMT must be the same and the
2)                                                       time between each PMT must be the same.
3)                      Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is negative, the PMT is positive, and the
4)                                                                       FV is positive.
Be consistent with your unit of time! If you are calculating monthly payment, you need monthly interest rate and total number of months! (The
5)                                               period can be monthly, quarterly, yearly or any other length).
=PMT(rate = period rate, nper = total number of periods, pv means amount invested or lent out today, fv means amount received after all the
periods have elapsed or amount paid after all the periods have elapsed, type refers to the PMT: PMT at end of period = 0, PMT at beginning of
6)                                                                        period = 1)
=RATE(nper = total number of periods, pmt means periodic payment, pv means amount invested or lent out today, fv means amount received
after all the periods have elapsed or amount paid after all the periods have elapsed, type refers to the pmt: pmt at end of period = 0, pmt at
7)                                                                 beginning of period = 1)

Loan Comparison
% Down                                                 Amount to       Monthly       Actual Cash       Adjusted
Option# Payment       APR   Years Points Extra Fee              Borrow         Payment        Received           APR          PMT w Balloon
1     5.0%      8.50%     30     1       400
2    15.0%      8.25%     30     3       400
3     4.5%      8.90%     30     1       450
4    12.0%      9.00%     30     2       100
5    15.0%      8.50%     30     2       125
6    20.0%      8.00%     15     0       500
7    15.0%      7.60%     15     1       750

Price                             430,000
Compounding Periods per year                     12
Balloon Payment (Optional)                  -50,000

Page 23 of 32
260aa84a-bb10-4c7e-8b55-cbd93bfe6d73.xls - Loan Analysis (an)

1)                                                PMT means periodic payment (same amount each period)
PMT function calculates the period payment for a loan (For the Borrower or the Lender). The Amount of each PMT must be the same and the
2)                                                       time between each PMT must be the same.
3)                      Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is negative, the PMT is positive, and the
4)                                                                       FV is positive.
Be consistent with your unit of time! If you are calculating monthly payment, you need monthly interest rate and total number of months! (The
5)                                               period can be monthly, quarterly, yearly or any other length).
=PMT(rate = period rate, nper = total number of periods, pv means amount invested or lent out today, fv means amount received after all the
periods have elapsed or amount paid after all the periods have elapsed, type refers to the PMT: PMT at end of period = 0, PMT at beginning of
6)                                                                        period = 1)
=RATE(nper = total number of periods, pmt means periodic payment, pv means amount invested or lent out today, fv means amount received
after all the periods have elapsed or amount paid after all the periods have elapsed, type refers to the pmt: pmt at end of period = 0, pmt at
7)                                                                 beginning of period = 1)

Loan Comparison
% Down                                                 Amount to       Monthly       Actual Cash       Adjusted
Option# Payment       APR   Years Points Extra Fee              Borrow         Payment        Received           APR          PMT w Balloon
1     5.0%      8.50%     30     1       400              408,500.00      -3,141.01       404,015.00      8.620%               -3,110.72
2    15.0%      8.25%     30     3       400              365,500.00      -2,745.88       354,135.00      8.591%               -2,714.00
3     4.5%      8.90%     30     1       450              410,650.00      -3,274.68       406,093.50      9.024%               -3,246.79
4    12.0%      9.00%     30     2       100              378,400.00      -3,044.69       370,732.00      9.231%               -3,017.38
5    15.0%      8.50%     30     2       125              365,500.00      -2,810.38       358,065.00      8.724%               -2,780.09
6    20.0%      8.00%     15     0       500              344,000.00      -3,287.44       343,500.00      8.024%               -3,142.95
7    15.0%      7.60%     15     1       750              365,500.00      -3,409.03       361,095.00      7.799%               -3,259.35

Price                             430,000
Compounding Periods per year                     12
Balloon Payment (Optional)                  -50,000

Page 24 of 32
Preset Value = How much future cash flows are worth
today. Think of it as interest going backwards; if we put
money in the bank today (present value) it will be worth
some future value amount in the future: Present Value is
the Opposite! We want to receive some cash amounts in
the future, what amount do we have to put in the bank
1)                               today?

PV function calculates present Value when the cash flows
are the same and are separated by regular time periods.
NPV function lets us calculate the present value when the
amounts are not the same. XNPV function lets us calculate
the present value when the amounts are not the same and
2)                 the times are not the same.
When an asset has an annuity cash flow pattern, you can use
Capital Investment Decision. An annuity has equal paymen
3)                    =PV( rate, nper, fv, type)                                                       intervals.

=NPV( rate, CF1, CF2…. (as range or cells separated by           Calculates the net present value for a series of cash flows tha
4) commas). NOTE: You cannot include Cash Flow at time 0.                                      time between each cash flow)
Returns the net present value for a schedule of cash flows tha
=XNPV(rate, values, dates) NOTE: you can include Cash            periodic. To calculate the net present value for a series of c
5)                        Flow at time 0.                                                     periodic, use the NPV function.

Cash Flow matters in Finance. Cash going out of the wallet
6)     is negative. Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and
the FV is negative. For the Lender the PV is negative, the
7)            PMT is positive, and the FV is positive.
Be consistent with your unit of time! If you are calculating
monthly payment, you need monthly interest rate and total
number of months! (The period can be monthly, quarterly,
8)                  yearly or any other length).

You are considering buying a machine that will yield
\$35,000.00 net cash flow in for the next ten years. If you
must earn a minimum return on investment of 15.00%,
1      should we buy a machine if it costs (\$165,500.00)?

Net Cash Flow at end of each year =                 35,000.00
Min Return (hurdle rate or Discount Rate) =              15%
Cost =                                            -165,500.00
Years                                                      10
PV =
Difference =
NPV =                                                                   NPV =

2                      Period                            CF                     CF
0                         -165,500.00
1    35,000.00              40,000.00
2      35,000.00            40,000.00
3      35,000.00            40,000.00
4      35,000.00            35,000.00
5      35,000.00            35,000.00
6      35,000.00            35,000.00
7      35,000.00            20,000.00
8      35,000.00            10,000.00
9      35,000.00            50,000.00
10      35,000.00             2,500.00

3 RRR                     0.15

Date              Year          CF      PV function XNPV algorithm XNPV
1/1/2007            0    -500.00     -500.00       -500.00
1/1/2008            1     200.00      173.91        173.91
1/1/2010            3     100.00       65.75         65.73
1/1/2011            4     100.00       57.18         57.15
1/1/2012            5     100.00       49.72         49.70
1/1/2013            6     100.00       43.23         43.20
-110.21       -110.31

1/1/2009
366
h flow pattern, you can use the PV function for
n annuity has equal payments at equal time
intervals.

r a series of cash flows that is periodic (equal
een each cash flow)
schedule of cash flows that is not necessarily
esent value for a series of cash flows that is
use the NPV function.
Preset Value = How much future cash flows are worth
today. Think of it as interest going backwards; if we put
money in the bank today (present value) it will be worth
some future value amount in the future: Present Value is
the Opposite! We want to receive some cash amounts in
the future, what amount do we have to put in the bank
1)                               today?

PV function calculates present Value when the cash flows
are the same and are separated by regular time periods.
NPV function lets us calculate the present value when the
amounts are not the same. XNPV function lets us calculate
the present value when the amounts are not the same and
2)                 the times are not the same.
When an asset has an annuity cash flow pattern, you can use
Capital Investment Decision. An annuity has equal paymen
3)                    =PV( rate, nper, fv, type)                                                       intervals.

=NPV( rate, CF1, CF2…. (as range or cells separated by           Calculates the net present value for a series of cash flows tha
4) commas). NOTE: You cannot include Cash Flow at time 0.                                      time between each cash flow)
Returns the net present value for a schedule of cash flows tha
=XNPV(rate, values, dates) NOTE: you can include Cash            periodic. To calculate the net present value for a series of c
5)                        Flow at time 0.                                                     periodic, use the NPV function.

Cash Flow matters in Finance. Cash going out of the wallet
6)     is negative. Cash coming into the wallet is positive.
For the borrow the PV is positive, the PMT is negative, and
the FV is negative. For the Lender the PV is negative, the
7)            PMT is positive, and the FV is positive.
Be consistent with your unit of time! If you are calculating
monthly payment, you need monthly interest rate and total
number of months! (The period can be monthly, quarterly,
8)                  yearly or any other length).

You are considering buying a machine that will yield
\$35,000.00 net cash flow in for the next ten years. If you
must earn a minimum return on investment of 15.00%,
1      should we buy a machine if it costs (\$165,500.00)?

Net Cash Flow at end of each year =                 35,000.00
Min Return (hurdle rate or Discount Rate) =              15%
Cost =                                            -165,500.00
Years                                                      10
PV =                                              -175,656.90
Difference =                                        10,156.90
NPV =                                               10,156.90           NPV =                3,991.86

2                      Period                            CF                     CF
0                         -165,500.00
1    35,000.00              40,000.00
2      35,000.00            40,000.00
3      35,000.00            40,000.00
4      35,000.00            35,000.00
5      35,000.00            35,000.00
6      35,000.00            35,000.00
7      35,000.00            20,000.00
8      35,000.00            10,000.00
9      35,000.00            50,000.00
10      35,000.00             2,500.00

3 RRR                     0.15

Date              Year          CF      PV function XNPV algorithm XNPV
1/1/2007            0    -500.00     -500.00       -500.00 #NAME?
1/1/2008            1     200.00      173.91        173.91
1/1/2010            3     100.00       65.75         65.73
1/1/2011            4     100.00       57.18         57.15
1/1/2012            5     100.00       49.72         49.70
1/1/2013            6     100.00       43.23         43.20
-110.21       -110.31

1/1/2009
366
h flow pattern, you can use the PV function for
n annuity has equal payments at equal time
intervals.

r a series of cash flows that is periodic (equal
een each cash flow)
schedule of cash flows that is not necessarily
esent value for a series of cash flows that is
use the NPV function.
FV function calculates the future value of a lump sum (invested at very
beginning) or regular payments (called PMT and amount is the same for
1)                 each period and the amount is always the same).
2)             PMT means periodic payment (same amount each period)
PMT function calculates the period payment for a loan (For the Borrower or
the Lender). The Amount of each PMT must be the same and the time
3)                       between each PMT must be the same.
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
coming into the wallet is positive. Time period Matters in Finance: Example -
if you are making monthly payments, then total number of periods must be
4)                               total number of months.
For the borrow the PV is positive, the PMT is negative, and the FV is
negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.

=FV(rate = period rate, nper = total number of periods, pmt means periodic
payment, pv means amount invested or lent out today, type refers to the
6)        PMT: PMT at end of period = 0, PMT at beginning of period = 1)

=PMT(rate = period rate, nper = total number of periods, pv means amount
invested or lent out today, fv means amount received after all the periods
have elapsed or amount paid after all the periods have elapsed, type refers
7)       to the PMT: PMT at end of period = 0, PMT at beginning of period = 1)

Monthly PMT (end)                -100.00 Amount put in
years                                 30 Amount taken out
Months                                   Interest
Annual Rate                          9%                   .
Monthly Rate
FV

PV
years                                 35
Months
Annual Rate                       7.00%
Monthly Rate
Monthly PMT (begin)
Leave to Kids                250,000.00
FV function calculates the future value of a lump sum (invested at very
beginning) or regular payments (called PMT and amount is the same for
1)                 each period and the amount is always the same).
2)             PMT means periodic payment (same amount each period)
PMT function calculates the period payment for a loan (For the Borrower or
the Lender). The Amount of each PMT must be the same and the time
3)                       between each PMT must be the same.
Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
coming into the wallet is positive. Time period Matters in Finance: Example -
if you are making monthly payments, then total number of periods must be
4)                               total number of months.
For the borrow the PV is positive, the PMT is negative, and the FV is
negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.

=FV(rate = period rate, nper = total number of periods, pmt means periodic
payment, pv means amount invested or lent out today, type refers to the
6)        PMT: PMT at end of period = 0, PMT at beginning of period = 1)

=PMT(rate = period rate, nper = total number of periods, pv means amount
invested or lent out today, fv means amount received after all the periods
have elapsed or amount paid after all the periods have elapsed, type refers
7)       to the PMT: PMT at end of period = 0, PMT at beginning of period = 1)

Monthly PMT (end)               -100.00 Amount put in             36,000.00
years                                30 Amount taken out         680,414.56
Months                              360 Interest                 644,414.56
Annual Rate                         9%                   .
Monthly Rate                    0.750%
FV                           183,074.35

PV                          -183,074.35
years                                 35
Months                               420
Annual Rate                       7.00%
Monthly Rate                   0.5833%
Monthly PMT (begin)            1,024.80
Leave to Kids                250,000.00

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 45 posted: 9/28/2010 language: English pages: 32
Description: Formula for Calculating Interest Payments document sample