| A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
| 2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 3 |
|
PVT |
|
|
|
|
|
|
|
|
|
|
|
| 4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 5 |
|
Category: |
Discounted Cash Flow |
|
|
|
|
|
|
|
|
|
| 6 |
|
Family: |
DCF Specific Times |
|
|
|
|
|
|
|
|
|
| 7 |
|
Arguments: |
NPVDate, DisAER, Dates, Cashflows,
[DayCountDisc], [PrdsDisc], [StartDCF], [FinishDCF], [PVTOptions] |
|
|
|
|
| 8 |
|
Meaning: |
Present Value of cashflows at specific Dates |
|
|
|
|
|
|
|
|
| 9 |
|
Description: |
Returns the PV
(present value) of the cashflow represented by Dates and the corresponding
Amounts |
|
| 10 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 11 |
|
Dates |
1 Jan 00 |
1 Jan 01 |
1 Jan 02 |
1 Jan 03 |
|
|
|
|
|
|
|
| 12 |
|
CashFlows |
(100) |
50 |
50 |
50 |
|
|
|
|
|
|
|
| 13 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 14 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 15 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 16 |
|
|
Disc Rate AER |
NPV Date |
DayCount |
|
|
|
Periods |
|
PVT |
|
|
| 17 |
|
|
46.75%
|
1 Jan 99 |
Omitted |
Actual Days/Actual Days In Month ('Decimal
Year'). |
Omitted |
|
(18.32) |
|
|
| 18 |
|
|
46.75%
|
1 Jan 00 |
|
30/360
(PSA). |
|
|
|
|
(26.89) |
|
|
| 19 |
|
|
46.75%
|
1 Jan 98 |
1.00 |
Actual/Actual
(365 or 366). |
|
|
|
(12.49) |
|
|
| 20 |
|
|
23.38% |
1 Jan 00 |
2.00 |
Actual
Days/360. |
|
|
|
|
(0.60) |
|
Excel XNPV |
| 21 |
|
|
23.38% |
1 Jan 00 |
3.00 |
Actual
Days/365. |
|
|
|
|
(0.06) |
|
(0.06) |
| 22 |
|
|
11.69% |
1 Jan 00 |
4.00 |
30/360
(European). |
|
|
|
|
20.74 |
|
|
| 23 |
|
|
11.69% |
1 Jan 00 |
5.00 |
Actual Days/Actual Days In Month ('Decimal
Year'). |
|
|
20.74 |
|
|
| 24 |
|
|
11.69% |
1 Jan 00 |
6.00 |
Actual/Actual
(Within Period). |
|
4 |
Cal Qtrs |
20.74 |
|
|
| 25 |
|
|
11.69% |
1 Jan 00 |
6.03 |
Main Period:Actual/Actual (Within
Period),Stub Periods:Actual Days/365. |
1 |
Cal Yrs |
20.74 |
|
|
| 26 |
|
|
11.69% |
1 Jan 00 |
6.00 |
Actual/Actual
(Within Period). |
|
(1) |
Cal Yrs |
20.74 |
|
|
| 27 |
|
|
11.69% |
1 Jan 00 |
6.00 |
Actual/Actual
(Within Period). |
|
12 |
Cal Yrs |
20.74 |
|
|
| 28 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 29 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 31 |
|
|
Notes: |
Excel NPV uses a daycount of ACT/365 |
|
|
|
|
|
|
|
| 32 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 33 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 34 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 35 |
|
|
Example of a different use of PVT: Assume
cashflows occur continuously |
|
|
|
|
|
| 36 |
|
|
Suppose the cashflows above were spread evenly
on a daily basis, according to timeperiods that START on the respective Dates |
|
|
| 37 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 38 |
|
|
Disc Rate AER |
NPV Date |
DayCount |
|
Options |
|
|
|
PVT |
|
|
| 39 |
|
|
10.00% |
1 Jan 00 |
6.00 |
|
|
|
|
(a) |
24.3426 |
|
|
| 40 |
|
|
|
|
|
|
1 |
|
|
(b) |
23.2185 |
|
|
| 41 |
|
|
How the answer was arrived at: |
|
|
|
|
|
|
|
|
|
| 42 |
|
Dates |
1 Jan 00 |
1 Jan 01 |
1 Jan 02 |
1 Jan 03 |
Total |
|
|
|
|
|
|
| 43 |
|
CashFlows |
(100) |
50 |
50 |
50 |
|
|
|
|
|
|
|
| 44 |
|
NPV (a) |
(100.00) |
45.45 |
41.32 |
37.57 |
24.3426 |
|
|
|
|
|
|
| 45 |
|
NPV (b) |
(95.3823) |
43.36 |
39.41 |
35.83 |
23.2185 |
|
|
|
|
|
|
| 46 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 47 |
|
|
Looking at the same example done 6-monthly, it
gives the same continous NPV: |
|
|
|
|
|
|
| 48 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 49 |
|
Dates |
1 Jan 00 |
1 Jul 00 |
1 Jan 01 |
1 Jul 01 |
1 Jan 02 |
1 Jul 02 |
1 Jan 03 |
1 Jul 03 |
|
|
|
| 50 |
|
CashFlows |
(50) |
(50) |
25 |
25 |
25 |
25 |
25 |
25 |
Total PV (longhand) |
| 51 |
|
|
(48.8273) |
(46.5550) |
22.1942 |
21.1614 |
20.1766 |
19.2376 |
18.3423 |
17.4887 |
23.2185 |
|
|
| 52 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 53 |
|
|
|
|
|
|
|
|
|
|
PVT |
|
|
| 54 |
|
|
|
|
|
|
|
|
|
|
23.2185 |
|
|
| 55 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 56 |
|
|
Another Continuous example |
|
|
|
|
|
|
|
|
|
| 57 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 58 |
|
|
Base |
3
months |
|
Base |
6
months |
|
Base |
12
months |
|
|
|
| 59 |
|
|
1
Jan 00 |
250 |
|
1
Jan 00 |
500 |
|
1
Jan 00 |
1000 |
|
|
|
| 60 |
|
|
1 Apr 00 |
250 |
|
1 Jul 00 |
500 |
|
1 Jan 01 |
1000 |
|
|
|
| 61 |
|
|
1 Jul 00 |
250 |
|
1 Jan 01 |
500 |
|
1 Jan 02 |
1000 |
|
|
|
| 62 |
|
|
1 Oct 00 |
250 |
|
1 Jul 01 |
500 |
|
1 Jan 03 |
1000 |
|
|
|
| 63 |
|
|
1 Jan 01 |
250 |
|
1 Jan 02 |
500 |
|
1 Jan 04 |
|
|
|
|
| 64 |
|
|
1 Apr 01 |
250 |
|
1 Jul 02 |
500 |
|
1 Jan 05 |
|
|
|
|
| 65 |
|
|
1 Jul 01 |
250 |
|
1 Jan 03 |
500 |
|
1 Jan 06 |
|
|
|
|
| 66 |
|
|
1 Oct 01 |
250 |
|
1 Jul 03 |
500 |
|
1 Jan 07 |
|
|
|
|
| 67 |
|
|
1 Jan 02 |
250 |
|
1 Jan 04 |
|
|
1 Jan 08 |
|
|
|
|
| 68 |
|
|
1 Apr 02 |
250 |
|
1 Jul 04 |
|
|
1 Jan 09 |
|
|
|
|
| 69 |
|
|
1 Jul 02 |
250 |
|
1 Jan 05 |
|
|
1 Jan 10 |
|
|
|
|
| 70 |
|
|
1 Oct 02 |
250 |
|
1 Jul 05 |
|
|
1 Jan 11 |
|
|
|
|
| 71 |
|
|
1 Jan 03 |
250 |
|
1 Jan 06 |
|
|
1 Jan 12 |
|
|
|
|
| 72 |
|
|
1 Apr 03 |
250 |
|
1 Jul 06 |
|
|
1 Jan 13 |
|
|
|
|
| 73 |
|
|
1 Jul 03 |
250 |
|
1 Jan 07 |
|
|
1 Jan 14 |
|
|
|
|
| 74 |
|
|
1 Oct 03 |
250 |
|
1 Jul 07 |
|
|
1 Jan 15 |
|
|
|
|
| 75 |
|
|
1 Jan 04 |
|
|
1 Jan 08 |
|
|
1 Jan 16 |
|
|
|
|
| 76 |
|
|
Total |
4,000 |
|
|
4,000 |
|
|
4,000 |
|
|
|
| 77 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 78 |
|
|
PVT |
3,326 |
|
PVT |
3,326 |
|
PVT |
3,326 |
|
|
|
| 79 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| 80 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|