Reverse IRR with payments escalating at fixed rates

OptimalKR

New Member
Joined
Sep 20, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.

SharedScreenshot.jpg


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
    SS1.jpg
    10.6 KB · Views: 1

Some videos you may like

Excel Facts

Add Bullets to Range
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
Joined
Sep 18, 2013
Messages
3,940
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 20, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Brillant! Thank you enormously. Had the sheet open and it works perfectly!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,358
Messages
5,571,710
Members
412,414
Latest member
KasunC
Top