Implied Annual Lease Rates based on Dynamic IRR

[ThatExcelLife]

Hi Team!

I am struggling with an IRR problem.

The situation is that I am trying to find out what the annual lease rates (i.e., annual cash inflows) should be to return a certain IRR.

See the image below. I want to build a building for $50,000,000 (i.e., initial cash outflow). I am going to lease it out for 20 years at XX annual lease rate to get to 6.7% return (i.e., IRR). The way I got to 20 equal payments of $4,585,353 below was through goalseek (I used the IRR function of the 21 cash flows, then simply goalseeked the first lease payment to get to 6.7%).

What I would love to do is simply adjust the 6.7% IRR as an input and BAM! the annual lease rate for x years (I am wanting to flex the term in years as well) will be calculated. Its tricky and from what I can gather in this site is to build a macro to auto-goalseek.

Wondering if there is a simpler way? I am not that handy with macros.

Thanks!

 

[AlanY]

couldn't the PMT() function works for you?

Book1
	A
1	- 50,000,000
2	20
3	0.00%
4	6.70%
5
6	£4,610,167.67
Sheet1

Cell Formulas
Range	Formula
A6	A6	=PMT(A4,A2,A1,0,0)

 

[ThatExcelLife]

So here is the problem, Alan. I was WWWAAAYYY over complicating the problem and I didnt see the simplest of answers right in front of me. This is a little embarrassing. Yes this WORKS! Thank you!

 

[AlanY]

you're welcome. We've all been there!

 

[ThatExcelLife]

I actually have a follow up question! Can you give me your thoughts on it?

Lets say I want to build in some lease escalation. In other words, in year 10 the lease payments will increase 10%: from $1,654,552 to $1,820,007. The formula here is a simple year 9 * 1.1.

To get to the $1,654,552 in year 1 below, I goalseeked to get to my 6.7% IRR. This takes into the consideration the escalation in lease payment in year 10.

The PMT function above (which works great for "even" cash flows), wont be able to handle the escalation. It feels like it would be circular?

Is there a way to have a PMT function that can take into consideration "uneven" cash flows? Or is there another approach (apart from a macro) that could solve this unevenness?

Ultimately, I want to be able to flex the IRR, the term, the year in which the escalation will take place, and the %.

Thanks, Alan!!

The