CMV: In Excel, the formula for arithmetic mean should be "mean()" instead of "average()"
ELI5: What Exactly does the Value from NPV mean?
ok,, so first, let's talk present value.
there's a concept of "time value of money" which basically means that a dollar today is worth a dollar in the future (there's an implicit assumption that dollars grow at positive interest rates - because you could put your dollar in some investment vehicle and earn interest).
so the present value of something is the value today (or at a specified period of time, but let's just say that period of time is today).
so the present value of a dollar today is a dollar. but what if i promise to give you a dollar a year from now? how much is that promise of a dollar a year from now worth to you? well, you discount it at the interest rate that dollar could earn to find it. so at 7%, the present value of a dollar is $1/(1.07). Likewise, a dollar two years from now is presumably going to be worth $1x(1.07)2, or $1.14. Said another way, the present value of $1.14 two years from now is $1 ($1.14/(1.072))
net present value just extends this simple concept to consider many cash flows.
if i ask you if i can borrow a dollar today and i'll pay you back a dollar a year from now you have a negative net present value. Because you've given me dollar and i'm giving you back the equivalent of $0.93 in today's dollars. You have essentially forgone a year of interest.
Now, in your example, you pay $200 for the four future cash flows of $75 each.
the net present value is simply the sum of all future cash flows. so the presetn value of your $200 (outgoing) is -200.
the present value of the four future cash flows is 75/(1.07) + 75/(1.072) + 75/(1.073) + 75/(1.074), or 254.04.
So you pay $200 to get the value of $254.04. So you have a positive NPV of $54.04.
make sense?
it's often helpful to draw a timeline with all your cashflows on it and you can increase/decrease all the cash flows to a single point of time to get the present value at that point in time.
edit: to extend this to your example of paying $200 for $300 four years from now, you have $200 outgoing and the equivalent of $300/(1.074) incoming, or $228.87 less $200 = $28.87.
More on reddit.comWhat do employers mean by “advance Excel skills”, “expert in Office Suite”, etc for entry-level roles?
What does it mean when excel reports 1E-6 : excel
Videos
We all know there are multiple kinds of average: there's median, mode, geomean, arithmetic mean, etc. It would be less ambiguous to have the formula for arithmetic mean to be mean() instead of average(). It is also shorter to type, so would save time. I know a lot of other programming languages do it this way, so it would be less confusing overall.
Please note that I'm not advocating for removing the average() formula entirely. I'm arguing that this is how it should have been from the beginning. For the sake of backwards compatibility, we keep average() and have it do the same things as mean(). I think it's just insane that mean() isn't even a default formula in excel. It should be.
Also, before anyone says anything, I know that I can add my own formulas with defined names and lambda functions and stuff. I know. I'm just saying that mean() should be a default formula.