# Reverse IRR with payments escalating at fixed rates

#### OptimalKR

##### New Member
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!

#### Attachments

• SS1.jpg
10.6 KB · Views: 1

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### StephenCrump

##### MrExcel MVP
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:

AB
1
2Capex7,000,000
3Escalation2%
4Years7
5Target IRR14%
6
7Yr 1 payment1,552,845.74
8
90-7,000,000.00
1011,552,845.74
1121,583,902.65
1231,615,580.71
1341,647,892.32
1451,680,850.17
1561,714,467.17
1671,748,756.51
17
18IRR14.00%
Sheet1
Cell Formulas
RangeFormula
B7B7=(1+esc)*PMT((1+IRR)/(1+esc)-1,N,-B2)
B9B9=-CAPEX
B10B10=Yr1Payment
B11:B16B11=B10*(1+esc)
B18B18=IRR(B9:B16)
Named Ranges
NameRefers ToCells
CAPEX=Sheet1!\$B\$2B9, B7
esc=Sheet1!\$B\$3B7, B11:B16
IRR=Sheet1!\$B\$5B7
N=Sheet1!\$B\$4B7
Yr1Payment=Sheet1!\$B\$7B10

#### OptimalKR

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

Replies
0
Views
395
Replies
3
Views
485
Replies
5
Views
2K
Replies
5
Views
1K
Replies
1
Views
257