Hi everyone,
I am trying to create a dynamic XIRR formula as I am working on a case study that involves a sensitivity analysis to see changes exit years. How could I go about writing an XIRR formula that ignores 0's while still calculating the correct XIRR? Below is a cut out from my file. There are projects with different start dates so I would like to write one formula that could be used for all. Really appreciate any help. Thanks!
<code style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: 14.4px; padding: 2px 4px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px;">Total Year 1 1 1 1 1 1 1 1 2
Analysis Year 0 1 1 1 1 1 1 1 1
Analysis Date 6/1/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19
Analysis Month Month 0 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8
</code>
Purchase ($26,666,667)
Loan Funding $17,333,333
Before Tax Cash Flow $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $124,222
Sale
Loan Payoff
Total Levered Cash Flow $14,232,248 ($9,333,333) $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $124,222
Levered <abbr title="internal rate of return" style="box-sizing: border-box; border-bottom: none transparent; cursor: pointer; text-decoration-line: none; text-decoration-style: initial;">IRR</abbr> 25.07%
Levered Equity Multiple 2.52X
I am trying to create a dynamic XIRR formula as I am working on a case study that involves a sensitivity analysis to see changes exit years. How could I go about writing an XIRR formula that ignores 0's while still calculating the correct XIRR? Below is a cut out from my file. There are projects with different start dates so I would like to write one formula that could be used for all. Really appreciate any help. Thanks!
<code style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: 14.4px; padding: 2px 4px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px;">Total Year 1 1 1 1 1 1 1 1 2
Analysis Year 0 1 1 1 1 1 1 1 1
Analysis Date 6/1/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19
Analysis Month Month 0 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8
</code>
Purchase ($26,666,667)
Loan Funding $17,333,333
Before Tax Cash Flow $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $124,222
Sale
Loan Payoff
Total Levered Cash Flow $14,232,248 ($9,333,333) $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $124,222
Levered <abbr title="internal rate of return" style="box-sizing: border-box; border-bottom: none transparent; cursor: pointer; text-decoration-line: none; text-decoration-style: initial;">IRR</abbr> 25.07%
Levered Equity Multiple 2.52X
