NPV calculation?

[enisthemenace]

Let me try this another way. I appreciate all the help, but I’m still struggling. I have been able to simulate the original example, but now I need to somehow get this to work on 1,000 more rows.

Here is what I have by column.

A: Number of years (this ranges from 1 - 50)
B: Rate (doesn’t matter what I put in here, as long as it’s a percentage)
C: Amount (ranges from $0.35 - $26,093.48)
D: this is where I want the NPV to end up

So, is there a function I can use with these corresponding cell letters to get me what I need, or no?

=NPV(B, SUM(C*A)) doesn’t work

 

[Rabbuk]

Let me try this another way. I appreciate all the help, but I’m still struggling. I have been able to simulate the original example, but now I need to somehow get this to work on 1,000 more rows.

Here is what I have by column.

A: Number of years (this ranges from 1 - 50)
B: Rate (doesn’t matter what I put in here, as long as it’s a percentage)
C: Amount (ranges from $0.35 - $26,093.48)
D: this is where I want the NPV to end up

So, is there a function I can use with these corresponding cell letters to get me what I need, or no?

=NPV(B, SUM(C*A)) doesn’t work

https://answers.microsoft.com/en-us...function/d7987138-4af9-4225-9510-49f57f2f19ea

 

[enisthemenace]

I think I figured it out. It’s “ugly” and required a second worksheet, plus a lot of “copy and paste”, but it is what it is.

Thanks All.

 

[sunset]

Why use excel functions when the actual formula is so simple? As already mentioned, Excel functions have trouble with cash flows switching from positive to negative and back (try an IRR function under this scenario and see what interesting solutions you receive) and also have trouble with irregular timing (i.e. cash flows on an annual basis shifting to semi-annual or one payment in October instead of December). The time value of money formula will allow you to derive just about any NPV solution you need: FV = PV (1.r)^n. From there it's simple math to get to annuity/perpetuity/irregular-cashflow PV solutions.

 

[enisthemenace]

Why use excel functions when the actual formula is so simple? As already mentioned, Excel functions have trouble with cash flows switching from positive to negative and back (try an IRR function under this scenario and see what interesting solutions you receive) and also have trouble with irregular timing (i.e. cash flows on an annual basis shifting to semi-annual or one payment in October instead of December). The time value of money formula will allow you to derive just about any NPV solution you need: FV = PV (1.r)^n. From there it's simple math to get to annuity/perpetuity/irregular-cashflow PV solutions.

I’ll try it again, but it wasn’t working out before. Thanks though.

 

[sunset]

I’ll try it again, but it wasn’t working out before. Thanks though.

Was meant more as a general comment, not a critique of your situation. Sorry if it came across otherwise. Somebody earlier had a string of formulas across years, that is the method I would use. Good luck.

 

[enisthemenace]

Was meant more as a general comment, not a critique of your situation. Sorry if it came across otherwise. Somebody earlier had a string of formulas across years, that is the method I would use. Good luck.

No worries. I appreciate the help in the thread, including yours. Thanks.

 

[cyfanatic13]

Haven't read anyone else's response yet but doing some quick mental math in my head I'm getting a total of roughly $113,236.76

 

[cyrocksmypants]

Nah, I’m clean. But I’ve read over 50% of adult Americans have it now.