Net Present Value of an annuity with dynamic payments

The problem I am attempting to solve is this:
Given a 60 year annuity starting at $75,000 per year (but paid monthly), each 5 years, the payment changes based on what happens to the CPI. How much would an investor pay to receive these 60 years of cash flows? If I'm doing this formula historically, I can plug in the CPI data. But I don't know how to set up the formula for the changing cash flows. Any ideas?
Thanks.

Hi racecannon,

In my former actuarial student life, here's my attempt at your problem... please don't ream me if I'm way off...

I'm attaching my spreadsheet, which I'm hoping you can see it since I don't work on a Mac...

Click here... NPV of annuity with dynamic payments.xls

Please let me know if you can't read it or have any questions.

I'm sure there's a more elegant way of doing this, but I figured I just write up something from basic principals of solving this problem. Any other more elegant solutions, I'd love to see.

Take care,
Dennis

Oops, I forgot to mention what I wrote in the spreadsheet...

On the left side is a simple example to see what's going on. There are 2 formulas out there that we can use.

PV would work, but I think it is only for constant payment.

I think NPV would work better in your case since we can assign the payments instead. (Note: end of period)

Once you are comfortable with the left side of the spreadsheet, the right side shows your question.

The way I did this is to have a lookup table where you can change the CPI to any value for every 5 years.

So, I made the payments change every 5 years (60 months).

Then I used the NPV to get the answer.

Confused yet...

Let me know if you need more clarification.

Thank you Dennis. I've got some work to do to rise to your level.
I'm afraid Occam's Razor took the case on this one. As it turns out, if an investment is pinned to the CPI, there's no need to adjust for inflation on the cash flows because every payment received is equivalent to the first payment. Hence each payment in the NPV calculation is identical. Duh, right?!
Thanks for your help. I'll review the VLOOKUP function too.

Tricky!

You can still use my spreadsheet by just changing all CPIs to 0%.

Glad you were able to figure it out.