Net Present Value Query - Please help

[hardgrafting]

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.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Discount rate[/TD]
[TD]5%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Time[/TD]
[TD]Values[/TD]
[TD]NPV[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.5[/TD]
[TD]-100[/TD]
[TD]-98[/TD]
[TD]=B3/(1+$B$1)^A3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.5[/TD]
[TD]20[/TD]
[TD]19[/TD]
[TD]=B4/(1+$B$1)^A4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]30[/TD]
[TD]27[/TD]
[TD]=B5/(1+$B$1)^A5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3.5[/TD]
[TD]80[/TD]
[TD]67[/TD]
[TD]=B6/(1+$B$1)^A6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]=sum(c3:c6)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]30/06/2018[/TD]
[TD]-100[/TD]
[TD]15.35[/TD]
[TD]=XNPV($B$1,$B$9:$B$12,$A$9:$A$12)[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]30/06/2019[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]30/06/2020[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]30/06/2021[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

@hardgrafting, let me try to explain:

1. Understand that numbers like 0.5, 1.5, 2.5, etc. are nice, clean numbers. But when it comes to years, "exactly half a year" doesn't fall on June 30. It's actually a little more than halfway through the day on July 2. So your fractional numbers will always vary between "clean math" functions and date/time functions.

2. You have C3:C7 formatted as a number to two decimal places. That is further masking the real value of "15" in C7 (which is really 14.98...).

3. The biggest issue is that your values in A3 and following mean "half a year FROM START OF ZERO, etc." But you didn't account for the zero start date in your data from Rows 9:12. The XNPV() function doesn't know how to interpret June 30, 2018 unless you give it relative start date. In other words, June 30 is only roughly equivalent to 0.5 when you consider a starting date of January 1 (which is the implied zero in the math function at the top part of your sheet).

3. To fix this, you need to insert a row at Row 9. Enter 01/01/2018 in the new A9, 0 in the new B9. Then move your XNPV formula to C9 and adjust it to include the zero start date:

=XNPV($B$1,$B$9:$B$13,$A$9:$A$13)

You will see that the value is now a lot closer to 14.98.

If you really wanted to see the math start to match up, you could change your dates in A10:A13, staggering them as July 2, 2018 ... July 3, 2019 ... July 2, 2020 ... July 3, 2021. This will account for the ACTUAL 0.5-year mark date of somewhere part of the way through July 2. It still won't be an exact match unless you figured out the EXACT half-year date, including leap years, down to the minute.

Hope that clears things up.

 

[hardgrafting]

thanks for this, really helpful.

Is there any way to find out the exact half-year dates, aside from trial and error?

 

[inactiveuserps07]

@hardgrafting, there really isn't. Again, using 365 days a year, .05 year falls on July 2 at noon. But that doesn't account for leap years, which fall at July 2, midnight. The XNPV() function doesn't get as specific as hour, only date. So you'll never get an answer with XNPV() that exactly matches straight math. However, if you round both answers to 1 or 2 decimal place and use July 2 as the half-year, they'll match.