hardgrafting
New Member
- Joined
- Feb 6, 2017
- Messages
- 25
Hi,
I am trying to work out a Net Present Value problem and would really appreciate help on the below:
I am finding the net present value of cash flows under 2 methods.
In A1:A7 I would like to work it out based on determined time, for example first cash flows occurs in 0.5 years, the second in 1.5 years (i.e 1 year after the first cash flow etc). Please see the formula I used for this set of calculations, the total of which comes to 15.
I Then want to work it out, using dates. So for example the first payment of -100 is made in end June 2018 (which corresponds to the payment in cell A3). The next payment is made 1 year after, as per B4 for a positive receipt of 20.
I cannot make the numbers match, as you can see I get 15 vs 15.35.
I think I am missing something conceptually and would really appreciate any help on this.
<tbody>
</tbody>
I am trying to work out a Net Present Value problem and would really appreciate help on the below:
I am finding the net present value of cash flows under 2 methods.
In A1:A7 I would like to work it out based on determined time, for example first cash flows occurs in 0.5 years, the second in 1.5 years (i.e 1 year after the first cash flow etc). Please see the formula I used for this set of calculations, the total of which comes to 15.
I Then want to work it out, using dates. So for example the first payment of -100 is made in end June 2018 (which corresponds to the payment in cell A3). The next payment is made 1 year after, as per B4 for a positive receipt of 20.
I cannot make the numbers match, as you can see I get 15 vs 15.35.
I think I am missing something conceptually and would really appreciate any help on this.
A | B | C | D | |
1 | Discount rate | 5% | ||
2 | Time | Values | NPV | |
3 | 0.5 | -100 | -98 | =B3/(1+$B$1)^A3 |
4 | 1.5 | 20 | 19 | =B4/(1+$B$1)^A4 |
5 | 2.5 | 30 | 27 | =B5/(1+$B$1)^A5 |
6 | 3.5 | 80 | 67 | =B6/(1+$B$1)^A6 |
7 | Total | 15 | =sum(c3:c6) | |
8 | Dates | |||
9 | 30/06/2018 | -100 | 15.35 | =XNPV($B$1,$B$9:$B$12,$A$9:$A$12) |
10 | 30/06/2019 | 20 | ||
11 | 30/06/2020 | 30 | ||
12 | 30/06/2021 | 80 | ||
13 |
<tbody>
</tbody>