NPV/XNPV for monthly cash flows

From the official documentation of the XNPV function, it requires a rate (which looks to be an annual discount rate), a range of cash-flow numbers, and a matching range for the dates the cash-flow happened on. So it discounts on an annualized rate based on a very granular day-by-day basis.

Looking at the NPV function definition, it assumes a range of cash-flows on an annual interval. And the rate is also an annual rate by default.

It would help us help you if you could post your data in a format that we can use (a spreadsheet would probably be best). Most don't want to hand copy data from a picture.

While considering that, you might also look at the help files for those two functions, as they have sample data with results that you can test on as well:
https://support.office.com/en-us/art...b-d67c16b664b7
https://support.office.com/en-us/art...b-ac28acf2a568

Originally Posted by ben_hensel

Looking at the NPV function definition, it assumes a range of cash-flows on an annual interval. And the rate is also an annual rate by default.

That is incorrect. It is only annual in that example.

Like most Excel financial functions that assume equal intervals, the interval can be any unit of time (daily, weekly, monthly, quarterly, annual, etc). The requirement is: the rate must be for the same unit of time.

Since "cc918's" cash flows are monthly, he would use a monthly discount rate, but only for NPV.

Originally Posted by cc918

So im trying to find the NPV for the monthly cash flows seen in the attached picture. I know that XNPV is for irregular cash flows, and NPV is for periodic.

The more correct statement is: XNPV can be used for cash flows with irregular frequency. It can be also used for cash flows with regular frequency.

In contrast, NPV can be used only for cash flows with regular frequency.

Originally Posted by cc918

My biggest issue is how to treat the discount rate when it in monthly, rather than yearly. For NPV I believe you would just divide the rate by 12

Actually, there are two ways to do it: divide by 12; or take the 12th root.

In your example, if the annual discount rate (10%) is in C6, the monthly rate can be C6/12 or (1+C6)^(1/12)-1.

Which to do depends on your purpose. The latter is comparable to XNPV, since XNPV compounds daily.

Originally Posted by cc918

do you do the same for XNPV? For some reason I thought XNPV might require the annual rate.

You are correct. So no, we do not the same for XNPV.

Originally Posted by cc918

Can anyone help determine the formulas for XNPV and NPV of the following and explain whether or not I should use NPV or XNPV?

Whether to use NPV or XNPV depends on how you want to interpret the dates associated with the cash flows.

I suspect that your intent is for the cash flows to occur at regular intervals, namely monthly. In that case, I would use NPV.

Note that XNPV calculates the exact number of days between cash flows. So in your example, the XNPV cash flows do not really occur at regular intervals. Instead, they occur between 28 and 31 days apart.

Also, you need to be careful with how you use NPV, especially if you want to compare with XNPV. Note that with XNPV, 4/1/2018 is treated as the "present value" date. So for a comparable result using NPV, the formula would be:

=NPV((1+C6)^(1/12)-1, E7:E17) + E6

The reason for writing +E6 instead of E6:E17 is: Excel NPV discounts the first value. So with E6:E17, NPV effectively makes the "present value" date the month before 4/1/2018.

Nevertheless, you might note that NPV and XNPV return very different values: about 647.22 for NPV, and about 0.11 for XNPV.

The reason, again, is: NPV assumes cash flows with regular frequency, whereas XNPV calculates based on the actual irregular frequency.

Also, XNPV compounds the daily rate, whereas in your example, NPV compounds the monthly rate.

The following table demonstrates that there is relatively little difference between the present values of each cash flow; but the sums are very different.

Please Login or Register  to view this content.

Heres the data in useable format


April-18
May-18
June-18
July-18
August-18
September-18
October-18
November-18
December-18
January-19
February-19
March-19


$(508,477.990083)
$(519,574.833522)
$(489,485.412582)
$(356,123.381102)
$(237,552.228965)
$(183,582.316160)
$(34,675.769159)
$620,090.433452
$695,823.278179
$664,427.333048
$256,540.289628
$(15,193.236290)


Some numbers are slightly different than before because the spreadsheet automatically updates with market pricing

[.... deleted by me ....]

Originally Posted by cc918

Heres the data in useable format

Not really. Copy-and-pasting April-18 results in the date 4/18/2018, not 4/1/2018 as you intended.

It would be better to attach an example Excel file using this forum's GUI, just as you had attached the PNG file in the first place.

Originally Posted by cc918

Some numbers are slightly different than before because the spreadsheet automatically updates with market pricing

And with these higher-precision and different values, the NPV and XNPV results are more similar, as demonstrated by the table below.

However, I question whether these are truly (net) "cash flows" at all.

Are you really depositing (re-investing) 34,000 to 519,000 (net) per month for the first 6 months, with an initial investment value of 508,000?!

Are you really withdrawing 256,000 to 695,000 (net) per month for the next 4 months, with a final investment loss(!) of 15,000?!

(I assume that negative values represent investments, and positive values represent withdrawals.)

I doubt that very much.

On the other hand, I also doubt that those values are the imputed values of an investment on those dates. (As I thought at first in my deleted posting.)


-----

Please Login or Register  to view this content.

See formulas in posting #6.