# PMT by fanzhongqing

VIEWS: 9 PAGES: 14

• pg 1
```									Present Value
My prize gives me \$100 each month for 10 years. What
is its PV (the present value) assuming the per annum
pmt                 -100    rate is 10%
years                 10
What do we mean by PV in this problem?
APR                 10%
=PV(rate,nper, pmt, [fv],[type])
PV              \$7,567.12

pmt                 -100    My prize gives me \$100 each month for 10 years and also
years                 10    at the end I get a lump sum of \$500. What is its PV? If the
APR                 10%     rate is 10% pa find the present value assuming the per
annum rate is 10%
fv                   500
=PV(rate,nper, pmt, [fv],[type])
PV              \$7,382.41
make pmt -100 because the bank is loosing money
is loosing money
Future Value
I deposit \$100 each month into a saving account. The
interest rate is 8% pa. What formula should I use to find
pmt             100    how much I can expect after 3 yea
years             3
APR              8%    Future value =FV(rate, nper, pmt, [pv],[type])

The FV function includes an optional pv argument. Modify
FV         \$4,053.56   the problem above in such a way that we would need to
use this pv argument.
Payment
=PMT(rate, nper, pv, [fv], [type])

PV                1000
APR                 8%
nper                 1

Monthly
pmt             -\$86.99 =PMT(rate/12,nper*12,PV)

Semimonthly
pmt             -\$43.42 =PMT(rate/24,nper*24,PV)

Weekly
pmt             -\$20.03 =PMT(rate/52,nper*52,PV)

Total of all payments (semimonthly)                Why are some Excel results a penny different from the image taken from the
Excel           -\$521.04
Royal Bank's website?
Bank            -\$521.16
Difference         \$0.12
How does the Bank compensate for this?
Explain =ROUND(quantity, 2) and ROUNDUP and ROUNDDOWN

1) decimals were different
2)
Number of Periods
I plan to save \$250 each month until I have \$100,000 in the
pmt                 250     saving acount. Assuming a rate of 8% pa, how many monthly
fv         \$ 100,000.00     payments must I make?
APR                  8%
Number of periods =NPER(APR/12,-pmt,0,fv)
nper        195.5413697
years               16.3

Let's check that out
FV            \$100,000.00
Rate
I plan to save \$400 each month for
pmt                 400     25 years to give myself a pension. If
my target is \$500,000 what must
years                 25
the APR be during this time?
fv       \$   500,000.00

APR                9.65%

Let's check that out
FV            \$500,000.00
PPMT and IPMT
I take out a \$250,000 mortgage at 6% over 25 years
pv            \$ 250,000.00             What is my monthly payment?                                Most of the early payments go
APR                     6%             How much does towards the principal, how much              towards paying interest. Very
years                    25            towards the interest                                       little goes toward the
principal.
Pmt              -\$1,610.75            In the early periods, most of the payment is used to pay
off interest                                               You do not have much EQUITY
after 5 years
Canadian mortgages are a bit more complicated than
this but the overall picture is correct

Month         PPMT               IPMT           \$1,800
1           \$360.75      \$1,250.00
12           \$381.10      \$1,229.66    \$1,600
24           \$404.60      \$1,206.15    \$1,400
36           \$429.56      \$1,181.19    \$1,200
48           \$456.05      \$1,154.70
60           \$484.18      \$1,126.57    \$1,000
IPMT
72           \$514.04      \$1,096.71      \$800
84           \$545.75      \$1,065.00                                                                             PPMT
\$600
96           \$579.41      \$1,031.34
108           \$615.15       \$995.61       \$400
120           \$653.09       \$957.67       \$200
132           \$693.37       \$917.38
\$0
144           \$736.13       \$874.62
156           \$781.54       \$829.22
168           \$829.74       \$781.01
180           \$880.92       \$729.84
192           \$935.25       \$675.50
204           \$992.94       \$617.82
216          \$1,054.18      \$556.58
228          \$1,119.20      \$491.56
240   \$1,188.23   \$422.53
252   \$1,261.51   \$349.24
264   \$1,339.32   \$271.43
276   \$1,421.93   \$188.83
288   \$1,509.63   \$101.12
300   \$1,602.74     \$8.01
y payments go    Original loan          \$ 250,000                      This assumes the house is still worth
interest. Very   Owing after 5 years    \$ 224,830                      \$250,000.
Equity                 \$  25,170                      The housing market can be volatile.
Total payments         \$  96,645

e much EQUITY
Home equity, the difference between the market
value and unpaid mortgage balance on a home

Month                  PPMT             Rermaining
1          \$360.75   \$249,639.25
2          \$362.56   \$249,276.69
3          \$364.37   \$248,912.32
4          \$366.19   \$248,546.13
5          \$368.02   \$248,178.10
6          \$369.86   \$247,808.24
IPMT
7          \$371.71   \$247,436.53
PPMT                        8          \$373.57   \$247,062.96
9          \$375.44   \$246,687.52
10          \$377.32   \$246,310.20
11          \$379.20   \$245,931.00
12          \$381.10   \$245,549.90
13          \$383.00   \$245,166.90
14          \$384.92   \$244,781.98
15          \$386.84   \$244,395.14
16          \$388.78   \$244,006.36
17          \$390.72   \$243,615.64
18          \$392.68   \$243,222.96
19          \$394.64   \$242,828.32
20          \$396.61   \$242,431.71
21   \$398.59   \$242,033.12
22   \$400.59   \$241,632.53
23   \$402.59   \$241,229.94
24   \$404.60   \$240,825.33
25   \$406.63   \$240,418.71
26   \$408.66   \$240,010.05
27   \$410.70   \$239,599.34
28   \$412.76   \$239,186.59
29   \$414.82   \$238,771.77
30   \$416.89   \$238,354.87
31   \$418.98   \$237,935.89
32   \$421.07   \$237,514.82
33   \$423.18   \$237,091.64
34   \$425.30   \$236,666.34
35   \$427.42   \$236,238.92
36   \$429.56   \$235,809.36
37   \$431.71   \$235,377.66
38   \$433.87   \$234,943.79
39   \$436.03   \$234,507.76
40   \$438.21   \$234,069.54
41   \$440.41   \$233,629.14
42   \$442.61   \$233,186.53
43   \$444.82   \$232,741.71
44   \$447.04   \$232,294.66
45   \$449.28   \$231,845.38
46   \$451.53   \$231,393.85
47   \$453.78   \$230,940.07
48   \$456.05   \$230,484.02
49   \$458.33   \$230,025.68
50   \$460.63   \$229,565.06
51   \$462.93   \$229,102.13
52   \$465.24   \$228,636.89
53   \$467.57   \$228,169.32
54   \$469.91   \$227,699.41
55   \$472.26   \$227,227.16
56   \$474.62   \$226,752.54
57   \$476.99   \$226,275.55
58   \$479.38   \$225,796.17
59   \$481.77   \$225,314.40
60   \$484.18   \$224,830.22

```
To top