RATE function for non annuity cash flows

[Answer_the_question]

Thank you in advance for your time/help!

I have a series of cash flows that take place over the next 40 years (in different amounts and not an annuity) for which I am trying to determine what discount rates will output specific NPVs ($150, $200, $250, and $300). Currently I just run a bunch of goal seeks to determine what interest rates apply, but this method is a bit of a pain and prone to human error. I know the RATE function does something similar to what I'd like but it only applies to annuities and does not apply in my case.

Is there another function or a data table method to achieve what i am looking for?

Thanks!

 

[Answer_the_question]

Hello kind stranger

You have gone way above and beyond in your answer above and I really appreciate your help. Unfortunately all of the document sharing sites are blocked here.

I dont think I could really share this with you without either one of us posting personal information online and I dont want to put you in that position or put myself.

I'll let you know if i come up with a better solution

Thanks again!

 

[joeu2004]

Errata....

Response Part #1
[....]

H9:  {=SUM(IF(COLUMN($C$3:$P$3)=COLUMN($C$3),-H7,$C$5:$P$5)/(1+H8)^(($C$3:$P$3-$C$3)/365))}
[....]
M10: {=XNPV(M8,IF(COLUMN($C$3:$P$3)=COLUMN($C$3),-M7,$C$5:$P$5),$C$3:$P$3)}

Because we have zero in C5, the formulas can be simplified:

H9:  =SUMPRODUCT($C$5:$P$5/(1+H8)^(($C$3:$P$3-$C$3)/365))
M10: =XNPV(M8,$C$5:$P$5,$C$3:$P$3)

Note that both formulas are normally-entered (just press Enter, as usual).

-----

Response Part #2
[....]

D13: {=SUM(IF(COLUMN($C$3:$P$3)=$C$3,-B8,C12:P12) / (1+$B$7)^(($C$3:$P$3-$C$3)/365))}

That should be ...=COLUMN($C$3). However, if we put 0 into C12 (and because the NPV discount rate is always positive), the formula can be simply:

D13: =XNPV(B7,C12:P12,C3:P3)

Note that the formula is normally-entered (just press Enter, as usual).