Reverse IRR with payments escalating at fixed rates

[OptimalKR]

Hi there,

I can't seem to find an exact solution on the forum, any help would be much appreciated.

This can be done with goal seek, but looking for a formula to solve the value required in B10. I would also like to avoid a VBA solution.

I have four variables,

• Initial investment amount/CAPEX (B2)
• Escallation rate which applies after year one (B3)
• Term/period for inflows (B4)
• Target IRR (B5)

I then have a cashflow, including initial investement and inflows for associated periods (B4) to calcuate the actual IRR. It requires the $ amount for year one to be input, then applies the escallation rate (B3) to the subsequent periods in the cashflow.

The year zero, or negative CAPEX value is in cell B9, with the year one inflow in cell B10.

I would like a formula to calculate what the year one amount should be (B10) to meet the Target IRR. Currently, I am calculating the actual IRR (B6), then have a check cell to see the difference (B7). I am then running goal seek - changing the year one amount so that the difference check value then equals zero (or as close to as possible).

Thanks!

 

[StephenCrump]

Welcome to the Forum!

You're discounting at 14% p.a. and inflating at 2% p.a., so your effective discount rate is 1.14/1.02-1

Hence:

	A	B
1
2	Capex	7,000,000
3	Escalation	2%
4	Years	7
5	Target IRR	14%
6
7	Yr 1 payment	1,552,845.74
8
9	0	-7,000,000.00
10	1	1,552,845.74
11	2	1,583,902.65
12	3	1,615,580.71
13	4	1,647,892.32
14	5	1,680,850.17
15	6	1,714,467.17
16	7	1,748,756.51
17
18	IRR	14.00%
Sheet1

Cell Formulas
Range	Formula
B7	B7	=(1+esc)*PMT((1+IRR)/(1+esc)-1,N,-B2)
B9	B9	=-CAPEX
B10	B10	=Yr1Payment
B11:B16	B11	=B10*(1+esc)
B18	B18	=IRR(B9:B16)

Named Ranges
Name	Refers To	Cells
CAPEX	=Sheet1!$B$2	B9, B7
esc	=Sheet1!$B$3	B7, B11:B16
IRR	=Sheet1!$B$5	B7
N	=Sheet1!$B$4	B7
Yr1Payment	=Sheet1!$B$7	B10

 

[OptimalKR]

Brillant! Thank you enormously. Had the sheet open and it works perfectly!