I've had to Goal Seek the value in cell B4 (Daily Hire Rate) to get cell F7 (XIRR) to equal cell B7 (IRR requirement).
Instead, I'm looking for a formula to use in B4 that would automatically set F7 to B7 but can't figure it out (e.g. XNVP or SUMPRODUCT) so any help would be greatly appreciated.
Instead, I'm looking for a formula to use in B4 that would automatically set F7 to B7 but can't figure it out (e.g. XNVP or SUMPRODUCT) so any help would be greatly appreciated.
Book1.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ASSUMPTIONS | |||||||
2 | Investment | $30,000,000 | ||||||
3 | Divestment | $10,000,000 | ||||||
4 | Daily hire rate | $30,239 | ||||||
5 | Hire rate received | Quarterly | ||||||
6 | Investment period years | 6 | IRR | |||||
7 | IRR requirement | 5.00% | 5.000% | |||||
8 | ||||||||
9 | Date | Days | Period | CAPEX | Hire Income | Cash Flow | ||
10 | 28/02/2022 | 0 | -$30,000,000 | -$30,000,000 | ||||
11 | 30/05/2022 | 91 | 1 | $2,751,776 | $2,751,776 | |||
12 | 31/08/2022 | 93 | 2 | $2,812,254 | $2,812,254 | |||
13 | 30/11/2022 | 91 | 3 | $2,751,776 | $2,751,776 | |||
14 | 28/02/2023 | 90 | 4 | $2,721,537 | $2,721,537 | |||
15 | 30/05/2023 | 91 | 5 | $2,751,776 | $2,751,776 | |||
16 | 31/08/2023 | 93 | 6 | $2,812,254 | $2,812,254 | |||
17 | 30/11/2023 | 91 | 7 | $2,751,776 | $2,751,776 | |||
18 | 29/02/2024 | 91 | 8 | $10,000,000 | $2,751,776 | $12,751,776 | ||
IRR |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F7 | F7 | =XIRR(F10:F18,A10:A18) |
D10 | D10 | =-B2 |
F10:F18 | F10 | =SUM(D10:E10) |
E11:E18 | E11 | =B$4*B11 |
B11:B18 | B11 | =A11-A10 |
A14:A17 | A14 | =A10+365 |
A18 | A18 | =A14+366 |
D18 | D18 | =B3 |